Video Screencast Help
Symantec Appoints Michael A. Brown CEO. Learn more.

automation policy to show count of win7 by site

Created: 12 Sep 2012 • Updated: 17 Sep 2012 | 15 comments
This issue has been solved. See solution.

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

Comments 15 CommentsJump to latest comment

TeleFragger's picture

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
 

Did we help you? Please Mark As Solution those posts which resolve your problem,

Andrew Bosch's picture

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]

------------------------------------
Sr. Principal SQA Engineer
Symantec

TeleFragger's picture

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

 

Did we help you? Please Mark As Solution those posts which resolve your problem,

seguidor1's picture

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

TeleFragger's picture

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...

 

Did we help you? Please Mark As Solution those posts which resolve your problem,

TeleFragger's picture

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

Did we help you? Please Mark As Solution those posts which resolve your problem,

Andrew Bosch's picture

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]

 

------------------------------------
Sr. Principal SQA Engineer
Symantec

TeleFragger's picture

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.

Did we help you? Please Mark As Solution those posts which resolve your problem,

Andrew Bosch's picture

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]

------------------------------------
Sr. Principal SQA Engineer
Symantec

SOLUTION
TeleFragger's picture

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

Did we help you? Please Mark As Solution those posts which resolve your problem,

Andrew Bosch's picture

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...

------------------------------------
Sr. Principal SQA Engineer
Symantec

TeleFragger's picture

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

Did we help you? Please Mark As Solution those posts which resolve your problem,

TeleFragger's picture

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...

Did we help you? Please Mark As Solution those posts which resolve your problem,

TeleFragger's picture

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...

Did we help you? Please Mark As Solution those posts which resolve your problem,

TeleFragger's picture

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...

Did we help you? Please Mark As Solution those posts which resolve your problem,