Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.
Endpoint Management Community Blog

How to get a quick count of active machines on a client facing NS?

Created: 15 Dec 2009 • Updated: 15 Dec 2009 • 5 comments
Ludovic Ferre's picture
+2 2 Votes
Login to vote

There is a fuzzy area between the count of managed machines in a Notification Server and the count of active machines.

First when is a managed machine considered as active or inactive? This can be defined and interpreted in many different ways based on the nature of one's business, whether it is fast changing or strictly controlled etc.

The SQL query below is not going to answer this question, but can serve as a starting point (you could add event request on the filter such as last basic inventory received or last client policy request etc):


use altiris

select count (*), ((DATENAME(Year,DataLastChangedDate)) + ' ' + (DATENAME(Month,DataLastChangedDate))) as 'Month'
  from (
			select ResourceGuid, Max(DataLastChangedDate) as 'DataLastChangedDate'
			  from ItemResource ir
			  join ResourceUpdateSummary rus
				on ir.guid = rus.resourceguid
			 where IsManaged = 1
			 group by resourceguid
		) t
 where datalastchangeddate > DATEADD(Month,- 3,GETDATE())
 group by ((DATENAME(Year,DataLastChangedDate)) + ' ' + (DATENAME(Month,DataLastChangedDate)))

Note that we point to the default Altiris notification server database name here (and you have correctly gathered that this is a 6.x implementation).

We pick the managed machines directly from the ItemResource table because it is much more efficient (specially on reporting servers or mid-tier NS's than can aggregate anything above the 45~50,000 machines) than to use vComputer (I was planning to write an article [rant?] about this view, and may be I should still do, but in short I think that abstractions can be very useful in some cases and counter-productive in many others. vComputer is a proof of the later, as it is a view on a view on a view aggregating many tables that is highly inefficient when all you need from that view is the IsManaged field).

Then we join the ItemResource table to the Resource Update Summary (use the top right 'Connect search' field if you need to know more about this table) and pick up the most recent data change (one per resource that is).

This is stored in a data set 't' on which we apply additional filters and grouping to show the count of updated computers per month for the last 3 months (so in most cases you'll see 4 month in the resulting data set).

Comments 5 CommentsJump to latest comment

Ludovic Ferre's picture

Here is a couple of queries that can be used to drill down onto the specifi type of data that was modified, thus we can better judge if the "active" machines are really active or whether they are kept active because of ntetwork discovery, AD Import or any other non Altiris Agent based data modifications:


use altiris select count (distinct (InventoryClassGuid)), ((DATENAME(Year,DataLastChangedDate)) + ' ' + (DATENAME(Month,DataLastChangedDate))) as 'Month' from ( select InventoryClassGuid, ResourceGuid, Max(DataLastChangedDate) as 'DataLastChangedDate' from ItemResource ir join ResourceUpdateSummary rus on ir.guid = rus.resourceguid where IsManaged = 1 and DataLastChangedDate is not null group by inventoryclassguid, resourceguid ) t where datalastchangeddate > DATEADD(Month,- 3,GETDATE()) group by ((DATENAME(Year,DataLastChangedDate)) + ' ' + (DATENAME(Month,DataLastChangedDate))) select i.name as 'Data class name', i.description as 'Description', count(*) as [Count #] from ( select InventoryClassGuid, ResourceGuid, Max(DataLastChangedDate) as 'DataLastChangedDate' from ItemResource ir join ResourceUpdateSummary rus on ir.guid = rus.resourceguid where IsManaged = 1 and DataLastChangedDate is not null group by inventoryclassguid, resourceguid ) t join item i on t.InventoryClassGuid = i.Guid where datalastchangeddate > DATEADD(Month,- 3,GETDATE()) group by InventoryClassGuid, i.name, i.description order by [Count #] desc

Which on my test server returns:

76 2009 December
53 2009 November
11 2009 October
3 2009 September
AeX AC Machine Usage 16717
AeX AC Client Connectivity 16713
AeX AC Discovery Discovered Resources 16712
AeX AC Location 16710
AeX AC Identification 10024
AeX AC NT Services 7

I am currently off-net, on a retreat of some kind. I'll be back real soon, and you sure will hear from me then ;-).

Ludovic FERRÉ
Principal Remote Product Specialist
Symantec

+2
Login to vote
KSchroeder's picture

I guess we're just lazy...we usually just look at the [Client Date] field in Inv_AeX_AC_Identification!  Yes it is not the most accurate, but we send basic inv. every day, and there are a few odd machines out there from the future (I think we have a few that think it is 2030!) but for a quick and dirty result, you can't beat:

SELECT COUNT(*)
FROM Inv_AeX_AC_Identification acid
WHERE DATEDIFF(dd, acid.[Client Date], GETDATE()) < 30

We use the same logic in many of our collection definitions and reports to ensure we only get "active" machines.  Much less fancy than your method, but effective (and reasonably fast, esp. if we declare a var for the GETDATE() call).

Thanks,
Kyle
Symantec Trusted Advisor

For Forum threads, please click "Mark as Solution" if answered.
For all content, please give a thumbs up if you agree with or support the post.

-1
Login to vote
Ludovic Ferre's picture

I would never run a count on any result set that doesn't include IsManaged. Most of my customers have AD import, network discovery or other process to check the environment and deploy the agent.

On the basic inventory side I always advise my customers to set it to be sent at a rather wide interval. From a week to a few weeks.

Crazy? No, not at all (at least not on this account). Basic Inventory is run every-time the Altiris Agent is started and all variable information (date time, user) is removed from the gathered data to create a hash. You can find it in the registry actually. It also runs when major changes are detected (such as network address changes, system name etc).

If the hash has changed the basic inventory will be sent in all case but if a network blockout is in place (in which case it'll be queued).

So the Basic Inventory interval indicates the minimal interval at which a Basic Inventory event will be sent but doesn't prevent many Basic Inventory to be sent during over a short period of time.



I am currently off-net, on a retreat of some kind. I'll be back real soon, and you sure will hear from me then ;-).

Ludovic FERRÉ
Principal Remote Product Specialist
Symantec

+2
Login to vote
KSchroeder's picture

Ahh yes, well we normally also have IsManaged checked in there (either from the much-hated vComputer or vResource views), I just didn't mention it.  Note I did say "quick and dirty"! :-)

Thanks,
Kyle
Symantec Trusted Advisor

For Forum threads, please click "Mark as Solution" if answered.
For all content, please give a thumbs up if you agree with or support the post.

-1
Login to vote
JeffDG's picture

If you're looking for data changes, and not looking in the tables that AD Import uses, then IsManaged becomes a bit of a moot issue, allowing you to avoid the pitfalls of vComputer.

Thumbs-up to the developers for putting out a "vComputerExcludingIPInfo" in the v7 database that takes a ton of the slow SQL out of vComputer.

-1
Login to vote