SQL UDT Report to pull out subnets or IP's

FormerMember

Hi

I created this report to pull out a range of IPaddresses, it could be adjusted to suite your needs by filtering for other types of data

  SELECT sr.[1],sr.[2],sr.[3],sr.[4],sr.[5],sr.[6],sr.[7],sr.[8],sr.[9],sr.[10],sr.[11],sr.[12],sr.[13],sr.[14],sr.[15],sr.[ViaLayer3] , (SELECT s.LastSuccessfulScan FROM UDT_Switch s WHERE s.NodeID = sr.[11]) as [SwitchLastSuccessfulScan]
, (SELECT r.LastSuccessfulScan
FROM UDT_Router r
WHERE r.NodeID = sr.[11]) as [RouterLastSuccessfulScan]
FROM (SELECT a.IPAddress as [1], d.DNSName as [2], e.MACAddress as [3], n.Caption as [4], p.Name as [5], p.PortDescription as [6], ep.VLANID as [7],
ep.LastSeen as [8], n.Status as [9], p.OperationalStatus as [10], n.NodeID as [11], p.PortID as [12], ep.ConnectionType as [13], ep.FirstSeen as [14], p.Flag as [15], 0 as [ViaLayer3]
FROM Nodes n
INNER JOIN UDT_Port p ON p.NodeID = n.NodeID AND p.Flag <> 1
INNER JOIN UDT_PortToEndpoint ep ON ep.PortID = p.PortID
INNER JOIN UDT_Endpoint e ON e.EndpointID = ep.EndpointID
LEFT JOIN UDT_IPAddress a ON e.EndpointID = a.EndPointID AND a.LastSeen is null
LEFT OUTER JOIN UDT_DNSName d ON d.IPAddress = a.IPAddress AND d.LastSeen is null
LEFT JOIN (
SELECT r.NodeID as RouterID, s.NodeID as SwitchID FROM UDT_PortToPort pp
INNER JOIN UDT_Port pr on pr.PortID = pp.Port2ID
INNER JOIN UDT_Port ps on ps.PortID = pp.Port1ID
LEFT OUTER JOIN UDT_Router r ON r.NodeID = pr.NodeID
LEFT OUTER JOIN UDT_Switch s ON s.NodeID = ps.NodeID
) RouterSwitch ON RouterSwitch.RouterID = a.RouterNodeID AND RouterSwitch.SwitchID = p.NodeID
WHERE ep.ConnectionType IS NOT NULL  AND ep.LastSeen is null AND (a.IPAddress LIKE '10.10.10.%')
           
UNION (
SELECT a.IPAddress as [1], d.DNSName as [2], e.MACAddress as [3], n.Caption as [4], p.Name as [5], p.PortDescription as [6], ep.VLANID as [7],
ep.LastSeen as [8], n.Status as [9], p.OperationalStatus as [10], n.NodeID as [11], p.PortID as [12], ep.ConnectionType as [13], ep.FirstSeen as [14], p.Flag as [15], 0 as [ViaLayer3]
FROM Nodes n
INNER JOIN UDT_Port p ON p.NodeID = n.NodeID AND p.Flag <> 1
INNER JOIN UDT_PortToEndpoint ep ON ep.PortID = p.PortID
INNER JOIN UDT_Endpoint e ON e.EndpointID = ep.EndpointID
LEFT JOIN UDT_IPAddress a ON e.EndpointID = a.EndPointID AND a.LastSeen is null
LEFT OUTER JOIN UDT_DNSName d ON d.IPAddress = a.IPAddress AND d.LastSeen is null
LEFT JOIN (
SELECT r.NodeID as RouterID, s.NodeID as SwitchID
FROM UDT_PortToPort pp
INNER JOIN UDT_Port pr on pr.PortID = pp.Port1ID
INNER JOIN UDT_Port ps on ps.PortID = pp.Port2ID
LEFT OUTER JOIN UDT_Router r ON r.NodeID = pr.NodeID
LEFT OUTER JOIN UDT_Switch s ON s.NodeID = ps.NodeID
) RouterSwitch ON RouterSwitch.RouterID = a.RouterNodeID AND RouterSwitch.SwitchID = p.NodeID
WHERE ep.ConnectionType IS NOT NULL  AND ep.LastSeen is null AND (a.IPAddress LIKE '10.10.10.%'))
UNION (
SELECT a.IPAddress as [1], d.DNSName as [2], e.MACAddress as [3], n.Caption as [4], '' as [5], '' as [6], '' as [7], a.LastSeen as [8], n.Status as [9], '' as [10], n.NodeID as [11], '' as [12], 3 as [13], a.FirstSeen as [14], '' as [15], 1 as [ViaLayer3]
FROM UDT_IPAddress a
LEFT JOIN UDT_Endpoint e ON a.EndpointID = e.EndPointID
LEFT JOIN Nodes n ON a.RouterNodeID = n.NodeID
LEFT JOIN UDT_DNSName d ON a.IPAddress = d.IPAddress
AND d.LastSeen is null
WHERE 1=1 AND a.LastSeen is null
AND (a.IPAddress LIKE '10.10.10.%')   ) ) sr ORDER BY sr.[8]

