Solved

Converting my Query to a Report in SCCM 07

Posted on 2009-05-04
2
2,720 Views
Last Modified: 2013-11-21
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
Comment
Question by:ServerSupport
2 Comments
 
LVL 1

Accepted Solution

by:
drgonzo1967 earned 250 total points
ID: 24368842
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
 
LVL 5

Assisted Solution

by:balmasri
balmasri earned 250 total points
ID: 24384168
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

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

1. Boot PC and press F10, select storage options and change the compatibility from “AHCI” to “IDE”, save and exit 2. Boot PC and press F12 3. Upon PXE display of searching for DHCP server, press Pause break to obtain MAC address 3. Open Configu…
The question has been asked on multiple occasions as to how best to do printing in a remote desktop or terminal services environment.   It seems that this particular question has plagued several people and most especially as Terminal Services, as…

830 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