log
1 Topic
System Updates queries, how to find counts and list machines
Hi, I'm trying to make a bunch of queries + new dashboard, that is similar to the built-in queries in the System Update Assessment in Azure LogAnalytics. But I simply don't know, how I can make the right queries. I have tried to describe the needed queries below. Does anyone have something they would share with me - or similar queries, which can inspire me. My customer has made their own definitions/grouping: // CRITICAL NON-SECURITY UPDATES (monthly): Critical Updates // IMPORTANT SECURITY UPDATES (monthly): Security Updates, Security-only update, Update Rollups, Monthly Rollups // IMPORTANT FEATURE UPDATES (planned): Feature Packs, Updates, Servicing Stack Updates, Upgrades, Service Packs // Output: Pie Chart // Need Critical Updates = CriticalUpdatesCountTotal // Need Important Security Updates = SecurityUpdatesCountTotal // Need Important Feature Updates = FeatureUpdatesTotal // Up to date = NoPendingTotal ------------------------------- Basically I want to find number of machines, which has CRITICAL NON-SECURITY UPDATES pending, which are more than 1 months old to exclude this months updates number of machines, which have IMPORTANT SECURITY UPDATES pending which are more than 1 months old to exclude this months updates number of machines, which have IMPORTANT FEATURE UPDATES pending which are more than 1 months old to exclude this months updates number of machines, which have NO pending updates which are more than 1 months old to exclude this months updates Hints ?? // ---------------------------------------------------------------------------------- // Query 1: Count of machines (dashboard) // ---------------------------------------------------------------------------------- Update // find all Windows updates - excluding Definition Updates and Drivers | where OSType!="Linux" and Optional==false and Classification != "Definition Updates" and Classification != "Drivers" // Find newest updates entries per computer | summarize hint.strategy=partitioned arg_max(TimeGenerated, *) by Computer,SourceComputerId,UpdateID // Summarize number of machines, which has CRITICAL NON-SECURITY UPDATES pending, which are more than 1 months old to exclude ths months updates CriticalUpdatesCountTotal = UpdateState=~"Needed" and Approved!=false and Classification contains "Critical Updates" and PublishedDate < now(-31d) // Summarize number of machines, which have IMPORTANT SECURITY UPDATES pending which are more than 1 months old to exclude ths months updates SecurityUpdatesCountTotal = UpdateState=~"Needed" and Approved!=false and ( (Classification contains "Security Updates") or (Classification contains "Rollups") ) and PublishedDate < now(-31d) // Summarize number of machines, which have FEATURE UPDATES pending which are more than 1 months old to exclude ths months updates FeatureUpdatesTotal = UpdateState=~"Needed" and Approved!=false and ( (Classification contains "Feature Packs") or (Classification == "Updates") or (Classification == "Upgrades") or (Classification contains "Service Packs") ) and PublishedDate < now(-31d) // Summarize number of machines, which have NO pending updates which are more than 1 months old to exclude ths months updates NoPendingTotal = UpdateState=~"Needed" and Approved!=false and ( (Classification contains "Feature Packs") or (Classification == "Updates") or (Classification == "Upgrades") or (Classification contains "Service Packs") ) and PublishedDate < now(-31d) // Output: Pie Chart // Need Critical Updates = CriticalUpdatesCountTotal // Need Important Security Updates = SecurityUpdatesCountTotal // Need Important Feature Updates = FeatureUpdatesTotal // Up to date = NoPendingTotal // ---------------------------------------------------------------------------------- Query 2: List of machines which has CRITICAL NON-SECURITY UPDATES pending, which are more than 1 months old // ---------------------------------------------------------------------------------- Query 3: List of machines which has IMPORTANT SECURITY UPDATES pending, which are more than 1 months old // ---------------------------------------------------------------------------------- Query 4: List of machines which has FEATURE UPDATES pending, which are more than 1 months old // ---------------------------------------------------------------------------------- Query 5: List of machines which has NO pending updates, which are more than 1 months old // ---------------------------------------------------------------------------------- // Microsoft Definition // ---------------------------------------------------------------------------------- // Critical update - A widely released fix for a specific problem that addresses a critical, non-security-related bug. // Definition update - A widely released and frequent software update that contains additions to a product’s definition database. Definition databases are often used to detect objects that have specific attributes, such as malicious code, phishing websites, or junk mail. // Driver - Software that controls the input and output of a device. // Feature pack - New product functionality that is first distributed outside the context of a product release and that is typically included in the next full product release. // Security update - A widely released fix for a product-specific, security-related vulnerability. Security vulnerabilities are rated by their severity. The severity rating is indicated in the Microsoft security bulletin as critical, important, moderate, or low. // Service pack - A tested, cumulative set of all hotfixes, security updates, critical updates, and updates. Additionally, service packs may contain additional fixes for problems that are found internally since the release of the product. Service packs my also contain a limited number of customer-requested design changes or features. // Tool - A utility or feature that helps complete a task or set of tasks. // Update - A widely released fix for a specific problem. An update addresses a noncritical, non-security-related bug. // Update rollup - A tested, cumulative set of hotfixes, security updates, critical updates, and updates that are packaged together for easy deployment. A rollup generally targets a specific area, such as security, or a component of a product, such as Internet Information Services (IIS). // Security-only update - An update that collects all the new security updates for a given month and for a given product, addressing security-related vulnerabilities // Monthly Rollup - A tested, cumulative set of updates. They include both security and reliability updates that are packaged together. The Monthly Rollup is product specific, addresses both new security issues and nonsecurity issues in a single update and will proactively include updates that were released in the past. Security vulnerabilities are rated by their severity. The severity rating is indicated in the Microsoft security bulletin as critical, important, moderate, or low. This Monthly Rollup would be displayed under the title Security Monthly Quality Rollup when you download or install. This Monthly Rollup will be classified as an "Important" update on Windows Update and will automatically download and install if your Windows Update settings are configured to automatically download and install Important updates. // Servicing Stack Updates (SSU) - The "servicing stack" is the code that installs other operating system updates. Additionally, it contains the "component-based servicing stack" (CBS), which is a key underlying component for several elements of Windows deployment, such as DISM, SFC, changing Windows features or roles, and repairing components. The CBS is a small component that typically does not have updates released every month.Solved19KViews0likes6Comments