Client Management Suite

Every now and then, it's nice to have metrics. So today I’m going to illustrate how simple trends can be divined from a few standard metrics that we can find in our Altiris databases. 

Metrics are useful to gather for the following most excellent reasons,

• Managers love them, which makes metric analysis the perfect fall-back task.  Should you ever experience the urgent need avoid doing something incredibly tedious, and you’ve already tidied your desk, metric analysis is the way to go.
   
• Metric trends can, on occasion, provide useful insight. Further, a small subset of such insightful analyses may also one day provide actual business value.

So, if you don't gather metrics you are denying yourself this fabulous excuse for not doing actual work from time to time. I, for example, find it very relaxing to gaze at monthly counts of the following metrics to see how our environment is faring;

1. DS6.9 Task Execution
2. Computers created in our v7.5 Notification Server
3. SMP7.5 Software policies created by our application packager
4. Incidents created in our helpdesk
5. Hardware model take-up

All of these I examine at a high level, just as trends, so I can visualise how counts of these vary month to month.

What I’m now going to do is present some SQL queries right off the bat for the above (so you can get running them), leaving the explaining of how they work (and why I’ve written them this way) till later in the article.

Query for DS6.9 Task  Execution

In DS6.9, the classic table which permits a glimpse the server load is the event_schedule table. This table stores the execution history of currently defined jobs. This table is however not a good choice for long-term trending though; jobs have their own lifecycle and very few persist through the full life of a Deployment server.

Job lifecycle is important as you have to understand the consequences of deleting a job when it’s retired from use; its references are removed from both the table that stores the job data (the event table) and the table that stored the execution history (the event_schedule table). This is an important general point; when doing long term trending on databases you must be aware of any actions taking place which will prune your data and skew your results.

Rescue is at hand though; a table exists in the DS eXpress database which does keep execution info tucked away permanently. This is the history_archive table. The query I used for seeing how busy the deployment server is over time it this,

SELECT   CAST (MONTH(complete_time) AS VARCHAR (2)) + '-' + CAST (YEAR(complete_time) AS VARCHAR (4)) AS 'Date',
         COUNT(*) AS 'Total'
FROM     history_archive
WHERE    complete_time IS NOT NULL
GROUP BY MONTH(complete_time), YEAR(complete_time)
ORDER BY YEAR(complete_time), MONTH(complete_time) ASC;

This is a good ‘basic’ query for trending on your deployment server and seeing how much value your automation is providing.

Short term trending is useful as an admin as it provides a birds-eye view of job execution, and allows a rough correlation between load with resource consumption. If you see anomalous peaks, further queries can be executed to see where your current ‘hot’ jobs are located.

 Longer term trending is useful for capacity planning; for example we’ve seen the number of tasks handled by our deployment server on average triple year on year since it went into production in 2011. That’s a useful stat to know.

New computers created in our v7.5 Notification Server

The table most often used to query computers by Altiris administrators in Notification Server is the vComputer table.

The only caveat here is that any process which might be purging machines which have fallen out of contact need a bit of consideration. So, consider this a good starter query.

SELECT   CAST (MONTH(CreatedDate) AS VARCHAR (2)) + '-' + CAST (YEAR(CreatedDate) AS VARCHAR (4)) AS 'Date',
         COUNT(*) AS 'Total'
FROM     vComputer
WHERE    IsManaged = 1
GROUP BY MONTH(CreatedDate), YEAR(CreatedDate)
ORDER BY YEAR(CreatedDate), MONTH(CreatedDate) ASC; 

On our SMP7.5 platform, this gives the following,

As you can see from the above table, this trending shows a drop off the registrations of new machines. This is because we started a migration to 7.5 back in Feb and have now moved to a situation where we’re in that tail coupled with the BAU new PC and Laptop builds.

SMP 7.5 Software policies created by our applications packager?

So… how busy is our application packager with rollouts? This query lets me know if I should be asking if they’re at a point in needing some extra automation here and there to assist…

SELECT   CAST (MONTH(CreatedDate) AS VARCHAR (2)) + '-' + CAST (YEAR(CreatedDate) AS VARCHAR (4)) AS 'Date',
         COUNT(*) AS 'Total'
FROM     vitem
WHERE    vitem.ClassGuid = '2D3A170E-5028-4570-BA0C-3DB775CB8BDE'
GROUP BY MONTH(CreatedDate), YEAR(CreatedDate)
ORDER BY YEAR(CreatedDate), MONTH(CreatedDate) ASC; 

Which in our case gives the following,

Now, this isn’t the number of applications being built -it’s just the number of products which are being rolled out across site. So that’s about one package a week being rolled out at the moment whose three rollout phases can be handled within the resource we’ve allotted.

As an aside, this constant change we are bringing to our estate is due to the fact we adopted some time ago what I call the ‘rising baseline’ model of computer management. In this model, software product version numbers across our machines continually rise in response to user requirements and security advisories. The result is our policies configure a baseline of minimum software version numbers; a baseline which is being revised upwards across our estate on a weekly basis.

Incidents created in our Altiris 6 helpdesk

And yes, our Altiris 6 Helpdesk isn’t dead quite yet! And the view we use to get lots of lovely data about our tickets is workitem_current_view.

SELECT   CAST (MONTH(workitem_created_on) AS VARCHAR (2)) + '-' + CAST (YEAR(workitem_created_on) AS VARCHAR (4)) AS 'Date',
         COUNT(*) AS 'Total'
