Microsoft recently Microsoft announced a new feature to manage Windows driver and firmware updates with Microsoft Intune. A very welcome addition to the Windows Updates features we have had for a long time in Microsoft Intune. This new feature gives us more options to manage the deployment of drivers and firmware for our Windows devices. We are now able to deploy drivers in a more controlled manner by deploying them in deployment rings and first testing them on our own devices before deploying them to our end users.
I’m not going into detail on this new feature, but in the new driver update profiles we have the option to approve and deploy the drivers automatically or manually, depending on our needs.
If we chose to approve the drivers manually this means regularly visiting the Intune portal to manually review the available drivers.
To help you with this task and don’t forget this task I created an Azure Logic Apps flow to send out a report on a scheduled base to a mailbox.
Attached to that email is an Excel sheet with an overview of all recommended drivers that need to be reviewed. The drivers are listed per driver update profile on a separate sheet.
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
Information regarding Driver updates for Windows is available via Microsoft Graph, as I also showed in this recent post. We can query this information using HTTP actions in an Azure Logic Apps flow, which we can run on an occurrence. We first query Microsoft Graph for all the driver update queries, which is followed by querying every single driver update profile for its recommended drivers (we can also query only the other drivers or all drivers per profile). Next, we filter out only the drivers that have a status of needs review to be further processed.
The data we now have is stored in an Excel sheet on SharePoint, after which it is attached to an email, ready to get sent out to a mailbox of choice.
The bicep files and deployment script for easy deployment can be found on my GitHub repository.
Requirements
To create 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) permission we need is:
DeviceManagementConfiguration.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 last, we need to have a mailbox. I used a service account with permission 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.
In the first part, we add a trigger, add actions to create an empty Excel sheet and query Microsoft Graph for the Windows driver update profile we have in our Intune tenant.
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.
We do this by using a Compose action, which is found under the Data Operations actions. We can search for Compose or for Data Operations.
This is the input from an empty Excel sheet, you can just copy-paste this into the inputs field of the Compose action. Otherwise, you need to create an empty Excel sheet yourself 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"
}
This should look like the Compose action below.
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 to where the Excel sheets need to be stored.
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.
Now it’s time to add our first HTTP action to the flow, with which we pull data from Microsoft Graph.
Add the first HTTP action.
As Method select GET.
As URI enter:
https://graph.microsoft.com/beta/deviceManagement/windowsDriverUpdateProfiles
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 body is from our HTTP action.
We could manually create our Schema, but it’s much easier to create a schema using the Use sample payload option. To create an example payload we can save and 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.
No matter which one you chose, add the data in the sample payload pop-up.
And this is the Parse JSON action in the first part of our flow.
Add the second part of the Logic Apps flow
In the second part of the flow, we are going to query every Driver Update profile for its driver inventory. We filter on the approval status of the driver and add worksheets and tables to the previously created Excel sheet.
We add a second HTTP action to our flow. With this query, we query every Driver profile for its driver inventory.
As Method select GET.
As URI enter:
https://graph.microsoft.com/beta/deviceManagement/windowsDriverUpdateProfiles/[ID]/driverInventories?$filter=category%20eq%20%27recommended%27
Replace [ID] with the ID we can find as dynamic content.
If you want to query all drivers instead of only the recommended drivers, remove the whole filter (everything behind the question mark).
If you want to query other drivers instead of recommended drivers, replace recommended with other.
When adding the ID, the HTTP action is automatically added to 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.
Next, we add an action to create worksheets in the Excel sheet. One sheet, per driver update profile.
For this add a Create Worksheet action, which is an Excel Online (Business) action.
Choose the SharePoint site as Location where we in one of the first actions created the Excel sheet.
Select the Document library.
In the File field, we add the ID, found in the dynamic content of the Create file action.
We need to provide a name for the sheets in the Name field. But we can’t add the same name for multiple sheets, therefor we need to come up with something dynamic as most likely we have multiple driver update profiles. Therefore I add a random number behind the sheet name, by using an expression:
rand(1,100)
This is what the Create worksheet action looks like.
After the sheets are created, we need to add a table to the sheets. Without a table, the flow can’t process data from an Excel sheet.
Add a Create table action, which is also an Excel Online action.
Select the location, Document library, and add ID of the Create file action in the File field.
We also need to add a table range. Which range depends on the number of columns the table gets to store its data.
As we need to add the table range to every previously created sheet, we need to add the name of the sheet to the table range.
We can’t just add the name of the sheet to the Table range field, it needs to be in between quotes.
Add ”! (a single quote and an exclamation mark) to the Table range field before the actual table range.
The dynamic content Name of the Create worksheet should be added in between the single quotes.
We also need to add a Table name. The table name also needs to be unique, so again I added an expression to add a random number:
rand(1,100)
And we need to add the Column names.
The second part of the Logic Apps flow is ready.
Add the third part to the Logic Apps flow
In the first parts of the flow, we queried Microsoft Graph for the Windows driver update profile, and for every driver, we retrieved the recommended drivers. We also created an Excel sheet and for every driver profile, we added a sheet and table to the Excel sheet.
In the next part, we filter out all drivers that have an approval status of needs review. Only the drivers that have this needs review status are added to the table in the Excel sheet.
We add a Condition action to the flow, which is a Control action.
In the left field, we add approvalStatus from the last Parse JSON action.
We choose is equal to from the dropdown list and add needsReview to the left field.
When the condition is true the data needs to be further processed. Therefore we add the next actions under the True box. We don’t put any action under False.
We can’t directly the retrieved data to the Excel sheet. We need to first compose a row with the data we want to add to the Excel sheet.
Add a Compose action to the flow with the below JSON in the inputs field:
{
"name":,
"version":,
"manufacturer":,
"releaseDateTime":,
"applicableDeviceCount":,
"approvalStatus":,
"ProfiledisplayName":
}
Now we need to make sure the values we retrieved with the HTTP action are added in the Compose action.
Add the corresponding dynamic content (values) from one of the two Parse JSON actions, like below.
Most items are from the last Parse JSON action, the displayName is from the first Parse JSON action.
The Compose action should look like the below example.
Now that we have composed the rows, we can add the rows to the Excel sheet.
Add an Add row into a table action.
Select the Location and Documents library.
Add the ID from the Create file action and Name from the Create table action.
As the body, we add the Outputs from the Compose action.
This is the third part of our flow.
Add the last part to the Logic Apps flow
With the previous parts of the flow, we have created our Excel sheet with the Windows driver update info we want to have. But I want to receive this sheet per e-mail, as that’s also my reminder to review the drivers that need to be reviewed. This part is added in the last part of the flow.
We first add a Delay action to the flow, which delays the flow for a few minutes before it sends out the e-mail. We do this because sometimes not all data is already processed and available in the sheet when we immediately send out the e-mail. This means without the delay, you would receive an Excel sheet that doesn’t hold all data we retrieved.
We first need to retrieve the content of the Excel sheet with a Get file content action before we can attach it to the e-mail.
Select the SharePoint site address and add the ID of the Create file action.
The last action is a Send an email from a shared mailbox action, which is an Office 365 Outlook action.
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.
The end result
The end result is we have this Azure Logic Apps flow created!
But more important we now receive a reminder per e-mail that we need to review our Windows drivers that are not yet approved.
In that e-mail, we find, as an attachment, an Excel sheet with different sheets and an overview of those drivers.
That’s it for this blog post.
Thanks for reading.
One small note; I don’t know how to remove the default shit with a flow, thus the first sheet is empty in the Excel sheet.
2 Comments
Hey Peter, this is great work. I’ve been messing around with this reporting for use within the environment I work in. One thing I’m finding is that the filter doesn’t seem to filter out only recommended like it seems to be written to work. Are you seeing the same thing in your experience? I can’t see a property that is included in the object that I’m able to pull this way.
Hi Thor,
When I use this filter, it filters out only the recommended drivers in my environment:
?$filter=category%20eq%20%27recommended%27