Asset Management Suite

Just how good is your Inventory Forwarding?

For those of you using multiple Notification Servers to gather inventory, how often do you check your forwarding to make sure that it's showing the same data on both the source and destination NS? If you're not, and you run your reports on the central NS, you could be in for a surprise!

My environment has 5 production NSs (plus test NSs and such, but we don't need to go to that level of detail). Four of those servers have clients attached to them, and run things like Software Delivery, Patch Management, and Inventory. Each night, these servers forward their changes in Inventory to the 5th NS. Reports are executed against this central Notification Server, and not against the other servers.

After some embarrassment in cases where I reported data from the central NS, and later discovered (usually when someone said "Hey, that's not right!") that the data was erroneous, and more importantly, that the source NS was correct, I decided it was time to find a way to determine how close to synchronized my NSs actually were, and to detect when there was a problem BEFORE I sent out bad data!

My first step in finding a way to determine the sync level of my NSs was to find an attribute that I could compare. Most of my reports involve software audit data, so that was the one I was most concerned with. Luckily, there's a nice table that is populated by SW Audit that helps out here, and that's the Inv_AeX_SW_Audit_Results table. This table has one row per PC that shows the last time a Software Audit Scan was run for that PC. Comparing the value in this table on both the source and destination NS will give you an indication of how "fresh" the data is on your rollup NS.

Now that the data that needs to be compared has been identified, writing some SQL to do the comparison is the next step. All of this SQL stuff is done on the Central NS and/or the SQL server that supports the Central NS. I'm going to simplify to 3 NSs, 2 NSs that connect to clients (NS1/DB1 and NS2/DB2) and one Central NS (CentralNS,CentralDB). All of the databases will have the default name of "Altiris". I'm going to provide the instructions for SQL 2005, but everything described works with SQL 2000 as well.

Let me warn you now, if you're running MSDE or SQL Server 2005 Express on your central NS: (a) You probably don't have a large enough environment to need multiple NSs, and (b) this approach isn't going to work for you.

First, on CentralDB, you will need to add two "Linked Servers". To do this, go into "Microsoft SQL Server Management Studio" and connect to your CentralDB database. Under "Server Objects" (in the Object Explorer pane), there is a "Linked Servers" option. Right click there and select "New Linked Server..."

Put in DB1 as your linked server:

Click to view.

Go to the Security option and either select "Be made using the login's current security context" (if you have the same reporting SQL User on all of your databases) or "Be made using this security context:" and input the SQL Username and password for the destination server. This user needs "read" rights to the table on the remote server. Repeat this process for each of your NSs here.

The Linked Server lets your reports access the SQL DB of other NSs "live" (bypassing the forwarding mechanism that you're trying to test). That said, don't expect blazing performance on this report, and use caution in using it during production hours. Have your report generate "off hours" and just view the results.

Now, the next big question to ask is "That's great, now what do I compare?" A lot of the properties are potentially quite static, and once they forward once, shouldn't change very frequently. That makes them bad metrics for measuring your forwarding performance, as you'll get a lot of "false negatives". Other attributes that change frequently don't lend themselves to tracking just how out of date they are. Luckily, I've found one attribute that works very well.

The table I suggest using for the comparison is the "Inv_AeX_SW_Audit_Results" table. This table is, in my experience, a hidden gem of "quality" metrics on your software audit information. Essentially, it tells you the last time a PC ran a Software Audit scan (aexauditpls.exe), along with some statistics on the scan. The key column to look at is the "Scan Time" column:

Click to view.

So, now we can, by comparing the value of this column (and it's a nice 1:1 relationship in that no computer should have more than 1 "Scan Time") between the source NS and the rollup NS, we can tell whether we have fresh data on the rollup NS. These values should be the same if we're getting good forwarding, right? Well not quite...let's take a look at a scenario.

SW Audit Scan Time: Every Monday at 6am
Forwarding: Daily at 2am
Report Time: Monday, May 5 at 9:00am

Let's assume, for the sake of the scenario, that everything is working perfectly.

PC1 Scan Time Source NS: 5/5/08 6:20a
PC1 Scan Time Rollup NS: 4/28/08 6:18a

This is entirely normal and expected. When forwarding occurred, the scan time was 4/28, so there was nothing to forward that night. However, when you run a report showing the live data, the source NS shows a 1 week more recent date. This makes for a LOT of false positives, especially if you randomize your SW Audit scans over the week or month (aexruncontrol for example).

How do you filter out the false-positives for this? There are plenty of ways to do it, I guess. The simplest is to look at your scan frequency (in this case weekly), and if the difference is approximately that amount or less, ignore the difference. Now, let's look at the SQL to accomplish this, building it step by step.

First, we want the GUIDs and scan times for each PC on the rollup server:

Select _resourceguid
,ares.[scan time]
 
from 
inv_aex_sw_audit_results ares

Not much real information here, but it does give you the basis to work from.

Now we need to add in one of the remote servers. Because forwarded resources get the same GUID on the roll-up server, we'll join the tables based on the _ResourceGuid column:

Select
_resourceguid
,ares.[scan time]
,cfns.[scan time]
From
inv_aex_sw_audit_results ares
left join db1.Altiris.dbo. inv_aex_sw_audit_results cfns on ares._resourceguid = cfns._resourceguid

OK...so now, we have a list with GUIDs, and scan times from one of our CFNSs (the other CFNS are Null). This works great for a single remote/roll up NS, but who would build such a configuration with just one source NS rolling up? Not likely. Adding the second NS gets a bit more tricky! You do it with a subquery and a "UNION" command in SQL:

Select
_resourceguid
,ares.[scan time]
,cfns.[scan time]
From
inv_aex_sw_audit_results ares
left join 
(
   select _resourceguid,[scan time] from db1.Altiris.dbo. inv_aex_sw_audit_results
   UNION
   select _resourceguid,[scan time] from db2.Altiris.dbo. inv_aex_sw_audit_results
) cfns on ares._resourceguid = cfns._resourceguid

See how we combined the source NSs in the query? Now it all comes together in one block, so we see each rollup guid, it's Scan time, and finally the source Scan Time, all together in one spot!

Now to add some filtering and make the information useful! To tell you the truth, I really don't want to look through the thousands of scan times looking for discrepancies, do you? So instead of showing the scan times themselves, let's look at the deltas between them:

Select
_resourceguid
,Age=datediff(dd,ares.[scan time],cfns.[scan time]
From
inv_aex_sw_audit_results ares
left join 
(
   select _resourceguid,[scan time] from db1.Altiris.dbo. inv_aex_sw_audit_results
   UNION
   select _resourceguid,[scan time] from db2.Altiris.dbo. inv_aex_sw_audit_results
) cfns on ares._resourceguid = cfns._resourceguid
Order by datediff(dd,ares.[scan time],cfns.[scan time]

OK, so now the biggest gaps float to the top of the list! But where are they from? Which NS is having forwarding problems (and isn't this the real issue we're trying to get to?)

Select
_resourceguid
,Age=datediff(dd,ares.[scan time],cfns.[scan time]
,cfns.NS
From
inv_aex_sw_audit_results ares
left join 
(
   select NS='NS1',_resourceguid,[scan time] from db1.Altiris.dbo. inv_aex_sw_audit_results
   UNION
   select NS='NS2',_resourceguid,[scan time] from db2.Altiris.dbo. inv_aex_sw_audit_results
) cfns on ares._resourceguid = cfns._resourceguid

Order by datediff(dd,ares.[scan time],cfns.[scan time]

Next, I really don't care much about those PCs that are less than my scan interval (see discussion about false-positives above), so let's add a "where datediff(dd,ares.[scan time],cfns.[scan time] <= 7" to the bottom of that. Now we have a list of just our discrepancies.

Again, is it important to know which PCs are having problems? At this stage, not so much. I'm more interested in which NS is having problems, so let's consolidate a bit.

Select
NS,count (*)
From
inv_aex_sw_audit_results ares
left join 
(
   select NS='NS1',_resourceguid,[scan time] from db1.Altiris.dbo. inv_aex_sw_audit_results
   UNION
   select NS='NS2',_resourceguid,[scan time] from db2.Altiris.dbo. inv_aex_sw_audit_results
) cfns on ares._resourceguid = cfns._resourceguid
where datediff(dd,ares.[scan time],cfns.[scan time] <= 7

group by ns

And you get a nice little report like:

Click to view.

Don't get alarmed when you see the raw numbers here. It's not really a matter of looking a raw counts to find problems. You'll need to look at this for a few days or even weeks to spot trends. For example, I tend to see these counts climbing throughout the week, and dropping down over the weekend...every week. You also need to put the count in context. The highest of these is ~1.6% of the PCs served by that server, so not that alarming.

If I see a server climbing steeply, or not coming back down, I know where to start looking, and I'm not wasting my time checking forwarding if everything is working well!

Well, there you have it! A way to make sure that your Notification Servers are forwarding data reliably and accurately, and giving you a tool to focus your problem solving efforts where they are needed! Good luck, and happy forwarding!