Video Screencast Help

Server Documentation, SQL and Monitor Solution 7.1

Created: 09 Sep 2013 • Updated: 30 Sep 2013
Language Translations
ianatkin's picture
+3 3 Votes
Login to vote

Today I'm going to talk about something that server admins fear the most. Server Documentation.

As we all know, installing a server, configuring it and then generally playing with it (often till it breaks) is fun. But the moment you want a server to move into production, you just know someone is going to ask that fearsome question of "... and where is the documentation?". The emotional impact of this  question should not be underestimated;  it can be so severe it can result in a rather unsightly Administrator Implosion Event.

But, documentation is important. The process is critical  to confirm that what you think you've done is what you have done. It's also really rather helpful in the future when you need to make some changes, or as can often be the case, undo them.

So here's me nearly at that point of making a Monitor Solution 7.1 server live in our environment. So, I decided to avoid an AIE by documenting the setup. Very quickly I realised how complex documenting Monitor Solution is. Apart from all the usual bread-and-butter server installation and agent deployment bit,  we need to consider,

  1. Policy documentation
  2. Rule Documentation
  3. Metric documentation

And there is really quite a lot there. Doing this manually is just soul destroying so I opened up a SQL window on the server and started to see what I could do to automate the task. After a couple of days the end result was very satisfying; I'd created a process for dumping the monitor server configuration in a format detailed enough to certainly convince me that it was a job well done.

As there is so much to my thought threads on approaching this, I've decided to make this a small series of bite sized articles. This first article will focus on how to present a summary computers with policies  so you can with ease include word graphics like this below in your server docs,

 Mon_Rep1.png

This is quite a useful report. When I first ran it I found  a machine which had fallen through the net and didn't have any policies applied. 

 

1. First Steps -What Policies Apply to What Computers?

Before we can write out full SQL for this report, we first need to find the T-SQL  which exposes our Monitor policies. The first step here is to find the policy class guid which is applicable to Monitor Solution.

 

1.1 Monitor Solution's Policy Guid

The view vitemClass will list all classes which are created by the solutions installed on the SMP so by first query here was,

SELECT type, 

       guid 

FROM   vitemclass 

WHERE  type LIKE '%Monitor%' 

Now, what I expected here was for a type to appear which would ring bells for agentless and agentbased monitor policies, only there wasn't. Instead I got 87 rows of types, none of which looked like a Monitor Policy object. So you get to see what these types look like, below is a small sample of the output.

 

Type Guid
Altiris.Monitor.Common.Metrics.MetricPort 4AC48F2E-8F56-43D7-8FE3-02BADF6FB1EB
Altiris.Monitor.Solution.ImportMonitorPack.ScheduleMonitorPack ED31F2A4-9099-42AA-9FBB-03C9CE75530D
Altiris.Monitor.Common.Metrics.MetricIpmi 235A6CAB-FAEE-4AC7-AB32-050EE43C065C
Altiris.Monitor.Solution.ECMsgSubscriber 522B0B17-A721-4254-BDFA-08A77A36B208
Altiris.Monitor.Solution.NSMsgSubscriber BBE14D8D-1C13-4858-9953-0AA57D93C7B5
Altiris.Monitor.Common.Metrics.MetricAgentlessWmi 226CDBE8-0197-4A56-A32F-0DB83777E879
Altiris.Monitor.Solution.ImportMonitorPack.MonitorPackInDatabase 3FC776B5-F42E-4512-946F-116340288758
Altiris.Monitor.Common.SiteServer.SiteServerConfigurationPolicy 604EFC00-0258-430A-9A28-13C4F91595A2
.... ....

 

A quick scouring of CONNECT helped here with Andrew Bosch's response to a response to a forum request. The user was asking for a query to find all targets associated with a policy. This query used the policy class guid ''0821A65B-5338-464C-824D-9F7CEC48EA56' for monitor policies.

If we put this into our vItemClass query however we see it won't be quite enough,

