Client Management Suite

 View Only

  • 1.  need help to use count

    Posted Apr 24, 2012 02:31 AM

    i got this report at i need to counte the number of versions

    SELECT vc.Guid,vc.Name, arp.DisplayName,arp.DisplayVersion

    FROM vComputer vc

    JOIN Inv_AddRemoveProgram arp ON arp._ResourceGuid=vc.Guid

    WHERE DisplayName LIKE 'Snagit%' AND arp.InstallFlag=1

    so i cant see how many we got of each version



  • 2.  RE: need help to use count

    Posted Apr 24, 2012 04:12 AM

    I'd be giving IT Analytics a look or maybe this would be enough:

     

    SELECT arp.DisplayName, arp.DisplayVersion, count(arp.DisplayVersion)

    FROM Inv_AddRemoveProgram arp

    WHERE arp.DisplayName LIKE 'snagit%' AND arp.InstallFlag=1

    group by arp.DisplayName, arp.DisplayVersion



  • 3.  RE: need help to use count

    Posted Apr 24, 2012 09:52 PM

     

    SELECT arp.DisplayName,arp.DisplayVersion,COUNT(arp._ResourceGuid)

    FROM Inv_AddRemoveProgram arp

    WHERE arp.DisplayName LIKE 'Snagit%' AND arp.InstallFlag=1

     

    This will give you a count for all computers that have reported software inventory.



  • 4.  RE: need help to use count

    Posted Apr 24, 2012 11:17 PM

    ahem 

    if you have bad software package installs like microsoft you will get a bad count from both queries as it does not exclude duplicate installs

    I have run something like below 

     

    select distinct  ac.name, prog.DisplayName, prog.displayversion
    FROM dbo.Inv_AeX_AC_Identification ac
    INNER JOIN dbo.Inv_AddRemoveProgram prog ON ac._ResourceGuid = prog._ResourceGuid 
    WHERE   prog.DisplayName ='Microsoft Office Professional Plus 2010' or prog.DisplayName ='Microsoft Office 2010'or prog.DisplayName ='Microsoft Office Standard 2010'
     and ac.[OS Name]not like '%server%'
    order by ac.Name asc