Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SCCM Report - Update Installed software on a specific computer Report to run against Collection

Posted on 2013-12-05
2
Medium Priority
?
1,945 Views
Last Modified: 2013-12-18
Hello SCCM Experts - Any assistance you can provide would be greatly appreciated!

I am trying to edit the current SCCM 2007 Report - "2E - Installed software on a specific computer" so it will report against a collection than a single computer.

The current report SQL Report is below, what should I update that it will allow the use of a collection?  What are your thoughts?

Select distinct
v_R_System_Valid.Netbios_Name0 AS "Computer Name",
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName AS "Product Name",
"Publisher" = CASE when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher is NULL or v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher = '-1') then 'Unknown'
Else v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher
End,
"Version" = CASE when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion is NULL or v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion  = '-1') then 'Unknown'
Else v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion
End,
"Language" = CASE when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.Language0 is NULL or v_GS_INSTALLED_SOFTWARE_CATEGORIZED.Language0 < 0) then 'Unknown'
Else CAST(v_GS_INSTALLED_SOFTWARE_CATEGORIZED.Language0 as varchar)
End,
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallType0 as "Installation Type",
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.FamilyName AS "Product Family",
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.CategoryName AS "Product Category",
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ProductID0 AS "ProductID",
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.SoftwareID as "Software ID",
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.CM_DSLID0 AS "DSL ID",
"Installed Location" = CASE when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstalledLocation0 IS NULL or v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstalledLocation0 = '-1') then 'Unknown'
Else v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstalledLocation0
End,
"Install Source" = CASE when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallSource0 is NULL or v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallSource0 = '-1') then 'Unknown'
Else v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallSource0
End,
"Uninstall String" = CASE when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.UninstallString0 is NULL or v_GS_INSTALLED_SOFTWARE_CATEGORIZED.UninstallString0 = '-1') then 'Unknown'
Else v_GS_INSTALLED_SOFTWARE_CATEGORIZED.UninstallString0
End,
"Install Date" = CASE when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallDate0 is NULL ) then 'Unknown'
Else CAST(v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallDate0 as varchar)
End,
"Registered User" = CASE when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.RegisteredUser0 is NULL or v_GS_INSTALLED_SOFTWARE_CATEGORIZED.RegisteredUser0 = '-1') then 'Unknown'
Else v_GS_INSTALLED_SOFTWARE_CATEGORIZED.RegisteredUser0
End
FROM v_GS_INSTALLED_SOFTWARE_CATEGORIZED
INNER JOIN v_R_System_Valid on v_R_System_Valid.ResourceID = v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ResourceID
Where v_R_System_Valid.Netbios_Name0 = @Name
order by v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName, Publisher, Version
0
Comment
Question by:rmessing171
2 Comments
 
LVL 16

Accepted Solution

by:
DcpKing earned 1500 total points
ID: 39702036
Firstly, you could change all the " to ' to get the query to work correctly in T-SQL

Then if your current single machine is denoted by
Where v_R_System_Valid.Netbios_Name0 = @Name

Open in new window

you could change that to a list of names like this:
where v_R_System_Valid.Netbios_Name0 in (@Name1, @Name2, @Name3)

Open in new window

where you arrange to provide each name to the query.

If you're doing this in, for example, an SSRS report and you are also changing your input from a single name in a drop-list to a drop-list where you can select multiple names then what you'll get a string of server names, like 'Fred,Bill,Jim', instead of a single value for @name like 'Fred' for the server.  If that is the case, but the query should remain the same, you'll need a slightly different way of doing things so that you can accept the list of servers.

Please answer stating what kind of input the query will get with multiple servers (with example!) .

hth

Mike
0
 

Author Comment

by:rmessing171
ID: 39706489
Hi Mike - Thank you for the quick reply.  Please forgive me I am new to SCCM and reporting.  I do not know what you mean by changing all of the  "to" to get the query working.

Could I change the query code so I could reference a Collection rather than listing several computernames?
0

Featured Post

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question