Can I turn a query into a Report?
I have created a query, now is therea way that I can thencreate a report based off that query? If so, how?
January 6th, 2009 9:10pm

Hi,There is no easy way to turn a query into a report. It takes alittle effort to do it. I normally recommend that you create the basic part of your reports in SQL Server Management Studio - I added 4 post to my blog where I give some examples and guide you through the process from A-Z:http://blog.coretech.dk/category/confmgr07/config-mgr-inventory-and-reporting/To create a report based on your query take a look at these articles:http://www.myitforum.com/articles/19/view.asp?id=9942Kent Agerlund
Free Windows Admin Tool Kit Click here and download it now
January 6th, 2009 9:38pm

How about just being able to print the results of a query quickly and easily?
January 7th, 2009 12:01am

How big is the query? if it's not tooinvolved I can likely convert it for you if you are not sure how to do it in Management Studio like Kent mentioned.John Marcum, Systems Management Architect - www.TrueSec.com
Free Windows Admin Tool Kit Click here and download it now
January 7th, 2009 12:30am

Kent Agerlund said: There is no easy way to turn a query into a report. It takes alittle effort to do it. There IS an easy way: run the query and have a look at smsprov.log on the siteserver. You will see how the WQL query is turned into a SQL query!
January 7th, 2009 4:12pm

Hi Torsten,I am not going to argue with you about converting the WQL query into a SQL query. That's easily done by looking in the smsprov.log. However if your a copying the SQL statement directly into a Config mgr. report you will most likely get an error message.Kent Agerlund
Free Windows Admin Tool Kit Click here and download it now
January 7th, 2009 4:47pm

It always worked for me (I just had to replace all " with '). There might be situations where you have to do some tweaking but that's IMHO the easiest starting point for beginners.
January 7th, 2009 5:21pm

Like Torsten said, getting a SQL statement from query analyzer into a report is very easy. Getting it into WQL for a collection is a pain for me but I know others that do it easily. Going the other way however, from WQL to SQL is pretty easy most of the time. I guess that's maybe becauseI have the SQL tables and views memorized but not the WQL equivalents. John Marcum, Systems Management Architect - www.TrueSec.com
Free Windows Admin Tool Kit Click here and download it now
January 7th, 2009 5:26pm

Okay.So I run the query, open smsprov.log and then do I just copy the statement after the "Execute SQL" starting with "="? and do I have to copy all of them as i noticed there are a few "Execute SQL" in the log after i run my query.
January 7th, 2009 9:45pm

You will have the Execute SQL below the Execute WQL statement. You'll notice that the ones you are looking for are almost similar. If you run the query while monitoring the log file with SMS trace then you should be able to find it easily. Kent Agerlund
Free Windows Admin Tool Kit Click here and download it now
January 7th, 2009 10:00pm

I did that but it really didn't see which one it was, they all look so similiar, I'm thinking that the one I copy will be the one that shortyl after it is says results 73 of 74, because if I look at the query in configmgr that is how many results I have is 73.
January 7th, 2009 10:22pm

So i copied and pasted into report and didn't change anything and I got the following error when trying to run the report: An error occurred when the report was run. The details are as follows: Invalid column name '%Reader%'. Error Number: -2147217900 Source: Microsoft OLE DB Provider for SQL Server Native Error: 207Then I went through and replaced all the (") with (') and then i got a different error: An error occurred when the report was run. The details are as follows: The SELECT permission was denied on the object 'Add_Remove_Programs_DATA', database 'SMS_DNV', schema 'dbo'. Error Number: -2147217911 Source: Microsoft OLE DB Provider for SQL Server Native Error: 229I guess I'm just too much of a noob for this, and I should just give SQL Server Management Studio a try.
Free Windows Admin Tool Kit Click here and download it now
January 7th, 2009 10:32pm

The query I'mtrying to copy is below...I think its the correct one.select distinct SMS_R_System.Name0,__System_ADD_REMOVE_PROGRAMS0.Version00,SMS_R_System.User_Name0 from System_DISC AS SMS_R_System INNER JOIN Add_Remove_Programs_DATA AS __System_ADD_REMOVE_PROGRAMS0 ON __System_ADD_REMOVE_PROGRAMS0.MachineID = SMS_R_System.ItemKey where (__System_ADD_REMOVE_PROGRAMS0.DisplayName00 like '%Reader%' AND __System_ADD_REMOVE_PROGRAMS0.Version00 < '8.1')
January 7th, 2009 10:38pm

