There was a need to know the amount of licenses each nodes consumed. mesverrum came up with SWQL, however it didn't run against a large node count. SQL is running with reasonable speed
Once we have the license count, I'm sure it can be expanded to calculate the cost per node per module for change back to the department.
'<img src="/Orion/images/StatusIcons/Small-' + n.StatusLED +'"></>' +
'<a href="/Orion/View.aspx?View=NodeDetails&NetObject=N:'+cast(n.NodeID as varchar)+'">'+n.Caption+'</>' [Device Name]
,count(distinct i.caption) [NPM Lic]
,count(distinct ncm.nodeid) as [NCM Lic]
,count(distinct com.id)+count(distinct sql.ID)*50+count(distinct iis.ID)*30+count(distinct exc.ID)*50+count(distinct sch.ID)*5 as [SAM Lic]
, count(distinct udt.PortID) as [UDT Lic]
from nodes n with (NOLOCK)
left join interfaces i with (NOLOCK) on i.nodeid=n.nodeid
left join NCM_NodeProperties ncm with (NOLOCK) on ncm.Corenodeid=n.nodeid
left join APM_Application sam1 with (NOLOCK) on sam1.NodeID=n.nodeid
left join APM_ApplicationTemplate at with (NOLOCK) on at.id = sam1.templateid
left join APM_Application sam with (NOLOCK) on sam.NodeID=n.nodeid and (at.CustomApplicationType is null)
left join APM_Component com with (NOLOCK) on com.applicationid=sam.id
left join APM_Application sql with (NOLOCK) on sql.NodeID=n.nodeid and at.CustomApplicationType='absa'
left join APM_Application iis with (NOLOCK) on iis.NodeID=n.nodeid and at.CustomApplicationType='abia'
left join APM_Application exc (NOLOCK) on exc.NodeID=n.nodeid and at.CustomApplicationType='abxa'
left join APM_Application sch with (NOLOCK) on sch.NodeID=n.nodeid and at.CustomApplicationType='abta'
left join UDT_Port udt with (NOLOCK) on udt.nodeid=n.nodeid
group by n.caption, n.StatusLED, n.NodeID
order by n.caption