Creating Custom Reports Sccm 2012 Text

Jonathan Friesen - Writing Coach

Reporting in configmgr 2012 is a powerful way to get alot of information about almost everything in your environment. If you search on google for how to create a custom report youll get several great articles/posts on the subject. What most of them lack though is how to create the actual query part of the report. You could of course search the internet and hope that somene else has already published a query for exactly what youre trying to accomplish, but most of the time youll not find what youre looking for.

Academic Position Paper

In this post ill walk through the steps to create a custom report for usage in the configmgr console, and the best part, you dont need any knowledge on how to write sql queries. Environment setup create an in console query locate the sql query statement create a custom report run the custom report in order to use the reporting functionality in configmgr. Youll need to have the reporting services point site system role installed on the sql server for your primary site server. In my lab environment ive got a primary site server called cm01.contoso.com where the sql server is running locally. In my case ive installed the reporting services point on cm01.contoso.com.

Its recommended to use a dedicated service account as the reporting services point account. The picture below shows that the reporting services point account is set to a domain account called contoso\cm_rs. Additionally my environment looks like this: configmgr 2012 r2 sql server 2012 sp1 with sql server report builder 3.0 you may think, why are we creating an in console query first? the answer is simple, we will be using the this query to get the correct sql query statement that is needed in order to create a custom report. In this post well be creating a simple query to get all devices with any version of silverlight installed. Go to the monitoring node, right click on queries and select choose create query. Click on show query language and paste the following wql query into the query statement field: this post will not cover the basics of creating a query. locate the sql query statement now that weve created the query to get devices that has silverlight installed, we need to get our hands on the sql query statement.

When you are working in the configmgr console, almost anywhere you click youre querying the database for information. This information is being collected by the sms provider and sent back to be shown in the console. So if we were to run our newly created query from the configmgr console, wed ask the sms provider to get the results from our sql server.

Its being generated as a wql query wmi query language which cannot be used to get information from a sql server directly. Now, heres the really awesome part. since the sms provider is a wmi provider, the sms provider needs to interprete and convert the wql query into a sql query instead. This conversion can be found in the smsprov.log file, located in c:\program files\microsoft configuration manager\logs if youve installed configmgr 2012 in the default location. In the monitoring node, right click on the newly created query that we called devices with silverlight and select run. Open smsprov.log with cmtrace.exe and look for a row starting with execute sql. Compare the row with the wql query we used earlier: if the row youve located seems to be the correct one, copy the contents of the whole row into e.g. You now have the full sql query statement which can be used to create custom reports.

Right click on reports and choose create report. In this example ive choosen software distribution application monitoring.  in the left pane, right click on data sources and select add data source. Select use a shared connection or report model and click browse. If the connection was successfully established, youll see the following message: 13.

In the data source properties window, click on ok. Right click on datasets and choose add dataset in the left pane. In the name field, enter sqlquerystatement can be whatever you want, but no spaces or special characters. Select use a dataset embedded in my report. select the configmgr item from the data source drop down. In the query field, paste the saved sql query statement from earlier. In the enter data source credentials window that pops up, enter the password for the reporting services point account see note about this account in the beginning of this post. If you didnt get an error, click ok and skip the next part and jump directly to step 18.

How to Write a Great College Scholarship Essay

From the details of this error message, we can see that there is a permissions issue. In order to resolve this, we need to give the reporting services point account the permissions to read from the database. Leave all the report builder windows open, and launch sql server management studio on the sql server and logon. Right click on the reporting services point account in my lab environment thats contoso\cm_rs and choose properties. Under database role membership for: cm_ lt site_code gt put a check mark next to db_datareader and click ok. Go back to the dataset properties window from before and click on refresh fields again.

How to Go About Writing a Dissertation