Turn SCCM SQL Queries into Web Reports

Datetime:2016-08-23 04:04:06          Topic: SQL           Share

The goal of this exercise is to add custom reports to your Configuration Manager site so you can dazzle executives while you sneak out the door.  If you’re smart, you’ll make some that include pie and bar charts with lots of spiffy colors and formatting.  That would give you enough time to get to Starbucks and back before they even look back in your direction.

Ok, serious stuff now.

I’m using (long inhale….) Microsoft System Center Configuration Manager (aka SCCM) “Current Branch” build 1606, with the latest hotfixes, running on Windows Server 2012 R2 and SQL Server 2014 SP1.  In addition, I’ve installed SQL Report Builder 3.0 for SQL Server 2008 R2 **.

If you read any of my posts on SQL queries for SCCM, you can follow the same basic approach for the query building part.  It’s much easier/better/smoother to do that in SQL Management Studio than in the Report Builder.

** If you download SQL Report Builder for SQL 2016, it will look prettier, but the reports won’t import or run in SCCM 1606. It is partly to do with the schema level buried inside the RDL report file.  I’m hoping this gets addressed soon, since SQL Server 2016 is the cat’s meow already.  Anyhow, the SQL Report Builder is installed with the default settings.

Step 1 – Build a SQL query in SSMS

  • (we already did this in previous postshere andhere and a few others)
  • Test the query until you get the results you want
  • Copy the SQL statement to the clipboard and proceed

Step 2 – Create a SQL Report

  1. IMPORTANT: Make sure you log onto the server using an account with permissions to create and view reports
  2. Open SQL Report Builder.
  3. Select “New Report”
  4. Choose the desired wizard (I’m using Table or Matrix Wizard for this example)

  1. Select “ Create a dataset ” at the bottom of the New Table or Matrix form, and click Next.
  2. Select the “ New ” button.
  3. Enter a Name for the data source.  For this example, I used the name of the SCCM database, which is “CM_PS1”.  Leave the other settings as-is, and select the “ Build …” button.
  4. When the Connection Properties form opens, TYPE IN the SCCM SQL Server host name (do not click the drop-down or you will grow old waiting for it to browse your network).  I prefer the FQDN, but the NetBIOS name will also work (usually).
  5. Select the SCCM database which is “CM_PS1” in my example.
  6. Click Test Connection to verify it works, then click OK
  7. Back in the Data Source Properties form, click OK .  (Note that the 2 radio button options will be disabled while creating the Data Source, but afterwards, if you click Edit, they are enabled.  You just can’t do much with them yet.  So just leave it so the connection is embedded in the report for now)
  8. Back in the “ New Table or Matrix ” wizard, you should now see one Data Source Connection (e.g. “CM_PS1”) with a sub-title “(in this Report)”.  Click Next .
  9. You should now be in the Design a Query form.  Click to expand the ‘ dbo ‘ schema in the left-hand Database View panel.
  10. Expand Views and scroll to see how all of the SCCM database views are visible here (or should be).  Now click on the “ Edit as Text ” link at top-left.
  11. Paste your SQL query statement into the top edit box and click the “!” (exclamation) link to run the query.  When it looks right, click Next.
  12. The next form is where you arrange the column structure for the table layout.  In this example, drag the ResourceID field into the “ Row Groups ” box.  Then select all of the fields except ResourceID and drag them into the Values box.  Click Next .
  13. Since this example isn’t using grouping or other aggregate / scalar functionality, it just a raw table output, in the next form (Choose the Layout), I unchecked both options at left, and click Next .
  14. In the “Choose a Style” form, you can change the CSS theme if you like, or leave the default as “Ocean”, and click Finish .
  15. In the Report Designer form, click on the “ Click to add title ” text box and change the name to something relevant.  In this case, I entered “Physical Computers”.
  16. You can do much more from here, but for now, I’m just saving the RDL file so I can import it into the SCCM reports library.  It’s recommended that you prepare or identify a common folder to save your custom report files.  It will make it easier to manage and protect them.  Once you’ve saved the report, you can close the SQL Report Builder if you like.

