Get notifications of missing devices in Windows Update for Business reports

Microsoft recently added a new feature to their Windows Update for Business reports as announced in this Tech Community article. The data of these reports now show data of devices that are missing from the Windows Update reports.

The DeviceDiagnosticDataNotReceived alert compares device data from Entra ID with Windows Update for Business reports to help identify devices that aren’t sending diagnostic data.
You can use Log Analytics or Windows Update for Business reports, which are built on queries from Log Analytics, to locate missing devices. Let’s see how you can work with this alert in either service.

That’s interesting information to understand which devices are missing from the report and need your attention.

It is great to have this information in WUfB reports in an online portal. But wouldn’t it be nice to get a weekly overview in your mailbox with these devices so you get notified to take action? Of course, that would be nice!

Let’s create our own solution to get this job done.

The solution in short

The data of the missing devices is available in a Log Analytics Workspace as you can see in the below example.

By running a API query, we can pull that data into an Azure Logic Apps workflow. We can process that data and add it, for example, to an Excel sheet for better readability. That Excel sheet can then be sent, using a shared mailbox, to any mailbox of our choice.

That would be the most simple solution. But I have seen most (or all) of these missing devices miss the device name in this data. Only the Azure Device ID is shown, with the issue description and recommendation.

I would rather also have the device name in the report, instead of having to look for that manually in Entra ID. To get the device name, we run an Graph API query, but this time to get the device name from Entra ID.

The flow is also available on my GitHub repo for easy deployment.

Requirements

To create this solution, we have a few requirements.

To retrieve the data from the Log Analytics Workspace we need permission. I use a managed identity for this, which is assigned the Log Analytics reader role. The role can be assigned on the resource group level.

To query Microsoft Graph to retrieve the device name, we need to assign the Device.Read.All permission to the Managed Identiry.

To create an Excel sheet, we need to save it (temporary) 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 (if you want to send the file via email). I used a service account with permission to send an email from a shared mailbox.

Setup the Logic App flow – Part 1

When the requirements are in place, we can start creating the Logic Apps flow.

In the first part of the flow we create an Excel sheet to store our data in. We pull the data of the missing devices from our Log Analytics Workspace and process this to variables we can use in our 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 Settings to 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.

Next, we add our first action by selecting +New step.
We add an Initialize variable action. In this variable, we store the Workspace ID of the Log Analytics workspace in which our data is stored.

We give the variable a name of choice, select String as Type, and add the Workspace ID.

This is an additional step, we could also add the workspace later in the flow directly in the HTTP action.

First, we need to compose an Excel file, otherwise, we end up with an empty sheet. We do this by using a Compose action, that 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, and 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.

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.

You can manually enter a File name, ending with .xlsx. But I want it to hold the current date. 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’)

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. In the File field, we enter the Id of the create file action.

My table only contains the displayName, deviceId, and startTime from the configuration, thus the table range is A1:C1.
Add these items to the Columns names field separated with a comma. And add a Table name.

If you want to add more data from the LA Workspace to the columns, for example, the Description it needs to be added here (and later in in the flow as well).

Now it’s time to add our first HTTP action to query the Log Analytics Workspace.

Add the first HTTP action.
As Method select GET.
As URI enter:

https://api.loganalytics.io/v1/workspaces/[WORKSPACEID]/query

We need to replace [WORKSPACEID] in the URI with WorkspaceID found as Dynamic content.

Enter in the left box at Queries “query”.
And add below KQL query in the right box;

UCDeviceAlert
| where AlertSubtype == "DeviceDiagnosticDataNotReceived"
| where TimeGenerated > ago(1d)
| project DeviceName, AzureADDeviceId, AlertStatus, AlertSubtype, StartTime, AlertData, Description, Recommendation

Next, choose Add Parameter and select Authentication.
As Authentication type select Managed identity.
Select your Managed identity from the list.
And add https://api.loganalytics.io as Audience.

This is our HTTP action.

