Endpoint Protection

I've been looking through the forum's, and have come up with a few SQL queries to identify duplates and to delete them, however it doesn't appear to have any affect on the license counts in use as per the GUI on the home screen of the SEPM.

We are running 12.1 RU6MP1a.

Also in querieng the SEM_COMPTTER, SEM_CLIENT and SEM_AGENT tables as well as doing inner joins og each table on the COmputer_ID field, the SQL query results all vary with different ammounts, and the SEPM Licensing window (showing t he number of licenses in use) shows a different number as well, none of the number match up.

Here are my sample queries, I also have them for the SEM_Client table as well.

Please advise how I can write the correct query to find and delete all duplicates so that my in use license count decreases, as currently the only way to do this is to delete them manyaually form the SEPM and with over 600 machines a day to remove it is not feasible to do manually.  Changing the SEPM system setting to auto-purge machines older than X days can;t be adjusted below the set 30 day limit for auditing purposes.

Find all machines in the table:

DECLARE @TimeZoneDiff int    
SELECT @TimeZoneDiff = datediff(minute, getutcdate(), getdate())

SELECT computer_ID
 , computer_name
 , TIME_STAMP
 , dateadd(minute, @TimeZoneDiff, dateadd(second, TIME_STAMP/1000, '01-01-1970 00:00:00')) as [TimeStamp]
 , dateadd(s,convert(bigint,TIME_STAMP)/1000,'01-01-1970 00:00:00') LastUpdateTime
 , DATEDIFF(dd,(dateadd(s,convert(bigint,TIME_STAMP)/1000,'01-01-1970 00:00:00')),GETDATE()) as [TCddDate Diff]
 , DATEDIFF(hh,(dateadd(s,convert(bigint,TIME_STAMP)/1000,'01-01-1970 00:00:00')),GETDATE()) as [TChhDate Diff]
 , DATEDIFF(mm,(dateadd(s,convert(bigint,TIME_STAMP)/1000,'01-01-1970 00:00:00')),GETDATE()) as [TCmmDate Diff]
FROM SEM_COMPUTER
 GROUP BY computer_name, TIME_STAMP, computer_ID
 Order by computer_name, TIME_STAMP

Find Duplicates in the table:

DECLARE @TimeZoneDiff int    
SELECT @TimeZoneDiff = datediff(minute, getutcdate(), getdate())

  SELECT Table1.computer_ID
  , Table1.COMPUTER_NAME
  , Table2.computer_name
  , COUNT(Table2.COMPUTER_NAME) AS NumOccurrences
  , Table1.TIME_STAMP
  , dateadd(minute, @TimeZoneDiff, dateadd(second, Table1.TIME_STAMP/1000, '01-01-1970 00:00:00')) as [TimeStamp]
  , dateadd(s,convert(bigint,Table1.TIME_STAMP)/1000,'01-01-1970 00:00:00') LastUpdateTime
  , DATEDIFF(dd,(dateadd(s,convert(bigint,Table1.TIME_STAMP)/1000,'01-01-1970 00:00:00')),GETDATE()) as [TCddDate Diff]
  , DATEDIFF(hh,(dateadd(s,convert(bigint,Table1.TIME_STAMP)/1000,'01-01-1970 00:00:00')),GETDATE()) as [TChhDate Diff]
  , DATEDIFF(mm,(dateadd(s,convert(bigint,Table1.TIME_STAMP)/1000,'01-01-1970 00:00:00')),GETDATE()) as [TCmmDate Diff]
  , COUNT(Table1.COMPUTER_NAME) AS [NumOccurrences]
  , row_number() OVER(PARTITION BY Table1.COMPUTER_NAME ORDER BY Table1.TIME_STAMP) AS [rn]
  FROM SEM_COMPUTER As Table1
    inner join SEM_COMPUTER as Table2 on Table1.COMPUTER_NAME = Table2.COMPUTER_NAME
  Where DATEDIFF(dd,(dateadd(s,convert(bigint,Table1.TIME_STAMP)/1000,'01-01-1970 00:00:00')),GETDATE()) > 2
        and table2.COMPUTER_NAME != 'CTXTEMPLATE'
        and table2.COMPUTER_NAME != 'DALW2K8R2UPDT'
        and table2.COMPUTER_NAME != 'W2K8R2CTXUPDT'
        GROUP BY table2.computer_name, Table1.computer_name, Table1.COMPUTER_ID, table1.TIME_STAMP
        HAVING (count(table2.computer_name)>1)
 Order by Table1.COMPUTER_NAME, Table1.TIME_STAMP
 

Delete duplicates form the table:

DECLARE @TimeZoneDiff int    
SELECT @TimeZoneDiff = datediff(minute, getutcdate(), getdate())

  SELECT Table1.computer_ID
  , Table1.COMPUTER_NAME
