SEPM Duplicates Query

Created: 07 Nov 2012 | Updated: 14 Nov 2012 | 13 comments

Briandr73

This issue has been solved. See solution.

Hi,

I love this little query to identify duplicates names in SEPM 11 (not duplicate hardware id's). The problem I have with it when its run more than once in a short period of time the results don't update. Let's say ComputerA is listed twice from this query. If I find the ComputerA that needs to be deleted and then re-run the query it indicates both still exist although I know I did delete the one I wanted to get rid of. Ideas or suggestions on improving this query?

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

Discussion Filed Under:

Security, Endpoint Protection (AntiVirus), Tip/How to, Troubleshooting

Comments RSS Feed

Comments 13 Comments • Jump to latest comment

Brian81 Trusted Advisor

SEPM Duplicates Query - Comment:07 Nov 2012 : Link

What happens if you wait a few minutes than re-run?

Do you have replication partners in place?

SEP Knowledge Base

Endpoint SWAT

Briandr73

SEPM Duplicates Query - Comment:07 Nov 2012 : Link

I have two SEPM'S for each of my two sites and they are only setup for load balancing. No replication. If I wait a few minutes the query results are the same. I will check tomorrow to see if the query results get updated. Thanks.

SameerU Partner Accredited

SEPM Duplicates Query - Comment:12 Nov 2012 : Link

Please upgrade to latest version SEP 12.1 RU1 MP1

Regards

Ian_C. Partner

SEPM Duplicates Query - Comment:12 Nov 2012 : Link

Please ignore SameerU. This has nothing to do with what version of SEP you are using. Instead, it has to do with what information you are pulling out of the database.

You missed the [DELETED] field in the database.

0 = Not deleted, i.e. active
1 = Deleted, i.e. won't display in the console.

Here is your query, slightly re-written to take into account the [DELETED] field.

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

Two things about the re-write

I've converted all computer names to UPPER() case
More importantly, using the 'DECLARE TimeZoneDiff' variable, all your times are now displayed in your local time zone.

Have fun.

Please mark the post that best solves your problem as the answer to this thread.

SOLUTION

Ian_C. Partner

SEPM Duplicates Query - Comment:12 Nov 2012 : Link

Looks like you grabbed that query from my Feb 24 post in the SQL thread.

I have now updated that post with the above query.

Please mark the post that best solves your problem as the answer to this thread.

Briandr73

SEPM Duplicates Query - Comment:14 Nov 2012 : Link

Thank you for the help. As far as the guy advising on the upgrade to 12.1, go find points from someone else!! What you recommend has absolutely nothing to do with the question posed!! Thanks Ian for the revised query. I'd like to take this a step further. How can this be re-written so it deletes all duplicates? I know the clients will re-register so I see no harm in doing this. Thanks.

Ian_C. Partner

SEPM Duplicates Query - Comment:14 Nov 2012 : Link

I would advise against deleting entries directly from the database like this. AFAIK, Symantec also do not support this.

Your best bet is to delete the clients from the SEPM console. That way all linked entries are marked correctly.

You could ofcourse use the DELETE command from SQL, but that seems extreme and def prone to breaking something. You should rather UPDATE the [DELETED] field with a 1. Maybe something like this:

UPDATE dbo.SEM_COMPUTER
SET dbo.SEM_COMPUTER.DELETED = 1
FROM ... (same criteria as used in your query) ...

PS Use at your own risk. I am no SQL expert.

PPS Not only do you have SEM_COMPUTER, but SEM_CLIENT as well.

PPPS Or do as others have done & set the auto purge option down to 1 day let the system take care of it.

Please mark the post that best solves your problem as the answer to this thread.

Briandr73

SEPM Duplicates Query - Comment:14 Nov 2012 : Link

I imagine there are some Altiris guys in the house. I think I asked this before, but can't recall for sure. If I wanted to run this query on the NS 7 do I need to have the first line be something like this:

USE myserver.myorg.org.sem5.dbo.[V_SEM_COMPUTER]

If I run that command above then I get an error similiar to the following:

Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'blah'. No entry found with that name. Make sure that the name is entered correctly.

Sem5 is on another server......

Thanks.

Ian_C. Partner

SEPM Duplicates Query - Comment:14 Nov 2012 : Link

You first need to create linked server objects in the database. Then you can run your query.

This is what it would look like.

Please mark the post that best solves your problem as the answer to this thread.

Briandr73

SEPM Duplicates Query - Comment:14 Nov 2012 : Link

Hi,

So once the linked objects are created is my illustration of how to run this with the 'USE' command correct or am I not on target? Thanks.

Ian_C. Partner

SEPM Duplicates Query - Comment:14 Nov 2012 : Link

That should be pretty much spot on. This is how it has been explained to me

HowTo: in your query use 4 part naming identifier = [linked server].[database].[owner].[object]

For me, the linked server is simply a short name (myserver), not the full FQDN (myserver.myorg.tld). This translates to

SELECT
  [COMPUTER_NAME]
  , [OPERATING_SYSTEM]
  , [DELETED]
FROM [myserver].[sem5].[dbo].[V_SEM_COMPUTER]

Hope that helps.

Thank you for marking this thread as answered.

Please mark the post that best solves your problem as the answer to this thread.

Briandr73

SEPM Duplicates Query - Comment:15 Nov 2012 : Link

I do recall installing ITAnalytics a ways back. Haven't played around with it much, but I or it apparently setup a linked connection to my server. Should be able to use this without doing anything else, right? Here is how I am thinking this needs to be re-worked:

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

I am just not sure where to add what I would assume to be this line:

ITANALYTICS_SEP_SYMANTEC_CMDB_MYNAV_SEM5

Does it go under 'WHERE [DELETED] = 0

Thanks.

Ian_C. Partner

SEPM Duplicates Query - Comment:17 Nov 2012 : Link

Does it go under 'WHERE [DELETED] = 0

That is incorrect.

The four part identifier must be part of your FROM statement. You have 2 FROM statements in your query, thus twice you must use

FROM [LinkedServer].[dbo].[SEM5].[table or view name]

To spell it out, this is what I understand your query should look like

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 [LinkedServer].[dbo].[SEM5].[V_SEM_COMPUTER] -- Put your linked servername here
WHERE [COMPUTER_NAME] in
   (
      SELECT [COMPUTER_NAME]
      FROM [LinkedServer].[dbo].[SEM5].[V_SEM_COMPUTER] -- Put your linked servername here
      WHERE [DELETED] = 0
      GROUP BY [COMPUTER_NAME]
      HAVING COUNT([COMPUTER_NAME]) >1
   )
ORDER BY [COMPUTER_NAME]
   , [Time Stamp] DESC

...

Please mark the post that best solves your problem as the answer to this thread.

SEPM Duplicates Query

Comments 13 Comments • Jump to latest comment

You missed the [DELETED] field in the database.

Your best bet is to delete the clients from the SEPM console. That way all linked entries are marked correctly.

PPPS Or do as others have done & set the auto purge option down to 1 day let the system take care of it.

Would you like to reply?

Links