Improve company productivity with a Business Account.Sign Up

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

Converting my Query to a Report in SCCM 07

Hey everyone,

I currently have a query setup that displays the system name and last logged on user of any machine that has any 5 differant version of Adobe Pro installed. It would be nice if I could get a report that could produce the same information but I'm not exactly sure how to go about creating the report. I figured it wouldn't work, but I tried copying the code from the query into a new report and it failed. Below is the code, does anyone have any idea's?

select distinct SMS_R_System.Name, SMS_R_System.LastLogonUserName from  SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Adobe Acrobat 7.0 Professional" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Adobe Acrobat 7.1.0 Professional" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Adobe Acrobat 8 Professional" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Adobe Acrobat 8 Professional - English, Fran├žais, Deutsch" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Adobe Acrobat 8.1.3 Professional" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Adobe Acrobat 8.1.4 Professional"

Thanks in advance.
0
ServerSupport
Asked:
ServerSupport
2 Solutions
 
drgonzo1967Commented:
Hey ServerSupport.

Give the below code a try in your report.  What you posted above is actually WQL, probably from a Collection.  Just remember that you cant use WQL in SQL Management Studio or SCCM/SMS Reports.  Hopefully someday they will give us that 'Advanced' button to write SQL code to generate Collections!  Until then we will need to know both.  Hope this helps!

-gonzo
select distinct SYS.Name0 as [Computer Name], SYS.User_Name0 as [User Name]
from  V_R_System SYS inner join v_gs_ADD_REMOVE_PROGRAMS ARP
	on sys.ResourceID = ARP.ResourceId 
where ARP.Displayname0 = 'Adobe Acrobat 7.0 Professional' 
	or ARP.Displayname0 = 'Adobe Acrobat 7.1.0 Professional' 
	or ARP.Displayname0 = 'Adobe Acrobat 8 Professional' 
or ARP.Displayname0 = 'Adobe Acrobat 8 Professional - English, Fran├žais, Deutsch' 
or ARP.Displayname0 = 'Adobe Acrobat 8.1.3 Professional' 
or ARP.Displayname0 = 'Adobe Acrobat 8.1.4 Professional'

Open in new window

0
 
balmasriCommented:
the best way to build your report is to open SQL server console > your database >views > create new view > then build your view . after that copy the SQL statement and paste it to your report . then cancel that view
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now