2 Replies Latest reply on Apr 22, 2015 10:15 AM by clarsen

    Does Anyone Know Where I Can Find Orion / PM SQL Query Code?

    clarsen

      I need to customize the Patch Manager "Vulnerable Machines" Orion query into two queries, one for servers and one for workstations, and I also need to limit each to showing machines having >0 outstanding patches.  I wouldn't mind including some other counts (patches downloaded, patches pending reboot, etc.) in the same or another custom query table, but I need to examine the original SQL code before I can begin with the stick-poking of a copy thereof.  Can anyone help?

       

      Thanks,

      Curtis

        • Re: Does Anyone Know Where I Can Find Orion / PM SQL Query Code?
          vdubn

          The name of your db may be different, but here is a QQL query I have created for just what you are looking for, these generate reports in Orion, and you could probably build a custom view on a page with these as well.  Hope this helps:

           

          List of Non-Compliant Machines (our laptops all start with LAP-, hence setting the LIKE 'LAP-%', change this to whatever naming convention you use.  Also, I have commented out the lines that will give you Workstations and Servers with a "Pending Reboot" status, as well as a Failed install or Needing updates.

           

          SELECT [FullDomainName]

                ,[IPAddress]

                ,[ComputerRole]

                ,[LastSyncTime]

                ,[LastSyncResult]

                ,[DownloadedCount]

                ,[FailedCount]

                ,[InstalledCount]

                ,[InstalledPendingRebootCount]

                ,[NotInstalledCount]

                ,[UnknownCount]

            FROM PM_WsusNodes

           

          --Laptops that are Non-Compliant

          WHERE FulldomainName LIKE 'LAP-%' AND (FailedCount != '0' OR NotInstalledCount != '0' OR InstalledPendingRebootCount != '0' OR UnknownCount != '0')

           

          --Machines that are up to date

          --WHERE ComputerRole LIKE 'Workstation' AND (FailedCount = '0' AND NotInstalledCount = '0' AND InstalledPendingRebootCount = '0' AND UnknownCount = '0')

           

          --Workstations that have Pending Reboots

          --WHERE ComputerRole LIKE 'Workstation' AND InstalledPendingRebootCount != '0'

           

          --Servers that have Pending Reboots

          --WHERE ComputerRole LIKE 'Server' AND InstalledPendingRebootCount != '0'

           

          --Workstations that Need Patches

          --WHERE ComputerRole LIKE 'Server' AND NotInstalledCount != '0'

           

          --Servers that have Failed installs

          --WHERE ComputerRole LIKE 'Server' AND FailedCount != '0'

          --GROUP BY

          ORDER By FullDomainName

          1 of 1 people found this helpful
          • Re: Does Anyone Know Where I Can Find Orion / PM SQL Query Code?
            clarsen

            vdubn - Thank much for these examples: I will definitely use them and extensions of them once the Patch Manager data issue has been fixed.  Let me explain:

             

            At my work place we review new patches for applicability and content before approving them for specific computer groups.  In short, we only deploy approve patches to any given machine, not all possible patches that machine could have.

             

            Unfortunately there's a problem with the process that copies data from the Patch Manager DB to the NPM DB: it ignores all Approved content in source table it copies data from.  That makes everything on the web page effectively useless to us, and also eliminates any potential custom SQL resources we may want to set up (as well as any custom alerts).  This is really disappointing -- and frankly, I don't understand how it could have been missed, because my patch process model is very common -- but it explains why the web page numbers have always been skewed.  The fix for this is simple (copy *all* columns from the given EminentWare table to the target NPM table) but we've been told that the request has been submitted to Development, but that there's no way to know how long this will take to complete.

             

            Fortunately, in the meantime I can run a regularly-scheduled report that gets emailed to myself and our Operations folks.  While it's not real-time at least the needed Approved count numbers are there, and they're accurate.  I'll be (very!) happy to use custom SQL resources, alerts, etc., once they're all working.

             

            Thanks again, and I hope the info helps any folks who were looking for the same thing.