SMS Reporting for the n00b! 1

[jefrowski]

Hi guys,

Can I ask some advice?

I need to create a report that will show:

Count of PC's by department, and how many of each operating system live in each department?

So far I have copied an exe onto each client that shows which department they are in, i.e HR, Sales, etc, but now I need to create a report that shows how many PC's in HR for example have windows 2000, XP, etc.

I am bashing my head on a wall trying to get this done! so any help is gratefully received!

 

[Mich]

OK, you're going to have to build a custom report using a SQL query. The operating system count is the easy part. How exactly did you distinguish which department a machine was a part of? From the sound of it you created a (generic??) EXE, placed it on a machine, and named it accordingly. If it's a Sales PC you called it sales.exe? If it's HR you called it hr.exe even though the actual executable didn't change? Let me know. This won't be too difficult, we'll just have to dig around in some tables.

-If it ain't broke, break it and make it better.

 

[jefrowski]

Thanks for helping out Mich!

OK:

Using our login scripts I have the script copying an exe file onto the users PC, depending on which group they are in (i.e HR, Sales, etc), just like you presumed!

I now need to create a report that breaks down the following:

a:/ How many PC's per department
b:/ Count of operating systems per department

I would like it to look like the report that shows a count of total operating systems (report 107 I think???)

I think my lack of SQL knowledge is the main problem here! So I really appreciate your offer of help!!

 

[Mich]

a)

SELECT filename as Department, COUNT(*) AS 'Count'
FROM v_gs_softwarefile where filename like '%-2113%'
GROUP BY filename
ORDER BY filename

One caveat here, you will need to give this EXE some sort of distinctive filename such as the '-2113'. You need this so you can pull just the files (department files) that you need. Otherwise you'll get a count of everything. This will work as long as the file you place on each PC is populating the SoftwareFile table. Notice the difference between this query and the query for 107.

b)

Here you want to combine the queries for 61 and 107. This one is a little messier,

SELECT Caption0 as C054, sf.filename as Department, COUNT(*) AS 'Count'
FROM v_GS_OPERATING_SYSTEM OPSYS, v_gs_softwarefile sf where sf.filename like '%-2113%'
GROUP BY Caption0, sf.filename
ORDER BY Caption0

A problem you're going to run into with each query is that your departments will have names like hr-2113 and sales-2113. If this report is informational only then it doesn't matter.

A word of advice, if you name your PCs appropriately i.e. hr-sally, sales-jim, then you can query directly off the PC name knocking out a lot of this file copy/query stuff.

Hope this helps.

-If it ain't broke, break it and make it better.

 

[jefrowski]

Hi Mich,

Thanks ever so much for your help!
The first report works fine, the second one is just the kind of thing I am after, but is returning crazy number in the count column!!
I'll have a good play around with it!!

The way I used the files was to name them sales.exe, hr.exe and copy the file onto the users c:\ depending on which AD security group they are in, it all works a treat so far!!

Thanks again for your help!

 

[jefrowski]

Hi Mich

Thanks for helping out!
Now, I have a slight issue! You mentioned that it may report all exe files? I think that it what is happening, and also mentioned a unique identifier for each file? Could you explain this for me?
I have sales.exe, hr.exe, etc, but the numbers it reports in the second report are way off!
I've tried fiddling with it, but to no avail!

I would have preferred the way that you mentioned, and to rename all of the PC's, but unfortunately this is not going to happen anytime soon! So, sorry to hassle, but if you can help out a little more I would be grateful!!

 

[Mich]

It will help me to debug the query if I can see how you modified to fit your environment.

Executing the query without "where sf.filename like '%-2113%'" will give you a count of every file by operating system. It returned almost 40K lines in my environment. If I were doing things the way you are I'd have to dig through 40K lines to find hr.exe. There are a couple of ways to get around this,

1) Modify the filename that your are delivering to each PC. If you give the file a unique name( sales-2113.exe, hr-jefrowski.exe, etc.) and keep it consistent (sales-jefrowski.exe and hr-jefrowski.exe) you can create a query that will show the results you're looking for for each department. This can be done using the WHERE statement.

2) If you only care to check one department's count at at time, you can use "where sf.filename = 'hr.exe'". This will give you the count for the HR department only.

When you define the report in SMS you can use @variable, but we'll cross that bridge when we get to it.
-If it ain't broke, break it and make it better.


-If it ain't broke, break it and make it better.

 

[jefrowski]

Hi Mich,

The only change I have made to the query was to change the filename to sales.exe?
I tried as an experiment to change the filename to word.exe, and the numbers that came back were believable??
I am rather confused as to why when I change the filename to sales.exe that is is coming back with mental figures!
I can confirm that only PC's in the sales department have sales.exe copied onto them, and that the only change I have made to the query is the sales.exe filename??

 

[Mich]

I think I might know what is going on here. Is your WHERE clause the following: where sf.filename like '%sales.exe%'.

If so, that would explain an inflated number. The query would be reporting on sales.exe, but would also include wonderbrasales.exe or tbacksales.exe (<whatever>sales.exe). Change your WHERE statment to this - where sf.filename = 'sales.exe'. See if these results are better and let me know.

-If it ain't broke, break it and make it better.

 

[Mich]

CRAP!! There is a flaw in the second query...sorry . Try this one, it should look much better,

SELECT Caption0 as C054, sf.filename as Department, COUNT(*) AS 'Count'
FROM v_GS_OPERATING_SYSTEM OPSYS join v_gs_softwarefile sf on opsys.resourceid=sf.resourceid
where sf.filename = 'sales.exe'
GROUP BY Caption0, sf.filename
ORDER BY Caption0

-If it ain't broke, break it and make it better.

 

[jefrowski]

Hi Mich,

After hours of trying to learn sql joins, I came up with this one:

SELECT Caption0 as C054, sf.filename as Department, COUNT(*) AS 'Count'
FROM v_GS_OPERATING_SYSTEM OPSYS
inner join v_gs_softwarefile sf on OPSYS.ResourceID=sf.ResourceID
where sf.filename like 'sales.exe'
GROUP BY Caption0, sf.filename
ORDER BY Caption0

This seems to work fine?
I'll try your one aswell, thanks ever so much for helping out here, you have saved me hours of banging my head against the monitor!!