ITMS Administrator Group

Hello,

There is environment with the mess in Active Directory. Let's say there are over 20000 Computers in Active Directory and 50% that is 10000 computers are managed by NS. I suspect that about 95% of unmanaged computers in AD no longer exist but will not be deleted any soon from Active Directory. I need to sort it out on the NS side.

Basically I'd like to focus on computers recently discovered by means of the AD import from last 7 days without Symantec Management Agent.

I tried the Vcomputer table and sort by [CreatedDate] from last 7 days. It does not give any sensible results though.

Does anyone know what SQL table the computers are put into when discovered by AD Import ?

Thanks,

Tomasz

Thomas,

 

You might try the ev_Directory_import_status table, it includes the resource (_resourceguid), _eventtime (last event time of the import), and the import ruleruleguid.

 

 

thomas, I whipped up a query that might help sort computers imported from AD. A thing you might want to note; is the a computer needs has be part of import rule or they wont get deleted. this query should help indentify those computers select C.Name as [Computer Name] ,c.CreatedDate ,DATEDIFF(day,c.CreatedDate,GETDATE()) [Days Created] ,isnull(i.Name,'Active') as [Status] ,a.[last AD Import] ,b.[# of AD Imports] ,a.[Import rule] from RM_ResourceComputer C left join ResourceAssociation ra ON ra.ParentResourceGuid = c.guid and ra.ResourceAssociationTypeGuid = '3028166F-C0D6-41d8-9CB7-F64852E0FD01' left join vItem i on i.Guid = ra.ChildResourceGuid left join ( select _ResourceGuid, MAX(_eventTime) as [last AD Import], i.Name as [Import rule] from dbo.Evt_Directory_Import_Status ad left join vItem i on i.Guid = ad.ImportRuleGuid group by _ResourceGuid, i.Name ) a on a._ResourceGuid = c.Guid left join ( select _ResourceGuid, COUNT(*) as [# of AD Imports] from dbo.Evt_Directory_Import_Status ad group by _ResourceGuid ) b on b._ResourceGuid = c.Guid where DATEDIFF(day,c.CreatedDate,GETDATE() ) <= 7 order by DATEDIFF(day,c.CreatedDate,GETDATE()) desc

WDRAIN1, Thanks for the help.

The report returns some results. I have some questions however:

• Why the NULL is replaced by 'Active' for all ResourceAssociation Name ? What does ’Active’ mean if all association names are ULL?
• What type does ResourceAssociationTypeGuid = '3028166F-C0D6-41d8-9CB7-F64852E0FD01' stand for ?
• Some computers discovered yesterday have number of [# of AD Imports]. Import runs daily how is that possible ?
• Some computers have hundreds in Days Created column and tens in [# of AD Imports]. Import runs daily. How do I understand these values ?

 

Regards,

Tomasz

 

 

Depending on your NS Purging Maintenance settings, computers could be configured be retired in instead of deleted, the sql statement "isnull(i.Name,'Active') as [Status]" will report the retired status of the computer if your NS Purging Maintenace Settings is configured to be retired.

this is useful if you want to find out if computers imported from the MS AD Import connector that are in a retired status are still in  your AD.

the ResourceAssociationTypeGuid = '3028166F-C0D6-41d8-9CB7-F64852E0FD01' is for asset

The [# of AD Imports] represents the number of AD Imports that have been ran on a computer resource. The Evt_Directory_Import_Status table is one to many table, meaning a resource will be in this table many times depending on the number of times it has been imported. The Computers discovered yesterday where discovered by the AD import connector and that is why they have a number in the [# of AD Imports]. The field "last AD Import" should match the time of your configured MS AD Import rule for computers.

The descrepancies between the "Days Created" and the "[# of AD Imports]" could be caused a number of factors. The Days Created will represent the date the computer was created in Altiris. The methods to add a computer resource in Altiris, include AD import through the connector, Import through Domain Membership/Wins, network discovery, and installing the symantec management agent on a computers (oh yeah if you have the asset managment suite you can manually create an computer asset also). So I would look at the CreatedDate and Days Created values as the date Altiris created the computer resource and the [# of AD Imports] as the number of the times a AD Import has been performed on that computers resource. So also another secnario would be if you created another computer ad import rule, lets say ten or more so days ago that could reflect why the is a difference, and then there is also the possiblity that your NS is configured to purge records in this table based on your NS Purging Maintenance settings.

Thank you very much for the comprehensive explanation. I have much better understanding of those figures now.

I marked your report as the solution.

Regards,

Tomasz

 

Great question and SQL query.  We are running into that same situation here.  How can we determine which of those machines are "managed"?

In the above SQL query, add the ,c.IsManaged statement to get whether the computer is managed or not.

1= managed 0=UnManaged

The below sql query will give you a summary of what is managed and not managed.

 

select
case when c.IsManaged = 1 then 'Managed'
else 'Unmanaged' end IsManaged
, COUNT(*) total
from RM_ResourceComputer c
group by c.IsManaged