FROM     workitem_current_view
GROUP BY MONTH(workitem_created_on), YEAR(workitem_created_on)
ORDER BY YEAR(workitem_created_on), MONTH(workitem_created_on) ASC;

And the output for the last few months is,

So, after a brief rise in the spring (our Windows 7 rollout) we are settling back to business-as-usual value of about 1200 tickets monthly.

Take Up of New Hardware Models                                                    

One of the problems that sometimes comes with being an Altiris Administrator is people rarely tell you what works, just what doesn’t. One of the queries I like to run after ratifying new hardware is one that trends the take-up of that model.

If there is a high take up and I’ve got no negative feedback, I can sit back and feel confident that all has gone well.

Here I’ve used a combination of the vComputer view (to get the computer’s created date in NS) and the vHWComputerSystem view to extract the model. In the query below, I’m looking specifically at the take up of the Dell Optiplex 9010,

SELECT   CAST (Month(createddate) AS VARCHAR (2)) + '-' + CAST (Year(createddate) AS VARCHAR (4)) AS 'Date',
         Count(*) AS 'Total'
FROM     vcomputer
         INNER JOIN
         vhwcomputersystem
         ON vcomputer.guid = vhwcomputersystem._resourceguid
WHERE    vcomputer.ismanaged = 1
         AND vhwcomputersystem.model = 'Optiplex 9010'
GROUP BY Month(createddate), Year(createddate);  

The results below show that this model has indeed been taken up very well. The Optiplex 9010 was evidently our user’s desktop of choice for the hardware replacement that came with the Windows 7 rollout.

History to These Queries

For the first few years when I started out using Altiris, I was creating a lot of ad-hoc queries. And they were bluntly (from a T-SQL point of view) really, really bad. They looked beautiful, don't get me wrong; lots of cursors, loops and enough string manipulation to make your eyes water. They did in a couple of pages (of what was effectively iterative SQL programming code) what T-SQL could do natively in just few lines. 

So, today’s article was really written for all you Altiris administrators out there who, like me, sometimes find ourselves retreating back to our programming backgrounds whenever the T-SQL going gets tough. For the rest of your guys (who do SQL properly anyway) please feel free to find the fact that I ever did this differently very amusing...

My First Basic Query - DS6.9 Jobs executed by Month

I started out with Altiris using Deployment Server 6. In those early days, I wanted to see how many jobs were being run as I was curious to see the usage across the estate of the jobs I’d provided to the desk-side teams.

Now, I’m not going to show you how I wrote my query back then. Frankly, that would be way too embarrassing. So,  I’m going to write it the way I did when I realised T-SQL wasn’t a programming language…. ;-)

To start, let's look at seeing how many jobs our DS has been running monthly. To figure out how to write this query, we first need to think about how we'd count up all the jobs. After we've done that, we can focus on how to split this up month by month.

As job execution history is stored in the event_schedule table, the total number of jobs executed by the engine can be calculated using this query which uses the aggregate function COUNT (aggregate functions in T-SQL perform a calculation on multiple values to return a single value).

SELECT COUNT(*) AS 'Total'
FROM   event_schedule
WHERE  end_time IS NOT NULL;  

This returns in my case the following large number (this DS server has been around for a while)

That's the easy bit. Now what do we about breaking down this total figure into month bins for trending? The answer is to use the T-SQL 'GROUP BY'  clause. This allows us a way to refine our aggregate query so that distinct bins can be created, each holding the result of that bins aggregate value. We want to bin by each month, in each year. This then gives us,

SELECT   MONTH(end_time) AS 'Month',
         YEAR(end_time) AS 'Year',
         COUNT(*) AS 'Total'
FROM     event_schedule
WHERE    end_time IS NOT NULL
GROUP BY MONTH(end_time), YEAR(end_time); 

Although this T-SQL does successfully group the results into monthly bins, the output at this stage isn't quite right,

The issue here is that we just don't have any ordering yet. This is easy, the 'ORDER BY' clause will sort this in a jiffy. Whe  (take note we order by year first as this is the most significant metric in our ordering; month is the next most significant)

SELECT   MONTH(end_time) AS 'Month',
         YEAR(end_time) AS 'Year',
         COUNT(*) AS 'Total'
FROM     event_schedule
WHERE    end_time IS NOT NULL
GROUP BY MONTH(end_time), YEAR(end_time)
ORDER BY YEAR(end_time), MONTH(end_time) ASC; 

A final tweak is to make this a bit more suitable for importing into Excel. For that we want just one column for a date (rather than the two we have here). This is just a case of casting the Month and Year column values and concetanting them together,

SELECT   CAST (MONTH(end_time) AS VARCHAR (2)) + '-' + CAST (YEAR(end_time) AS VARCHAR (4)) AS 'Date',
         COUNT(*) AS 'Total'
FROM     event_schedule
WHERE    end_time IS NOT NULL
GROUP BY MONTH(end_time), YEAR(end_time)
ORDER BY YEAR(end_time), MONTH(end_time) ASC;

Which gives a much more spreadsheet-friendly output,

Summary

Today I’ve introduced how you can write some simple trending type SQL queries using T-SQL ‘GROUP BY’ clause. I’ve done this as I didn’t know when I started out using T-SQL just how powerful this clause was.

This can give rapid metrics in a format easy to consume by spreadsheet applications, which then allows you to see how the attributes you are interesting in vary with time.

If you’ve got a bit of time on your hand, you could do worse than create your own dashboards to pull all this information together nicely.