This Article takes you through the steps to import data from the SEP DB into the Altiris CMDB. Then how to use that data to ensure you have matching agent coverage in SEP and Altiris and that both agents are healthy.
Prerequisite
The Altiris Service account must have read access for the SEP DB.
Creating a Custom Data Class to Import the Data
-
In the console browse to Settings > all settings.
-
Expand Notification Server > Resource and Data Class Settings > Data Classes > Inventory.
-
Right Click on Custom and select New > Editable Data Class.
-
Provide a name for the data class, I have called mine SEP_Agent_Data. (This will create a table called Inv_SEP_Agent_data).
-
Deselect Multi-Rowed as we only want to store the most recent data for each client.
-
Click Add New Attribute – this will store the SEP Agent version.
-
Enter a Name, I have called mine Agent_Version.
-
Leave the rest as default and click ok.
-
Click Add New Attribute – this will store the last update time of the agent.
-
Enter a Name, I have called mine Last_Update_Time.
-
Change the data type to Date.
-
Click OK.
-
Click Add New Attribute – this will store the last scan time of the agent.
-
Enter a Name, I have called mine Last_Scan_Time.
-
Change the data type to Date.
-
Click OK.
-
Click Add New Attribute – this will store the last download time of the agent.
-
Enter a Name, I have called mine Last_Download_Time.
-
Change the data type to Date.
-
Click OK.
-
Click Save Changes.
Associating the data class with the Computer Resource Type
-
In the console browse to Settings > all settings.
-
Expand Notification Server > Resource and Data Class Settings > Resource Types > Asset Types > IT.
-
Click on Computer..
-
Scroll to the bottom and click Add Data Classes
-
Expand Inventory > Custom.
-
Select the data class created above.
-
Click Save Changes.
-
Click Save Changes.
Creating a MS ODBC Data Source
-
Open the MS ODBC Data Source Administrator.
-
Click on the System DSN Tab.
-
Click Add.
-
Select SQL Server
-
Click Next.
-
Enter a name for the data source, I have called mine SEP Data Import.
-
Enter the Name or IP Address of the SQL Server.
-
Click Next.
-
Select Windows Authentication.
-
Click Next.
-
Change the default DB to SEM5 (or the name of your SEP DB).
-
Click Next.
-
Click Finish.
Creating a Connector data Source
-
In the Console browse to Settings > All Settings.
-
Expand Notification Server > Connector.
-
Right Click Data Sources and select New > ODBC Data Source.
-
Enter a name for your data source, I have called mine SEP Import.
-
Enter the name of the Data Source Created above.
-
In the import query enter;
-
select sc.COMPUTER_NAME, sc.BIOS_SERIALNUMBER, sa.AGENT_VERSION, convert(datetime,dateadd(s,sa.LAST_UPDATE_TIME/1000,'01/01/1970 00:00:00')) [Last Update Time], convert(datetime,dateadd(s,sa.LAST_SCAN_TIME/1000,'01/01/1970 00:00:00')) [Last Scan Time], convert(datetime,dateadd(s,sa.LAST_DOWNLOAD_TIME/1000,'01/01/1970 00:00:00')) [Last Download Time] from SEM_AGENT SA
join SEM_COMPUTER sc on sc.COMPUTER_ID = sa.COMPUTER_ID
-
Click Save Change.
Creating the Connector Import Rule
-
In the Console browse to Settings > All Settings.
-
Expand Notification Server > Connector.
-
Right Click Import/Export rules and select New > Resource Import Export Rule.
-
Give a name to the rule, I have called mine SEP Agent Data.
-
Click on Data Source and select the data source created above.
-
Change the resource type to Computer.
-
Change the lookup key to Serial Number.
-
Change Serial Number to BIOS_SERIALNUMBER
-
Select Create Resource if it does not exist.
-
Select Update Existing Resources.
-
Set Removed Assets to Left unchanged.
-
Set Resource Name to COMPUTER_NAME.
-
Click Select Data Classes.
-
Click << to remove all existing data classes.
-
Click On Group and expand Inventory.
-
Click on custom.
-
Highlight SEP_Agent_Data.
-
Click >.
-
Click OK.
-
Ensure the following match in SEP_Agent_Data;
-
Agent_Version = AGENT_Version.
-
Last_Update_Time = Last Update Time
-
Last_Scan_Time = Last Scan Time
-
Last_Download_Time
-
Apply a schedule.
-
Click Save Changes.
Reporting on the Data
There are 4 reports that are worth creating for this as detailed below;
- Clients that are active in Altiris in the last 30 days but not in SEP for at least 30 days;
select vc.name, ccr.Last_Config, sad.Last_Update_Time, sad.Last_Scan_Time, sad.Last_Download_Time
from vComputer vc
left join (select resourceguid, max(starttime) [Last_Config] from Evt_NS_Client_Config_Request group by ResourceGuid) ccr on ccr.ResourceGuid = vc.Guid
left join Inv_SEP_Agent_Data sad on sad._ResourceGuid = vc.Guid
where datediff(day,ccr.Last_Config,GETDATE()) < '30'
and (DATEDIFF(day,sad.Last_Update_Time,GETDATE()) > '30' or sad.Last_Update_Time is null)
and (DATEDIFF(day,sad.Last_Scan_Time,GETDATE()) > '30' or sad.Last_Scan_Time is null)
and (DATEDIFF(day,sad.Last_Download_Time,GETDATE()) > '30' or sad.Last_Download_Time is null)
-
Clients that are active in SEP in the last 30 days but not in Altiris in the last 30 days;
select vc.name, ccr.Last_Config, sad.Last_Update_Time, sad.Last_Scan_Time, sad.Last_Download_Time
from vComputer vc
left join (select resourceguid, max(starttime) [Last_Config] from Evt_NS_Client_Config_Request group by ResourceGuid) ccr on ccr.ResourceGuid = vc.Guid
left join Inv_SEP_Agent_Data sad on sad._ResourceGuid = vc.Guid
where (datediff(day,ccr.Last_Config,GETDATE()) > '30' or ccr.Last_Config is null)
and (DATEDIFF(day,sad.Last_Update_Time,GETDATE()) < '30'
or DATEDIFF(day,sad.Last_Scan_Time,GETDATE()) < '30'
or DATEDIFF(day,sad.Last_Download_Time,GETDATE()) > '30')
-
Clients that are inactive in both SEP and Altiris for Greater than 30 days;
select vc.name, ccr.Last_Config, sad.Last_Update_Time, sad.Last_Scan_Time, sad.Last_Download_Time
from vComputer vc
left join (select resourceguid, max(starttime) [Last_Config] from Evt_NS_Client_Config_Request group by ResourceGuid) ccr on ccr.ResourceGuid = vc.Guid
left join Inv_SEP_Agent_Data sad on sad._ResourceGuid = vc.Guid
where (datediff(day,ccr.Last_Config,GETDATE()) > '30' or ccr.Last_Config is null)
and (DATEDIFF(day,sad.Last_Update_Time,GETDATE()) > '30' or sad.Last_Update_Time is null)
and (DATEDIFF(day,sad.Last_Scan_Time,GETDATE()) > '30' or sad.Last_Scan_Time is null)
and (DATEDIFF(day,sad.Last_Download_Time,GETDATE()) > '30' or sad.Last_Download_Time is null)
-
Clients that are not fully functional in SEP;
select vc.name, ccr.Last_Config, sad.Last_Update_Time, sad.Last_Scan_Time, sad.Last_Download_Time
from vComputer vc
left join (select resourceguid, max(starttime) [Last_Config] from Evt_NS_Client_Config_Request group by ResourceGuid) ccr on ccr.ResourceGuid = vc.Guid
left join Inv_SEP_Agent_Data sad on sad._ResourceGuid = vc.Guid
where (DATEDIFF(day,sad.Last_Update_Time,GETDATE()) > '30' or sad.Last_Update_Time is null)
or (DATEDIFF(day,sad.Last_Scan_Time,GETDATE()) > '30' or sad.Last_Scan_Time is null)
or (DATEDIFF(day,sad.Last_Download_Time,GETDATE()) > '30' or sad.Last_Download_Time is null)
If you follow this previous article of mine to extend the AD import then you can have 3 sources of truth for comparison. https://www-secure.symantec.com/connect/articles/finding-broken-agents-extending-ad-import