Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2119
  • Last Modified:

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

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
rmessing171
Asked:
rmessing171
Advertise Here
1 Solution
 
DcpKingCommented:
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
 
rmessing171ConsultantAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Learn More
Advertise Here
Tackle projects and never again get stuck behind a technical roadblock.
Join Now