azure - KML query to group software by computer from ConfigurationData table - Stack Overflow

admin管理员组

文章数量:1332345

Synopsis: I need help with KML syntax to query Azure logs and return summary rows with expanded detail rows.

Question: We have several servers managed in Azure Arc, and Azure's logs include a Change Tracking > ConfigurationData table where the current software is listed. I'm trying to understand KML syntax to query this log for results as follows: One row per distinct SoftwareName (and its total count) and expand that row to show the computer(s) that have that softwareName installed.

I haven't mastered KQL language, and I don't know if this type of result is possible or if the general layout is not designed for this. Apologies if this is not the correct site, but I see other Azure related questions here.

Details: Here's an example query of all data I'm looking to retrieve:

ConfigurationData
| where ConfigDataType == "Software" and SoftwareType == "Application"
| summarize Count = count() by SoftwareName
| join kind=inner (
    ConfigurationData
    | where ConfigDataType == "Software" and SoftwareType == "Application"
    | project Computer, SoftwareName
) on SoftwareName
| project SoftwareName, Count, Computer
| order by SoftwareName asc

And it returns this:

What I want is essentially this, but with a distinct list of results - so the first software should only appear 1x (not 4x), and obviously I wouldn't include the Computer column at this level. Expanding the row should show all four Computers that have it installed).

Does anyone know the syntax to get there? (Or similar)? Thanks!

Synopsis: I need help with KML syntax to query Azure logs and return summary rows with expanded detail rows.

Question: We have several servers managed in Azure Arc, and Azure's logs include a Change Tracking > ConfigurationData table where the current software is listed. I'm trying to understand KML syntax to query this log for results as follows: One row per distinct SoftwareName (and its total count) and expand that row to show the computer(s) that have that softwareName installed.

I haven't mastered KQL language, and I don't know if this type of result is possible or if the general layout is not designed for this. Apologies if this is not the correct site, but I see other Azure related questions here.

Details: Here's an example query of all data I'm looking to retrieve:

ConfigurationData
| where ConfigDataType == "Software" and SoftwareType == "Application"
| summarize Count = count() by SoftwareName
| join kind=inner (
    ConfigurationData
    | where ConfigDataType == "Software" and SoftwareType == "Application"
    | project Computer, SoftwareName
) on SoftwareName
| project SoftwareName, Count, Computer
| order by SoftwareName asc

And it returns this:

What I want is essentially this, but with a distinct list of results - so the first software should only appear 1x (not 4x), and obviously I wouldn't include the Computer column at this level. Expanding the row should show all four Computers that have it installed).

Does anyone know the syntax to get there? (Or similar)? Thanks!

• azure
• azure-arc

Share Improve this question Follow edited Nov 21, 2024 at 8:56 Jahnavi 8,09311 gold badge66 silver badges1212 bronze badges Recognized by Microsoft Azure Collective asked Nov 20, 2024 at 21:07 tb1tb1 1,3861414 silver badges1717 bronze badges 3

• Where are you running all these log tables? @tb1 – Jahnavi Commented Nov 21, 2024 at 9:18
• 1 portal.azure - Azure Arc area, picking a machine and clicking on the Logs menu – tb1 Commented Nov 21, 2024 at 14:21
• Have you tried using make_list function to retrieve all together. @tb1 – Jahnavi Commented Nov 22, 2024 at 8:07

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

KML query to group software by computer from Configuration Data table: -

To achieve your requirement, you can use make_list() function available in Kusto which retrieves a list of computers by filtering it with specific software and config data type.

ConfigurationData
| where ConfigDataType == "Software" and SoftwareType == "Application"
| summarize 
    Count = count(), 
    computerinfo = make_list(Computer) 
  by SoftwareName
| project SoftwareName, Count, computerinfo
| order by SoftwareName asc

Output:

As I am not having much data elated to the configuration of arc resources, I have applied the same logic to the Heartbeat table in the VM as shown below and it worked as expected.

本文标签： azureKML query to group software by computer from ConfigurationData tableStack Overflow