Often we can just use a Parse JSON action to process the output of the HTTP action, so we can use the variables from the output later in the flow. The information we need is stored in rows (see the screen shot). Therefor we need to pull out the rows in two different steps, for which I use two Initialize variable actions.

First we pull out the body.
Add an Initialize variable action and enter DeviceResults as Name.
Choose Object as Type and add Body from the Dynamic content as value.

When we now run the flow and check the output of this Initialize variable, this is the data we have.

Add another Initialize variable action. Select Array as Type and as value we add an Expression:

variables('DeviceResults')?['tables'][0]?['rows']

This pulls out the array with the rows after which we can further process it.

Now that we have the arrays, we add a For each action, to loop through each array (Row). We use the output from the latest Initialize variables action.

Next, add a Compose action, which is a Data operations action. With this action we loop through the missing devices found in the query.
As Input add Current item from the Dynamic content list.

When we now run the flow and have a look at the Compose action, we see we have multiple runs in the For each.

We have separated the data per missing device, but it is still one array with information. I want to pull out all the values so I can use them as variables later in the flow.
To perform this job, we create a JSON format of the data and after that, we Parse that information. I only need the deviceId and startTime later in the flow, so this is my JSON. If you also want other values, you need to expand the JSON.
Add another Compose action and add below as Input;

{
"deviceId":,
"startTime":
}

Per missing device, we have this array with information available. These are 8 rows with information, that are identifiable with numbers, starting at 0.
I need the device ID, which is on the second row, but because numbering starts with 0, this row is number 1.
And I need start time, identified as number 4.

We can pull out the rows with these two expressions;

outputs('Compose_loop_over_all_devices')[1]

outputs('Compose_loop_over_all_devices')[4]

This is the Compose action. Please note the location of the Expression in the input field.

When running the flow, we see we finally have the information we need.

We only need to parse the information, so we can use it later in the flow.

Add a Parse JSON Action.
As content add Outputs in the Content (1) field.
We can create the Schema by pasting the output of the previous Compose action as example payload in the action (2).

Which gives us our Parse JSON action.

Setup the Logic App flow – Part 2

It took quite some steps to process the data from the LA Workspace to variables.
In this second part of the flow we pull data from Entra ID to retrieve the missing devices hostnames. We store the information in the Excel sheet and send the sheet as attachment via e-mail.

Add a new HTTP action to the flow.

Add an HTTP Action.
Choose GET as Method.
As URI enter:

https://graph.microsoft.com/v1.0/devices?$filter=(deviceId eq '[DEVICEID]')

Replace [DEVICEID] with deviceId of the Parse JSON action.

Next, 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.

We need to parse the output of the HTTP action again with a Parse JSON action.

To create the schema, run the flow and copy the output of the HTTP action as sample payload.

We now have the information pulled from the LA Workspace and Entra ID. To store it in the Excel sheet, we first need to add the data into a table.
For this we use a Compose action.

Add below schema to the Inputs field.

{
"displayName":,
"deviceId":,
"startTime":
}

The values we add to the Inputs field can be found as Dynamic content.
The deviceId and startTime are from the device details Parse JSON Action (that follows the Compose actions)
The displayName is from the last Parse JSON action.

Next we add an Add a row into table action which is an Excel Business action. With this action we add the data to the previously created Excel sheet on SharePoint.

As location choose the SharePoint group where the sheet is stored and also select the library. Add Id to identify the File and add the name of the Table.

And as last add Outputs from the last Compose action is Body.

Close the For each actions and add a Delay action to the flow, this is a Schedule action.

This action 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.

The last action in out flow is to send an e-mail from a shared mailbox.

Add the information to you needs to the different fields.

Choose Add new parameter and select Attachments.

Add Name and File content as dynamic content.

And this is our flow!

The end result

And the end result is an e-mail.

With a report of all the missing devices from the Windows Update for Business report!

That’s it for this post.
Thanks for reading!