select Type from vItemClass where Guid like '0821A65B-5338-464C-824D-9F7CEC48EA56'
Type
Altiris.Monitor.Common.MonitorPack.MonitorPackAgentBased

 

This was a surprise; this will only give us agent-based policy types. As agentless types also exist, there must be another Guid,  

 

SELECT type, 

       guid 

FROM   vitemclass 

WHERE  type LIKE 'Altiris.Monitor.Common.MonitorPack.MonitorPack%' 

 

This is much more revealing,

Type Guid
Altiris.Monitor.Common.MonitorPack.MonitorPackAgentLess F4767927-21AF-4875-B1B0-400852689DB1
Altiris.Monitor.Common.MonitorPack.MonitorPackAgentBased 0821A65B-5338-464C-824D-9F7CEC48EA56
Altiris.Monitor.Common.MonitorPack.MonitorPackFolder 3334B352-C022-4E0A-9640-A297D1E3DAC4
This means that in order to capture both agent-based and agentless policies, we'll actually need two guids 'F4767927-21AF-4875-B1B0-400852689DB1' and '0821A65B-5338-464C-824D-9F7CEC48EA56'.
 
 

1.2 Getting a list of all Enabled Monitor Solution Policies

Now we know what ClassGuids to look for, we can now get a list of all policies with the following SQL which examines which joins the item and itemclass tables to extract Monitor Solution policies,
 
 
SELECT name 

FROM   item 

       JOIN itemclass 

         ON item.guid = itemclass.guid 

WHERE  itemclass.classguid = '0821A65B-5338-464C-824D-9F7CEC48EA56' 

        OR itemclass.classguid = 'F4767927-21AF-4875-B1B0-400852689DB1' 

ORDER  BY name ASC 
 
 
Now whilst this SQL is technically correct, it is frowned upon as it's a bit inefficient to use the item table directly (because it will tend to be large on a production system). Instead, best practice is to use the very much faster view vNonResourceItem which avoids querying resources unnecessarily.
 
 
SELECT name 

FROM   vnonresourceitem 

       JOIN itemclass 

         ON vnonresourceitem.guid = itemclass.guid 

WHERE  itemclass.classguid = '0821A65B-5338-464C-824D-9F7CEC48EA56' 

        OR itemclass.classguid = 'F4767927-21AF-4875-B1B0-400852689DB1' 

ORDER  BY name ASC 
 
 
This query is fast and reveals 135 policies monitor policies on my system. However only a fraction of these policies are deployed to my server estate so we now need to improve the query to check for enabled policies only. The solution to this also comes from Andrew Bosch's post where he uses the view vPolicyAppliesToResource which stores policies, their state as well as the resources targeted. With the help of another join, this view then allows us to update the above query so that it exposes only enabled Monitor Policies. 
 
 
SELECT DISTINCT [name] 

FROM   vnonresourceitem 

       JOIN vpolicyappliestoresource 

         ON vnonresourceitem.guid = vpolicyappliestoresource.policyguid 

WHERE  ( classguid = '0821A65B-5338-464C-824D-9F7CEC48EA56' 

          OR classguid = 'F4767927-21AF-4875-B1B0-400852689DB1' ) 

       AND enabled = 1 

ORDER  BY [name] 
 

This works a treat,

Name
Altiris Deployment 6.x Servers : Express Services
Altiris Deployment 6.x Servers: AxEngine Activity
Altiris Deployment 6.x Servers: DBManager Activity
Altiris Deployment 6.x Servers: PXE Manager
Altiris Deployment 6.x Servers: PXE Services
Altiris Deployment 6.x Server : WOLProxy Check
Basic Server Monitoring (Disk and CPU)
File Servers (Agent-based)
 
So, now I'm pretty close to getting what I want. A report of computers details all the Monitor Policies which have been applied to them. 
 

1.3 Enabled Monitor Policies by Computer

As the vPolicyAppliesToResource table links computers to policies, the computer Guids are already available in the previous query. To convert these to computer names requires just one more join, this time to the vComputer view as follows,
 
--Query to list all computers with monitor policies applied 

