Solved

Converting my Query to a Report in SCCM 07

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Case Summary: In this Article we introduce the new method to configure the default user profile using Automated profile copy with sysprep rather than the old ways such as the manual copy of a configured profile to default user profile Old meth…
This is my 3rd article on SCCM in recent weeks, the 1st (http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Server/Windows_Server_2008/A_4466-A-beginners-guide-to-installing-SCCM2007-on-Windows-2008-R2-Server.html) dealing with installat…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

785 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