So wondering if anybody can help with a SQL report I'm trying to create. I know under License Details I can see a breakdown of how many SAM licenses are being used total. But what I am trying to do is my company is starting a charge back model to departments based on what we monitor through SolarWinds. I am trying to create a report that shows how many components are being used grouped by a Node Custom Property we have setup. Below is my current SQL query:
SELECT NodesCustomProperties.Owner AS Owner, COUNT(Distinct(APM_AlertsAndReportsData.ComponentId)) AS COUNT_of_ComponentID
FROM NodesCustomProperties LEFT JOIN APM_AlertsAndReportsData ON (NodesCustomProperties.NodeID = APM_AlertsAndReportsData.NodeId)
WHERE APM_AlertsAndReportsData.ComponentStatus not like 'Unmanaged' AND APM_AlertsAndReportsData.ComponentStatus not like 'Not Running' AND APM_AlertsAndReportsData.ComponentStatus not like 'Offline'
GROUP BY Nodes.Owner
The query works but the issue I'm noticing is the numbers are way off from what is displayed on the License Details page for SAM. For instance one of my owner groups shows to have over 10,000+ Components but my total on License Details is only 7,088. I created the Where clause to try and filter out possibly some of the Components that were no longer being used. It dropped the numbers a bit but still showing quite a bit more components than it appears I'm truly using.
Is there a better table I should be using for running this query than APM_AlertsAndReportsData.ComponentStatus? I started off by creating the report in Orion Report Writer then pulled the SQL from there which is where I found the View to gather my component count. Then began editing on my own. I'm not a pro by an means at SQL so any help would be appreciated.