DMobley1022 said: So i copied and pasted into report and didn't change anything and I got the following error when trying to run the report: An error occurred when the report was run. The details are as follows: Invalid column name '%Reader%'. Error Number: -2147217900 Source: Microsoft OLE DB Provider for SQL Server Native Error: 207Then I went through and replaced all the (") with (') and then i got a different error: An error occurred when the report was run. The details are as follows: The SELECT permission was denied on the object 'Add_Remove_Programs_DATA', database 'SMS_DNV', schema 'dbo'. Error Number: -2147217911 Source: Microsoft OLE DB Provider for SQL Server Native Error: 229I guess I'm just too much of a noob for this, and I should just give SQL Server Management Studio a try. That's "expected" ;-) Now you've turned the WQL query into a SQL one. You can copy'n paste that in SQL Management Studio and will get a result. Unfortunately you cannot use SQL tables in ConfigMgr reports (ok, you could, but then you would have to change permissions on the tables. That's not supported). Now just replace the table "'Add_Remove_Programs_DATA" with the corresponding view "v_GS_Add_Remove" (I don't know the exact name on top off my head) and maybe some columns names and you're done :-) Here's a whitepaper that will get you started;http://www.microsoft.com/downloads/details.aspx?FamilyId=87BBE64E-5439-4FC8-BECC-DEB372A40F4A&displaylang=en
Free Windows Admin Tool Kit Click here and download it now
January 7th, 2009 11:29pm

I want to thank everyone for all there comments and help. I'm downloading SQL Server Management Studio express right now and I will use the links provided hereto get teh report i need.Thanks;Dustin
January 8th, 2009 12:00am

Management studio should be installed from the client tools on you SQL cd that you installed SQL from. Should not have to download anything.John Marcum, Systems Management Architect - www.TrueSec.com
Free Windows Admin Tool Kit Click here and download it now
January 8th, 2009 12:33am

I tried that and couldn't get it to work, thinking because I have Vista, and at this point i'm so flustered and not even thinking straight, I could easily be overlooking something, grabbed the wrong SQL Server 2005 disc....so I downloaded.
January 8th, 2009 12:49am

There are various resources available that may be of use. Creating your own Reports isn't quite as intuitive as creating your own Queries. However, with a bit of practice, it can become easier.Here are the links for a few resources:-Creating SMS Web Reports from SMS Querieshttp://www.myitforum.com/articles/8/view.asp?id=9962 orhttp://myitforum.com/cs2/blogs/bleary/archive/2007/03/12/download-creating-sms-web-reports-from-sms-queries.aspxSoftware Metering Console Viewhttp://smsug.ca/blogs/garth_jones/archive/2007/03/04/195.aspxUsing SMS 2003 SQL Views to Create Custom Reportshttp://www.microsoft.com/technet/prodtechnol/sms/sms2003/customreports/9a0d62fa-d8d5-4a26-b772-18cac737e67e.mspx orhttp://www.microsoft.com/downloads/details.aspx?familyid=9D6F3A2F-52C6-4574-B8B0-16846DA2FC55Reviewing the Inventory Datahttp://www.microsoft.com/technet/prodtechnol/sms/sms2003/opsguide/ops_29a1.mspxSQL Server Viewshttp://www.microsoft.com/technet/prodtechnol/sms/sms2003/opsguide/ops_5h9q.mspxAlso, another blog that has some tremendous advice on web reporting is :-http://myitforum.com/cs2/blogs/jnelson/Plus, There's now detailed documentation on the SQL views in SMS/SCCM. :-http://blogs.technet.com/wemd_ua_-_sms_writing_team/archive/2008/10/30/announcement-the-creating-custom-reports-by-using-configuration-manager-2007-sql-views-documentation-package-is-now-available-for-download.aspxDirect download available from http://www.microsoft.com/downloads/details.aspx?FamilyId=87BBE64E-5439-4FC8-BECC-DEB372A40F4A
Free Windows Admin Tool Kit Click here and download it now
January 8th, 2009 1:50pm

Also, you mentioned if you can run queries quickly and print off results etc. One possible option would be to grab a copy of smsextract.xlt which came with the old SMS 2.0 Support Tools. It certainly works with SMS 2003, but I haven't tested with SCCM 2007. I would guess it would still work though.Tom
January 8th, 2009 2:00pm

This is much, much easier if you decide to move to SCCM R2 and use the new reporting services point that leverages SQL Reporting Services. That way you can simply use the SQL based report wizard we shipped with SCCM R2 and paste your sql query into that wizard and have a report up and running in no time.Thanks,Bhaskar
Free Windows Admin Tool Kit Click here and download it now
January 12th, 2009 8:15pm

Hi Bhaskar, Could you point me to more information regarding the "SQL Based report wizard" in SCCM R2. Can I access this from the console or do I need to login to the site server? Thanks!
December 9th, 2010 9:42am

Hi again Bhaskar...I think I found the wizard you are talking about above just by simply right clicking Reports and choosing New>Reports. Is this the wizard you were talking about? So then I pasted my SQL statement from my query into the Report wizard and then ran the report, but I get this result: An error occurred when the report was run. The details are as follows: Invalid object name 'SMS_R_System'. Error Number: -2147217865 Source: Microsoft OLE DB Provider for SQL Server Native Error: 208 Any help? Thanks!
Free Windows Admin Tool Kit Click here and download it now
December 9th, 2010 9:47am

An error occurred when the report was run. The details are as follows: Invalid object name 'SMS_R_System'. SMS_R_System is not a SQL view it is a WQL view. You need to translate the WQL query to SQL. SMS_R_System = v_R_System in SQL.http://www.enhansoft.com/
December 9th, 2010 1:04pm

Thanks Garth...is there a guide or something out there that lists all the WQL views and thier SQL View counterparts? I don't have access to SQL Studio or anything like what is mentioned in the above posts so I can't look it up myself. All I have is the SCCM Console. Thanks!
Free Windows Admin Tool Kit Click here and download it now
January 6th, 2011 9:50am

Sorry no there is not but as a general rule sms_G = v_GS and SMS_R = v_Rhttp://www.enhansoft.com/
January 6th, 2011 4:19pm

Can anyone assist with turning the following WBL Query into an SQL Report: select SMS_R_System.Name, SMS_G_System_WORKSTATION_STATUS.LastHardwareScan, SMS_R_System.LastLogonUserName, SMS_R_System.OperatingSystemNameandVersion, SMS_G_System_INSTALLED_SOFTWARE.Publisher, SMS_G_System_INSTALLED_SOFTWARE.ProductName, SMS_G_System_INSTALLED_SOFTWARE.ProductVersion from SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId order by SMS_R_System.Name, SMS_G_System_INSTALLED_SOFTWARE.Publisher, SMS_G_System_INSTALLED_SOFTWARE.ProductName, SMS_G_System_INSTALLED_SOFTWARE.ProductVersion I have attempted to convert the SQL statement from the smsprov.log but I'm not having much joy :S Many Thanks.
Free Windows Admin Tool Kit Click here and download it now
February 15th, 2011 11:25am

Can you post what you have translated so far? It is easier for us to help point out the issues to you, so that next time you can do it yourself. http://www.enhansoft.com/
February 15th, 2011 12:59pm

Hi, This is what I have got so far: select all v_R_System.Name0,___System_WORKSTATION_STATUS0.LastHWScan,v_R_System.User_Name0,v_R_System.Operating_System_Name_and0,___ System_INSTALLED_SOFTWARE1.Publisher00,___System_INSTALLED_SOFTWARE1.ProductName00,___System_INSTALLED_SOFTWARE1.ProductVersion00 from System_DISC AS v_R_System INNER JOIN WorkstationStatus_DATA AS ___System_WORKSTATION_STATUS0 ON ___System_WORKSTATION_STATUS0.MachineID = v_R_System.ItemKey INNER JOIN INSTALLED_SOFTWARE_DATA AS ___System_INSTALLED_SOFTWARE1 ON ___System_INSTALLED_SOFTWARE1.MachineID = v_R_System.ItemKey order by v_R_System.Name0,___System_INSTALLED_SOFTWARE1.Publisher00,___ System_INSTALLED_SOFTWARE1.ProductName00,___System_INSTALLED_SOFTWARE1.ProductVersion00 Many Thanks.
Free Windows Admin Tool Kit Click here and download it now
February 16th, 2011 4:15am

I’m not sure where you got this query from as it is not WQL and it is trying to use tables instead of views. · Change System_DISC to dbo.v_R_System · Change WorkstationStatus_DATA to dbo.v_GS_WORKSTATION_STATUS · Change INSTALLED_SOFTWARE_DATA to dbo.v_GS_INSTALLED_SOFTWARE · Change MachineID to ResourceID · Change ItemKey to ResourceID · Change the Alias to something nicer, dbo.v_R_System = R or Sys, dbo.v_GS_WORKSTATION_STATUS to WS, etc. · Adjust column heading to correct column heading, For example Publisher00 = Publisher0, I will also tell you this is a crazy report and be prepared to kill a few trees! I will tell you to read this blog post http://support.enhansoft.com/Blogs/post/How-to-Perform-a-Basic-Software-Audit.aspx http://www.enhansoft.com/
February 19th, 2011 12:12pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics