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