Version 1

    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.

     

    Select

    '<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