SQL query on how to make report on all software in seperate regional offices in SCCM 2007R2
New to SCCM 2007 reports.
Can anyone tell me how to create a report that wil show me all the software installed on the desktop computers with the computer name.
Since I have computers desktops in different offices I want one report to show San Francisco, LA,San Diego computers with list of all software installed on them. The computer host names start with sfo-username,lax-username,san-username for all the computers.
Another report to show all software for computer Seattle, Portland and the host names are sea-username,pdx-username etc.
I only have access the the Configuration Manager Console. I tried the SCCM 2007 viewer but it doesn't have what I need to make this report. Also how can I see the SQL statement underneath the SCCM 2007 viewer.
All Replies
- You're not the first person to ask for this sort of question, and be told that reports of "All Software" for "All Computers" are usually not a good idea.
See http://www.myitforum.com/forums/m_207727/tm.htm or http://smsug.ca/blogs/garth_jones/archive/2007/05/31/417.aspx for an explanation why.
Basically each PC can have around 200 to 300 items in "Add/Remove Programs", even if you limit it to "Installed Software" it can still be hundreds per PC. Multiply that by the number of PCs, and you start to quickly get into reports that have millions of rows.
My advice is to tackle your problem from a different angle. For example, start with the Software 1A or Software 6A reports in the Asset Management category. These will give you totals for certain applications in certain collections. You can then start drilling down into the application you think are relevant for your exercise and see the list of installed PCs. You'll see a lot of applications you will probably decide to ignore.
You could create collections with the PCs you need (San F, LA, San D etc.) based on whatever criteria you thing is most appropriate (PC Name, OU name etc.).
Regards,
Tom Watson,
E-Mail: Tom_...@...
Blog: http://myitforum.com/cs2/blogs/tom_watson - Thanks for the info Tom. Just that Management want to know all the software located in each office location. I looked at Software 1A and 6A and now know what you mean my so many items. Lots of them are drivers and patches too so I could elimate all those out. Don't think they would want to know all the patches each computer has. I just do it with the list of software only.
?s for you, How do you determine what is a patch or driver on a PC and should you removed them for an audit?
This is a long weekend for me but within a month or so I will be doing up a blog on how to do a software audit based on a discussions on that I have had with a SW auditor.
The very short version of this is:
· Give them a Count of all SW installed on all PCs
· Randomly pick a few PC and provide a list of all SW on those PCs
· Review the list of PCs within your AD and ConfigMgr does it match, if not, why not.
· Review you ConfigMgr settings, will they affect the outcome of the reports above?
· Etc.
http://www.enhansoft.com/
