Saving client health (or other) data to a SQL Server
Summary: most computer management specialists write and run SQL queries, but not many save data for later use. Yet saved data can allow you to do historic comparisons, trending, complex dataset queries, and other powerful analysis.
I routinely save client health data and then run queries against that saved data to produce graphs of client health trends. An example is my blog posting on client activity history. I often show those reports to people, in public and private forums, and a common question is how to produce such reports. I'm pleased to share the general process with you, in five parts: the queries, saving the data, producing HTML reports from script (automatically), producing graphs from script, and e-mailing the reports to the interested parties from script (we won't go into every detail, but will cover the key points that might not be obvious). Today we'll focus on saving the data.
WARNING: Do NOT write data to your SMS or ConfigMgr databases. Instead, create a seperate database (anywhere, even on your console machine). Anytime you directly write to the SMS or ConfigMgr databases you run various serious risks of corrupting production data, losing data, creating future conflicts that will cause upgrades to fail, or causing various other unpredictable problems that no one can help you correct (other than to rebuild the site from scratch). Even backups won't help, because the bad data could exist for a long time before the problem is detected, and thus be stored in all your backups. You might even want to use a seperate computer, just to minimize the risks as much as possible (the risks including performance or capacity problems, or accidentally using the wrong database). Installing a seperate instance of SQL Server somewhere else is quite easy, as is creating a database (see the online help if you don't find it intuitive). It's true that setting up a serious large-scale production SQL Server is a task that must be left to a professional DBA, but we don't need anything near that serious.
Given that you have a database, it's actually quite easy to save data using SQL Server. The first thing you'll need is a table, and SQL Server's Enterprise Manager makes that easy. Navigate to the Tables node, right click, and select New Table... It will prompt you for column name, datatype, and whether NULLs are accepted ('yes' is a reasonable answer in most informal cases like ours). You don't have to worry about adding all the right columns now because you can come back and add more later if you like (by right clicking the table name and selecting the Design menu option). You might like to use some kind of naming convention for your columns, to have some consistency that will make your code more readable later, but that's a minor point. When you close the window, SQL Server will ask for a name for the table. Anything will do, but a naming convention can help there too.
Now that you have a table, you'll want to put data into it. You could just "Open Table" the table in SQL Server Enterprise Manager and start typing. That works but it's not automatic at all, so there's rarely any benefit to it. Better yet is to "insert" data. If you have reason to change it (maybe correct it), you can "update" the data. Or if you end up with some bad data that you don't need, then you can "delete" it. Inserting is quite safe, but updates and deletes can easily affect data that you didn't intend to affect (maybe all of it), so you have to be more careful with them. Backups are good, of course. Or experiment on a test table until you're confident you understand the syntax (online help, a book, or an Internet search engine will soon get you up to speed on any syntax subtleties you need). Aren't you glad you're not doing this in the SMS database? ;-)
I always save my data using a bit of vbscript that is a varient on the following example. I've greatly reduced the number of columns in the example, for readability, but otherwise it's a complete code sample. (Well, you'll have to connect to the database, and come up with the data values somehow, but that's exactly the same as any script that queries for data (using SQL "select" statements) - there's lots of examples of that on the Internet).
If you're not inclined to use vbscript (or similar scripting), you can still save data by using the SQL statements directly. You could even write up the relevant SQL statements (a SQL script) and run the script automatically every day by creating a SQL job (see the online help for that, but it's fairly straightforward).
To save the data as a new record, you would use a statement like: INSERT INTO ClientHealth_CHT VALUES ('central', 'CEN', '1/1/2008', 3282, 3875, 4257)
To update (change) the record, you would use a statement like: UPDATE ClientHealth_CHT SET polled_1day=3175, polled_7day=3986, client_count=4256 WHERE sitecode='CEN' AND date_stored='1/1/2008' AND environment='central'
For those who are into vbscript (or might get into it), here's my subroutine for saving data. You'll have to change table and column names, and add columns, as needed for your table.
todays_date = left(now, instr(1,now," "))
Sub Save_Data( hierarchy, sitecode, polled_1day, polled_7day, total )
'check if the record already has the numbers for this day
exists=vbFalse
SQL="Select * FROM ClientHealth_CHT WHERE sitecode='" & sitecode & "' AND date_stored='" & CDate(todays_date) & "' AND environment='" & Left(hierarchy,30) & "'"
objRS_Store.Source = SQL
objRS_Store.Open
if objRS_Store.RecordCount>0 then exists=vbTrue
objRS_Store.Close
if NOT exists then
data = "'" & left(hierarchy,30) &"','"& sitecode &"',"& polled_1day &","& polled_7day &","& CDate(todays_date) &"',"& total
SQL="INSERT INTO ClientHealth_CHT VALUES (" & data & ")"
objRS_Store.Source = SQL
objRS_Store.Open
wscript.echo "saved the data for " & hierarchy & " " & sitecode
Else
SQL="UPDATE ClientHealth_CHT SET polled_1day=" & polled_1day & ", polled_7day=" & polled_7day & ", client_count=" & total & " WHERE sitecode='" & sitecode & "' AND date_stored='" & CDate(todays_date) & "' AND environment='" & left(hierarchy,30) & "'"
objRS_Store.Source = SQL
objRS_Store.Open
wscript.echo "updated the data for " & hierarchy & " " & sitecode
End If
End Sub