How To: Build Client Health Trend Line

I’ve received great feedback on my previous post, Client Health – More than Just Percentages, as well as several requests for the SSRS .RDL file. Instead of sharing the file, I’m going to talk you through the process to create it yourself! First, let me say with that previous post, those are examples, and you probably wouldn’t show them all on the same page.  I’ll break these down into a couple posts.

This example is to create the trend line report to get a better understanding of client health over time (both percentage, and client count).

Prerequisite: You must have Client Heath options configured to retain the data for 30 days (or whatever number you require). In the admin console, go to Monitoring->Client Status, and select Client Status Settings. You can see for my environment, I’m retaining the information for 31 days.

DesignAQuery_0_20171120

Here is the SQL used for the reports – just update the SiteCode and desired CollectionID.

SELECT  [Date]
      ,[CollectionID]
      ,[SiteCode]
	  ,ROUND([ClientsActive] * 100.1 / [ClientsTotal], 1) AS PercentActive
	  ,[ClientsActive]
	  ,ROUND([ClientsHealthy] * 100.1 / [ClientsTotal], 1) AS PercentHealthy
	  ,[ClientsHealthy]
  FROM [v_CH_ClientSummaryHistory]
  where [Clientstotal] > 0 and datediff(day,[date],getdate()) < 30 and SiteCode='GMR'
and CollectionID='SMS00001'
  order by date

 

1. Using Report Builder, create a new report and connect to your ConfigMgr database. After you’ve verified connectivity, you’ll see the “Design an query” page. Select “Edit as Text”:

DesignAQuery_1_201711202. Paste your query, and click the red exclamation mark to run it (always test it here, to ensure you receive the expected results):

DesignAQuery_2_20171120

3. Click Next, and then chose “Line” for the chart type, and then Next again. Now you need to select the fields, as shown here:

DesignAQuery_3_20171120

4. Click Next again to see a preview (don’t pay much attention to the shape of the graph in the preview – it’s not using your values), and then Finish.

5. Now from this view, add a catchy title (“Client Health Counts – Active vs. Healthy”) and click the Run icon in the tool bar to view the data. Nice start, but still room for improvement.

DesignAQuery_4_20171120

6. Click Design to clean this up a little. Let’s modify the scale, so we can focus on what’s important. Single-click on the Y axis (you may need to click it a second time to get proper focus), and then right-click to select “Vertical Axis Properties.”

DesignAQuery_5_20171120

7. The easy button is to clear the Always include zero option and click OK:

DesignAQuery_6_20171120

8. Perform similar steps to get to “Horizontal Axis Properties” and change the Axis type to Scalar:

DesignAQuery_7_20171120

9. You now have a pretty nice graph to show you client health

DesignAQuery_8_20171120

Now that you’ve successfully created “Client Health Counts – Active vs. Healthy”, go back and create a similar report to show percentages instead of counts (Hint: The SQL query above contains all the info you need).

Stay Tuned! I’ll follow up this post with a couple more to cover gauges and spark lines!

 

Greg

 

About Greg Ramsey
Greg Ramsey is a systems engineer specializing in global systems management for Dell IT. He has a B.S. in Computer Sciences and Engineering from the Ohio State University and is a Microsoft Most Valuable Professional (MVP) for Microsoft System Center Configuration Manager. Greg coauthored SMS 2003 Recipes: A Problem-Solution Approach (Apress, 2006), Microsoft System Center Configuration Manager Unleashed (Sams, 2009), Microsoft System Center 2012 Configuration Manager Unleashed (Sams, 2012), and Microsoft System Center 2012 Configuration Manager: Administration Cookbook (Packt Publishing, 2012), and Microsoft System Center 2012 Configuration Manager R2 Unleashed (Sams, 2012). Greg is cofounder of the Ohio SMS Users Group, and the Central Texas Systems Management User Group.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: