SWIS API query is not fetching complete count from Solarwinds.

[RahulRAJAS]

Hi All,
We are using SWIS API to fetch CI data from Solarwinds, we got the query as follows from the Solarwinds team and they said they could find ~22000 CI records which includes duplicates and by excluding duplicates they could filter 1909 CIs.
But when we used that query in ServiceNow we saw ~1Lakh plus records flowing on to the staging table and still loading but as we have previously faced Load issue on the MID Server we have killed the transaction. We went back to check on the results and got to know on Solarwinds due to multiple MAC addresses added to the same CI or node it created those many records. So we have alter the query to fetch only one MAC address version of CI and ordered by IP Address and re run the query then we fetched ~97000 records onto staging table and as we used name as coalsce field we could fetch 1895 unique records.
But we are still lagging 14 records when compare to solarwinds count.

Original query shared by the Solarwinds team

SELECT DISTINCT
    n.NodeName AS [Name],
    n.IP_Address AS [ip_address],
    n.MachineType AS [model],
    n.SysName AS [dns_name],     
    'solarwinds' AS [discovery_source],
    m.MAC AS [mac_address],
    a.OSVersion AS [os_version],
    a.Domain AS [domain], 
    a.ServicePack AS [service_pack],
    p.NumberOfCores AS [cpu_core_count],
    TOSTRING(p.SpeedMHz) + ' MHz' AS [cpu_speed],
    p.Name AS [cpu_model],
    TOSTRING(ROUND(n.TotalMemory / 1073741824.0, 2)) + ' GB' AS [RAM],
    c.Device_Category AS [Device_Category], 
    c.DeviceType AS [device_type], 
    c.OwningTeam AS [owned_by], 
    c.Region AS [Region], 
    c.Site AS [site], 
    c.SiteCategory AS [site_category],
    c.Custom_Serial_Number AS [serial_number],
    c.OwningTeam AS [contact],
    c.Custom_Serial_Number AS [asset_tag]
 
FROM 
    Orion.NodesCustomProperties AS c
JOIN 
    Orion.Nodes AS n ON c.NodeID = n.NodeID
LEFT JOIN
    Orion.AssetInventory.ServerInformation AS a ON a.NodeID = n.NodeID
LEFT JOIN
    Orion.NodeMACAddresses AS m ON m.NodeID = n.NodeID
LEFT JOIN
    Orion.AssetInventory.Processor AS p ON p.NodeID = n.NodeID

[Ravi Gaurav]

Hi @RahulRAJAS 

Try The below SQL :
SELECT
n.NodeName AS [Name],
n.IP_Address AS [ip_address],
n.MachineType AS [model],
n.SysName AS [dns_name],
'solarwinds' AS [discovery_source],
(
SELECT TOP 1 MAC
FROM Orion.NodeMACAddresses m
WHERE m.NodeID = n.NodeID
ORDER BY MAC
) AS [mac_address],
a.OSVersion AS [os_version],
a.Domain AS [domain],
a.ServicePack AS [service_pack],
(
SELECT TOP 1 NumberOfCores
FROM Orion.AssetInventory.Processor p
WHERE p.NodeID = n.NodeID
ORDER BY NumberOfCores DESC
) AS [cpu_core_count],
(
SELECT TOP 1 TOSTRING(p.SpeedMHz) + ' MHz'
FROM Orion.AssetInventory.Processor p
WHERE p.NodeID = n.NodeID
ORDER BY SpeedMHz DESC
) AS [cpu_speed],
(
SELECT TOP 1 Name
FROM Orion.AssetInventory.Processor p
WHERE p.NodeID = n.NodeID
ORDER BY Name
) AS [cpu_model],
TOSTRING(ROUND(n.TotalMemory / 1073741824.0, 2)) + ' GB' AS [RAM],
c.Device_Category AS [Device_Category],
c.DeviceType AS [device_type],
c.OwningTeam AS [owned_by],
c.Region AS [Region],
c.Site AS [site],
c.SiteCategory AS [site_category],
c.Custom_Serial_Number AS [serial_number],
c.OwningTeam AS [contact],
c.Custom_Serial_Number AS [asset_tag]
FROM Orion.NodesCustomProperties AS c
JOIN Orion.Nodes AS n ON c.NodeID = n.NodeID
LEFT JOIN Orion.AssetInventory.ServerInformation AS a ON a.NodeID = n.NodeID

