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.
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?
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 188.8.131.52 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.
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.
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
INNER JOIN Orion.PM.WsusNodes ON TargetGroup LIKE CONCAT('%',SafeName,'%')
LEFT JOIN Orion.Nodes ON Nodes.IpAddress = WsusNodes.IpAddress
ORDER BY SafeName, FullDomainName