Video Screencast Help
Search Video Help Close Back
to help

What is the SQL Script to query the SEM5 database to find duplicate SEP client ?

Created: 23 Jul 2012 | Updated: 24 Jul 2012 | 10 comments
Dushan Gomez's picture
Dushan Gomez
0 0 Votes
Login to vote
This issue has been solved. See solution.

Hi,

I believe that there were issues with duplicate Server name in my SEPM database which was  caused by deploying VM from templates with SEP active in it, so I wonder if anyone can assist me with the SQL script to query the database SEM5 so that I can delete it manually from the SQL Server DB ?

This is the Tech Note about the Database schema http://www.symantec.com/business/support/index?page=content&id=TECH105256

Does Installing SEP v11.0.6 in a server (VM) and then clone the server creates duplicate entry in the DB ?

Thanks

Discussion Filed Under:
, , , , , , , , , , , , , ,

Comments 10 Comments • Jump to latest comment

pete_4u2002's picture
pete_4u2002 Symantec Employee
What is the SQL Script to query the SEM5 database to find duplicate SEP client ? - Comment:24 Jul 2012 : Link

do you mean the sep client have duplicate entries?

Cheers!
Pete

Help Link: http://www.symantec.com/business/support/overview.jsp?pid=54619

0
Login to vote
  • Actions
Dushan Gomez's picture
Dushan Gomez
What is the SQL Script to query the SEM5 database to find duplicate SEP client ? - Comment:24 Jul 2012 : Link

Yes in the SEPM database ?

Dushan Gomez
IT Manager
VCP 4 and 5 | MCITP Exchange Server | MCTS SharePoint Server | MCP Windows XP

 

+1
Login to vote
  • Actions
pete_4u2002's picture
pete_4u2002 Symantec Employee
What is the SQL Script to query the SEM5 database to find duplicate SEP client ? - Comment:24 Jul 2012 : Link

select computer_name from SEM_COMPUTER where computer_name in (select computer_name from SEM_COMPUTER group by computer_name having (count(computer_name)>1))

Cheers!
Pete

Help Link: http://www.symantec.com/business/support/overview.jsp?pid=54619

+2
Login to vote
  • Actions
Dushan Gomez's picture
Dushan Gomez
What is the SQL Script to query the SEM5 database to find duplicate SEP client ? - Comment:24 Jul 2012 : Link

When I execute the following SQL script:

 

select computer_name, computer_domain_name, current_login_user
from SEM_COMPUTER
where computer_name in (
select computer_name 
from SEM_COMPUTER 
group by computer_name 
having (
count(computer_name)>1
)
)
and computer_name not like '%WorkStation%'
 
there is no way to differentiate which record is currently unique or in use by the actual SEPM, any chance to know which one is the latest record ?

Dushan Gomez
IT Manager
VCP 4 and 5 | MCITP Exchange Server | MCTS SharePoint Server | MCP Windows XP

 

+1
Login to vote
  • Actions
Dushan Gomez's picture
Dushan Gomez
What is the SQL Script to query the SEM5 database to find duplicate SEP client ? - Comment:24 Jul 2012 : Link

Thanks for the reply Pete,

One question, why is the IP address and the Timestamp is in decimal ?

any reason and way to convert it into its correct format ?

Dushan Gomez
IT Manager
VCP 4 and 5 | MCITP Exchange Server | MCTS SharePoint Server | MCP Windows XP

 

+1
Login to vote
  • Actions
pete_4u2002's picture
pete_4u2002 Symantec Employee
What is the SQL Script to query the SEM5 database to find duplicate SEP client ? - Comment:24 Jul 2012 : Link

the data inserted in the same format..yes there is way to calculate , i need to check..

Cheers!
Pete

Help Link: http://www.symantec.com/business/support/overview.jsp?pid=54619

0
Login to vote
  • Actions
pete_4u2002's picture
pete_4u2002 Symantec Employee
What is the SQL Script to query the SEM5 database to find duplicate SEP client ? - Comment:24 Jul 2012 : Link

i tried to get these columns computername, timestamp and Ip address from teh table, hope this helps

 

select computer_name, dateadd(s,convert(bigint,[TIME_STAMP])/1000,'01-01-1970 00:00:00') as [Time Stamp],CAST((case when IP_ADDR1 < 0 then 0xFFFFFFFF + IP_ADDR1 else IP_ADDR1 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when IP_ADDR1 < 0 then 0xFFFFFFFF + IP_ADDR1 else IP_ADDR1 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when IP_ADDR1 < 0 then 0xFFFFFFFF + IP_ADDR1 else IP_ADDR1 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when IP_ADDR1 < 0 then 0xFFFFFFFF + IP_ADDR1 else IP_ADDR1 end & 0xFF as VARCHAR) as LOCAL_IP_ADDRESS from SEM_COMPUTER where computer_name in (select computer_name from SEM_COMPUTER group by computer_name having (count(computer_name)>1))

Cheers!
Pete

Help Link: http://www.symantec.com/business/support/overview.jsp?pid=54619

+1
Login to vote
  • Actions
Ian_C.'s picture
Ian_C. Partner
What is the SQL Script to query the SEM5 database to find duplicate SEP client ? - Comment:24 Jul 2012 : Link

Hi Pete.

Thanks for the calculation to convert the IP address to text. Here is your query, using the V_SEM_COMPUTER view making yours query a little bit easier to read.

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

SELECT computer_name
 , dateadd(minute, @TimeZoneDiff, dateadd(second, [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)
 )



PS

  • Can you tell us why the IP is stored in that format?
  • Can you tell us why in the V_SEM_COMPUTER view, the IP addresses are converted to human readable format, but not the time stamp?
  • I have adjusted your time stamp conversion to display current local time.
Please mark the post that best solves your problem as the answer to this thread.
+1
Login to vote
  • Actions
Mohan Babu's picture
Mohan Babu
What is the SQL Script to query the SEM5 database to find duplicate SEP client ? - Comment:24 Jul 2012 : Link

https://www-secure.symantec.com/connect/forums/generate-report-about-virus-definition-installed-all-sep-clients-managed-sepm#comment-6155491

Mohan Babu

moglie20@gmail.com

+91 9884382160

Your satisfaction is very important to us.If you find above information helpful or it has resolved your issue...please mark it accordingly :)

0
Login to vote
  • Actions
Ian_C.'s picture
Ian_C. Partner
What is the SQL Script to query the SEM5 database to find duplicate SEP client ? - Comment:24 Jul 2012 : Link

Dushan, we meet again.

 

 so I wonder if anyone can assist me with the SQL script to query the database SEM5

There are various scripts scattered throughout the forums. I try and keep this thread up to date.

Try this query first. If that fails, try the next three comments listed below.

My comment from 17th Jan is the same as what Mohan posted above. That will give you the last contact time. 18th Jan comment will help you find the duplicate computers and know which one to delete. You can also try this one from the 9th of Feb.

 

so that I can delete it manually from the SQL Server DB

Definitely not supported by Symantec. You really should only delete computer objects using the console. Once you have found the computer objects, keep the one with the green dot.

 

Does Installing SEP v11.0.6 in a server (VM) and then clone the server creates duplicate entry in the DB

With out preperation, YES. At the very least, you have to delete the HWID.XML file and delete the Registry keys as laid out in the article #102815 by Symantec. For SEP v12 use article 54706. You might also be interested in this article by Mithun which has been converted into tech article 173650.

Please mark the post that best solves your problem as the answer to this thread.
SOLUTION
+2
Login to vote
  • Actions