UDT Report help needed please

Hi guys, I'm not a SQL guy and therefore would appreciate help with a report needed for UDT, as there isn't one provided out of the box.

We are needing to create a UDT report that lists physical user port and VLAN membership for users on the network.  The complexity of this is that we need to exclude any wireless devices or wireless user MAC addresses, and also exclude any other network devices.  Therefore we just need to include user devices connected physically to switches on site.

I've worked with NPM for over 10 years, but the UDT module is new to me.  Can you also advise what UDT needs from NPM to function, i.e. do we need to enable VLAN monitoring within NPM for all switch nodes to enable UDT to be able to query on VLAN usage for our report?

Thanks a lot!

Cheers Paul

  • Here are two queries I created a few years back. 

    Notice that there is a line that says "AND uds.accountname not like '%any-user-account-you-do-not-want-to-see%'"

    You can delete this line or add as necessary.  For me, I use this to remove accounts such as service accounts so I don't see them in the reports. 

    Wired Devices

    SELECT pec.[FirstSeen]
    ,pec.[PortID]
    ,pec.[EndpointID]
    ,pec.[VlanID]
    ,pec.[ConnectionType]
    ,pec.[ID]
    ,n.[Caption] AS Switch
    ,p.[Name] AS Switch_Port
    ,ipc.[IPAddress]
    ,e.[MACAddress]
    ,ipc.ipaddressID
    ,dnc.DNSName
    ,uia.[UserID]
    ,uds.[AccountName]
    ,uds.[Title]
    ,uds.[Department]
    ,uds.[Office]
    ,DATEADD(HOUR, -6, uia.[LogonDateTime]) AS LogonDateTime

    FROM [SolarWindsOrion].[dbo].[UDT_PortToEndpointCurrent] pec
    Join [SolarWindsOrion].[dbo].[UDT_Port] p on p.PortID = pec.PortID
    Join [SolarWindsOrion].[dbo].[NodesData] n on n.NodeID = p.nodeid
    Join [SolarWindsOrion].[dbo].[UDT_Endpoint] e on e.EndpointID = pec.EndpointID
    Join [SolarWindsOrion].[dbo].[UDT_IPAddressCurrent] ipc on ipc.EndpointID = pec.EndpointID
    Join [SolarWindsOrion].[dbo].[UDT_DNSNameCurrent] dnc on dnc.IPAddressID = ipc.IPAddressID
    Join [SolarWindsOrion].[dbo].[UDT_UserToIPAddressCurrent] uia on uia.IPAddress = ipc.IPAddress
    Join [SolarWindsOrion].[dbo].[UDT_User] uds on uds.UserID = uia.UserID
    WHERE pec.ConnectionType = '1'


    AND uia.LogonDateTime IN (SELECT TOP 2 LogonDateTime FROM [SolarWindsOrion].[dbo].[UDT_UserToIPAddressCurrent] i WHERE i.IPAddress = ipc.IPAddress ORDER BY LogonDateTime DESC)
    AND uia.LogonDateTime = (SELECT MAX(LogonDateTime) FROM [SolarWindsOrion].[dbo].[UDT_UserToIPAddressCurrent] i WHERE i.IPAddress = ipc.IPAddress AND i.UserID = uia.UserID)
    AND uds.accountname not like '%any-user-account-you-do-not-want-to-see%'


    --AND LogonDateTime < CURRENT_TIMESTAMP
    --and LogonDateTime > CURRENT_TIMESTAMP-1.0/1.0
    order by n.[Caption],dnc.DNSName,uia.[LogonDateTime] DESC

    Wireless


    SELECT wc.[ID]
    ,wc.[Index]
    ,wc.[NodeID]
    ,wc.[Name]
    ,wap.IPAddress
    ,uia.[UserID]
    ,uds.[AccountName]
    ,uds.[Title]
    ,uds.[Department]
    ,uds.[Office]
    ,dnc.[DNSName]
    ,wc.[MACAddress]
    ,wc.[IPAddress]
    ,wic.Name AS Controller
    ,wap.Name AS AP
    ,wc.[SSID] AS SSID


    ,DATEADD(HOUR, -6, uia.[LogonDateTime]) AS LogonDateTime


    FROM [SolarWindsOrion].[dbo].[Wireless_Clients] wc
    Join [SolarWindsOrion].[dbo].[Wireless_Interfaces] wi on wi.ID = wc.ParentID
    Join [SolarWindsOrion].[dbo].[Wireless_AccessPoints] wap on wap.ID = wi.ParentID
    Join [SolarWindsOrion].[dbo].[Wireless_Controllers] wic on wic.NodeID = wc.NodeID
    Join [SolarWindsOrion].[dbo].[UDT_UserToIPAddressCurrent] uia on uia.IPAddress = wc.IPAddress
    Join [SolarWindsOrion].[dbo].[UDT_User] uds on uds.UserID = uia.UserID
    Join [SolarWindsOrion].[dbo].[UDT_DNSNameCurrent] dnc on dnc.IPAddress = wc.IPAddress

    Where

    wc.IPAddress not like '192.168.%'
    AND uds.accountname not like '%any-user-account-you-do-not-want-to-see%'

    AND uia.LogonDateTime IN (SELECT TOP 2 LogonDateTime FROM [SolarWindsOrion].[dbo].[UDT_UserToIPAddressCurrent] i WHERE i.IPAddress = uia.IPAddress ORDER BY LogonDateTime DESC)
    AND uia.LogonDateTime = (SELECT MAX(LogonDateTime) FROM [SolarWindsOrion].[dbo].[UDT_UserToIPAddressCurrent] i WHERE i.IPAddress = uia.IPAddress AND i.UserID = uia.UserID)

    --AND LogonDateTime < CURRENT_TIMESTAMP
    --and LogonDateTime > CURRENT_TIMESTAMP-1.0/1.0
    order by dnc.DNSName,uia.[LogonDateTime] DESC

  • Forgot to mention.   You will need to add all of your read-only domain controllers to show user accounts.  If you already monitoring the DCs you can edit the nodes and check 'Poll to monitor Active Directory users logged in to your network'