Today is another part of the Microsoft Intune monitoring series, in which I show you how we can monitora part of our Intune environment. I don’t know what your Intune (lab) tenant looks like, but at least my lab tenants become a bit messy (ok one not anymore because everything got removed because of an f#ck up :D). But I had lots and lots of configuration profiles, compliance policies, scripts etc, but also a lot of these things were not assigned anymore and seem to not be used anymore. So these could be cleaned up, to keep a bit of an overview of my active stuff.
I thought it would be nice to receive an overview once in a while in my mailbox with an overview of all things in my tenant which is not assigned, so I can review this and remove the old unused stuff. There is no built-in option to receive some kind of report, so I needed to find a solution myself.
I started digging around in Microsoft Graph to query all configuration items, scripts etc and I soon realized two things:
- The report would become too big to also query all apps in the tenant with the same flow
- Microsoft really made a mess of all the configuration types in Microsoft Graph
Why it’s a mess? Older configuration profiles can be found via Graph as deviceConfigurations (including Windows Update rings), others are found as configurationPolicies (Settings Catalog, Endpoint Security for example). But we also have intents, which also hold a few Endpoint Security profiles, like AD and firewall.
Ok, no problem that this is a little messy, as long as all data is available and we can determine if a configuration is assigned or not, we can pull the data with a Logic Apps flow and further process it to a report.
This blog post is part of the MEM (Intune) Monitoring series. An article with a short explanation of every MEM Monitoring flow I shared and links to the related articles can be found here.
The solution in short
All the configurations are available via Microsoft Graph. We can query all these configurations using an Azure Logic Apps flow. which can run on an occurrence. For every pulled configuration we determine if the configuration is assigned or not. If the configuration is not assigned, we store it in an Excel sheet which is stored on a SharePoint site. The sheet is filled with information regarding the unassigned configurations and when all data is processed, the sheet is sent via email to your mailbox.
The solution can be easily deployed with Bicep files, which can be found on my GitHub repo.
Requirements
For this solution, we have some requirements. To query Microsoft Graph we need to have permission to perform this job. There are different options to authenticate to MS Graph and assign permissions, but I prefer an Azure Managed Identity.
The required Graph (Application) permissions we need are:
DeviceManagementConfiguration.Read.All
DeviceManagementRBAC.Read.All
DeviceManagementServiceConfig.Read.All
DeviceManagementApps.Read.All
To create an Excel sheet before we can send it via email, we (temporarily) need to save it to a SharePoint documents library. So a requirement is a document library and a (service) account with read-write permissions to the library.
And as last, we need to have a mailbox. I used a service account with permissions to send an email from a shared mailbox.
Setup the first part of the Logic App flow
When the requirements are in-place, we can start creating the Logic Apps flow.
Sign in to the Azure portal and open the Logic App service. I created a blank Logic App of type Consumption.
When the flow is created, click on the name of the flow at the top of the screen, open the Identity section, and on the tab User assigned add your Managed Identity.
Open the Overview tab, which shows a few templates, and choose Recurrence.
Change the interval settings to your needs.
First, we need to compose an Excel file, otherwise, we end up with an empty sheet.
Thanks, Rene Laas, for explaining to me how I can create an Excel sheet in a flow.
We do this by using a Compose action, which is found under the Data Operations actions.
This is the input from an empty Excel sheet, you can just copy-paste this into the inputs field. Otherwise, you need to create an empty Excel sheet and import it once in a flow to retrieve this information.
{
"$content": "UEsDBBQABgAIAAAAIQAgWYpOfQEAABEGAAATAAgCW0NvbnRlbnRfVHlwZXNdLnhtbCCiBAIooAACAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAC0lE1PwkAQhu8m/odmr6ZdwMQYQ+Hgx1E5YOJ1bad0w35lZ0H490634MFgEYuXbtrtvO+z8047nm60StbgUVqTs2E2YAmYwpbSLHL2On9Kb1mCQZhSKGsgZ1tANp1cXoznWweYULXBnNUhuDvOsahBC8ysA0M7lfVaBLr1C+5EsRQL4KPB4IYX1gQwIQ2NBpuMH6ASKxWSxw09bkk8KGTJffti45Uz4ZyShQhEytem/OaS7hwyqozvYC0dXhEG4wcdmp2fDXZ1L9QaL0tIZsKHZ6EJg28U/7B++W7tMusWOUBpq0oWUNpipakDGToPosQaIGiVxTXTQpo99yF/Kp5565Da6OF0gn2fmurUkRD4IOGrU52OFEHvI0MTcgnlqd7FCoPVve1bmV+a76KOuSCPy/DMmTejFIW7IieOQJ8WtNf+CFHsiCGGrQI894RH0S7nNp83rbgMoOOg9z/vl2ijd3zeDzCMzjR4dLA/M1z/JwOPP/TJJwAAAP//AwBQSwMEFAAGAAgAAAAhABNevmUCAQAA3wIAAAsACAJfcmVscy8ucmVscyCiBAIooAACAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACskk1LAzEQhu+C/yHMvTvbKiLSbC9F6E1k/QExmf1gN5mQpLr990ZBdKG2Hnqcr3eeeZn1ZrKjeKMQe3YSlkUJgpxm07tWwkv9uLgHEZNyRo3sSMKBImyq66v1M40q5aHY9T6KrOKihC4l/4AYdUdWxYI9uVxpOFiVchha9EoPqiVcleUdht8aUM00xc5ICDtzA6I++Lz5vDY3Ta9py3pvyaUjK5CmRM6QWfiQ2ULq8zWiVqGlJMGwfsrpiMr7ImMDHida/Z/o72vRUlJGJYWaA53m+ew4BbS8pEVzE3/cmUZ85zC8Mg+nWG4vyaL3MbE9Y85XzzcSzt6y+gAAAP//AwBQSwMEFAAGAAgAAAAhAM0fwcjkAgAAVwcAAA8AAAB4bC93b3JrYm9vay54bWysVFFvmzAQfp+0/4B4p9gECEFNphBAi9RUVZu1j5ULJlgBjIzTpJr633eGkDTLNEXdUGLj8/H5++7Od/1tVxbaKxUN49VYx1dI12iV8JRVq7H+Yxkbnq41klQpKXhFx/obbfRvk69frrdcrF84X2sAUDVjPZey9k2zSXJakuaK17SCnYyLkkhYipXZ1IKStMkplWVhWgi5ZklYpXcIvrgEg2cZS2jIk01JK9mBCFoQCfSbnNVNj1Yml8CVRKw3tZHwsgaIF1Yw+daC6lqZ+PNVxQV5KUD2DjvaTsDPhT9GMFj9SbB1dlTJEsEbnskrgDY70mf6MTIxPgnB7jwGlyHZpqCvTOXwwEq4n2TlHrDcIxhG/4yGobTaWvEheJ9Ecw7cLH1ynbGCPnalq5G6viWlylShawVpZJQySdOxPoQl39KjAVSJTR1sWAG7luPgkW5ODuV8J2CxE34fzzspNHifhzeA+UBe4QTQke4LcA4QGD8PXW/koQCwEJo5oTW0Zl7kzYbudGBFw6kXWs7As8MBBEC4fsLJRuZ74gp2rNvA8mxrQXb9Dkb+hqVHCj/R/jHU/NvQ770rXeqKPjK6bY4S1VLbPbEq5dtWzduH921rfmKpzCE6luWC2s72nbJVDlyx5dqOomspTmP9hEvYcYnhMdRwwsX8QKZtA0CqnbWqTd2Dag0Y+o2a29BCqnx1hpinWKkx+88SUiR3QlNT6zjCyPKUB93Jm0a2s7YRDOhhG02HaGQbKBo4hu2NLMOzB5Yxs0MrcoZRGAXO+/+8zFA/2PH7/qhY5kTIpSDJGrrqPc0C0kAZdYKA70eygeMFaAAU7RjHho1HyAgC1zacMB44QxzOIic+klXys09eJc9sv6ZEbgT0dCDdrn01xnvrwZh1hn2eTnqSfx+quO+//pvjA6gv6IXO8eOFjrPbxXJxoe9NtHx+ii91ni6CcLr3N/8YHbPNnhrbmjP7nE9+AQAA//8DAFBLAwQUAAYACAAAACEAiYjjDwYBAADXAwAAGgAIAXhsL19yZWxzL3dvcmtib29rLnhtbC5yZWxzIKIEASigAAEAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAvJNPa8MwDMXvg30H4/uiJN3KKHV6GYNeRwa7Gkf5Q2M7WOq2fPuZDtIVSnYJvRgk4fd+fsLb3bftxScG6rxTMktSKdAZX3WuUfK9fH14loJYu0r33qGSI5LcFfd32zfsNcdL1HYDiajiSMmWedgAkGnRakr8gC5Oah+s5liGBgZtDrpByNN0DeGvhiwuNMW+UjLsq5UU5ThE5/+1fV13Bl+8OVp0fMUCiMc+PkCUOjTISv7WSWSUcN0+X9KeYyx4dj+VcDqzOYZsSYYvHw7UIvKZY2oRnCazMOslYcyR2NuPGP+0kiSBqQsdo13NRfN0a5p8jubx1jTTpuDiOxY/AAAA//8DAFBLAwQUAAYACAAAACEA6eHjsNoBAACNAwAAGAAAAHhsL3dvcmtzaGVldHMvc2hlZXQxLnhtbAAAAP//AAAA//+ckktrwzAMgO+D/Qfje+s4bccakpbBKOtt7HV3HCUx9SPYTh+M/fc56doOcik1fsrWJ8lSutwribZgnTA6w3QcYQSam0LoKsOfH6vRI0bOM10waTRk+AAOLxf3d+nO2I2rATwKBO0yXHvfJIQ4XoNibmwa0OGmNFYxH462Iq6xwIpeSUkSR9EDUUxofCQk9hqGKUvB4dnwVoH2R4gFyXzw39WicSea4tfgFLObthlxo5qAyIUU/tBDMVI8WVfaWJbLEPeeThlHext6HMbkZKaXDywpwa1xpvTjQCZHn4fhz8mcMH4mDeO/CkOnxMJWdAm8oOLbXKKzMyu+wCY3wh7OsO67bNKKIsPf0V8bhZV2U3SZTnc/eJEWImS4iwpZKDP8RDFZpH3xfAnYuX975Fn+DhK4h2CAYtTVZm7Mpnu4DqKoUyUD3VVfm68WFVCyVvo3s3sBUdU+QGZB5RcAAP//AAAA//+yKc5ITS1xSSxJ1LcDAAAA//8AAAD//7IpSExP9U0sSs/MK1bISU0rsVUy0DNXUijKTM+AsUvyC8CipkoKSfklJfm5MF5GamJKahGIZ6ykkJafXwLj6NvZ6JfnF2UXZ6SmltgBAAAA//8DAFBLAwQUAAYACAAAACEAdT6ZaZMGAACMGgAAEwAAAHhsL3RoZW1lL3RoZW1lMS54bWzsWVuL20YUfi/0Pwi9O75Jsr3EG2zZTtrsJiHrpORxbI+tyY40RjPejQmBkjz1pVBIS18KfetDKQ000NCX/piFhDb9ET0zkq2Z9Tiby6a0JWtYpNF3znxzztE3F128dC+mzhFOOWFJ261eqLgOTsZsQpJZ2701HJSarsMFSiaIsgS33SXm7qXdjz+6iHZEhGPsgH3Cd1DbjYSY75TLfAzNiF9gc5zAsylLYyTgNp2VJyk6Br8xLdcqlaAcI5K4ToJicHt9OiVj7AylS3d35bxP4TYRXDaMaXogXWPDQmEnh1WJ4Ese0tQ5QrTtQj8TdjzE94TrUMQFPGi7FfXnlncvltFObkTFFlvNbqD+crvcYHJYU32ms9G6U8/zvaCz9q8AVGzi+o1+0A/W/hQAjccw0oyL7tPvtro9P8dqoOzS4rvX6NWrBl7zX9/g3PHlz8ArUObf28APBiFE0cArUIb3LTFp1ELPwCtQhg828I1Kp+c1DLwCRZQkhxvoih/Uw9Vo15Apo1es8JbvDRq13HmBgmpYV5fsYsoSsa3WYnSXpQMASCBFgiSOWM7xFI2hikNEySglzh6ZRVB4c5QwDs2VWmVQqcN/+fPUlYoI2sFIs5a8gAnfaJJ8HD5OyVy03U/Bq6tBnj97dvLw6cnDX08ePTp5+HPet3Jl2F1ByUy3e/nDV39997nz5y/fv3z8ddb1aTzX8S9++uLFb7+/yj2MuAjF82+evHj65Pm3X/7x42OL906KRjp8SGLMnWv42LnJYhighT8epW9mMYwQMSxQBL4trvsiMoDXlojacF1shvB2CipjA15e3DW4HkTpQhBLz1ej2ADuM0a7LLUG4KrsS4vwcJHM7J2nCx13E6EjW98hSowE9xdzkFdicxlG2KB5g6JEoBlOsHDkM3aIsWV0dwgx4rpPxinjbCqcO8TpImINyZCMjEIqjK6QGPKytBGEVBux2b/tdBm1jbqHj0wkvBaIWsgPMTXCeBktBIptLocopnrA95CIbCQPlulYx/W5gEzPMGVOf4I5t9lcT2G8WtKvgsLY075Pl7GJTAU5tPncQ4zpyB47DCMUz62cSRLp2E/4IZQocm4wYYPvM/MNkfeQB5RsTfdtgo10ny0Et0BcdUpFgcgni9SSy8uYme/jkk4RVioD2m9IekySM/X9lLL7/4yy2zX6HDTd7vhd1LyTEus7deWUhm/D/QeVu4cWyQ0ML8vmzPVBuD8It/u/F+5t7/L5y3Wh0CDexVpdrdzjrQv3KaH0QCwp3uNq7c5hXpoMoFFtKtTOcr2Rm0dwmW8TDNwsRcrGSZn4jIjoIEJzWOBX1TZ0xnPXM+7MGYd1v2pWG2J8yrfaPSzifTbJ9qvVqtybZuLBkSjaK/66HfYaIkMHjWIPtnavdrUztVdeEZC2b0JC68wkUbeQaKwaIQuvIqFGdi4sWhYWTel+lapVFtehAGrrrMDCyYHlVtv1vewcALZUiOKJzFN2JLDKrkzOuWZ6WzCpXgGwilhVQJHpluS6dXhydFmpvUamDRJauZkktDKM0ATn1akfnJxnrltFSg16MhSrt6Gg0Wi+j1xLETmlDTTRlYImznHbDeo+nI2N0bztTmHfD5fxHGqHywUvojM4PBuLNHvh30ZZ5ikXPcSjLOBKdDI1iInAqUNJ3Hbl8NfVQBOlIYpbtQaC8K8l1wJZ+beRg6SbScbTKR4LPe1ai4x0dgsKn2mF9akyf3uwtGQLSPdBNDl2RnSR3kRQYn6jKgM4IRyOf6pZNCcEzjPXQlbU36mJKZdd/UBR1VDWjug8QvmMoot5Blciuqaj7tYx0O7yMUNAN0M4mskJ9p1n3bOnahk5TTSLOdNQFTlr2sX0/U3yGqtiEjVYZdKttg280LrWSuugUK2zxBmz7mtMCBq1ojODmmS8KcNSs/NWk9o5Lgi0SARb4raeI6yReNuZH+xOV62cIFbrSlX46sOH/m2Cje6CePTgFHhBBVephC8PKYJFX3aOnMkGvCL3RL5GhCtnkZK2e7/id7yw5oelStPvl7y6Vyk1/U691PH9erXvVyu9bu0BTCwiiqt+9tFlAAdRdJl/elHtG59f4tVZ24Uxi8tMfV4pK+Lq80u1tv3zi0NAdO4HtUGr3uoGpVa9Myh5vW6z1AqDbqkXhI3eoBf6zdbggescKbDXqYde0G+WgmoYlrygIuk3W6WGV6t1vEan2fc6D/JlDIw8k488FhBexWv3bwAAAP//AwBQSwMEFAAGAAgAAAAhAAiuNHKXAgAAPQYAAA0AAAB4bC9zdHlsZXMueG1spFTfa9swEH4f7H8Qendlu7GXBNtlaWoodGOQDvaq2HIiph9BUoKzsf99J9tJHDq20b5Yp/Ppu+/uOym7a6VAB2Ys1yrH0U2IEVOVrrna5PjrcxlMMbKOqpoKrViOj8ziu+L9u8y6o2CrLWMOAYSyOd46t5sTYqstk9Te6B1T8KfRRlIHW7MhdmcYra0/JAWJwzAlknKFe4S5rP4HRFLzfb8LKi131PE1F9wdOyyMZDV/3Cht6FoA1Taa0Aq1UWpi1JpTks77Io/kldFWN+4GcIluGl6xl3RnZEZodUEC5NchRQkJ46vaW/NKpAkx7MC9fLjIGq2cRZXeKwdiDo4isz/QgQrwRJgUWaWFNsiBStCkzqOoZH3EPRV8bbgPa6jk4ti7Y+/ohB3iJIc2eyfxKYfFwiEuxJlA7AmAo8hAKceMKmGDBvv5uIP0Coaqh+ni/hG9MfQYxcnoAOkSFtlamxqGeFx67yoywRoHRA3fbP3q9A6+a+0cCF1kNacbrajwpZxODAaUUzEhVn7QvzVX2G2D1F6W0j3WOYYr45twMqGQwezx+o3HH6P12G+GRW1zjQ+II9pXpM/pkdc7x5/9zRQwzQMEWu+5cFz9gTBg1u2lBaFXwPlb1jXnnAU6UbOG7oV7Pv/M8cX+xGq+l/E56gs/aNdB5PhiP3mlotTnYK17sjBesKK94Tn++bD4MFs+lHEwDRfTYHLLkmCWLJZBMrlfLJflLIzD+1+ju/6Gm949TUUGD8bcCngPzFDsUOLq4svxaNPT72YUaI+5z+I0/JhEYVDehlEwSek0mKa3SVAmUbxMJ4uHpExG3JNXvgghiaL+bfHkk7njkgmuTlqdFBp7QSTY/qUIclKCXN794jcAAAD//wMAUEsDBBQABgAIAAAAIQCxTH/+pQEAAC8DAAARAAgBZG9jUHJvcHMvY29yZS54bWwgogQBKKAAAQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAB8kk1P4zAQhu8r8R8i31MnKZQqaoN2Qb3sIiFRBOLmtYdiGn/InhLy79dx0rRF1Uo+eOYdPx6/nsXNl6qTT3BeGr0k+SQjCWhuhNSbJXlar9I5STwyLVhtNCxJC57cVBc/FtyW3Dh4cMaCQwk+CSTtS26X5B3RlpR6/g6K+Umo0EF8M04xDKHbUMv4lm2AFlk2owqQCYaMdsDUjkQyIAUfkXbn6ggQnEINCjR6mk9yeqhFcMqfPRCVo0olsbXhTUO7x2zBe3Gs/vJyLGyaZtJMYxuh/5y+3P95jE9Npe684kCqheAlSqyhWtDDNuz87u8HcOzTYxAE7oChcdUDhD6T3zWzjfzYxtN7qTN9C21jnPABcBIFggDPnbQYvrLHnyRCdc083oe/fZMgfrXDTT+FkjrCvsndbQ4+ZTca/W2HSPBoZt80iCTYU/Zm7pXn6e3dekWqIsuv0myWZlfrfF7m12WRvXaPOjnf2dUn1NDef4lFkWbXaXG5zmflNKxj4h5QxQllCBvj2r59PkZxeDWG6XlEhrvBTG7OpI5HvPoHAAD//wMAUEsDBBQABgAIAAAAIQAfm12L7wAAAIoBAAAQAAgBZG9jUHJvcHMvYXBwLnhtbCCiBAEooAABAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAJyQTU/DMAyG70j8hyj3NRlIE5rSTONLXCY4jN2j1O0iWjtKwtT+ewwTBXHkZvu1H7+22YxDL06QciCs5bLSUgB6agJ2tXzdPy5upMjFYeN6QqjlBFlu7OWFeUkUIZUAWTACcy2PpcS1UtkfYXC5YhlZaSkNrnCaOkVtGzzck38fAIu60nqlYCyADTSLOAPlmbg+lf9CG/Kf/vJhP0U2bM02xj54V/hKuws+Uaa2iIfRQy+esQ8IRv3uMTuHroNkjZqjOxqiw4lLc/TE+MTTb7cuAwt/ciYezq+1y1Wlr7X+2vJdM+rnifYDAAD//wMAUEsDBBQABgAIAAAAIQCjZsZsDwEAAJIBAAATAAgBZG9jUHJvcHMvY3VzdG9tLnhtbCCiBAEooAABAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAJzQyW6DMBAG4HulvoPlO/HgQFgERKxSbz2kvSNjEiRsI+zQoKrvXqMuuec4mplP/0xyvIkRLXzWg5IpdneAEZdMdYM8p/jt1DghRtq0smtHJXmKV67xMXt+Sl5nNfHZDFwjS0id4osxU0yIZhcuWr2zbWk7vZpFa2w5n4nq+4HxSrGr4NIQCnAg7KqNEs70z+EfL17Mo2Sn2JZOv5/WycbNkl98Rb0wQ5fiz8ovq8oH36F1VDouuIUT7aPAgRCAFrRsorz+wmjahilGshX29FJJY2Nv6Etn1cXE4/ShzZzBDawBEPhN4ULY+IF/8EJvH4JXH/K8rmhRUgiahNx3EvKXKkvI/ZnZNwAAAP//AwBQSwMEFAAGAAgAAAAhAH+LQ8PAAAAAIgEAABMAKABjdXN0b21YbWwvaXRlbTEueG1sIKIkACigIAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAIzPP2vDQAyH4a9ibs/JaaAtxnaGrgkUunQVZ519kJOOk1Ln47cu/Td20/I+P9Qfb/nSvFHVJDy4vW9dQxxkSjwP7mpx9+iOY1+6UqVQtUTafBSsXRncYlY6AA0LZVSfU6iiEs0HySAxpkBw17b3kMlwQkP4VdwXc9P0A63r6teDlzpv2R5ez6eXT3uXWA050HdVwv/WE0cpaMvmPcAzVmOqT8JW5aJu7CcJ10xsZ2Scabtg7OHvt+M7AAAA//8DAFBLAwQUAAYACAAAACEARURas7gAAADJAAAAGAAoAGN1c3RvbVhtbC9pdGVtUHJvcHMxLnhtbCCiJAAooCAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAMjcGKAjEQRO+C/xD6HpNo1FGM4m5G8L6C15Dp0YFJt0zisrDsv29ORdWDeofTTxrFN055YHJgFhoEUuRuoIeD29dFNiByCdSFkQkdEMPpOJ8durzvQgm58ITXgknUYah59Q5+t5vWr9utlfpjZaS11sim9We5W6690Z+Xxlj9B6Kqqd5kB89SXnulcnxiCnnBL6QKe55SKLVOD8V9P0T0HN8Jqail1hsV31Wf7mkEdfwHAAD//wMAUEsDBBQABgAIAAAAIQC9hGIjkAAAANsAAAATACgAY3VzdG9tWG1sL2l0ZW0yLnhtbCCiJAAooCAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABsjjsOwjAQBa+C0pMt6NDiNIEKUeUCxjiKpazX8i4f3x4HQYGUep5mHnYkvHUc1UcdSvKdwRNnGjyl2aqXzYvmKIdmUk17AHGTJystBZdZeNTWMYFMNvvEISo8dvC1abXBWF3SGOyDVF8xPbs71dQ5XLPNZUkh/CAeb0HXJx+CF/9cxwtA+Dtu3gAAAP//AwBQSwMEFAAGAAgAAAAhAFoCIWi4AAAAyQAAABgAKABjdXN0b21YbWwvaXRlbVByb3BzMi54bWwgoiQAKKAgAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAADI3BigIxEETvwv5D6HtMMro6ilGio+B9hb2GTI8OTLplEkUQ/92ciqoH9Ta7VxzEE8fUM1kwUw0CKXDb09XC5e8kaxApe2r9wIQWiGG3/Zls2rRuffYp84jnjFGUoS95biy83Uy72fLo5GlvnJxXy5V02vzK/UqbRXM8HOrKfEAUNZWbZOGW832tVAo3jD5N+Y5UYMdj9LnU8aq46/qADYdHRMqq0nqhwqPo438cQG2/AAAA//8DAFBLAwQUAAYACAAAACEA+FUjoZUHAACAJQAAEwAoAGN1c3RvbVhtbC9pdGVtMy54bWwgoiQAKKAgAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA7Fpbb5tIFH5fqf8Bsc82+I6tOlUSN1Kkpq022ctbNQyDzRYDnRkS+9/vmRnuGIxJug+rTR5iA+fMuX7nQt5/OOx97ZlQ5oXBWh8NTV0jAQ4dL9iu9Zi7A0v/cPUe8xUOA04C/nSMyCPekT3S4OK3ta5re5T9LTz0Ge3JWt+EON4DmXyqcPd+s9bNgzmCX3Mxu7sZmdbdbDGbT63pxDKnH+fX1x8345vbsbm4q9L+kUo7rd7ZEIapF3Gpyy0liBMNaQF50ZxEjmGV5BGHEcgpLydmELJNl/MZcZE7cZeWOZrN8HIxtRxnKf6aBCFdA7sFbIX5Wt9xHq0Mg0mrsOHewzRkocuHONwboet6mBhj05wbe8KRgzgyCoZIGe1RH0YRBekp9wiTzK85p54dc8L0q3e/vD8wZ6Wk0jiiW8KFT1iEMCh8udD5WdJYNAxBd05jIr+6HvEdJkw3t8eWPR9NFq41mlpL17Xt8XJqL8Y2XsCHma4FbKwiJmAT9UEZE+TNBHt5eRm+TIYh3QrbjYy/Hj6psEsNdmDdn41eq6+SD+Re67Px0nat6WJAxsQeTF1iDZbYQQNrOjft5cSZjedZdIB+ax0Cx57PbWdgWe5kMJ2a04HlIHOA5yZyx/ZsbFtm5i5vH4WUa0HuqE7nGam76/Sdjs/oiU9EukoB1nrB5ekBENORTw4CBrIQIz9iwIzse5lHmnkPKEBbyTxT9gQv5Psp25QNJe5aFyHzuEOUOH96fPc7AwiAsPOCLxjHFCLB1GsqnKDbQAJ6flfKyeqBOB56JPQZUvghSd6Ox5aJ7xDjbQwMkaqJ7vJzxTTyWmIQYR/5nRXs3p1IotQ5dOgW5IlAdyHdbwAsYx8A4UeMfA/AwMnz9CcltbPPEeA89tbD0OAQwxALKrcj3A0jvMANI8R3ApUWxldEeUDoLdRGGkJgNadhZ7xtFLQlx7swbxe8AQDqKYdWXuCQw1q3AMc930e2D9UkKwOOxyIfHVXxV9SayFhZIzzoIGiA/OLdYjqjFZRs50vgHxOOLaCTJIiwPOBVhhkH+CZaGc1GDOSCGFkJsHiA2PTymtgKWOL5e/ByJdGhzTmkaBMHdhiDGZw2KCump0jvMixuckPpmojEtS7LNRTwYHsOY0R5WOvXGIMU/B5STTFItRVXSthY9VSDtxOGAtu7yfQKGKpiF0k9l8Fb3cNdQK5u6TyGc/jPonh5URRrZQ6no7n4TEM8MyjTPinWUEoYeB6LzrUQup9DTrIQg/j7RIIt32nPyI/B/+PZTFU9aZcCh7xClM45XUQ61YNOTYT2fz34r9eDhoYoS6cRjI/nqkIjj53nOASG0KyYlOtFI91bJJlERAVnrTlTh5eiWJU2LzfL6DKz1PhcYJoa7b9pHmXBTpBS6Vlgjg2gE3Ohl0ScybkPBtXvMDLUZmdKBsVZ9AzqoHQmLrWpcVBoVG0/xN+zDvZX6MOTrrDWEvaXsteE650fcQdewDiC6SttZJ28kY1i6ktLOthIrMSM0XBk5M9CCSu00UUCeSd7MoS29EyDnGarEdp5X1SfRxO/t8immvlPIUZqkZNQOLHte7CaokTqlLT8BhiWGT9AQ2jIJ4Y5Ncyx4eAhJGo+EnaSIlX4LY6XvMoylNs/ocbXbHmTdlu3T98qN7JOrTAQJsN5/eG0C24aoh28wmIpFtKW3nbUMEk7WCqlOIjxrtxhFrrjZg4rDwCei/HwQglUw1tam7Ucf6qfzrAYKhQsLEtzSjJEaLLxPb1HANNxj/ti11VcOhSHAsB4YJxMR2o1WTrlSdI3smex/TfBYlHafEAjsZMvPi9joAz7nRxfQupUFyMV7WpTSqM4Pgq2MUB3H1kg68k2pMd2S5+XRWmWrHTfhhklz54YLi/klqVlEIRcYlp6JZ3104taw8/TzmOq69cgvjxhIqbxHdGCeG8TqoWuxtAzXAuplgrJhtoTPIGiyBcEYrAAJjAlRCFMyDC1a1BrtTiChTRMnMAtOwK5kOkaQXiXMRu+++WUaGoPVdVCXUUlbauzR72P8sWKDN47iN3Rzau9L5d/KWTtE7a9wlEtGB7BcXGv/Oi02lM1R4XFq1b27VuecxNah17pLbdlyeqt0yY/sU25in6FNTCkY3Kvtk5elVcw2QZGBkeEV6UdTAl2r0QMp89Xjk6pC+uXvrRq09JOLQSpbHrhXVxpCd8sqwLBk7umlVg4wKqpTdWGPZOQ6ELi8k6pO/nN5vaasRB7AHnOR+gd+LG3u4FXwqG5xhY2e9UQqDoCrJDlCwAlvKSAkFIHZO/ZEutmzyWh1EbXmeTxyDjZ3yftvziyM2lqUigKTXRC27NhluuhIqWqfdaIlR3ebI4qm3wNeRmHE7bpKUvVVB3ZlHEqj7y+UKUs+zrAUjzSgPmNuISK13aVqtgF+hJOjmh5i93qJbTjV9BOXkErOvO+MsO7658O1ic8nTa73WGz0dW9WQlf9ycGZ/cnBm/3JwZ39ycGf19O/CRWKr2LlKDu/vbpXI3qhOJlrMoE6AtVgoGsKpfnmTy7ulqogcobdES5jP38m79za+mFpJzZLtQ49T9UV/8AAAD//wMAUEsDBBQABgAIAAAAIQAozmaDtgAAAMkAAAAYACgAY3VzdG9tWG1sL2l0ZW1Qcm9wczMueG1sIKIkACigIAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAyNTQvCMBBE74L/Iew9pn61UYxSSQXvCl5DutVCsytNFEH87+Y0zDyYtzt8wiDeOMaeycB8VoBA8tz2dDdwvZykBhGTo9YNTGiAGA776WTXxm3rkouJRzwnDCIPfc6zNfC15Vovy2YuC31s5KrSWtbHupbLqrRVbXVz2jQ/EFlN+SYaeKT03CoV/QODizN+ImXY8RhcynW8K+663qNl/wpISS2KolT+lfXhFgZQ+z8AAAD//wMAUEsDBBQABgAIAAAAIQB0Pzl6wgAAACgBAAAeAAgBY3VzdG9tWG1sL19yZWxzL2l0ZW0xLnhtbC5yZWxzIKIEASigAAEAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAhM/BigIxDAbgu+A7lNydzngQkel4WRa8ibjgtXQyM8VpU5oo+vYWTyss7DEJ+f6k3T/CrO6Y2VM00FQ1KIyOeh9HAz/n79UWFIuNvZ0pooEnMuy75aI94WylLPHkE6uiRDYwiaSd1uwmDJYrShjLZKAcrJQyjzpZd7Uj6nVdb3T+bUD3YapDbyAf+gbU+ZlK8v82DYN3+EXuFjDKHxHa3VgoXMJ8zJS4yDaPKAa8YHi3mqrcC7pr9cd/3QsAAP//AwBQSwMEFAAGAAgAAAAhAFyWJyLDAAAAKAEAAB4ACAFjdXN0b21YbWwvX3JlbHMvaXRlbTIueG1sLnJlbHMgogQBKKAAAQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACEz8FqwzAMBuB7oe9gdF+c9jBKidNLGeQ2Rgu9GkdJTGPLWEpp336mpxYGO0pC3y81h3uY1Q0ze4oGNlUNCqOj3sfRwPn09bEDxWJjb2eKaOCBDId2vWp+cLZSlnjyiVVRIhuYRNJea3YTBssVJYxlMlAOVkqZR52su9oR9bauP3V+NaB9M1XXG8hdvwF1eqSS/L9Nw+AdHsktAaP8EaHdwkLhEubvTImLbPOIYsALhmdrW5V7QbeNfvuv/QUAAP//AwBQSwMEFAAGAAgAAAAhAHvzAqPDAAAAKAEAAB4ACAFjdXN0b21YbWwvX3JlbHMvaXRlbTMueG1sLnJlbHMgogQBKKAAAQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACEz8FqwzAMBuB7Ye9gdF+cdDBKidPLKOQ2Rge7GkdxzGLLWOpY336mpxYGPUpC3y/1h9+4qh8sHCgZ6JoWFCZHU0jewOfp+LwDxWLTZFdKaOCCDIfhadN/4GqlLvESMquqJDawiOS91uwWjJYbypjqZKYSrdSyeJ2t+7Ye9bZtX3W5NWC4M9U4GSjj1IE6XXJNfmzTPAeHb+TOEZP8E6HdmYXiV1zfC2Wusi0exUAQjNfWS1PvBT30+u6/4Q8AAP//AwBQSwECLQAUAAYACAAAACEAIFmKTn0BAAARBgAAEwAAAAAAAAAAAAAAAAAAAAAAW0NvbnRlbnRfVHlwZXNdLnhtbFBLAQItABQABgAIAAAAIQATXr5lAgEAAN8CAAALAAAAAAAAAAAAAAAAALYDAABfcmVscy8ucmVsc1BLAQItABQABgAIAAAAIQDNH8HI5AIAAFcHAAAPAAAAAAAAAAAAAAAAAOkGAAB4bC93b3JrYm9vay54bWxQSwECLQAUAAYACAAAACEAiYjjDwYBAADXAwAAGgAAAAAAAAAAAAAAAAD6CQAAeGwvX3JlbHMvd29ya2Jvb2sueG1sLnJlbHNQSwECLQAUAAYACAAAACEA6eHjsNoBAACNAwAAGAAAAAAAAAAAAAAAAABADAAAeGwvd29ya3NoZWV0cy9zaGVldDEueG1sUEsBAi0AFAAGAAgAAAAhAHU+mWmTBgAAjBoAABMAAAAAAAAAAAAAAAAAUA4AAHhsL3RoZW1lL3RoZW1lMS54bWxQSwECLQAUAAYACAAAACEACK40cpcCAAA9BgAADQAAAAAAAAAAAAAAAAAUFQAAeGwvc3R5bGVzLnhtbFBLAQItABQABgAIAAAAIQCxTH/+pQEAAC8DAAARAAAAAAAAAAAAAAAAANYXAABkb2NQcm9wcy9jb3JlLnhtbFBLAQItABQABgAIAAAAIQAfm12L7wAAAIoBAAAQAAAAAAAAAAAAAAAAALIaAABkb2NQcm9wcy9hcHAueG1sUEsBAi0AFAAGAAgAAAAhAKNmxmwPAQAAkgEAABMAAAAAAAAAAAAAAAAA1xwAAGRvY1Byb3BzL2N1c3RvbS54bWxQSwECLQAUAAYACAAAACEAf4tDw8AAAAAiAQAAEwAAAAAAAAAAAAAAAAAfHwAAY3VzdG9tWG1sL2l0ZW0xLnhtbFBLAQItABQABgAIAAAAIQBFRFqzuAAAAMkAAAAYAAAAAAAAAAAAAAAAADggAABjdXN0b21YbWwvaXRlbVByb3BzMS54bWxQSwECLQAUAAYACAAAACEAvYRiI5AAAADbAAAAEwAAAAAAAAAAAAAAAABOIQAAY3VzdG9tWG1sL2l0ZW0yLnhtbFBLAQItABQABgAIAAAAIQBaAiFouAAAAMkAAAAYAAAAAAAAAAAAAAAAADciAABjdXN0b21YbWwvaXRlbVByb3BzMi54bWxQSwECLQAUAAYACAAAACEA+FUjoZUHAACAJQAAEwAAAAAAAAAAAAAAAABNIwAAY3VzdG9tWG1sL2l0ZW0zLnhtbFBLAQItABQABgAIAAAAIQAozmaDtgAAAMkAAAAYAAAAAAAAAAAAAAAAADsrAABjdXN0b21YbWwvaXRlbVByb3BzMy54bWxQSwECLQAUAAYACAAAACEAdD85esIAAAAoAQAAHgAAAAAAAAAAAAAAAABPLAAAY3VzdG9tWG1sL19yZWxzL2l0ZW0xLnhtbC5yZWxzUEsBAi0AFAAGAAgAAAAhAFyWJyLDAAAAKAEAAB4AAAAAAAAAAAAAAAAAVS4AAGN1c3RvbVhtbC9fcmVscy9pdGVtMi54bWwucmVsc1BLAQItABQABgAIAAAAIQB78wKjwwAAACgBAAAeAAAAAAAAAAAAAAAAAFwwAABjdXN0b21YbWwvX3JlbHMvaXRlbTMueG1sLnJlbHNQSwUGAAAAABMAEwD4BAAAYzIAAAAA",
"$content-type": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
}
After the Compose action, we add a Create file action, which is a SharePoint action. With this action, we create an Excel sheet on a SharePoint library, as file content we use the outputs from the Compose action.
First sign in with your (service) account.
Select the Site Address from the drop-down list or choose Enter custom value and manually enter the site address.
Via the folder icon, you can browse the folder path.
You can manually enter a File name, ending on .xlsx. But I want it to hold the current date and time. We can do this by entering an Expression. Place your cursor on the correct place in the file name field and add the below expression:
formatDateTime(utcNow(), ‘yyyy-MM-dd-HH-mm’)
As you can see the expression is added to the file name field.
As File content, we want to add the output from the compose action. We add this as Dynamic content.
Select Outputs from the Dynamic content list.
And this is our Create file action.
Our sheet needs to have a table, so we add a Create table action to the flow, which is an Excel action.
Choose the SharePoint site from the list and select the Document library.
Choose the SharePoint site from the list and select the Document library.
If the file is located in a subfolder, manually add the folder like below. The folder path is followed by Name, which is found as dynamic content and is the name of the previously created sheet.
My table only contains the ID, Displayname, and Type from the configuration, thus the table range is A1:C1.
Enter the columns names of your choice. I kept it simple with ID,DisplayName,Type.
And lastly, enter a Table name.
Now it’s time to add our first HTTP action to the flow, with which we pull data from Microsoft Graph.
In some of the queries, we can directly find information on the assignment of a configuration. On other queries, we need to run an additional query to get information on the assignment of the configuration. I show two examples, with which you can query all the configurations.
As an example, I use the deviceConfigurations. In the output is no assignment information found, so we an additional HTTP action, we query for the assignments of the configurations.
Add the first HTTP action.
As Method select GET.
As URI enter:
https://graph.microsoft.com/beta/deviceManagement/deviceConfigurations?$select=id,displayName
I’m only interested in the ID of the configuration and Displayname of the configuration, that’s why I use a $select. Otherwise, a lot of information will be pulled from the Graph, which we don’t use in the flow.
Choose Add Parameter and select Authentication.
As Authentication type select Managed identity.
Select your Managed identity from the list.
And add https://graph.microsoft.com as Audience.
Next, we need to add a Parse JSON action. We parse the output of the HTTP action, to be able to use the values later on in the flow.
As Content, we select Body from the Dynamic content list that is from our HTTP action.
As Schema, we can run the current flow, open the run via runs history and grab the body from the HTTP action. Then add it via the Use sample payload option to create the schema. We can also grab the same information from the Response preview field when we run the same query via Graph Explorer.
This is the schema for the Device Configurations.
{
"properties": {
"@@odata.context": {
"type": "string"
},
"value": {
"items": {
"properties": {
"@@odata.type": {
"type": "string"
},
"displayName": {
"type": "string"
},
"id": {
"type": "string"
}
},
"required": [
"@@odata.type",
"id",
"displayName"
],
"type": "object"
},
"type": "array"
}
},
"type": "object"
}
This is our Parse JSON action.
By using the previously retrieved ID of the device configurations, we can query for the assignments.
Add an HTTP action to the flow.
Choose GET as Method.
As URI enter:
https://graph.microsoft.com/beta/deviceManagement/deviceConfigurations/[ID]/assignments
Replace [ID] with the ID from the Parse JSON action found via dynamic content. This will add the HTTP action in a For each action.
Choose Add Parameter and select Authentication.
As Authentication type select Managed identity.
Select your Managed identity from the list.
And add https://graph.microsoft.com as Audience.
Again, the HTTP action is followed by a Parse JSON action.
Add the body from the dynamic content list (make sure you select the one from the latest HTTP action). And enter the schema.
This is the schema:
{
"properties": {
"@@odata.context": {
"type": "string"
},
"value": {
"items": {
"properties": {
"id": {
"type": "string"
},
"intent": {
"type": "string"
},
"source": {
"type": "string"
},
"sourceId": {
"type": "string"
},
"target": {
"properties": {
"@@odata.type": {
"type": "string"
}
},
"type": "object"
}
},
"required": [
"id",
"source",
"sourceId",
"intent",
"target"
],
"type": "object"
},
"type": "array"
}
},
"type": "object"
}
Now we need to filter out all configurations which don’t have an assignment.
When we run the last query and the configuration is not assigned, the value of the output is empty. We can use this information in our filter.
We use a Condition action, which is a Control action to get the filtering job done. We filter on the length of the value, which is 0 when empty.
To use the length of the value, we use the below expression:
length(body(‘Parse_JSON_GET_Device_Configurations_Assignment’)?[‘value’])
Enter this expression in the left field.
As you can see the expression holds the name of the last Parse JSON action, where the empty spaces are replaced with an underscore. Make sure the Parse JSON action name matches my example, or change it to your action name.
Next, from the drop-down list select is equal to and enter 0 to the right field.
Now that we have filtered out the configurations without an assignment, we are going to add every unassigned configuration to a row in the Excel sheet. To do this, we need to compose every row with a Compose action.
Add a Compose action under True with the below JSON in the Inputs field:
{
"ID":,
"DisplayName":,
"Type":
}
Add the corresponding dynamic content from the last Parse JSON to the inputs field, like below.
With an Add a row action (another Excel action) we will add the output from the compose action to the Excel sheet.
As Location select the SharePoint site and select the Document library.
Enter the folder path (if a subfolder is used) and select Name as dynamic content from the create file action.
Enter the Table name and add Outputs from the last compose action to the Body field.
And this is the first part of our Logic App.
Add the second part of the Logic Apps flow
As written earlier, we also have configurations in which we have information on the assignment directly by running the first Graph query. I use managedAppPolicies as an example, which contain App protection and configuration policies.
For every configuration type, we have a separate Graph URI and therefore we need to add a parallel branch for every configuration type to the flow. Every branch uses an own HTTP action to query Graph for the information needed.
Click on the plus sign between the Create file and the first HTTP action and select Add a parallel branch.
In the new branch, we enter an HTTP action.
As Method select GET.
As URI enter:
https://graph.microsoft.com/beta/deviceAppManagement/managedAppPolicies
Choose Add Parameter and select Authentication.
As Authentication type select Managed identity.
Select your Managed identity from the list.
And add https://graph.microsoft.com as Audience.
Again, we need to Parse the information.
This is the schema:
{
"properties": {
"@@odata.context": {
"type": "string"
},
"value": {
"items": {
"properties": {
"@@odata.type": {
"type": "string"
},
"allowedAndroidDeviceManufacturers": {},
"allowedAndroidDeviceModels": {
"type": "array"
},
"allowedDataIngestionLocations": {
"items": {
"type": "string"
},
"type": "array"
},
"allowedDataStorageLocations": {
"type": "array"
},
"allowedInboundDataTransferSources": {
"type": "string"
},
"allowedIosDeviceModels": {},
"allowedOutboundClipboardSharingExceptionLength": {
"type": "integer"
},
"allowedOutboundClipboardSharingLevel": {
"type": "string"
},
"allowedOutboundDataTransferDestinations": {
"type": "string"
},
"appActionIfAndroidDeviceManufacturerNotAllowed": {
"type": "string"
},
"appActionIfAndroidDeviceModelNotAllowed": {
"type": "string"
},
"appActionIfAndroidSafetyNetAppsVerificationFailed": {
"type": "string"
},
"appActionIfAndroidSafetyNetDeviceAttestationFailed": {
"type": "string"
},
"appActionIfDeviceComplianceRequired": {
"type": "string"
},
"appActionIfDeviceLockNotSet": {
"type": "string"
},
"appActionIfDevicePasscodeComplexityLessThanHigh": {},
"appActionIfDevicePasscodeComplexityLessThanLow": {},
"appActionIfDevicePasscodeComplexityLessThanMedium": {},
"appActionIfIosDeviceModelNotAllowed": {
"type": "string"
},
"appActionIfMaximumPinRetriesExceeded": {
"type": "string"
},
"appActionIfUnableToAuthenticateUser": {},
"appDataEncryptionType": {
"type": "string"
},
"appGroupType": {
"type": "string"
},
"approvedKeyboards": {
"type": "array"
},
"azureRightsManagementServicesAllowed": {
"type": "boolean"
},
"biometricAuthenticationBlocked": {
"type": "boolean"
},
"blockAfterCompanyPortalUpdateDeferralInDays": {
"type": "integer"
},
"blockDataIngestionIntoOrganizationDocuments": {
"type": "boolean"
},
"connectToVpnOnLaunch": {
"type": "boolean"
},
"contactSyncBlocked": {
"type": "boolean"
},
"createdDateTime": {
"type": "string"
},
"customBrowserDisplayName": {
"type": "string"
},
"customBrowserPackageId": {
"type": "string"
},
"customBrowserProtocol": {
"type": "string"
},
"customDialerAppDisplayName": {},
"customDialerAppPackageId": {},
"customDialerAppProtocol": {
"type": "string"
},
"customSettings": {
"items": {
"properties": {
"name": {
"type": "string"
},
"value": {
"type": "string"
}
},
"required": [
"name",
"value"
],
"type": "object"
},
"type": "array"
},
"dataBackupBlocked": {
"type": "boolean"
},
"dataRecoveryCertificate": {},
"daysWithoutContactBeforeUnenroll": {
"type": "integer"
},
"deployedAppCount": {
"type": "integer"
},
"description": {
"type": "string"
},
"deviceComplianceRequired": {
"type": "boolean"
},
"deviceLockRequired": {
"type": "boolean"
},
"dialerRestrictionLevel": {
"type": "string"
},
"disableAppEncryptionIfDeviceEncryptionIsEnabled": {
"type": "boolean"
},
"disableAppPinIfDevicePinIsSet": {
"type": "boolean"
},
"disableProtectionOfManagedOutboundOpenInData": {
"type": "boolean"
},
"displayName": {
"type": "string"
},
"encryptAppData": {
"type": "boolean"
},
"enforcementLevel": {
"type": "string"
},
"enterpriseDomain": {
"type": "string"
},
"enterpriseIPRanges": {
"type": "array"
},
"enterpriseIPRangesAreAuthoritative": {
"type": "boolean"
},
"enterpriseInternalProxyServers": {
"type": "array"
},
"enterpriseNetworkDomainNames": {
"type": "array"
},
"enterpriseProtectedDomainNames": {
"type": "array"
},
"enterpriseProxiedDomains": {
"type": "array"
},
"enterpriseProxyServers": {
"type": "array"
},
"enterpriseProxyServersAreAuthoritative": {
"type": "boolean"
},
"exemptApps": {
"type": "array"
},
"exemptedAppPackages": {
"type": "array"
},
"exemptedAppProtocols": {
"items": {
"properties": {
"name": {
"type": "string"
},
"value": {
"type": "string"
}
},
"required": [
"name",
"value"
],
"type": "object"
},
"type": "array"
},
"exemptedUniversalLinks": {
"items": {
"type": "string"
},
"type": "array"
},
"faceIdBlocked": {
"type": "boolean"
},
"filterOpenInToOnlyManagedApps": {
"type": "boolean"
},
"fingerprintAndBiometricEnabled": {},
"fingerprintBlocked": {
"type": "boolean"
},
"gracePeriodToBlockAppsDuringOffClockHours": {},
"iconsVisible": {
"type": "boolean"
},
"id": {
"type": "string"
},
"indexingEncryptedStoresOrItemsBlocked": {
"type": "boolean"
},
"isAssigned": {
"type": "boolean"
},
"keyboardsRestricted": {
"type": "boolean"
},
"lastModifiedDateTime": {
"type": "string"
},
"managedBrowser": {
"type": "string"
},
"managedBrowserToOpenLinksRequired": {
"type": "boolean"
},
"managedUniversalLinks": {
"items": {
"type": "string"
},
"type": "array"
},
"maximumAllowedDeviceThreatLevel": {
"type": "string"
},
"maximumPinRetries": {
"type": "integer"
},
"maximumRequiredOsVersion": {},
"maximumWarningOsVersion": {},
"maximumWipeOsVersion": {},
"mdmEnrollmentUrl": {
"type": "string"
},
"minimumPinLength": {
"type": "integer"
},
"minimumRequiredAppVersion": {},
"minimumRequiredCompanyPortalVersion": {},
"minimumRequiredOsVersion": {},
"minimumRequiredPatchVersion": {
"type": [
"string",
"null"
]
},
"minimumRequiredSdkVersion": {},
"minimumWarningAppVersion": {},
"minimumWarningCompanyPortalVersion": {},
"minimumWarningOsVersion": {},
"minimumWarningPatchVersion": {
"type": [
"string",
"null"
]
},
"minimumWipeAppVersion": {},
"minimumWipeCompanyPortalVersion": {},
"minimumWipeOsVersion": {},
"minimumWipePatchVersion": {
"type": [
"string",
"null"
]
},
"minimumWipeSdkVersion": {},
"minutesOfInactivityBeforeDeviceLock": {
"type": "integer"
},
"mobileThreatDefenseRemediationAction": {
"type": "string"
},
"neutralDomainResources": {
"type": "array"
},
"notificationRestriction": {
"type": "string"
},
"numberOfPastPinsRemembered": {
"type": "integer"
},
"organizationalCredentialsRequired": {
"type": "boolean"
},
"passwordMaximumAttemptCount": {
"type": "integer"
},
"periodBeforePinReset": {
"type": "string"
},
"periodOfflineBeforeAccessCheck": {
"type": "string"
},
"periodOfflineBeforeWipeIsEnforced": {
"type": "string"
},
"periodOnlineBeforeAccessCheck": {
"type": "string"
},
"pinCharacterSet": {
"type": "string"
},
"pinExpirationDays": {
"type": "integer"
},
"pinLowercaseLetters": {
"type": "string"
},
"pinMinimumLength": {
"type": "integer"
},
"pinRequired": {
"type": "boolean"
},
"pinRequiredInsteadOfBiometricTimeout": {
"type": "string"
},
"pinSpecialCharacters": {
"type": "string"
},
"pinUppercaseLetters": {
"type": "string"
},
"previousPinBlockCount": {
"type": "integer"
},
"printBlocked": {
"type": "boolean"
},
"protectInboundDataFromUnknownSources": {
"type": "boolean"
},
"protectedApps": {
"type": "array"
},
"protectionUnderLockConfigRequired": {
"type": "boolean"
},
"requireClass3Biometrics": {
"type": "boolean"
},
"requirePinAfterBiometricChange": {
"type": "boolean"
},
"requiredAndroidSafetyNetAppsVerificationType": {
"type": "string"
},
"requiredAndroidSafetyNetDeviceAttestationType": {
"type": "string"
},
"requiredAndroidSafetyNetEvaluationType": {
"type": "string"
},
"revokeOnMdmHandoffDisabled": {
"type": "boolean"
},
"revokeOnUnenrollDisabled": {
"type": "boolean"
},
"rightsManagementServicesTemplateId": {},
"roleScopeTagIds": {
"items": {
"type": "string"
},
"type": "array"
},
"saveAsBlocked": {
"type": "boolean"
},
"screenCaptureBlocked": {
"type": "boolean"
},
"simplePinBlocked": {
"type": "boolean"
},
"smbAutoEncryptedFileExtensions": {
"type": "array"
},
"targetedAppManagementLevels": {
"type": "string"
},
"thirdPartyKeyboardsBlocked": {
"type": "boolean"
},
"version": {
"type": "string"
},
"warnAfterCompanyPortalUpdateDeferralInDays": {
"type": "integer"
},
"windowsHelloForBusinessBlocked": {
"type": "boolean"
},
"wipeAfterCompanyPortalUpdateDeferralInDays": {
"type": "integer"
}
},
"required": [
"@@odata.type",
"displayName",
"description",
"createdDateTime",
"lastModifiedDateTime",
"roleScopeTagIds",
"id",
"version",
"isAssigned"
],
"type": "object"
},
"type": "array"
}
},
"type": "object"
}
As we can see in the body of the HTTP action, when we now run the flow, we have information on the assignment of the configuration. We can use this isAssigned information for our filter.
Add a Condition action to the flow.
In the left box, enter the dynamic content isAssigned. Select is equal to from the drop-down and enter false to the right box.
Under True we need to add the Compose and Add a row actions again, to add the retrieved information to the Excel sheet.
And we have our second branch created.
Add the third part of the Logic Apps flow
With the above two examples, we can add parallel branches for every configuration item, script etc to our flow. But one thing is an exception to this; the Intune Roles.
We can’t clean up built-in roles, so we need to first filter out the built-in roles to only add the custom roles to our overview.
The rest of this parallel branch for the Intune roles is almost the same as the first example of the device configurations, as we need an additional HTTP query to retrieve the assignment. The query to retrieve assignments is also a little different, so let’s build this parallel branch together.
Add an HTTP action to the flow.
As Method select GET.
As URI enter:
https://graph.microsoft.com/beta/roleManagement/deviceManagement/roleDefinitions?$select=id,displayName,isBuiltIn
Choose Add Parameter and select Authentication.
As Authentication type select Managed identity.
Select your Managed identity from the list.
And add https://graph.microsoft.com as Audience.
Add a Parse JSON action to the flow.
With this schema:
{
"properties": {
"@@odata.context": {
"type": "string"
},
"@@odata.count": {
"type": "integer"
},
"value": {
"items": {
"properties": {
"displayName": {
"type": "string"
},
"id": {
"type": "string"
},
"isBuiltIn": {
"type": "boolean"
}
},
"required": [
"id",
"displayName",
"isBuiltIn"
],
"type": "object"
},
"type": "array"
}
},
"type": "object"
}
To filter out the built-in Intune roles, we use a Filter array action. This is a Data Operations action.
As the information is found in the value of the Parse JSON Action, add value to the From field.
Add isBuiltin to the left value box, choose is equal to from the drop-down and add false to the right value field.
In the output from the filter action, we now will only find custom roles with an isBuiltin value of false.
To get information on the assignment of these roles, we use an additional HTTP action.
Add an HTTP action to the flow.
As Method select GET.
As URI enter:
https://graph.microsoft.com/beta/deviceManagement/roleDefinitions('[id]')?$expand=roleassignments
Replace [id] with the ID of the filter action. This will add the action in a For each.
Don’t forget to add the authentication information.
Next add a Parse JSON Action, with below schema:
{
"properties": {
"@@odata.context": {
"type": "string"
},
"@@odata.type": {
"type": "string"
},
"description": {
"type": "string"
},
"displayName": {
"type": "string"
},
"id": {
"type": "string"
},
"isBuiltIn": {
"type": "boolean"
},
"isBuiltInRoleDefinition": {
"type": "boolean"
},
"permissions": {
"items": {
"properties": {
"actions": {
"items": {
"type": "string"
},
"type": "array"
},
"resourceActions": {
"items": {
"properties": {
"allowedResourceActions": {
"items": {
"type": "string"
},
"type": "array"
},
"notAllowedResourceActions": {
"type": "array"
}
},
"required": [
"allowedResourceActions",
"notAllowedResourceActions"
],
"type": "object"
},
"type": "array"
}
},
"required": [
"actions",
"resourceActions"
],
"type": "object"
},
"type": "array"
},
"roleAssignments": {
"items": {
"properties": {
"@@odata.type": {
"type": "string"
},
"description": {
"type": "string"
},
"displayName": {
"type": "string"
},
"id": {
"type": "string"
},
"members": {
"type": "array"
},
"resourceScopes": {
"type": "array"
},
"scopeMembers": {
"type": "array"
},
"scopeType": {
"type": "string"
}
},
"required": [
"@@odata.type",
"id",
"displayName",
"description",
"scopeMembers",
"scopeType",
"resourceScopes",
"members"
],
"type": "object"
},
"type": "array"
},
"roleAssignments@odata.context": {
"type": "string"
},
"rolePermissions": {
"items": {
"properties": {
"actions": {
"items": {
"type": "string"
},
"type": "array"
},
"resourceActions": {
"items": {
"properties": {
"allowedResourceActions": {
"items": {
"type": "string"
},
"type": "array"
},
"notAllowedResourceActions": {
"type": "array"
}
},
"required": [
"allowedResourceActions",
"notAllowedResourceActions"
],
"type": "object"
},
"type": "array"
}
},
"required": [
"actions",
"resourceActions"
],
"type": "object"
},
"type": "array"
},
"roleScopeTagIds": {
"items": {
"type": "string"
},
"type": "array"
}
},
"type": "object"
}
Again we can use the length of the value to determine if the role has an assignment or not.
Add a Condition to the flow, within the right field this expression:
length(body(‘Parse_JSON_GET_RBAC_Custom_role’)?[‘roleAssignments’])
Choose is equal to from the drop-down and add 0 to the right field.
Again we need to Compose the information.
And add the information to the Excel sheet.
And this is what the flow looks like with the first three branches added.
Add the last part to the Logic Apps flow
When all branches are added, the data is stored in an Excel sheet on SharePoint. If you want to receive it via e-mail, we need to add some additional steps to the flow.
We start with a Delay action of a few minutes. If you don’t add a delay, you might see that sometimes data is missing from one of the branches in the sheet. The sheet is sent too soon via email.
This is followed by a Get file content (SharePoint) action, to retrieve the information from the Excel sheet.
Select the Site address and enter ID from the Create file action as File identifier.
And as the last action, we add the action to send the email, with the Excel sheet as an attachment.
Enter an email address for the sender and recipient. Add a subject and some text to the body.
Choose Add new parameter and select Attachments so we can add the attachment to the email. Add Name from the Create file action and add File content from the Get file content action.
And the flow is ready.
The end result
The end result of this flow is that you regularly receive an overview of all the Intune items which are not assigned and thus most likely are not used.
Microsoft Graph URIs
This is an overview of the Microsoft Graph URIs to grab the Intune items from your tenant:
Intune items; | URIs; |
Device configurations (includes WUfB rings) | /deviceManagement/deviceConfigurations?$select=id,displayName |
Compliance policies | /deviceManagement/deviceCompliancePolicies?$select=id,displayName |
Feature update rings | /deviceManagement/windowsFeatureUpdateProfiles?$select=id,displayName |
Config Policies (Settings Catalog, kiosk, Endpoint security; Local user group membership) | /deviceManagement/configurationPolicies?$select=id,name |
Intents (Endpoint security; AV, firewall, Disk encryption, sec baseline profiles) | /deviceManagement/intents |
App protection policies + App config policies | /deviceAppManagement/managedAppPolicies |
Device Scripts | /deviceManagement/deviceManagementScripts?$select=id,displayName |
Proactive remediations | /deviceHealthScripts?$select=id,displayName |
Windows Autopilot deployment profiles | /deviceManagement/windowsAutopilotDeploymentProfiles?$select=id,displayName |
Intune custom roles | /roleManagement/deviceManagement/roleDefinitions?$select=id,displayName,isBuiltIn |
Thanks for reading and keep your tenants cleaned up!
2 Comments
Nice One!
Hi Peter,
Can we use logic app to notify with mentoring the status Conflict Intune Policy’s details ?