Paul Thomsen at myITforum.com

Summary: in recent posts we've examined some techniques for automatically generating and delivering highly customized reports. Here's some examples (especially the graphs).

The techniques I posted enable you to automatically produce highly formatted, analytical computer management reports, complete with graphs and e-mailing to your internal customers. But before you invest the effort into producing them you'll want to visualize what they could look like, and how that benefits you. This posting gives a variety of examples. (Some time ago I blogged a couple of graphs, and occasionally I've shown some in various presentations, so they may look familiar.)

This is a very typical report format example:

The "active" (left) graph is ConfigMgr client activity as seen at the central site on one of our hierarchies after a week. The "healthy" (right) graph is after two weeks. You'll notice the sudden dip in mid November. Clearly we had a serious infrastructure problem at that time. These reports often help to alert us to such issues, so that if our server monitoring doesn't catch the event, we can quickly go looking for it (and in this case we can see our Infrastructure Team fixed it within a day or so). There's a smaller dip at the end of December, which would be the holiday period. Because these reports show activity after a week or two, the dips don't become obvious right away (unless the issues is especially bad). You'll also notice that the table gives the numbers for the day the report was produced, and even highlights less-than-optimal number by giving them a yellow background.

The following snippet is for another hierarchy which happens to be smaller and more stable, as is typical for most people's "production" hierarchies. The swings in activity are less dramatic. The holiday dip is the only real anomaly.

This is a graph for a particular site:

This site obviously had a serious problem in late November. It also had the holiday dip in late December. Since then the software inventory and advertisement activity have been very consistent with each other and at a respectable 80% level (in one week). The heartbeat (green) activity is much higher (90%), but that's much more frequent, and so computers that are online for even a brief period will report heartbeat where they might not report the other activity. It's also simpler, so clients that are 'partially healthy' might report heartbeat where they wouldn't report other activity.

The most important point with that graph is the descending blue line - there's no good reason to explain that descent. So obviously something's wrong with hardware inventory at this site. Someone should be investigating it.

From those graphs we're obviously seeing a trend that no matter which site or hierarchy we look at, there's a dip in late December. And yet these are 'percentage' reports, rather than absolute numbers. So you might wonder why the percentages would dip in December - sure, there's less activity then, but there should also be less computers online. So the activity as a percentage of online computers should be about the same. But these reports don't account for offline computers - they just report on the basis of the total ConfigMgr (SMS) population. That's why I call these "activity" reports - it's the total activity for the client population. We'd like to distinguish online vs. offline, and offline vs. broken, but those are difficult challenges in themselves. It's much easier to just report on the whole population. I've talked about those issues a bit in previous posts, and will get into them more in future posts.

Client health reports are not always about 'client activity'. Here's one for 'client reach' (how many clients are in the hierarchy):

We can see that in early November there was a nice (and gradual) increase in the client count. That reflects a new effort to increase the client reach (a change of boundary strategy, in particular). Through December the numbers declined a little bit, probably reflecting that users were decomissioning computers faster than they were buying new ones - people generally don't start projects when holidays are imminent, and they do cleanup their environment. In mid January there was a sudden drop of about 20,000 clients - this corresponds with a new 'dogfooding' (beta) project where we needed to seperate a site from the hierarchy. So that's an intentional drop.

The above reports have largely looked at activity for hierarchies and sites over the previous week or two for each day. What if we look at it one day at a time? Here's a datacenter site where the heartbeats are set to every 2 days, on a fixed (as opposed to relative) schedule:

There seems to be a couple of days where the heartbeat activity dipped dramatically, but then recovered. That might be a reporting problem, as opposed to an infrastructure problem - that's one downside of complex, script-based reporting: subtle anomalies can expose bugs in the scripts, causing a failure to record data (until the bug is fixed or the anomaly passes).

Here's a day-by-day report for a desktop (non-datacenter) site with more typical agent settings:

That's a good example of why it's generally not a good idea to look too closely at client health or client activity - there are too many anomalies, making it difficult to extract meaning from the reports. But we have started reporting activity at the daily level over the last few months, and it has proved useful in some cases. That's especially true when a serious problem occurs and some activity drops to zero, and then we fix it and want to verify it goes back to normal (at least above zero).

For contrast, here's a datacenter site, looking at 2-week activity. It's hard to get closer to 100% success than that. Of course data center servers don't move around, are powered up all the time, aren't rebuilt frequently, etc. But we can dream of 'desktop' client health looking like this:

From the above discussion you might say that these are actually reports of site and hierarchy health, rather than client health. There's a lot of truth to that, but the client health story is still contained in these reports. Server-side problems affect all your clients all at once, and so they tend to be dramatic, but they're also relatively easy to fix (fix the configuration, disk, or whatever, and you're done). Environmental and client-side problems take longer to fix because there's many more moving parts, and your options for making the repair are more limited.

The important point is that by establishing trends over 'normal' periods, you can tell when client-side or environmental issues get worse (or better). If your clients generally show 85% activity over 1 week, then if you start seeing 80% activity then a change in strategy may be needed. If you go to 90%, then you must be doing something right. And if you've gotten 85% activity for the last year, in a well managed shop, then you can confidently tell management that 100% is never going to happen (unless they chain the users to their desks).

So there's some examples of client health reporting using 'complex' reporting. I've got others that are in 'prototype' stages. And there are other subtleties to the reports above. But you get the idea. What's most important is that you think about your reporting needs and consider applying the techniques I've detailed to make your computer management even more successful.

I've focused on client health here, but the same techniques can easily be used for patch management compliance reports, software distribution reports, OSD reports, DCM reports, etc. The queries will be different, but the technical implementation will be almost identical. More importantly, the business benefit (to your internal customers) will be comparable as well. So it is worth your time to invest a little effort in these techniques.

Summary: scripts can analyze your systems in powerful and flexible ways, producing detailed and well-formatted results. Sometimes it's handy to e-mail those results to people.

It's time to finish our series on producing complex computer management client health reports. I've divided the process into five parts:

1. the queries
2. saving the data
3. producing HTML reports from script (automatically)
4. producing graphs from script (and figuring out how to enhance those graphs)
5. e-mailing the reports to the interested parties from script

E-mailing is one of the easiest parts of the process.

Sub Email_It( results_file )

  strBody = fso.OpenTextFile( results_file ).readall
  const ADS_NAME_INITTYPE_GC = 3
  Dim nto : Set nto = CreateObject("NameTranslate")
  nto.Init ADS_NAME_INITTYPE_GC,""  'this seems to be necessary for authentication

  SET objMsg = WScript.CreateObject("CDO.Message")
  With objMsg
   .From = primary_recipient
   .To = primary_recipient
   .CC = secondary_recipients
   .Subject = "A meaningful subject line"
   .HTMLBody = strBody
   .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
   .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = emailserver
   .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
   .Configuration.Fields.item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate")=2
   .Configuration.Fields.Update

   success=false
   while success=false
     on error resume next
     .Send
     if err=0 then
        on error goto 0
        success=true
        wscript.echo "results e-mailed"
     else
        wscript.echo "e-mail send did not work. Error: " & err & " - " & err.description
        on error goto 0
        wscript.sleep 1000  'wait a second before trying again
     end if 'send was successful
   wend 'successful send

 End With

End Sub

The subroutine probably won't need any changing. It just needs three values (variables):

1. the output to be e-mailed (the "results_file"). That's where steps 3 and 4 of our client health report process come in, but the steps could be as simple as setting the "strBody" variable to a text string.
2. a server name to set the "emailserver" variable to. It has to be SMTP capable, which might be true of your corporate Exchange (or equivalent) server. If you don't have such a server, there may be SMTP servers on the Internet, but I don't know the details for them. You'll have to search for them.
3. The "primary_recipient" and "secondary_recipients" variables. They're simple strings with this kind of format: "Some Body <somebody@dummy_company.com>; Some Body Else <somebody.else@dummy_company.com>"

Summary: SMS and ConfigMgr, and possibly other systems, make a lot of logs available in case you have to troubleshoot a tricky problem. If you need to look at some of those logs on a lot of clients or sites, then doing so manually will be very labor intensive. Why not do it via script?

There are a wide variety of problems where you have to look at a lot of logs. For example, the problem might be rare but nasty. Or it might be fairly common but counterintuitive - some people may think that the problem is so unlikely that the small subset of logs you manually look at are not representative of the rest of your clients or servers. Maybe the problem comes and goes. Maybe it occurs under special circumstances but you don't know which ones, yet. Or maybe you're just a perfectionist and you want to find every last problem.

Some relatively simple scripting will allow you to get a computer to do the log analysis.

The core part of the code is:

    set fso = CreateObject("scripting.filesystemobject")
    contents = fso.OpenTextFile( logfile ).readall
    lines = split( contents, vbCRLF )
    linecount = ubound(lines)
    for i=0 to linecount
          if instr(1, ucase( lines(i) ), "ERROR") then wscript.echo "error found in " & logfile & " at line " & i & ": " & lines(i)
    next

The "if" statement is actually where you'll probably do most of the coding. There will likely be a lot of lines with the word "error" (or whatever else you're looking for) that aren't relevant to your issue. So you'll need to add a little logic to filter those out. Or maybe you just want to see the first occurence of each error (some errors repeat MANY times).