--  , Table2.computer_name
  , COUNT(Table2.COMPUTER_NAME) AS NumOccurrences
  , Table1.TIME_STAMP
  , dateadd(minute, @TimeZoneDiff, dateadd(second, Table1.TIME_STAMP/1000, '01-01-1970 00:00:00')) as [TimeStamp]
  , dateadd(s,convert(bigint,Table1.TIME_STAMP)/1000,'01-01-1970 00:00:00') LastUpdateTime
  , DATEDIFF(dd,(dateadd(s,convert(bigint,Table1.TIME_STAMP)/1000,'01-01-1970 00:00:00')),GETDATE()) as [TCddDate Diff]
  , DATEDIFF(hh,(dateadd(s,convert(bigint,Table1.TIME_STAMP)/1000,'01-01-1970 00:00:00')),GETDATE()) as [TChhDate Diff]
  , DATEDIFF(mm,(dateadd(s,convert(bigint,Table1.TIME_STAMP)/1000,'01-01-1970 00:00:00')),GETDATE()) as [TCmmDate Diff]
--  , COUNT(Table1.COMPUTER_NAME) AS [NumOccurrences]
  , row_number() OVER(PARTITION BY Table1.COMPUTER_NAME ORDER BY Table1.TIME_STAMP) AS [rn]
into #temp FROM SEM_COMPUTER As Table1
inner join SEM_COMPUTER as Table2 on Table1.COMPUTER_NAME = Table2.COMPUTER_NAME
  Where DATEDIFF(dd,(dateadd(s,convert(bigint,Table1.TIME_STAMP)/1000,'01-01-1970 00:00:00')),GETDATE()) > 2
and table2.COMPUTER_NAME != 'CTXTEMPLATE'
and table2.COMPUTER_NAME != 'DALW2K8R2UPDT'
and table2.COMPUTER_NAME != 'W2K8R2CTXUPDT'
GROUP BY table2.computer_name, Table1.computer_name, Table1.COMPUTER_ID, table1.TIME_STAMP
HAVING (count(table2.computer_name)>1)
--Order by Table1.COMPUTER_NAME, Table1.TIME_STAMP

delete from SEM_COMPUTER where COMPUTER_ID in(select COMPUTER_ID from #temp)

drop table #temp

Did you try the one here:

http://www.symantec.com/connect/articles/compilation-sql-queries-sepm-database

DECLARE @TimeZoneDiff int   
SELECT @TimeZoneDiff = datediff(minute, getutcdate(), getdate())
SELECT UPPER([COMPUTER_NAME])
   , [COMPUTER_ID]
   , [HARDWARE_KEY]
   , [CURRENT_LOGIN_USER]
   , dateadd(minute, @TimeZoneDiff, dateadd(second, [TIME_STAMP]/1000, '01/01/1970')) as [Time Stamp]
   , [IP_ADDR1_TEXT]
FROM [V_SEM_COMPUTER]
WHERE [COMPUTER_NAME] in
   (
      SELECT [COMPUTER_NAME]
      FROM [V_SEM_COMPUTER]
      WHERE [DELETED] = 0
      GROUP BY [COMPUTER_NAME]
      HAVING COUNT([COMPUTER_NAME]) >1
   )
ORDER BY [COMPUTER_NAME]
   , [Time Stamp] DESC

This might help, please have a look

https://www-secure.symantec.com/connect/articles/how-find-duplicated-hardware-ids-database

Thanks, however this isn't my issue.  My duplicate machines have different hardware ID's.  I need to delete the older mahcine name and whether I delete from the SEM_Computer table, SEM_CLient table or the SEM_Agent table, or all three, it has no effect on the GUI license window showing my over deployment.

I have seen that and it only deletes the data from that view, I also tried deleting the data from the actual table SEM_Computer, but it has no affect on the license counts in the GUI.

Hi Greg,

I don't recomend to play with the database since SEPM database has so many linked entries in multiple tables and no body here is enough capable to tell you SQL stuff. Only backline team can help they will not I am sure.

there is another workaround which I am using and you can also use a MOVE Client Script in the no support folder that you can use to delete multiple objects from the console. below is brief idea

Generate list of duplicate clients from your SQL query

put comptuername or IP address in the txt files

create a new group in the SEPM Console

Move all your duplicate clients list by using MOVE CLIENT script

and now from console in that new group sort by last time stamp and select all >>>>then delete....

I hope it will help.

regards

Erm, why do you have a requirement to remove over 600 duplicates a day?  Are you using non-persistent VMs?

If so, I'd recommend checking out the below articles for using SEP on non-persistent VMs:

http://www.symantec.com/docs/HOWTO81133
http://www.symantec.com/docs/HOWTO81120
http://www.symantec.com/docs/HOWTO81134
http://www.symantec.com/docs/HOWTO81115

If there's some other reason for your 600 duplicates then ignore me :)

I would suggest to follow one of the following 2 options to ease the job of deleting the duplicate clients (with same computer name and different Hardware ID).

1) Integrating AD with SEPM and impurting the group structure from AD. (provided that all the SEP clients are in computer mode, not user mode). This will make sure that one computer name will only have one entry in the SEPM.

2) Use MS Excel to find the computer names of the duplicate entries. Use MoveClients utility to move all those clients with duplicate computer names(from all over the SEPM) to a particular (new/dedicated) group in SEPM. During this, both the currently online client entries as well as their old duplicate entries will be moved to the destination group. Once moved, you can manually select the clients with oldest connection date (by holding the ctrl button) and delet them all at once. Use the MoveClients utility to move currently online clients back to their original groups. 

About MoveClients utility: http://www.symantec.com/docs/TECH98302

Note: Check the MoveClients.pdf file included in the installation disk on how to use the MoveClients utility.