4 Replies Latest reply on Dec 1, 2011 6:42 AM by jawells

    SQL UDT Report to pull out subnets or IP's

    jawells

      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

        • Re: SQL UDT Report to pull out subnets or IP's
          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.%'