The next question is: how do we get the large number of logs we need? There are two approaches to that:

1. create a small script to write the log back to a common share. The script should make a folder for each client or include the computer name in the log name so the logs don't get overwritten (and so you can further investigate any suspicous clients). Even a .BAT file can do that. Then advertise the script to all your clients (or a relevant subset). The only trick in this case is to spread the workload over time so you don't have 10,000 clients trying to copy a log to the share all at once. And if the share is on a workstation (as opposed to a server), then remember the 10 connection limit.
2. create a read-only share on all clients that points to the %windir%\system32\ccm\logs folder. You might as well restrict access to just you and your fellow admins, and maybe even make it a hidden share.

For option 1, something like this batch file will do (substitute in your own server, share, and folder names, of course):

if not exist "\\<server>\<share>\<folder>\%computername%" mkdir "\\<server>\<share>\<folder>\%computername%"
copy %windir%\system32\ccm\logs\execmgr.log \\<server>\<share>\<folder>\%computername%

For option 2, I don't have a script that I can share at this time. So I'll leave that as an exercise for you or the community. It is on my ToDo list.

If you go with option 1, then once you've got that share with a bunch of folders and logs, the next trick is to get your script to work through the folders and logs, so it can do the above checking. Code like this will do that:

 Set mainfolder = fso.GetFolder( path )
 Set folders = mainfolder.SubFolders
 for each folder in folders
     set folder = fso.GetFolder(path & "\" & folder.name)
     Set files = folder.Files
     For each file in files
        wscript.echo path & "\" & folder.name & "\" & file.name
        'parse it, as above
     next 'file
  next 'folder

If you go with option 2, then you'll first need a list of the relevant clients. I do a SQL query and save the results to a file named temp.txt. Then the following logic will check the logs on each of those clients:

  set infile = fso.opentextfile("temp.txt")
  computers = split(infile.readall,vbcrlf)
  for each computer in computers
    on error resume next
    set folder = fso.GetFolder( "\\" & computer & "\" & logshare )
    if err<>0 then
          wscript.echo "  " & computer & " online but the " & logshare & " share is not available: " & err
          on error goto 0
        else
            filetoget = "\\" & computer & "\" & logshare & "\" & filename
            on error resume next
            test = fso.GetFile( filetoget )
            if err=0 then
              on error goto 0
              'parse it, as above
            else
              on error goto 0
            end if 'get file
       end if 'got folder

Either way, you've soon got your computer reading lots of logs. You can concentrate on the serious business of analyzing the results.

In the 'credit where credit is due' department, I can't claim credit for the idea of creating a share for the ccm logs folder on all clients (option 2 above). That was Levi Stevens. And my coworkers and I have written a bunch of scripts for log collection ever since I joined Microsoft IT, so some parts of the other ideas are no doubt inspired by coworkers as well.

p.s. Bonus material 1: SMS and ConfigMgr logs turn over so that they don't grow to consume the whole disk. That complicates log reading a bit because the current log might be very short, and thus not tell you the whole story for that client or component. You can open the previous log the same way we did in the first code snippet above, and prepend the contents before doing the split (i.e. contents = contents_prev & contents). If the logs end in ".log" and ".lo_" then that's trivial. But what if there's multiple previous logs, as with Configmgr clients, then you can use this logic:

Function Get_Previous_Log( path, filename )

 set folder = fso.GetFolder(path)
 Set files = folder.Files
 for each file in files
    if ucase( left(file.name, len(filename) ) ) = ucase( filename ) then
      if ucase(file.name)<>ucase(filename & ".log") then if ucase(file.name) > ucase(prevfile) then prevfile = file.name
    end if
 next
  if prevfile<>"" then Get_Previous_Log = prevfile

end Function

Bonus Material 2: if you use option 2 to grab logs from the clients, you'll find that many clients aren't online, or are on slow links from where you are. The code above will work in those cases, but it's even better to ping the clients first, so you can filter out those that aren't online or are too slow. This will do that:

  Set loc = CreateObject("WbemScripting.SWbemLocator")
  Set WbemServices1 = loc.ConnectServer( "", "root\cimv2" )
  tooslow = 100 'ms

   Set PingStatus = WbemServices1.Get("Win32_PingStatus.Address='" & computer & "',Timeout=1000")
    if PingStatus.StatusCode = 0 then
      if debugmode then wscript.echo "  " & computer & " is online"
      if pingstatus.responsetime < tooslow then <try to get the folder, as above>
   end if 

UPDATE1: some people have pointed out another similar solution (well, partially similar):

http://geekswithblogs.net/drewby/articles/LogParser.aspx

That's based on the Microsoft Log Parser tool:

http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en

Someone has even created a GUI front-end for it:

http://www.softhypermarket.com/Visual-LogParser-download_46061.html

And Jamie Moyer gives a good summary of those solutions:

http://blogs.msdn.com/jamoyer/archive/2008/01/28/analyzing-sms-sccm-log-files-the-easy-way.aspx

UPDATE2: for the "option 1" method of copying files, the sample script I gave was for 32-bit clients. In particular, it finds the files at %windir%\system32\ccm\logs. But on 64-bit clients, the normal location is %windir%\sysWOW64\ccm\Logs. And 64-bit clients are getting more common these days (aren't they?). So some extra logic should be added to that process to copy from the appropriate location.

"Option 2" above doesn't suffer from that problem because it's copying from a pre-existing share. But the logic that makes that share available should also allow for 64-bit and 32-bit clients.

Either way, here's some vbscript that should help (but I don't guarantee that it works in 100% of the cases):

Set oFSO = CreateObject("scripting.filesystemobject")
sRoot=oShell.ExpandEnvironmentStrings("%SystemRoot%")
sTEMP=oShell.ExpandEnvironmentStrings("%TEMP%")

'what kind OS is this (probably)?
If oFSO.FolderExists(sRoot & "\sysWOW64\ccm\") Then
  sOS = "Windows64bit"
ElseIf oFSO.FolderExists(sRoot & "\system32\ccm\") Then
  sOS = "Windows32bit"
Else
  sOS = "Unknown"
End If

If sOS = "Windows64bit" Then 
  'logs will be at sRoot & "\sysWOW64\ccm\Logs"
ElseIf sOS = "Windows32bit" Then
  'logs will be at sRoot & "\system32\ccm\Logs"
Else
  'if you need to put logs somewhere, this should do: sTemp
End If

Summary: in a recent posting I detailed how I create graphs from scripts. This quick posting illustrates how you can expand on that subroutine.

That recent posting on creating graphs is part of a series of postings that details how you can create complex, automated, highly formatted computer management reports that you can even automatically e-mail to your internal customers.

Recently one of my report customers asked that I include an overall numeric average in the graphs themselves for the client health trend lines. Using the chart title is an obvious way to do this, since I'm not currently using the title and it would place the value in a consistent prominent spot on all the graphs. But what is the code to add a title?

So I opened Excel, typed in about 5 dummy values and graphed them manually (clicking the relevant buttons, etc.). Then I started the macro recorder and added a title manually. I stopped the recorder and looked at the macro it had recorded:

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SetElement (msoElementChartTitleCenteredOverlay)
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.ChartTitle.Text = "Test"
    Range("R13").Select

That's easy enough, but how does it relate to my script? Looking at the script, I can see that I already have an 'active chart' ("xl.ActiveShart"), so I don't need the first and third lines. And I can see that when I'm referring to the active chart in my script I have to prepend it with the Excel object ("xl."). So the second and third lines need that.

The tricky bit is that my script won't know what the constant "msoElementChartTitleCenteredOverlay" is. Maybe there's a doc somewhere that I could look it up, but that could be hard to find (this is obscure stuff). So why not get the macro to tell us?

That fifth line in the script above was just an accident when I move away from changing the graph and selected a cell, before stopping the macro recording. But it gives us part of the syntax we need. And from my graphing script we have a line where we give cells value, so we know the syntax is "Range("R13").value=msoElementChartTitleCenteredOverlay". I added that to the macro and ran it, and the value that popped into the cell was "1". So the value of that constant is 1.

Now we're all set to add the title logic to the script:

    xl.ActiveChart.SetElement(1) 'msoElementChartTitleCenteredOverlay
    xl.ActiveChart.ChartTitle.Text = "Average: " & average

As to where these two lines should be put in the script, it has to be after the chart is added ("xl.charts.add"), but it doesn't really matter where.

And of course I had to calculate the value of the "average" variable, but that's just traditional scripting logic (I totalled up all the daily percentages returned via the SQL query, and the number of such percentages, and then just before adding the title I divided the grand total by the number of percentages to get the average percentage).

So that's how you extend the graphing logic with any bonus features you want. It's actually quite easy. It took me longer to write this posting than it did to figure out, implement,and test the actual change. So go get creative!  ;-)

BONUS MATERIAL: I applied the above idea to another one of my reports but found the font was too small to read in the condensed graphs I include in my e-mails. So I used the same approach to figure out how to increase the font size but without any luck. The macro recorder ignored the font size increase. However, the macro editor supports 'intellisense', which means that it will tell you what methods and properties are available for each object. An object viewer or (of course) documentation will do the same. With a little exploration I determined this is the relevant code:

xl.ActiveChart.ChartTitle.Characters.Font.Size = 40

So that's another technique you can try when developing scripts to create graphs (or similar features).

UPDATE: I recently decided to take mercy on my internal customers and stop forcing them to receive my client health reports in e-mail. They still have that option, but they can also go to a web page instead. Maybe I'm mellowing as I get older, but the main reason for this change is actually that the reports are becoming more numerous, and so the spam was getting substantial. I believe it's important to nag some people about client health on a daily basis, but not necessarily everyone.

In so doing, I found that the small graphs don't look so good when viewed using a web browser. After much investigation and experimenting, my guess is that Word (our e-mail reader) imports the graphs in their full size, converts them to some format that is Word-friendly, and then downsizes them to the prescribed size as per my HTML files. The web browser presumably just does a straight downsizing in the original format, which loses pixels.

My solution to this was to make the graphs smaller in the first place, so there would be less downsizing and thus less loss of pixels. That works quite well. In particular, I had to specify the graph sizes (soon after creating them):

  xl.activechart.ChartArea.Height = 200
  xl.activechart.ChartArea.Width  = 300

Of course then the large fonts used for the axis titles, etc. were way out of proporation. So I brought them down from 32 to about 12 or so. For example:

  xl.Selection.TickLabels.Font.Size = 12

Even the line sizes seemed a little too thick, so I decreased them:

  xl.Selection.Border.Weight = 3 '4=xlThick

Now the graphs (and the whole reports) look good when viewed with a web browser, and are still better when viewed as e-mail.

In this case, I figured out what changes to make using a combination of experimentation and the "BONUS MATERIAL" approach.

Summary: creating graphs from your scripts is an especially powerful way to enhance your automatically-produced reports.

Today we continue our series on producing complex computer management client health reports. I've divided the process into five parts:

1. the queries
2. saving the data
3. producing HTML reports from script (automatically)
4. producing graphs from script
5. e-mailing the reports to the interested parties from script

The secret to coming up with this graphing code was to enable macro recording in Excel and then create the graph manually using sample data. Then open the macro and cut and paste the code to your vbscript. A little tweaking might be needed, but not a lot. I do that when I need to add or adjust a feature, such as using dashed lines, headers, or other features.

So here's the sample code. You'll have to adapt it for your tables, columns, output files, and possibly minor formatting details.

  if GraphHealth( environment, sitecode, 1 ) then
    tf.writeline "<td width=""225"">"
    tf.writeline "<a target=""_blank"" href=""" & rw_share & "\email_graphics\CH_online1_" & replace(environment," ","") & sitecode & ".gif"">"
    tf.writeline "<img border=""0"" src="""     & rw_share & "\email_graphics\CH_online1_" & replace(environment," ","") & sitecode & ".gif"" width=""225""></a></td>"
  end if
... 

 Function GraphHealth( environment, sitecode, days )

 'start Excel (but not visibly)
 Set xl = CreateObject("Excel.Application")
 xl.Workbooks.Add
 'xl.visible=true  'for debugging only
  
 'get the data to be graphed
 objRS.source = "select date_stored, polled_" & days & "day, ping_" & days & "day, client_count, any_activity_perc_" & days & "day, broken_perc_" & days & "day from ClientHealth_CHT where environment='" & environment & "' and sitecode='" & sitecode & "' order by date_stored "
 objRS.Open
 if objRS.RecordCount>0 then
  For iRow = objRS.AbsolutePosition to objRS.RecordCount
   date_stored = objRS.Fields(0).value
   polled      = objRS.Fields(1).value
   pinged      = objRS.Fields(2).value
   total       = objRS.Fields(3).value
   activity    = objRS.Fields(4).value
   broken      = objRS.Fields(5).value
      polled_perc = polled/total*100
      online_perc = (polled+pinged)/total*100
      known = activity + broken
      range = "A" & iRow & ":" & "E" & iRow
      xl.Range(range).Value = array(date_stored, polled_perc, online_perc, activity, known)
      xl.Range("A1:E" & iRow).CurrentRegion.Select
   objRS.MoveNext
  Next
  
  'graph the data
  xl.charts.add
  xl.activechart.ChartType = 4 'xlLine
  xl.activeChart.HasLegend = false
  'use the left axis text as title
    if days=1 then
      title = "1 day"
    else
      title = days & " days"
    end if
    for i=1 to len(title)
      ytitle = ytitle & mid(title,i,1) & vbCRLF
    next
  xl.activeChart.Axes(2,1).HasTitle = True  'y axis, primary side
  xl.activechart.Axes(2,1).AxisTitle.Characters.Text = ytitle
  xl.activeChart.Axes(2).AxisTitle.Select
  xl.Selection.Font.Size = 32
  xl.selection.Orientation = 0
  'set the data
  xl.activeChart.SeriesCollection(1).XValues= "=Sheet1!R1C1:R" & irow & "C1"
  xl.activeChart.SeriesCollection(1).Values=  "=Sheet1!R1C2:R" & irow & "C2"
    on error resume next  'we've seen this on the next line: Microsoft Office Excel: Unable to get the SeriesCollection property of the Chart class
  xl.activeChart.SeriesCollection(2).Values=  "=Sheet1!R1C3:R" & irow & "C3"
    if err<>0 then wscript.echo "graphing error: " & err.description
    on error goto 0
  'change the graph line to blue and thick
  xl.activeChart.SeriesCollection(1).Select
  xl.Selection.Border.ColorIndex = 5 'blue; 3 is red
  xl.Selection.Border.Weight = 4 '4=xlThick
  xl.activeChart.SeriesCollection(2).Select
  xl.Selection.Border.ColorIndex = 4 'green
  xl.Selection.Border.Weight = 4
  xl.activeChart.SeriesCollection(4).Select
  xl.Selection.Border.ColorIndex = 3
  xl.Selection.Border.Weight = 4
  xl.activeChart.SeriesCollection(3).Select 
  xl.activeChart.SeriesCollection(3).Format.Fill.ForeColor.RGB=1
  xl.activeChart.SeriesCollection(3).Format.Line.DashStyle=5
    'xl.activeChart.SeriesCollection(5).Select
    'xl.activeChart.SeriesCollection(5).Format.Fill.ForeColor.RGB=2
    'xl.activeChart.SeriesCollection(5).Format.Line.DashStyle=2
  'set a minimum value so we don't have a lot of wasteful whitespace
  xl.activeChart.Axes(2).MinimumScale = 0
  xl.activeChart.Axes(2).MaximumScale = 100
  'make the axes' font sizes large so that when the graph is made small they're still readable, and put commas in the numbers
  xl.activeChart.Axes(1).Select
  xl.Selection.TickLabels.Font.Size = 32
  xl.Selection.TickLabels.Font.Bold = true
  xl.activeChart.Axes(2).Select
  xl.Selection.TickLabels.Font.Size = 32
  xl.Selection.TickLabels.Font.Bold = true
  xl.Selection.TickLabels.NumberFormat = "#,##0"
  'save the graph as a file and exit
  xl.activechart.export rw_share & "\email_graphics\CH_online" & days & "_" & replace(environment," ","") & sitecode & ".gif", "GIF"  'or JPG but GIF is smaller
  xl.DisplayAlerts = False
  xl.Quit
  
  GraphHealth=true
 else
    wscript.echo "nothing to graph"
  GraphHealth=false
 End if
  objRS.Close

End Function

Summary: do you agree that computer health will help with client health; and what is "computer health" (AKA "desktop health" or "PC health") for you?

In a recent blog posting I mentioned that I believe computer health (i.e. overall computer health) should be considered part of client health (i.e. computer mgmt client health - SMS or ConfigMgr client health in my case). Basically, computer health could be the killer app for client health that ensures we get great client health solutions while also addressing the always-important computer health concerns. I believe the two disciplines share a lot of common ground in terms of issues, technical solutions, and processes. Even terminology and guidance could be similar.

Do you agree?

In particular:

• if you got a great computer health solution, do you think it would help with your client health challenges?
• would your management pay for a computer health solution where they wouldn't pay for a client health solution?
• most importantly: what do you need in a "computer health solution"?
• what would it monitor?
• who would be interested in that data?
• how important is it that the solution fix any problems found?

Fundamentally, I worry that we techies understand the nature and importance of computer management client health, but the people who sign the cheques think that client health is just a "product quality" issue, and thus should be free like any hotfix or service pack. I disagree with that assumption (rarely are client health issues actually computer management product quality issues), but I'm not optimistic we'll change their mind any time soon. The good news is that I suspect they probably would buy into good computer health management.  

I would greatly appreciate your opinions on this (actually, I'd appreciate your opinions on any of my postings). Or you can e-mail me. Or we'll talk at MMS 2008 or similar get-togethers. I'm hoping a consensus will become obvious, but I've been surprised before...

Just a quick note that I've updated a previous posting about my computer management blog list. It now has an attached OPML file with all the computer management blogs I follow.

That attachment has a .txt extension, which you can remove and then import the .OPML file to your favorite reader. The usual disclaimers apply. And if I'm missing anyone then please let me know - I'd like to add them. Your blog reader may have a link to download the file, or you can go to the web version of the blog, open that blog posting (click on its title), go to the bottom of the posting, right click the attachment link, and select Save Target As...

p.s. I actually updated that blog posting a few days ago, but I've added a few more key feeds in the last few days, so I've updated it again.

Summary: creating HTML-based reports from scripts is surprisingly easy, allowing you to provide highly flexible, very attractive reports to internal customers.

Today we continue our series on producing complex computer management client health reports. I've divided the process into five parts:

1. the queries
2. saving the data
3. producing HTML reports from script (automatically)
4. producing graphs from script
5. e-mailing the reports to the interested parties from script

The most important point to remember is that HTML files are just flat files. So creating them from script is the same as any other text file. The second point is that tools such as FrontPage (now Microsoft Office SharePoint Designer) allow you to prototype a report in minutes using an intuitive interface, and then you can use the HTML source they'll show you to know what your script needs to output. All you have to do is substitute the report data into the appropriate spots. The following example may make that clear.

To start with you'll need to open a file:

results_file = "CH_activity_today.html"
Set fso = CreateObject("Scripting.FileSystemObject")
on error resume next
Set tf = fso.CreateTextFile(results_file, True)
if err<>0 then
  wscript.echo "couldn't create " & results_file & ": " & err & " - " & err.description
  wscript.quit
end if
on error goto 0

Then create the HTML header for the report:

tf.writeline "<html>"
tf.writeline "<head>"
tf.writeline "<meta http-equiv=""Content-Language"" content=""en-us"">"
tf.writeline "<meta http-equiv=""Content-Type"" content=""text/html; charset=windows-1252"">"
tf.writeline "<title>SMS/ConfigMgr Client Activity Analysis</title>"
tf.writeline "</head>"
tf.writeline "<body>"

Then you can get into the real reporting content. This is just a small example:

output= formatnumber(client_count, 0,-2,-2,-2) & " Clients"
tf.writeline "<b><font face=""Arial"" size=""3"" color=""#0000FF"">" & output & "</font></b>"

The real content will likely take a fair bit of code, but it's just traditional SQL query and script logic interspersed with similar output of HTML code. You then finish off the file with this code:

tf.writeline "</body>"
tf.writeline "</html>"
tf.close

From there you can post the file to your favorite web server, or (as we'll discuss later), send it as an e-mail to your internal customers. To really complete the story you can create a scheduled task for the script so that it gets produced every day. Set it to 6AM and it will be waiting for you when you come in to work.

Summary: we're probably agreed that client health is a complex subject, meaning different things to different people. Articulating the subtleties to the interested parties is maybe the biggest challenge of them all. But sometimes a pretty picture can bring clarity that an infinite number of words never will. So I offer my pretty picture:

(click on the image for a full-scale, high clarity version)

There's a lot to this picture, but basically you work down from the highest level of detail to the lowest level. You could say that the high-level buckets (such as total computer count), can be broken down into smaller buckets (SMS clients, missing clients, and exceptions, for example). The buckets within buckets can go quite deep, depending on which part you're interested in.

Not all buckets are pictured (for example, there could be a variety of network-related environmental issues). There could even be more levels (for example, there could be multiple causes or symptoms of repository corruption).

So the next time a non-techie comes to you with their client health problem, thinking that it's so simple and thus can be fixed today, show them the bucket in this diagram that corresponds with their problem. They'll immediately see that there's plenty of other problems in the client health space, and few have a single simple solution!

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.

  '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

Summary: client health seems to be a reasonably intuitive problem - keep your computer management clients working properly. But different people have different intuitions, and to solve the problem fully you actually have to solve a lot of problems. Here's my current thinking on the scope of the client health management challenge.

As per an earlier posting, I've been thinking about and working on the client health problem for a number of years now (amongst other things). I like to think that a lot has been accomplished, but as my thinking has evolved I can see there's a lot of work yet to be done. Fortunately a number of people are taking the issue seriously these days, so maybe this will be the year we see some key breakthroughs. I'll fill you in on more of my thinking, but to start with let's talk about the scope of client health management - what are the broad challenges within this thing we call "client health"?

1. Terminology – let's all speak the same language by defining the terms, as intuitively as possible
2. Client coverage – how many should we have?
1. Less excluded clients (exclusion management is an art in itself)
3. Offline clients (powered off, on the Internet but unavailable, or on other networks)
4. The client health issues themselves
1. Broken computer management clients (e.g. SMS or ConfigMgr)
1. Distinguish fully vs. partially healthy
2. Stopped clients (often intentionally stopped)
3. Operational issues (incorrectly moved clients, etc.)
4. Due to product bugs
5. etc.
2. Dependent software breakage
1. The operating system
2. WMI
3. DCOM
4. etc.
3. Server issues causing client inactivity
1. Stopped, misconfigured, or failing services
2. Overloaded MPs or DPs
3. etc.
4. Environmental issues causing client inactivity
1. DNS or similar networking issues
2. Multi-hierarchy issues
3. etc.
5. Reporting on the above (including data generation and collection)
6. Remediation tools and strategies / guidance
7. Service success assessment – given how many machines have been or will be online & healthy, is a service (software distribution, patching, asset management, etc.) as successful as it should be? How successful should it expect to be?
8. The client health infrastructure itself must be fully monitorable, to keep it running properly
9. Computer health (AKA desktop health)

So that's nine big sets of issues, each of which has its own challenges. For issue 4 (the client health issues themselves) in particular, I also like to talk of the "buckets of buckets" issue. At the first level, we have four buckets that client health issues can be grouped into (computer mgmt clients, dependent software, server-side issues, and environmental issues). Each of those buckets can be broken down into further buckets (for example, dependent software can be broken down to WMI, DCOM, the operating system itself, etc.). Those buckets can be broken down again (WMI can have repository corruption, namespace problems, service problems, etc.). Presumably those buckets could have buckets again (WMI repository corruption could be caused by several different problems). The buckets are important because each will require a different strategy to address it, to one degree or another. Having a hierarchy of buckets helps to make the buckets manageable. And then there may be several solutions for each bucket, with a preference order for those solutions (use the safest one first).

You might ask why I include issue 9, computer health. In some ways it is like the "dependent software breakage" bucket, and thus could be seen as a redundant point, but it also includes issues that don't adversely affect the computer management client. For example, application failures or excessively long bootup times. The reason I include it is because I'm starting to think that some of the tools and processes that can be used to address computer health can also be used for computer management client health. Even better is that it might be easier to "sell" desktop health to management than it is to sell client health. The executives at your organization are almost certainly computer users, and so they understand that keeping computers running smoothly is important and not easy (otherwise their computers would always run flawlessly...). But they probably have only a vague idea of the need for client health, and they almost certainly don't understand why it should be hard. If you try to explain it to them, their eyes will surely glaze over. So computer health may be the killer app that allows us to finally address the client health problem seriously.