SWIS API query is not fetching complete count from Solarwinds.

RahulRAJAS · 3 hours ago

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