Step 3 – Import the Custom Report into SCCM

  1. Open the SCCM Report web site in Internet Explorer.
  2. Once you’re at the SQL Server Reporting Services home page, click “ConfigMgr_PS1” (where “PS1” is the site code, so yours may be different).
  3. If you’re like me, which I sure hope not, but in this case, maybe it’s okay, you hate the default folder organization, so I click “ Details View ” at the top right.
  4. If you prefer placing your custom reports into a special folder.  If so, click “New Folder” in the top horizontal menu bar, and give it a cool name.  Then open that folder (or whatever folder you wish to add your custom report into).
  5. Click the “ Upload File ” link in the top horizontal menu bar.  Click Browse to locate your RDL file, select the desired file and click OK . (Note: If you go through multiple import cycles while refining the report, you may need to click the Overwrite option to avoid errors).
  6. Click OK .
  7. Your report is now ready to run!

If your report doesn’t work when you run it, you may need to update the connection assignment.  To do this, click the small down-arrow next to the report, and choose “ Manage

From here, click “ Data Sources ” and update the connection settings as needed.

Note that this is VERY common when importing reports created by others (or downloaded from the web).  Since their connection settings aren’t portable, you will often need to change them using the above procedure, before you can run them.

Bonus – Queries the Hard Way

PS – If you really want to explore building SQL queries entirely within the SQL Report Builder, it is indeed possible.  Here’s how…

  1. When you get to the “ Design a Query ” form, select the fields from the tables or views you wish to merge.  In this example, I included “ v_R_System “, “ v_GS_COMPUTER_SYSTEM ” and “ v_GS_SYSTEM_ENCLOSURE ” in order to pull the fields ResourceID, Name0, AD_Site_Name0 , Model0, TotalPhysicalMemory0 , and SerialNumber0.
  2. This is where it gets funky : In the center of the form, you will see “ Auto Detect ” highlighted.  Click on it to disable that link, and the itty-bitty, teeny-weeny itsy-bitsy icon to the right of “ Edit Fields ” becomes active.  If you move the mouse pointer over it, the tool tip will show “ Add Relationship “, but if you click, you may not see anything happen.  It did something, but hid it from you.
    ( NOTE : This form was developed by blind people in a cave at night.  There is no Min/Max options on the frame heading at top-right.  Just a typical red “X” to close it.  However, you can resize the form).
  3. Click the tiny-little double down arrows directly to the right of that icon.  That will expand a hidden panel for “ Relationships ” (you might call this an Easter Egg).
  4. Click in the empty gray box under “ Left Table “.  A weird looking popup listbox will appear.  For this SQL join, the left table will be v_R_System .
  5. The box under “ Right Table ” should now become highlighted.  Click in that box to select “ v_GS_COMPUTER_SYSTEM
  6. Double-click in the “ Join Fields ” box and select the little icon at top-right that looks like microscopic, hot-water-shrunk database tables.  Click on it and it adds a row in the table grid below.  The “ Left Join Field ” and “ Right Join Field ” values will be empty.
  7. Click in the “ Left Join Field “.  It will display a list of the fields (columns) in the left table (e.g. v_R_System ).  Select ResourceID .  Repeat this for the “ Right Join Field ” and select ResourceID as well (from v_GS_COMPUTER_SYSTEM ).  Click OK
  8. Change the Join Type from “ Inner ” to “ Left Outer “.
  9. Because we’re joining three (3) tables/views, we need to add another join statement, so click the microscopic icon again and assign the Left Table to v_R_System again, but select v_GS_SYSTEM_ENCLOSURE for the Right Table .
  10. Repeat the same process to select ResourceID for the left and right join fields in the popup form.  Make sure the Join Type is also “ Left Outer ” as was the first join.
  11. Click “ Run Query ” to test and verify the results.

And now you should see why it’s easier to build queries in SQL Server Management Studio.

Cheers!





About List