Have fun

Kind Regards

James

Bedrich.Michalek

Be aware that UDT 2.0 changed its database schema and the query won't work. Below is modified query which you may use for your report (adjust the WHERE condition at the bottom to suit your needs)

 

SELECT [T].*,
(
SELECT [S].[LastSuccessfulScan] AS C18
FROM dbo.UDT_NodeCapability AS S WITH(NOLOCK)
WHERE (([S].[Capability] = N'2') AND ([S].[NodeID] = [T].[NodeID]))
) AS [SwitchLastSuccessfulScan],
(
SELECT [R].[LastSuccessfulScan] AS C20
FROM dbo.UDT_NodeCapability AS R WITH(NOLOCK)
WHERE (([R].[Capability] = N'3') AND ([R].[NodeID] = [T].[NodeID]))
) AS [RouterLastSuccessfulScan]
FROM
(
    SELECT DISTINCT [T4].[IPAddress] AS [IPAddress], [T5].[DNSName] AS [DNSName], [T6].[MACAddress] AS [MACAddress], [T7].[Caption] AS [Caption], [T8].[Name] AS [PortName], [T8].[PortDescription] AS [PortDescription], [T9].[VlanID] AS [VLAN], [T9].[LastSeen] AS [LastSeen], [T7].[Status] AS [NodeStatus], [T8].[OperationalStatus] AS [OperationalStatus], [T7].[NodeID] AS [NodeID], [T8].[PortID] AS [PortID], [T9].[ConnectionType] AS [ConnectionType], [T9].[FirstSeen] AS [FirstSeen], [T8].[Flag] AS [Flag]
    FROM dbo.Nodes AS T7 WITH(NOLOCK)
    INNER JOIN dbo.UDT_Port AS T8 WITH(NOLOCK) ON (([T8].[NodeID] = [T7].[NodeID]) AND ([T8].[Flag] <> 1))
    INNER JOIN dbo.UDT_PortToEndpoint AS T9 WITH(NOLOCK) ON ([T9].[PortID] = [T8].[PortID])
    INNER JOIN dbo.UDT_Endpoint AS T6 WITH(NOLOCK) ON ([T6].[EndpointID] = [T9].[EndpointID])
    LEFT JOIN dbo.UDT_IPAddress AS T4 WITH(NOLOCK) ON (([T6].[EndpointID] = [T4].[EndpointID]) AND ([T4].[LastSeen] IS NULL))
    LEFT JOIN dbo.UDT_DNSName AS T5 WITH(NOLOCK) ON (([T5].[IPAddress] = [T4].[IPAddress]) AND ([T5].[LastSeen] IS NULL))
    WHERE (([T9].[ConnectionType] IS NOT NULL) AND ([T9].[LastSeen] IS NULL))

    UNION

    SELECT DISTINCT [T10].[IPAddress] AS [IPAddress], [T11].[DNSName] AS [DNSName], [T12].[MACAddress] AS [MACAddress], [T13].[Caption] AS [Caption], '' AS [PortName], '' AS [PortDescription], '' AS [VLAN], [T10].[LastSeen] AS [LastSeen], [T13].[Status] AS [NodeStatus], '' AS [OperationalStatus], [T13].[NodeID] AS [NodeID], '' AS [PortID], 3 AS [ConnectionType], [T10].[FirstSeen] AS [FirstSeen], '' AS [Flag]
    FROM dbo.UDT_IPAddress AS T10 WITH(NOLOCK)
    LEFT JOIN dbo.UDT_Endpoint AS T12 WITH(NOLOCK) ON ([T10].[EndpointID] = [T12].[EndpointID])
    LEFT JOIN dbo.Nodes AS T13 WITH(NOLOCK) ON ([T10].[RouterNodeID] = [T13].[NodeID])
    LEFT JOIN dbo.UDT_DNSName AS T11 WITH(NOLOCK) ON (([T10].[IPAddress] = [T11].[IPAddress]) AND ([T11].[LastSeen] IS NULL))
    WHERE ((1 = 1) AND ([T10].[LastSeen] IS NULL))
) T
WHERE [T].[IPAddress] like '10.199.4.%'