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

SAM Licenses by Group and Node

SAM License Usage by App and Group:

SELECT

c.Name AS [GROUP NAME]

,a.Name AS [APPLICATION NAME]

,CASE

    WHEN a.CustomApplicationType = 'ABXA' THEN '50'

    WHEN a.CustomApplicationType = 'ABSA' THEN '50'

    WHEN a.CustomApplicationType = 'ABIA' THEN '30'

    WHEN a.CustomApplicationType = 'ABTA' THEN '5'

    ELSE COUNT(a.Components.ComponentID)

END AS [LICENSES]

,CASE

    WHEN a.Unmanaged = 1 THEN 'YES'

    ELSE 'NO'

END AS [UNMANAGED?]

FROM Orion.APM.Application a

JOIN Orion.Container c ON c.MemberSnapshots.EntityID = a.NodeID

WHERE a.Components.Status <> 27 --Not Disabled

AND c.MemberSnapshots.EntityType = 'Orion.Nodes'

GROUP BY c.Name, a.Name, a.CustomApplicationType, a.Unmanaged

ORDER BY c.Name, a.Name, [LICENSES] DESC



SAM License Usage by Node:

SELECT

a.Name AS [APPLICATION NAME]

,a.Node.Caption AS [DEVICE NAME]

,CASE

    WHEN a.CustomApplicationType = 'ABXA' THEN '50'

    WHEN a.CustomApplicationType = 'ABSA' THEN '50'

    WHEN a.CustomApplicationType = 'ABIA' THEN '30'

    WHEN a.CustomApplicationType = 'ABTA' THEN '5'

    ELSE COUNT(a.Components.ComponentID)

END AS [LICENSES]

,CASE

    WHEN a.Unmanaged = 1 THEN 'YES'

    ELSE 'NO'

END AS [UNMANAGED?]

FROM Orion.APM.Application a

WHERE a.Components.Status <> 27 --Not Disabled

GROUP BY a.Node.Caption, a.Name, a.CustomApplicationType, a.Unmanaged

ORDER BY a.Name, [LICENSES] DESC

Tags (1)
Comments

Database scheme has changed. This query does not properly exclude components that are assigned but disabled.

thanks for the notice, updated to use SWQL and avoid any future schema changes

Thank you the SAM by node is exactly what I needed. 

I get this error when I run both querys

 

Msg 208, Level 16, State 1, Line 1

Invalid object name 'Orion.APM.Application'.

Version history
Revision #:
1 of 1
Last update:
‎09-15-2014 07:15 PM
Updated by: