Version 1

    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

    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.

    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.

    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. 

    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