SELECT vc.name, 

       vnonresourceitem.name 

FROM   vpolicyappliestoresource 

       JOIN vcomputer vc 

         ON vpolicyappliestoresource.resourceguid = vc.guid 

       JOIN vnonresourceitem 

         ON vnonresourceitem.guid = vpolicyappliestoresource.policyguid 

WHERE  ( classguid = '0821A65B-5338-464C-824D-9F7CEC48EA56' 

          OR classguid = 'F4767927-21AF-4875-B1B0-400852689DB1' ) 

       AND enabled = 1 

ORDER  BY vc.name ASC 
 
 
 
Computer Policy
ALTIRIS-FS Basic Server Monitoring (Disk and CPU)
ALTIRIS-FS File Servers (Agent Based)
ALTIRIS-DS6A Altiris Deployment 6.x Servers: PXE Services
ALTIRIS-DS6A Altiris Deployment 6.x Servers : Express Services
ALTIRIS-DS6A Basic Server Monitoring (Disk and CPU)
ALTIRIS-DS6B Altiris Deployment 6.x Servers: DBManager Activity
ALTIRIS-DS6B Altiris Deployment 6.x Servers: AxEngine Activity
ALTIRIS-DS6B Altiris Deployment 6.x Servers: PXE Manager
ALTIRIS-DS6B Basic Server Monitoring (Disk and CPU)
... ...
 
My only objection to this report as it stands is the formatting. As it stands, it is not easy to read which means gleaning information from it will require just that little bit extra concentration. A few  things need to change before we have a report which is easy on the eye whilst still being informative,
 
  1. We need to remove the multiple instances of the server name from the left hand column
  2. We need all the policies for each server combined onto a single line
  3. We need to arrange the policies in order of policy dominance 
The last requirement sounds odd, but I'm a pattern recognition kind of guy. When looking at a list of policies on a machine, I want them listed in a known and useful way. Alphabetic fine for ordering machine names, but when looking at a policy list on any machine it would be interesting to order them using a useful metric, thus imparting through the ordering itself an extra level of information. I concluded for this that I'd like the policies arranged by dominance, so that wider a policy was distributed across the server estate the higher it would appear on the list.
 
 
 

2. Formatting Revision for "Enabled Policies by Computer" 

To get this more intuitive formatting, we'll need to tackle how to create this ordered list of policies by dominance. For SQL lovers, this is easy. We have already the list of policies by computer, so we just group the policies by GUID and count the number of machines in each,
 
SELECT vnonresourceitem.name, 

       Count(*) AS 'Count' 

FROM   vpolicyappliestoresource 

       JOIN vcomputer vc 

         ON vpolicyappliestoresource.resourceguid = vc.guid 

       JOIN vnonresourceitem 

         ON vnonresourceitem.guid = vpolicyappliestoresource.policyguid 

WHERE  ( classguid = '0821A65B-5338-464C-824D-9F7CEC48EA56' 

          OR classguid = 'F4767927-21AF-4875-B1B0-400852689DB1' ) 

       AND enabled = 1 

GROUP  BY vnonresourceitem.guid, 

          vnonresourceitem.name 

ORDER  BY [count] DESC, 

          vnonresourceitem.name 
 
 
For my setup, this gave a nice little table,
 
Name Count
Basic Server Monitoring (Disk and CPU) 23
SQL Server Basic Monitor  11
Altiris Deployment 6.x Servers : Express Services 3
Altiris Deployment 6.x Servers: AxEngine Activity 3
Altiris Deployment 6.x Servers: DBManager Activity 3
... ...
 
So what we need to do now is create a table which lists computers with policies with an extra column for this total policy count. This policy count will allow us to order the policies on each computer by this overall policy dominance.
 
In terms of SQL, this means joining the results of the computers and policies select, with the select above for policies and their counts. If we call the above table PolicyCount then this SQL would look like,
 

SELECT vc.name          AS 'Policy', 

       policycount.name AS 'Computer', 

       policycount.guid AS 'Policy Guid', 

       policycount.count 

