Query to count total number of windows updates and total number of softwares installed on machines

select a1.n1,a1.OSVersion1,a1.totalnumberofwindowsupdatesinstalled,b1.totalnumberofsoftwaresintalled from
(select a.Netbios_Name0 as 'n1', b.caption0 as 'OSVersion1', count(c.resourceid)
as 'totalnumberofwindowsupdatesinstalled' from v_r_system a inner join
v_gs_operating_system b on a.resourceid=b.resourceid
inner join v_gs_add_remove_programs c on a.resourceid=c.resourceid
where (c.displayname0 is not null and c.prodid0 like 'KB%')
group by a.Netbios_Name0, b.caption0, c.resourceid)a1,
(select a.Netbios_Name0 as 'n2', b.caption0 as 'OS Version', count(c.resourceid)
as 'totalnumberofsoftwaresintalled' from v_r_system a inner join
v_gs_operating_system b on a.resourceid=b.resourceid
inner join v_gs_add_remove_programs c on a.resourceid=c.resourceid
where (c.displayname0 is not null and c.prodid0 not like 'KB%')
group by a.Netbios_Name0, b.caption0, c.resourceid)b1
where a1.n1=b1.n2
order by a1.n1

Description:

This query will give you the count of windows updates and count of all software's installed on each machines in your SMS database.