Reporting Group

Hi,

When we query the SEP Database we are getting duplicated hostname when fetching the data. We are using below SQL command to get the report:

==============================================================

select distinct pattern.version'version',pattern.patterndate'pattern_date',
sem_agent.agent_version'agent_version',sem_client.computer_name'computer_name',
sem_computer.operation_system'operating_system',

dateadd(s,convert(bigint,sem_agent.creation_time)/1000,'01-01-1970 00:00:00')'creation_dttm',

dateadd(s,convert(bigint,sem_agent.last_update_time)/1000,'01-01-1970 00:00:00')
'last_update_time',

dateadd(s,convert(bigint,last_scan_time)/1000,'01-01-1970 00:00:00')'last_scan_time',sem_client.user_name'username',
v_sem_computer.ip_addr1_text'ip_address',identity_map.name'group_name'from((((
sem_agent

INNER JOIN sem_client on((sem_agent.computer_id=sem_client.computer_id)
and(sem_agent.domain_id=sem_client.domain_id))and(sem_agent.group_id=sem_client.
group_id))

INNER JOIN sem_computer on((sem_agent.computer_id=sem_computer.
computer_id)and(sem_agent.domain_id=sem_computer.domain_id))and(sem_agent.
deleted=sem_computer.deleted))

INNER JOIN pattern on sem_agent.pattern_idx=pattern.pattern_idx)
INNER JOIN identity_map on sem_client.group_id=identity_map.id)
INNER JOIN v_sem_computer on sem_computer.comp
sem_agent.deleted=0 order by version desc

===================================================================

The above SQL command will display a few duplicated hostnames but the information like IP address, Virus Definition date, and Last_update_time has different information. If we can program the code to remove the duplicates but will retain the entry with the highest last_update_time will probaly resolve the case for duplicate host machines.

Anyone has the same problem as me? how did you go about in changing the SQL command? any help would be appreciated.

Thanks in advance

Do you see them in the console as well? Are those imaged machines?

Have you seen this article:

Configuring Symantec Endpoint Protection 11.x client for deployment as part of a drive image

http://www.symantec.com/docs/TECH102815

What happens when you do a search in the console for them?

In SEM_Agent table there is a colum called DELETED with value 

1=delete

0=not deleted

try getting the names of only NOT deleted ones

No duplicates are seen when using the SEPM console only when querying directly from SQL. The db. sem_agent.deleted=0 is already set. :(

Can you follow this article, extract the SQL query used by SEPM and use the same in your reports.

https://www-secure.symantec.com/connect/articles/extract-sepm-reports-clear-sql-query-step-step-process

Hello Rafeeq,

I tried it on invetoryreport1-3 but it didn't work on my end. 

Anyway I have already created a local website to publish these reports but the problem I have is the few duplicates that is showing in the report.

Thanks for the help.