Client Management Suite

I found this query on Symantec Connect:

SELECT [COMPUTER_NAME]
, [COMPUTER_ID]
, [HARDWARE_KEY]
,[CURRENT_LOGIN_USER]
, dateadd(s,convert(bigint,[TIME_STAMP])/1000,'01-01-1970 00:00:00') as [Time Stamp]
,[IP_ADDR1_TEXT]
FROM [V_SEM_COMPUTER]
where [COMPUTER_NAME] in (
select [COMPUTER_NAME]
from [V_SEM_COMPUTER]
group by [COMPUTER_NAME]
having COUNT([COMPUTER_NAME]) >1)
order by [COMPUTER_NAME], [Time Stamp] desc

It is relatively straight forward and identifies duplicates in SEPM. Two questions. One can I run this on the NS 7? If so, how do I adjust it as I need two reports. One for each of my two SEPM's. Second when I ran the query initially it returned results, but as I have deleted duplicates the query is still showing names I deleted in the SEPM.

By the way I know you can enable purging to get rid of these records. I just need something that I can quickly reference and I don't believe the SEPM has something like that. Thanks.

You can run the report from NS 7 if the service account used by NS 7 to run the report has access to the SEPM databases.  You could actually put any SQL report there provided the account had access to the databases.

To use a simple example, you could execute the following report from NS7:

SELECT TOP 5 * FROM sem5A.dbo.[V_SEM_COMPUTER] where sem5A is the name of your database for SEPM A.

This assumes the same SQL server hosts SEM5A that hosts your Symantec_CMDB database for NS7.  If it's a different server, list it out:

SELECT TOP 5 * FROM sql_prod_016.sem5A.dbo.[V_SEM_COMPUTER] where sql_prod_016 is the name of the SQL server and the instance is default.

Once this works for one database, add the second, then join using UNION ALL:

SELECT TOP 5 * FROM sql_prod_016.sem5A.dbo.[V_SEM_COMPUTER]
UNION ALL
SELECT TOP 5 * FROM sql_prod_016.sem5B.dbo.[V_SEM_COMPUTER]

You may want to add a column to specify source:

SELECT TOP 5 *,'SEPM A' as 'Source' FROM sql_prod_016.sem5A.dbo.[V_SEM_COMPUTER]
UNION ALL
SELECT TOP 5 *,'SEPM B' as 'Source' FROM sql_prod_016.sem5B.dbo.[V_SEM_COMPUTER]

Does this help answer how you might query the SEPM databases from within Altiris?

Hi,

Thanks for helping out. The SEPM database is on a different server than the NS 7. The account we use for the NS is a generic AD account setup for use as a Service Account. So the next problem is this account does not have access to the SEPM. This can't be that hard, but I don't want to destabilize anything. What permissions would I need to give the account in SQL? Thanks. By the way I am not sure if IT Analytics might be the way to go. Been thinking about this, but I am just for something quick and easy. Bells and whistles can come later.

SELECT permissions, at the very least.  If you have a SQL administrator handling your SQL databases for SEPM, ask the SQL administrator to provide the appropriate security.  Otherwise something like this should get you started, but I'd practice with a non-production database:
http://msdn.microsoft.com/en-us/library/ms178569.aspx

IT Analytics is a decent option because it allows you to report against multiple CMDBs, so I imagine it could handle multiple sem5 databases, assuming they have the same schema.