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

UDT scripts - user to IP, no of devices user is logged on, locate MAC, IP address conflicts (swql)

Some of these resources are already there, but it's always fun to recreate them in SWQL.  Also it's much quicker to get to using specific resource and much easier to customize.

User to IP -  resolve user name to IP and hot many times did the user logged on

pastedImage_0.png

SELECT u.AccountName, u.IPAddresses.LogonCount, u.IPAddresses.IPAddress, n.caption

FROM Orion.UDT.User u

left join Orion.nodes n on n.IP_Address=u.IPAddresses.IPAddress

where u.AccountName like '%${SEARCH_STRING}%'

or u.IPAddresses.IPAddress like '%${SEARCH_STRING}%'

or n.caption like '%${SEARCH_STRING}%'

order by u.AccountName

Number of Devices User is logged on - useful in knowing how many devices is the user logged to.  many users but never properly log off.

pastedImage_3.png

SELECT top 100

u.AccountName, count(u.IPAddresses.IPAddress) as [No. of Devices]

FROM Orion.UDT.User u

where u.AccountName like '%${SEARCH_STRING}%'

group by u.AccountName

order by u.AccountName

Locate MAC - a dup of an included functionality.  But this one is nice and compact.

pastedImage_4.png

select DISTINCT

n.Caption as [Device]

--,n.DetailsUrl as [_LINKFOR_Device]

--,'/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Device]

,p.name as [Port]

--,p.StatusLED as [_IconFor_Port]

--,p.DetailsUrl as [_LinkFor_Port]

--, p.Description as [Port Name]

--,ip.IPAddress

,e.MACAddress as [MAC]

,case p2e.VlanID when 0 then null else p2e.VlanID end as [VLAN]

,CASE [p2e].ConnectionType WHEN 1 THEN 'D' WHEN 2 THEN 'I' ELSE 'U' END AS ConnType

from Orion.Nodes n

inner join Orion.UDT.Port as p on p.NodeID=n.NodeID and p.Flag <>1

inner join Orion.UDT.PortToEndpointCurrent as p2e on p2e.PortID = p.PortID

inner join Orion.UDT.Endpoint as e on e.EndpointID = p2e.EndpointID

where n.caption like '%${SEARCH_STRING}%'

or e.MACAddress like  '%${SEARCH_STRING}%'

or p2e.vlanid like  '%${SEARCH_STRING}%'

order by n.Caption

IP Address conflict - another duplicate resource.  It was fun reverse-engineering it. 

pastedImage_9.png

SELECT

c.IPAddress as [IP Address]

,concat('/Orion/NetPerfMon/NodeDetails.aspx?NetObject=IPAMN:',c.IPNodeID) as [_LinkFor_IP Address]

, case when c.IPStatus=1 then '/Orion/IPAM/res/images/sw/icon.Ip.Used.gif'

       when c.IPStatus=2 then '/Orion/IPAM/res/images/sw/icon.Ip.Available.gif'

       when c.IPStatus=4 then '/Orion/IPAM/res/images/sw/icon.Ip.Reserved.gif'

       when c.IPStatus=8 then '/Orion/IPAM/res/images/sw/icon.Ip.Transient.gif'

        end as [_IconFor_IP Address]

,'' as [Type] -- c.ConflictType

,concat('/Orion/IPAM/res/images/sw/',c.ConflictTypeIcon) as [_IconFor_Type]

,c.SubnetAddress as [Subnet]

,concat('/Orion/IPAM/res/images/sw/icon.Subnet.',c.SubnetStatus,'.gif') as [_IconFor_Subnet]

,tolocal(max(c.ConflictTimeUTC)) as [Time of Conflict]

,c.AssignedMac as [AssignedMac]

,concat('/NetPerfMon/images/Vendors/',c.AssignMACVendIcon) as [_IconFor_AssignedMac]

, c.ConflictingMac as [ConflictingMac]

,concat('/NetPerfMon/images/Vendors/',c.ConflictMACVendIcon) as [_IconFor_ConflictingMac]

--,c.SubnetStatus, c.IPStatus, c.InstanceType

FROM IPAM.Conflict c

--where ipaddress like '10.33.4.31'

group by  c.IPAddress, c.ConflictType, c.SubnetAddress, c.ConflictStatus, c.AssignedMac, c.ConflictingMac, c.ConflictTypeIcon, c.AssignMACVendIcon, c.ConflictMACVendIcon, c.GroupType, c.SubnetStatus, c.IPStatus, c.InstanceType, c.IPNodeID

order by max(c.ConflictTimeUTC) DESC

Thanks

Amit

Version history
Revision #:
1 of 1
Last update:
‎04-22-2018 01:39 PM
Updated by: