cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 7

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

 

 

0 Kudos
2 Replies
Level 12

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'