Don Hite at myITforum.com

Creating A SMS Client Health Monitoring Tool Web Report

Here you will find information on how to create an SMS Web Report for the Client Health Monitoring Tool database.

Note: This assumes that you have the SMS client health monitoring tool installed in your SMS_XXX database where XXX is your sites three letter site code. If the SMS Client Health Monitoring Tool is installed in another database you must specify the fully qualified naming convention to the database as a linked server.

First you need to create a Cleint Health View and apply the appropriate permissions using the SQL Query below:

Note: You can change the View name as needed by replacing all the instances of v_R_ClientHealth with the view name of your choice in the script below. Also change the Use SMS_XXX to your database name where XXX is your sites three letter site code.

SQL Query:

Use SMS_XXX

If Exists (Select * From dbo.SysObjects Where ID = Object_ID(N'[v_R_ClientHealth]')

and ObjectProperty(ID, N'IsView') = 1)

Drop View v_R_ClientHealth

Go

Create View v_R_ClientHealth As Select

ResourceID,

Name 'Machine Name',

Guid,

Sitecode 'Site Code',

LastUser 'Last User Name',

Type 'Client Type',

Version 'Client Version',

LastDDR 'Last DDR Recieved',

LastHW 'Last Hardware Scan Date',

LastSW 'Last Software Scan Date',

DDRState 'Last DDR State',

HWState 'Last Hardware State',

SWState 'Last Software State',

TOTState 'Last TOT State',

Active 'Active Client',

Obsolete 'Obsolete Client',

ChangeActive 'Active Status Change',

ChangeObsolete 'Obsolete Status Change',

LastPing 'Last Ping Time Stamp',

LastPolicy 'Last Policy Time Stamp',

WasOnline 'On Line',

LastPingResult 'Last Ping Status',

Classification 'Last Client State'

From ClientHealthResults

Go

Grant Select On v_R_ClientHealth To smsschm_users, webreport_approle

Go

Select * From v_R_ClientHealth

To create the Web Report follow the steps below:

Note: To use the SQL script as a web report copy and paste the Name, Category, Description and of course the SQL statement shown below to the appropriate new web report boxes or modify to your liking as needed.

Name: Client Health Results

Category: Client Health Reports

Description: Displays the current Client Health information for the site..

SQL Statement:

Select *

From v_R_ClientHealth