4 Replies Latest reply on Jun 26, 2014 12:01 PM by oliver.grist

    Orion.Nodes.NodeId vs Orion.PM.WsusNodes.NodeId

    oliver.grist

      Hello,

       

      I am looking to pull some patching information from Patch Manager to dispay within another system.

       

      Having a poke about the various table I notice the NodeID is a long string and not the 4 digit integer I was hoping for!

       

      I am aware Patch Manager was/is a seperate system but I am struggling to see a column I can use to join from Orion to PM, other than IP Address (a Node can have many and they can change).

       

      Currently I have a list of nodes (as integers from the Orion.Nodes.NodeId field) and for each one I want to check if they have patching and if so pull the various numbers, ideally as an INNER JOIN within the query.

       

      If you click a node with Patch Manager so the URL reads: /Orion/PM/WsusNodeDetails.aspx?NetObject=WSUSN: the "Node Status" panel links across to the Node page, using the integer Id, how is this worked out - is there a mapping I am overlooking?

       

      As a temporary measure we have named the patching groups "Account Code - Group Number" so assuming an account code XXXX we can use the query to get the required information:

       

      SELECT NodeId, IPAddress, SafeName as GroupName, FullDomainName, DownloadedCount, FailedCount, InstalledCount, InstalledPendingRebootCount, NotApplicableCount, NotInstalledCount, UnknownCount

      FROM Orion.PM.WsusGroups

      INNER JOIN Orion.PM.WsusNodes ON TargetGroup LIKE CONCAT('%',SafeName,'%')

      WHERE SafeName Like 'XXXX%'

      ORDER BY SafeName, FullDomainName

       

      Any help is much appreciated!

       

      Thanks,

      Oli.

        • Re: Orion.Nodes.NodeId vs Orion.PM.WsusNodes.NodeId
          Lawrence Garvin
          I am looking to pull some patching information from Patch Manager to dispay within another system.


          The easiest way to achieve this objective is to define a report using the Report Builder that gives you exactly what you want, and then load the generated SQL code from the Report Properties screen.

          Copy and paste the SQL code to whatever resource you want to use it in, create a SQL Login that has READ-ONLY rights to the EminentWare database, and run the query as needed.


          5-28-2014 3-39-55 PM.png


          FROM Orion.PM.WsusGroups INNER JOIN Orion.PM.WsusNodes

           

          I don't know that these objects actually exist in the API. Did you find them referenced in documentation somewhere?

            • Re: Orion.Nodes.NodeId vs Orion.PM.WsusNodes.NodeId
              oliver.grist

              Hi Lawrence,

               

              Thanks for the advice, I was unaware of the reporting capabilities for Patch Manager. I will hassle jmiddleton for a login and try this out.

               

              I downloaded SWQL Studio 1.8.116.0 and it lists the PM tables, it is possible to query most but not all off them (see screenshot below).

               

              I didnt check the documentation, I just looked at the tables (and attempted to) and figured out how they link together.

               

                • Re: Orion.Nodes.NodeId vs Orion.PM.WsusNodes.NodeId
                  Lawrence Garvin

                  Aha!

                   

                  The tables are there to hold cached data from the Patch Manager server for display in the web console.

                  I don't even know that any of those tables would have a complete set of data, as their only purpose is to provide sufficient data to populate the resources displayed on the web page.

                    • Re: Orion.Nodes.NodeId vs Orion.PM.WsusNodes.NodeId
                      oliver.grist

                      With the following query I am seeing ~ 95% match using the WsusNodes.IPAddress and Orion.Nodes.IPAddress columns.

                      There are only a handful that have an IP Address in WsusNodes that doesnt match the Nodes table.

                       

                      Can the WsusNodes IPAddress ever be internal? (I have not seen any instances of this yet), as this would ruin my plans.

                       

                      Revised query is:

                      SELECT WsusNodes.NodeId, Nodes.NodeId, Nodes.IpAddress as NodeIPAddress, WsusNodes.IpAddress as WSUSNODEIPADDRESS, SafeName as GroupName, FullDomainName, SafeName, DownloadedCount, FailedCount, InstalledCount, InstalledPendingRebootCount, NotApplicableCount, NotInstalledCount, UnknownCount

                      FROM Orion.PM.WsusGroups

                      INNER JOIN Orion.PM.WsusNodes ON TargetGroup LIKE CONCAT('%',SafeName,'%')

                      LEFT JOIN Orion.Nodes ON Nodes.IpAddress = WsusNodes.IpAddress

                      ORDER BY SafeName, FullDomainName