FROM   vcomputer vc 

       JOIN vpolicyappliestoresource 

         ON vc.guid = vpolicyappliestoresource.resourceguid 

       JOIN policycount 

         ON vpolicyappliestoresource.policyguid = policycount.guid 

ORDER  BY vc.name ASC, 

          policycount.count DESC 
 
 
Which isn't too bad looking. But PolicyCount doesn't exist, except as another T-SQL Select statement. So let's now fill this in...
 
SELECT vc.name          AS 'Computer', 

       PolicyCount.name AS 'Policy', 

       PolicyCount.guid AS 'Policy Guid', 

       PolicyCount.count 

FROM   vcomputer vc 

       JOIN vpolicyappliestoresource 

         ON vc.guid = vpolicyappliestoresource.resourceguid 

       JOIN (SELECT vnonresourceitem.name, 

                    vnonresourceitem.guid, 

                    Count(*) AS 'Count' 

             FROM   vpolicyappliestoresource 

                    JOIN vcomputer vc 

                      ON vpolicyappliestoresource.resourceguid = vc.guid 

                    JOIN vnonresourceitem 

                      ON vnonresourceitem.guid = 

                         vpolicyappliestoresource.policyguid 

             WHERE  ( classguid = '0821A65B-5338-464C-824D-9F7CEC48EA56' 

                       OR classguid = 'F4767927-21AF-4875-B1B0-400852689DB1' ) 

                    AND enabled = 1 

             GROUP  BY vnonresourceitem.guid, 

                       vnonresourceitem.name) PolicyCount 

         ON vpolicyappliestoresource.policyguid = PolicyCount.guid 

ORDER  BY vc.name ASC, 

          PolicyCount.count DESC 
 
 
This looks daunting it has to be said, but if you delete in your mind the inner SQL for the PolicyCount table it does make it mentally more digestible. This is now giving me a nice  list or computers (ordered by name) and policies (ordered by dominance). File this away mentally (if you can) as the SQL Select statement that provides a table called OrderedPolicies.
 
On my setup the output looks like,
 
Computer Policy Policy Guid Count
ALTIRIS-FS Basic Server Monitoring (Disk and CPU) 7B3B7ED6-B3AA-46FC-B401-812BB9AD1725 23
ALTIRIS-FS File Servers (Agent Based) 3A028371-EE4C-4B5B-B78F-ED071DFBAC69 2
ALTIRIS-DS6A Altiris Deployment 6.x Servers: PXE Services 873EFE24-0E2F-400C-93D1-C4A1A0698AD9 3
ALTIRIS-DS6A Altiris Deployment 6.x Servers : Express Services 2B036DCC-2E21-4AA0-A276-C54EF9EAA562 3
ALTIRIS-DS6A Basic Server Monitoring (Disk and CPU) 7B3B7ED6-B3AA-46FC-B401-812BB9AD1725 23
ALTIRIS-DS6B Altiris Deployment 6.x Servers: DBManager Activity D852DBE4-3802-406F-81B0-7BAC785B3DFF 3
ALTIRIS-DS6B Altiris Deployment 6.x Servers: AxEngine Activity AF551FA9-9065-40B4-A3E7-127E0626458E 3
ALTIRIS-DS6B Altiris Deployment 6.x Servers: PXE Manager 3303BA68-1EB0-46C5-B755-47F637BB1EFA 3
ALTIRIS-DS6B Basic Server Monitoring (Disk and CPU) 7B3B7ED6-B3AA-46FC-B401-812BB9AD1725 23
.... .... .... ....
 

This is now most of the way there. And unfortunately this is where it get's messy. You see what we've got to do now is merge all the ordered policy entries for each computer into a single string.  Each row should have two columns, one for the computer and the other for the policies stuffed into an ordered string.

T-SQL has an excellent command for stuffing strings into strings, and it's called STUFF. For SQL 2005 Servers and beyond, you can use the STUFF command in conjunction with FOR XML to concatenate strings. If I take the above SQL table as being called OrderedPolicies, the SQL for our nicely stuffed output is,

