1. BrightGauge Support
  2. Community
  3. Community - Community Queries for SQL based Datasets

Patch Updates Recently Installed

Avatar
Carl Chatten

To make charts showing which machines have recently had patches applied use the SQL query below and then use the gauges to filter the OperatingSystem to show servers or endpoints.

The query shows all patch installs from the last 7 days for all patches that are approved for install, are not superseded and has a classification of either: Critical/Important/Security/Roll-up:

 

SELECT [agentGuid],[ComputerName],[ReverseGroupName],[GroupName],[OperatingSystem],[LastLoggedOnUser]
      ,[KBArticle],[Title],[Product],[UpdateClassification],[UpdateClassificationDescription],[ReleaseDate]
      ,[ApprovalStatus],[ApprovalStatusDescription],[IsSuperseded],[PatchAppliedFlag],[PatchStatus],[PatchStatusDescription]
      ,[InstallDate],[Description],NEWID() AS id
  FROM [ksubscribers].[dbo].[vPatchStatus]
  WHERE [ApprovalStatus] = 0 AND [PatchAppliedFlag] = 1 AND [IsSuperseded] = 0 AND [UpdateClassification] < 200 AND (InstallDate >= DATEADD(d, - 7, GETDATE()))

Didn't find what you were looking for?

New post
Would you like to vote for this feature?
1 out of 1 found this helpful

Comments

2 comments

Sort by Date Votes
  • Avatar
    Brian Manning

    Carl,

    Thanks for the query!  I've made a slight modification that will also return a "Client" column so it can be tied better to reports.

     

    SELECT [agentGuid],[ComputerName],[ReverseGroupName],vPatchStatus.[GroupName],k.orgname AS Client,[OperatingSystem],[LastLoggedOnUser]
          ,[KBArticle],[Title],[Product],[UpdateClassification],[UpdateClassificationDescription],[ReleaseDate]
          ,[ApprovalStatus],[ApprovalStatusDescription],[IsSuperseded],[PatchAppliedFlag],[PatchStatus],[PatchStatusDescription]
          ,[InstallDate],[Description],NEWID() AS id
      FROM [ksubscribers].[dbo].[vPatchStatus]
     INNER JOIN machGroup ON machGroup.reverseName = vPatchStatus.ReverseGroupName
     INNER JOIN kasadmin.org k ON k.id = machgroup.orgFK
     WHERE [ApprovalStatus] = 0 AND [PatchAppliedFlag] = 1 AND [IsSuperseded] = 0 AND [UpdateClassification] < 200 AND (InstallDate >= DATEADD(d, - 7, GETDATE()))
     

    0
  • Avatar
    Bruce Berger

    I'm a BG newbie and this may be exactly what I'm after. How do I use it? My datasets are from LogicMonitor

    0