Solved

Converting my Query to a Report in SCCM 07

Posted on 2009-05-04
2
2,713 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

Are your corporate email signatures appalling?

Is it scary how unprofessional your email signatures look? Do users create their own terrible designs and give themselves stupid job titles? You can make this a lot easier for yourself by choosing an email signature management solution from Exclaimer today.

Join & Write a Comment

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…
Have you considered what group policies are backwards and forwards compatible? Windows Active Directory servers and clients use group policy templates to deploy sets of policies within your domain. But, there is a catch to deploying policies. The…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now