SELECT Upper(vc2.name)                     AS 'Computer', 

       Stuff((SELECT Cast(',' AS VARCHAR(max)) 

                     + OrderedPolicies.policy 

              FROM   (SELECT ..... ......) OrderedPolicies 

              WHERE  OrderedPolicies.computerguid = vc2.guid 

              FOR xml path('')), 1, 1, '') AS [Policies Applied] 

FROM   vcomputer vc2 

 

where I've made the SQL look a bit cleaner by not duplicating the SQL for the OrderedPolicies table. The output is now pretty much exactly as I'd like it.

Computer Policy
ALTIRIS-FS Basic Server Monitoring (Disk and CPU),File Server
ALTIRIS-DS6A Basic Server Monitoring (Disk and CPU),Altiris Deployment 6.x Servers: PXE Services,Altiris Deployment 6.x Servers : Express Services
ALTIRIS-DS6B Basic Server Monitoring (Disk and CPU),Altiris Deployment 6.x Servers: DBManager Activity,Altiris Deployment 6.x Servers: AxEngine Activity,Altiris Deployment 6.x Servers: PXE Manager
... ...
 

This is ready to be pasted into word as a table, have a table style applied and then perform a search/replace to replace the comma with a line break,

Mon_Rep1.png

 

3. The Final SQL

For those who have made it here, here is the final SQL code I use to create my Computer policy distribution tables. You'll notice The SQL code below replaces the comma with the text "NL" . This is simlpy so I can perform the search/replace in a larger document without worrying about linebreaks being pushed in erroneously. You can change this to whatever suites you.

SELECT computer, 

       Replace([policies applied], ',', '"NL"') 

FROM   (SELECT Upper(vc2.name)                     AS 'Computer', 

               Stuff((SELECT Cast(',' AS VARCHAR(max)) 

                             + OrderedPolicies.policy 

                      FROM   (SELECT TOP 5000 vc.name          AS 'Computer', 

                                              vc.guid          AS 'ComputerGuid' 

                                              , 

PolicyCount.name AS 'Policy', 

PolicyCount.guid AS 'Policy Guid', 

PolicyCount.count 

FROM   vcomputer vc 

JOIN vpolicyappliestoresource 

ON vc.guid = 

vpolicyappliestoresource.resourceguid 

JOIN 

(SELECT vnonresourceitem.name, 

vnonresourceitem.guid, 

Count(*) AS 'Count' 

FROM   vpolicyappliestoresource 

JOIN vcomputer vc 

ON 

vpolicyappliestoresource.resourceguid = vc.guid 

    JOIN vnonresourceitem 

      ON vnonresourceitem.guid = 

vpolicyappliestoresource.policyguid 

WHERE  ( classguid = 

'0821A65B-5338-464C-824D-9F7CEC48EA56' 

OR 

classguid = 'F4767927-21AF-4875-B1B0-400852689DB1' ) 

AND enabled = 1 

GROUP  BY vnonresourceitem.guid, 

vnonresourceitem.name) PolicyCount 

ON vpolicyappliestoresource.policyguid = 

PolicyCount.guid 

ORDER  BY vc.name ASC, 

PolicyCount.count DESC, 

PolicyCount.name) OrderedPolicies 

WHERE  OrderedPolicies.computerguid = vc2.guid 

FOR xml path('')), 1, 1, '') AS [Policies Applied] 

FROM   vcomputer vc2) FormattedComputerPoliciesTbl 

ORDER  BY computer ASC 

 

And with that it's time to end this article. It's been a lot of SQL, but ultimately you don't need to understand it. Just take a minute to,

  1. Paste the code above into SQL Server management studio
  2. Run it
  3. Copy the table formatted output into Microsoft Word
  4. Search and Replace, changing the "NL" string to a manual linebreak
  5. Format your table with your favourite style

The next coming in this series will cover the SQL for documenting how your active Monitor policies are built.

Ian./