[RahulRAJAS]

Hi Ravi, thanks for the reply.
I have tried this as well, but of no use I have also used MAX() as well. None of these limited the count until I have added where.

[Ravi Gaurav]

Instead of joining directly to MAC or Processor, pull only one value via subselect:

SELECT
n.NodeName AS [Name],
n.IP_Address AS [ip_address],
n.MachineType AS [model],
n.SysName AS [dns_name],
'solarwinds' AS [discovery_source],
(
SELECT TOP 1 MAC
FROM Orion.NodeMACAddresses m
WHERE m.NodeID = n.NodeID
ORDER BY MAC
) AS [mac_address],
(
SELECT TOP 1 NumberOfCores
FROM Orion.AssetInventory.Processor p
WHERE p.NodeID = n.NodeID
ORDER BY NumberOfCores DESC
) AS [cpu_core_count],
(
SELECT TOP 1 TOSTRING(p.SpeedMHz) + ' MHz'
FROM Orion.AssetInventory.Processor p
WHERE p.NodeID = n.NodeID
ORDER BY SpeedMHz DESC
) AS [cpu_speed],
(
SELECT TOP 1 Name
FROM Orion.AssetInventory.Processor p
WHERE p.NodeID = n.NodeID
ORDER BY Name
) AS [cpu_model],
TOSTRING(ROUND(n.TotalMemory / 1073741824.0, 2)) + ' GB' AS [RAM],
a.OSVersion AS [os_version],
a.Domain AS [domain],
a.ServicePack AS [service_pack],
c.Device_Category AS [Device_Category],
c.DeviceType AS [device_type],
c.OwningTeam AS [owned_by],
c.Region AS [Region],
c.Site AS [site],
c.SiteCategory AS [site_category],
c.Custom_Serial_Number AS [serial_number],
c.Custom_Serial_Number AS [asset_tag]
FROM Orion.NodesCustomProperties AS c
JOIN Orion.Nodes AS n ON c.NodeID = n.NodeID
LEFT JOIN Orion.AssetInventory.ServerInformation AS a ON a.NodeID = n.NodeID


This way, each NodeID = 1 row only, no matter how many MACs/CPUs exist.

[svirkar420]

Hi @RahulRAJAS , Can you try this code and let me know what happens?
SELECT DISTINCT
ISNULL(n.NodeName, n.IP_Address) AS [Name], -- ensures coalesce works
n.IP_Address AS [ip_address],
n.MachineType AS [model],
n.SysName AS [dns_name],
'solarwinds' AS [discovery_source],
(
SELECT TOP 1 m.MAC
FROM Orion.NodeMACAddresses m
WHERE m.NodeID = n.NodeID
ORDER BY m.InterfaceIndex
) AS [mac_address],
a.OSVersion AS [os_version],
a.Domain AS [domain],
a.ServicePack AS [service_pack],
(
SELECT COUNT(*) FROM Orion.AssetInventory.Processor p
WHERE p.NodeID = n.NodeID
) AS [cpu_core_count],
(
SELECT TOP 1 TOSTRING(p.SpeedMHz) + ' MHz'
FROM Orion.AssetInventory.Processor p
WHERE p.NodeID = n.NodeID
) AS [cpu_speed],
(
SELECT TOP 1 p.Name
FROM Orion.AssetInventory.Processor p
WHERE p.NodeID = n.NodeID
) AS [cpu_model],
TOSTRING(ROUND(n.TotalMemory / 1073741824.0, 2)) + ' GB' AS [RAM],
c.Device_Category AS [Device_Category],
c.DeviceType AS [device_type],
c.OwningTeam AS [owned_by],
c.Region AS [Region],
c.Site AS [site],
c.SiteCategory AS [site_category],
c.Custom_Serial_Number AS [serial_number],
c.OwningTeam AS [contact],
c.Custom_Serial_Number AS [asset_tag]
FROM
Orion.NodesCustomProperties AS c
JOIN
Orion.Nodes AS n ON c.NodeID = n.NodeID
LEFT JOIN
Orion.AssetInventory.ServerInformation AS a ON a.NodeID = n.NodeID;


If this solution helped you Please Mark this solution as accepted and helpful as it will be helpful for other users as well.
Best Regards.
Saurabh V.