3 Replies Latest reply on Nov 11, 2016 9:47 AM by frgpugs

    Patch Reporting Question

    lufffunk

      I have a Windows Inventory report which has been build using the standard SolarWinds Web-based reporting method. I would like to include the patch compliance based on approved patches only in that report but I don't see where I can pull that information from? I know when you look at a node in the web interface it has that information on it as it shows the approve status pie chart. Does anyone know the best way to pull all this information together? Do I have to link to the Patch Manager database? Thanks Mike

        • Re: Patch Reporting Question
          frgpugs

          This is taken from the system report Computer Update Status Approved Updates Not Installed that is listed under Windows Server Update Services Analytics.  Is this what you were looking for?  Its a query on the WSUS SQL DB.

           

           

          SELECT DISTINCT dbo.ComputerUpdateStatus.domainworkgroup AS [Domain or Workgroup (Update Server)], dbo.ComputerUpdateStatus.netbiosname AS [Name (Update Server)], dbo.ComputerUpdateStatus.title AS [Update Title], dbo.ComputerUpdateStatus.fulldomainname AS [Computer Name], (CASE WHEN dbo.ComputerUpdateStatus.UpdateApprovalAction IS NULL THEN NULL

          ELSE

          CASE dbo.ComputerUpdateStatus.UpdateApprovalAction

          WHEN 0 THEN 'Install'

          WHEN 1 THEN 'Uninstall'

          WHEN 3 THEN 'NotApproved'

          WHEN 2147483647 THEN 'All'

          ELSE 'Unknown'

          END

          END

          ) AS [Update Approval Action (Friendly Name)], (CASE WHEN dbo.ComputerUpdateStatus.UpdateInstallationState IS NULL THEN NULL

          ELSE

          CASE dbo.ComputerUpdateStatus.UpdateInstallationState

          WHEN 0 THEN 'Unknown'

          WHEN 1 THEN 'NotApplicable'

          WHEN 2 THEN 'NotInstalled'

          WHEN 3 THEN 'Downloaded'

          WHEN 4 THEN 'Installed'

          WHEN 5 THEN 'Failed'

          WHEN 6 THEN 'Installed Pending Reboot'

          ELSE 'Unknown'

          END

          END

          ) AS [Update Installation State (Friendly Name)] FROM dbo.ComputerUpdateStatus WHERE dbo.ComputerUpdateStatus.UpdateApprovalAction = 0  AND dbo.ComputerUpdateStatus.UpdateInstallationState = 2  ORDER BY dbo.ComputerUpdateStatus.title ASC 

          • Re: Patch Reporting Question
            lufffunk

            I've managed to use an external SQL query now to pull the data I was reporting in a NPM Web Report and join it to the Patch Manager DB, and also join to my McAfee database to pull a consolidated Patch and AV compliance report. What I wanted to do was be able to have linked my SolarWinds web report to the other databases rather than build a dedicated query to do the same thing. Thanks for replying!