Client Management Suite

 View Only
Back to discussions
Expand all | Collapse all

automation policy to show count of win7 by site

  • 1.  automation policy to show count of win7 by site

    Posted Sep 12, 2012 10:28 AM

    Ok so i can throw a basic sql code together and use the 

    select count(blah)

    where site = 'mysite1' and [os name] like 'windows 7%'

    and this will work to give me a count of machines for that site that are win7

    but how do you do it where there are say 10 sites?

    im ok with basic sql but dont know how to do this one..

    thanks



  • 2.  RE: automation policy to show count of win7 by site

    Posted Sep 12, 2012 11:15 AM

    fyi if anyone wants to know how...

     

     

    DECLARE @Var1 varchar(60), @Var2 varchar(60), @Var3 varchar(60), @Var4 varchar(60), @Var5 varchar(60), @Var6 varchar(60), @Var7 varchar(60), @Var8 varchar(60), @Var9 varchar(60), @Var10 varchar(60), @Var11 varchar(60)
     
    SELECT @Var1 = 'Site1', @Var2 = 'Site2', @Var3 = 'Site3', @Var4 = 'Site4', @Var5 = 'Site5', @Var6 = 'Site6', @Var7 = 'Site7', @Var8 = 'Site8', @Var9 = 'Site9', @Var10 = 'Site10', @Var11 = 'Site11'
     
    select @var1 as 'Site', Count(t0.[OS Name]) as 'Win7 Count'
    from vcomputer t0 left outer join
    inv_vars t1 on t0.guid = t1._resourceguid
    where t0.[OS Name] like 'windows 7%' and t1.[site] = @Var1
    Union
    select @var2, Count(t0.[OS Name]) as 'Win7 Count'
    from vcomputer t0 left outer join
    inv_vars t1 on t0.guid = t1._resourceguid
    where t0.[OS Name] like 'windows 7%' and t1.[site] = @Var2
    union
    select @var3, Count(t0.[OS Name]) as 'Win7 Count'
    from vcomputer t0 left outer join
    inv_vars t1 on t0.guid = t1._resourceguid
    where t0.[OS Name] like 'windows 7%' and t1.[site] = @Var3
    union
    select @var4, Count(t0.[OS Name]) as 'Win7 Count'
    from vcomputer t0 left outer join
    inv_vars t1 on t0.guid = t1._resourceguid
    where t0.[OS Name] like 'windows 7%' and t1.[site] = @Var4
    union
    select @var5, Count(t0.[OS Name]) as 'Win7 Count'
    from vcomputer t0 left outer join
    inv_vars t1 on t0.guid = t1._resourceguid
    where t0.[OS Name] like 'windows 7%' and t1.[site] = @Var5
    union
    select @var6, Count(t0.[OS Name]) as 'Win7 Count'
    from vcomputer t0 left outer join
    inv_bms_lcs_vars t1 on t0.guid = t1._resourceguid
    where t0.[OS Name] like 'windows 7%' and t1.[site] = @Var6
    union
    select @var7, Count(t0.[OS Name]) as 'Win7 Count'
    from vcomputer t0 left outer join
    inv_vars t1 on t0.guid = t1._resourceguid
    where t0.[OS Name] like 'windows 7%' and t1.[site] = @Var7
    union
    select @var8, Count(t0.[OS Name]) as 'Win7 Count'
    from vcomputer t0 left outer join
    inv_vars t1 on t0.guid = t1._resourceguid
    where t0.[OS Name] like 'windows 7%' and t1.[site] = @Var8
    union
    select @var9, Count(t0.[OS Name]) as 'Win7 Count'
    from vcomputer t0 left outer join
    inv_vars t1 on t0.guid = t1._resourceguid
    where t0.[OS Name] like 'windows 7%' and t1.[site] = @Var9
    union
    select @var10, Count(t0.[OS Name]) as 'Win7 Count'
    from vcomputer t0 left outer join
    inv_vars t1 on t0.guid = t1._resourceguid
    where t0.[OS Name] like 'windows 7%' and t1.[site] = @Var10
    union
    select @var11, Count(t0.[OS Name]) as 'Win7 Count'
    from vcomputer t0 left outer join
    inv_vars t1 on t0.guid = t1._resourceguid
    where t0.[OS Name] like 'windows 7%' and t1.[site] = @Var11
     


  • 3.  RE: automation policy to show count of win7 by site

    Posted Sep 12, 2012 04:52 PM

    Just use GROUP BY and you can simplify to a single query:

     

    select t1.[site], Count(t0.[OS Name]) as 'Win7 Count'
    from vcomputer t0 left outer join
    inv_vars t1 on t0.guid = t1._resourceguid
    where t0.[OS Name] like 'windows 7%'
    GROUP BY t1.[site]


  • 4.  RE: automation policy to show count of win7 by site

    Posted Sep 14, 2012 12:41 PM

    hmmm interesting.. your code is WAY CLEANER!!!!! thanks!

     

    i ran it and get something different now.. my results in my post will show the sites with count and also will show a site with 0 if none...

    yours shows it differently... I see null and also some blanks. This is a field that is a custom inventory so i expect there to be null's or blanks... mine doesnt show them and yours does.. 

    so i guess how do I show a site that has 0 by using your code?

     

    yours returned  (site 5 missing from the list)

     

    site Win7 Count
    NULL 60
              19
    Site 1 10
    Site 2 61
    Site 3 70
    Site 4 2
    Site 6 1
    Site 7 39
    Site 8 1
    Site 9 31
    Site 10 4
    Site 11 48
     
    mine returned
    Site Win7 Count
    Site 1 10
    Site 2 61
    Site 3 70
    Site 4 2
    Site 5 0
    Site 6 1
    Site 7 39
    Site 8 1
    Site 9 31
    Site 10 4
    Site 11 48

     



  • 5.  RE: automation policy to show count of win7 by site

    Posted Sep 14, 2012 02:47 PM

    I have been following this thread.  What table is the inv_vars  i cannot find it in 7.1 sp2.



  • 6.  RE: automation policy to show count of win7 by site

    Posted Sep 14, 2012 02:58 PM

    it is a custom inventory that we have.. it pulls in 25 items from a registry key...

    serial number, asset tag, user id, room, and other info pertaining to us...

     



  • 7.  RE: automation policy to show count of win7 by site

    Posted Sep 17, 2012 08:53 AM

    ok i changed the way it is done..

    how do you get something to show if you have a 0 count?

    here is one of the sections... 

     

    Select t1.[Name], Count(t3.[OS Name]) as 'Win7 Count'
    FROM [vCollection] t1 inner join
    [CollectionMembership] t2 on t1.GUID = t2.CollectionGUID inner join
    [Inv_Aex_AC_Identification] t3 on t2.ResourceGUID = t3._resourceGUID left join
    [vcomputer] t4 on t3.[_ResourceGuid] = t4.[Guid]
    WHERE t1.[Name] = 'HPW Benchtops' and t3.[OS Name] like 'windows 7%' and t4.[IsManaged] = '1'
    Group by t1.[Name]
     
    now this works.. i throw a union into it and keep poking the other 10 sites into it. All is good but one of our sites has 0 Windows 7 machines thus it does not show up. So if there are 0 how do you get it to still show?
     
    thanks!
    Jeff


  • 8.  RE: automation policy to show count of win7 by site

    Posted Sep 17, 2012 11:50 AM

    By default, COUNT() doesn't count NULL values.  So, you have to give it a value.  Try this:

     

    Select t1.[Name], ISNULL(Count(t3.[OS Name]), 0) as 'Win7 Count'
    FROM [vCollection] t1 inner join
    [CollectionMembership] t2 on t1.GUID = t2.CollectionGUID inner join
    [Inv_Aex_AC_Identification] t3 on t2.ResourceGUID = t3._resourceGUID left join
    [vcomputer] t4 on t3.[_ResourceGuid] = t4.[Guid]
    WHERE t1.[Name] = 'HPW Benchtops' and t3.[OS Name] like 'windows 7%' and t4.[IsManaged] = '1'
    Group by t1.[Name]

     



  • 9.  RE: automation policy to show count of win7 by site

    Posted Sep 17, 2012 11:56 AM

    it ran without errors but still just shows 10 of the 11 sites.. 11th has 0 machines with win7. Trying to get it to show 0 for now until they get started with their upgrade plan.



  • 10.  RE: automation policy to show count of win7 by site
    Best Answer

    Posted Sep 17, 2012 12:09 PM

    If a site is missing Windows 7 machines then we need to tweak the SQL so it doesn't filter out that site.  Meaning, we need to use a LEFT JOIN instead of an INNER JOIN.  Try this:

     

    Select t1.[Name], ISNULL(Count(t3.[OS Name]), 0) as 'Win7 Count'
    FROM [vCollection] t1
    JOIN [CollectionMembership] t2
       ON t1.GUID = t2.CollectionGuid
    LEFT JOIN [Inv_Aex_AC_Identification] t3
       ON t2.ResourceGUID = t3._resourceGUID
       AND t3.[OS Name] like 'windows 7%'
    LEFT JOIN [vcomputer] t4
       ON t3.[_ResourceGuid] = t4.[Guid]
       AND t4.[IsManaged] = '1'
    WHERE t1.[Name] = 'HPW Benchtops'
    Group by t1.[Name]


  • 11.  RE: automation policy to show count of win7 by site

    Posted Sep 17, 2012 12:16 PM

    still no.. i put all joins to left joins... still showing 10 of 11...



  • 12.  RE: automation policy to show count of win7 by site

    Posted Sep 17, 2012 02:02 PM

    Did you use the query I provided above or simply modify one you already had?  Another change I made that I didn't highlight was moving some of the filters in the WHERE clause up into the JOINs.  If you LEFT JOIN on a table but then put filter criteria in the WHERE clause, you are effectively negating the LEFT part of the JOIN and forcing an INNER JOIN.  If you are using the one I provided above and still are unable to see the last site, then I'm stumped, unfortunately...



  • 13.  RE: automation policy to show count of win7 by site

    Posted Sep 17, 2012 02:27 PM

    WOW gotcha.. i have to check out that code more.. yes it worked... sweet!!! appreciate it.. now off to do this for all of the other sites... just copy/paste and change the filter name in each...add union..

     

    thanks again!!!
    Jeff



  • 14.  RE: automation policy to show count of win7 by site

    Posted Sep 17, 2012 02:34 PM

    ok so yes this did work to get the 0 in there.. appreciate that.. but the numbers are not correct..

    my way with the where IsManaged = '1' leaves it at 13 machines but if I use it your way i have 14....

    so i went and counted and there are 13 showing in that collection so i am getting the 0 like i wanted but the numbers are off...

    ill go count a few others and see if that is true on others...



  • 15.  RE: automation policy to show count of win7 by site

    Posted Sep 17, 2012 02:42 PM

    ok so i added    AND t4.[IsManaged] = '1' behind the collection name... and viola it all works..

    i got the 0 count row now.. 
    and I also have 344 when i drop it in excel and do a sum on all.... my windows 7 collection has 344 so lookin good... appreciate it...



  • 16.  RE: automation policy to show count of win7 by site

    Posted Sep 17, 2012 02:45 PM

    guess im done with this .. i will deal with the site not showing up...

    seems in SQL it will work just fine.. send the automation policy email and it still doesnt show.. maybe ill get him to move a Windows 7 machine sooner.. hah...