licensing Dashboard using SCCM inventory & SSRS

  • Article

Being a ConfigMgr admin, we often asked by management team for interactive reports like Asset inventory, Asset licensing etc. Well, there is known feature in ConfigMgr as ‘Asset Intelligence’ for license reporting however I have seen not many organizations are availing this feature for some reasons. I had to create a report of ‘count of licensed applications being used in organization’ for one of customer. Since actual license count per application given by management, I manually specified those numbers in report and  created interactive report based on number of installed instances per application based on hardware inventory. This report might help to make job easy for application owners to understand number of licensed being used and plan application life cycle and cost accordingly.

You can use below query and customize as per your requirement  :-

if object_id('tempdb..#tempList') is not null 
drop table #tempList 
select ltrim(rtrim(DisplayName0)) as 'Software_Title' 
 , count(*) as Ticks 
into #tempList 
from v_ADD_REMOVE_PROGRAMS 
where DisplayName0 is not null 
and ltrim(rtrim(DisplayName0)) <> '' 
and (DisplayName0 like 'SnagIt%' 
 or DisplayName0 like 'Steelray Project Viewer%' 
 or DisplayName0 like 'Adobe Acrobat % Pro%' 
 or DisplayName0 like 'Adobe Acrobat % Standard%' 
 or DisplayName0 like'Microsoft Visio Professional 2010%' 
 or DisplayName0 like'Microsoft Office Visio 2010%' 
 or DisplayName0 like'Microsoft Office Visio Standard 2003%' 
 or DisplayName0 like'Microsoft Office Visio Standard 2007' 
 or DisplayName0 like'%Microsoft Office Project Professional 2010%' 
 or DisplayName0 like'%Microsoft Office Project Standard 2010%' 
 or DisplayName0 like'%Microsoft Office Project Standard 2003%' 
 or DisplayName0 like'%Microsoft Office Project Professional 2007%' 
 ) and DisplayName0 not like '%VMware ThinApp%' and DisplayName0 not like 'Snagit Stamps %' 
group by ltrim(rtrim(DisplayName0)) 
order by ltrim(rtrim(DisplayName0)) 
select case
  when Software_Title like 'Steelray Project Viewer%' then 'Steelray Project Viewer' 
   when Software_Title like 'SnagIt%' then 'SnagIt' 
   when Software_Title like 'Adobe Acrobat % Pro%' then 'Adobe Acrobat Pro' 
  when Software_Title like 'Adobe Acrobat % Standard%' then 'Adobe Acrobat Standard'
  else Software_Title
 end as SoftwareTitle
 
 case when Software_Title like 'Steelray Project Viewer%' then '122' 
 when Software_Title like 'Microsoft Office Visio 2010' then '214'  
  when Software_Title like 'Microsoft Visio Professional 2010' then '78' 
  when Software_Title like 'Microsoft Office Visio Standard 2003' then '0' 
  when Software_Title like 'Microsoft Office Project Professional 2010' then '36' 
  when Software_Title like 'Microsoft Office Project Standard 2010' then '404' 
  when Software_Title like 'Snagit%' then '360' 
  when Software_Title like 'Adobe Acrobat % Pro%' then '400'
  when Software_Title like 'Adobe Acrobat % Standard%' then '655' 
  end as 'Owned_Licenses', 
  
 Sum(Ticks) as 'License in use' 
from #tempList
group by case 
  when Software_Title like 'Steelray Project Viewer%' then 'Steelray Project Viewer' 
   when Software_Title like 'SnagIt%' then 'SnagIt' 
   when Software_Title like 'Adobe Acrobat % Pro%' then 'Adobe Acrobat Pro' 
   when Software_Title like 'Adobe Acrobat % Standard%' then 'Adobe Acrobat Standard'
  else Software_Title 
 end , 
 
 case when Software_Title like 'Steelray Project Viewer%' then '122' 
when Software_Title like 'Microsoft Office Visio 2010' then '214'  
  when Software_Title like 'Microsoft Visio Professional 2010' then '78' 
  when Software_Title like 'Microsoft Office Visio Standard 2003' then '0' 
  when Software_Title like 'Microsoft Office Project Professional 2010' then '36' 
  when Software_Title like 'Microsoft Office Project Standard 2010' then '404' 
  when Software_Title like 'Snagit%' then '360' 
  when Software_Title like 'Adobe Acrobat % Pro%' then '400' 
when Software_Title like 'Adobe Acrobat % Standard%' then '655'
  end 
 
  order by case 
  when Software_Title like 'Steelray Project Viewer%' then 'Steelray Project Viewer' 
   when Software_Title like 'SnagIt%' then 'SnagIt' 
   when Software_Title like 'Adobe Acrobat % Pro%' then 'Adobe Acrobat Pro' 
   when Software_Title like 'Adobe Acrobat % Standard%' then 'Adobe Acrobat Standard'
  else Software_Title
 end