Working Microsoft Graph API Solution

[scsh4]

Working solution to come.

[Qiu]

@scsh4 

Maybe you have done it as well, there are only a few threads here about the this API.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Interactive-Chart-Integrated-with-Micr...

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Exchange-server-connector/td-p/57199

[jrobiso2]

Primary issue for me is that MS graph can only pull 999 records at a time. JSON metadata returns a "odata.nextLevel" entry for the link to get the next 999.

Putting this into an Iterative Macro seems the solution. HOWEVER, during the iterations, the Download tool will fail (http returns invalid url) randomly.

I'm trying to pull 800,000 records, so this is a must have. And it's driving me nuts.

[PaulCollis]

Hey, I'm a little over my head potentially here but have you managed to get Alteryx to gain a token to then use in the API?  I've manually inputted a token using graph explorer to generate it and it works a treat but I'm really struggling to config the flow to self-generate the token?

[jrobiso2]

This should help.  The Text Input has spots for TenantID, ClientID, and ClientSecret. (Don't include the < > symbols).  The output is your authorization token.
From here, you would pass this into a Download or an Iterative Macro with Download.  In the Download tool, Headers section, "Include" the Authorization field by checking its box.

An example input URL to feed the URL field of Download tool might be something like https://api-eu.securitycenter.windows.com/api/machines (Although for that you'd need an iterative macro due to exceeding the return count of a single call.) You can even include Filters, like "https://graph.microsoft.com/v1.0/devices?$count=true&$top=999&$filter=(startswith(operatingSystem,'W...') or startswith(operatingSystem,'Mac')) and (approximateLastSignInDateTime ge 2023-05-30T12:00:00Z)" - but beware - Download does good URL encoding, but the data.NextToken output will already be url encoded, making the Iterative Macro even more difficult. Do NOT try to use the formula UrlEncode or Decode, as they are straight ASCII and won't do it right.

[jrobiso2]

For large volume Iterative type data pulls from MS Graph, I ended up making a C#-created exe.  C# can do the iteration without burping bad url's after a few dozen iterations.

[jrobiso2]

Any fresh information on the failure of Iterative Macros on large iteration count https requests to an API?

[Hiblet]

I am working on a generalised Graph API macro suite for Alteryx. I have a token getter, and I can make API calls, and can handle iteration, paging and adaptive throttling that the Graph API uses. The Graph API applies to all MS products, so this could be a very powerful utility, allowing automation within and across Microsoft 365 apps.  Also, it will work with Scheduler and Alteryx Server.

We are investigating use cases, and it looks like you have a use case right here.  Continuum Jersey might be able to make a solution for you if you are interested.

[jrobiso2]

Have you solved the issue of Iterative Macros making thousands of https API calls getting "burps", or brain farts, and not being able to complete the iterations - making Alteryx essentially useless? (graph often has a row limit of 999, and works better with $top=500, but still burps).

Perfect example is trying to pull all device info from Azure via "https://graph.microsoft.com/v1.0/devices?" - we have roughly 800K machines in there (yes, many are out-dated, but even if I put a $filter for "(approximateLastSignInDateTime ge 2024-03-05T12:00:00Z)" it's still about 295K, and the Iterative Macro is 100% assured of a bad burp fairly soon into the iterative run.

Yes, I've added BLOCK with an R-scripted WAIT into the macro. Doesn't seem to help much, if at all.

P.S. Are you here to help the community, or sell your company services?

[Hiblet]

Hi @jrobiso2 , Apologies, I am here to help the community with questions, but in this particular instance, the solution is not just "one answer", it is a nested set of macros, and quite a lot of work, many hours of development, and not something that can be given away.  

I can tell that I have angered you and that you are not interested, so I wish you every success in finding a solution that works for you.  No offence meant, and good luck.

Steve