SAM Licenses by Group and Node

Version 2

    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