Need Help with KQL Query for Software Update Information in Log Analytics

Swahela Mulla 95 Reputation points
2023-11-28T07:26:41.5+00:00

Hello Everyone,

I hope this message finds you well. I'm currently working on retrieving software update information from Log Analytics in Azure using Kusto Query Language (KQL). Unfortunately, my lack of experience in KQL is making it challenging for me to construct the necessary queries.

I have two tables in my Log Analytics workspace: (or use different table to retrieve below details)

UCClient:

AzureADDeviceId, DeviceName, City, Country, DeviceModel, _IsBillable, IsVirtual, OSArchitecture, OSBuild, OSVersion

UCClientUpdateStatus:

ClientState, ClientSubstate, DeploymentId, IsUpdateHealthy, TargetBuild,, TargetKBNumber, TargetVersion, UpdateCategory, UpdateDisplayName, UpdateReleaseTime

I'm aiming to fetch software update information for the last 30 days from the current date and time, including details about both quality and feature updates. (if you provide me seprate queries then also fine only suggest me where we need to add filters) Specifically, I need insights into the ClientState, IsUpdateHealthy, TargetBuild, TargetKBNumber, TargetVersion, UpdateCategory, UpdateDisplayName, and UpdateReleaseTime.

Reference Link : https://video2.skills-academy.com/en-us/azure/azure-monitor/reference/tables/ucclient

Sample query that I have taken from Windows Update from Business Reports:

// intialitze constants let _SnapshotTime = datetime(2023-11-28T06:00:00Z); // Prepare the subtables let UCClientUpdateStatus_Info = UCClientUpdateStatus | where TimeGenerated == _SnapshotTime;    let UCUpdateAlert_Info = UCUpdateAlert | where TimeGenerated == _SnapshotTime | where AlertStatus == "Active";    let UCDeviceAlert_Info = UCDeviceAlert | where TimeGenerated == _SnapshotTime | where AlertStatus == "Active"; let UCClient_Info = UCClient | where TimeGenerated == _SnapshotTime; // let QualityUpdateInfo = UCClientUpdateStatus_Info | where UpdateCategory == "WindowsQualityUpdate"; let DeviceQualityUpdateInfo = (QualityUpdateInfo | join kind=leftouter UCClient_Info on AzureADDeviceId | project         AzureADDeviceId,         DeviceName,         UpdateCategory,         OSVersion,         OSBuild,         TargetBuild,         ClientState,         ClientSubstate,         UpdateReleaseTime); let UpdateAlerts = (UCUpdateAlert_Info | where UpdateCategory == "WindowsQualityUpdate" | summarize Alerts=count() by AzureADDeviceId); let DeviceAlerts = (UCDeviceAlert_Info | summarize Alerts=count() by AzureADDeviceId); let Alerts = (UpdateAlerts | join kind=fullouter DeviceAlerts on AzureADDeviceId | extend TotalAlerts = iff(isempty(Alerts), 0, Alerts) + iff(isempty(Alerts1), 0, Alerts1) | summarize by AzureADDeviceId, TotalAlerts); DeviceQualityUpdateInfo | join kind = leftouter (Alerts) on AzureADDeviceId | extend Name =  iff(isempty(DeviceName), "---", tostring(DeviceName)) | sort by TotalAlerts desc | distinct     Name,     AzureADDeviceId,     OSVersion,     OSBuild,     TargetBuild,     ClientState,     TotalAlerts,     UpdateCategory,     UpdateReleaseTime | project-reorder     Name,     AzureADDeviceId,     OSVersion,     OSBuild,     TargetBuild,     UpdateReleaseTime,     ClientState,     TotalAlerts

If anyone could guide me on constructing KQL queries for this purpose, I would greatly appreciate the assistance. Additionally, if there are pre-existing queries that fulfill these criteria, please feel free to share them.

Thank you in advance for your help!

Best regards,

Swahela Mulla

Azure Monitor
Azure Monitor
An Azure service that is used to collect, analyze, and act on telemetry data from Azure and on-premises environments.
3,234 questions
Azure Data Explorer
Azure Data Explorer
An Azure data analytics service for real-time analysis on large volumes of data streaming from sources including applications, websites, and internet of things devices.
524 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. SwathiDhanwada-MSFT 18,756 Reputation points
    2023-11-29T06:26:39.53+00:00

    @Swahela Mulla To retrieve software update information from Log Analytics in Azure using KQL, you can use the following query:

    UCClientUpdateStatus
| where TimeGenerated > ago(30d)
| where UpdateCategory in ("SecurityUpdates", "CriticalUpdates", "Updates", "FeaturePacks")
| project ClientState, IsUpdateHealthy, TargetBuild, TargetKBNumber, TargetVersion, UpdateCategory, UpdateDisplayName, UpdateReleaseTime

    This query retrieves software update information for the last 30 days from the current date and time, including details about both quality and feature updates. The where clause filters the results to only include updates in the specified categories. The project operator selects the columns you are interested in.

    Note that this query assumes that the UCClientUpdateStatus table contains the information you are looking for. If you need to use a different table, you will need to modify the query accordingly.

    I hope this helps! Let me know if you have any further questions.

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.