Video Screencast Help
Scheduled Maintenance: Symantec Connect is scheduled to be down Saturday, April 19 from 10am to 2pm Pacific Standard Time (GMT: 5pm to 9pm) for server migration and upgrades.
Please accept our apologies in advance for any inconvenience this might cause.

Custom Report in SEPM 12.1 with SQL query

Created: 08 Jan 2013 • Updated: 12 Jan 2013 | 9 comments

Hi All,

Please help to write a query in SQL 2008 to generated the reports.

I have SEPM 12.1 RU2 ( Windows server 2008 server) and itigrated with SQL 2008 server.

I need below mention reports, these reports should be genrated automatically at a fix time and send my personal id.

1. All non updated SEP client.

2 All one day old definition SEP client.

3.All 5 days old definition SEP client.

5 All SEP client wich is showing SEP offline Status.

Note :- All reports with Graph chart and client details.

Please share the screen shot for above configuration.

Comments 9 CommentsJump to latest comment

_Brian's picture

You can just use the monitors/reports tabs in SEPM

Please click the "Mark as solution" link at bottom left on the post that best answers your question. This will benefit admins looking for a solution to the same problem.

harvansh Singh's picture

Hi Asish,

I am telling about SQL query.

Our SEPM date base on SQL server, then we can write a query in SQL. SQl will take data from SEPM and it will generate a report. I hope that SQL is having these features and it can be sent a particular mail id.

 

Regards

Harvansh Singh

Regards

Harvansh Singh

Ashish-Sharma's picture

HI ,

Check this thread lot of sql query available

https://www-secure.symantec.com/connect/forums/sql...

Check this artical also

 

Script for Monitoring AV Status of clients (desktop/Laptop)

https://www-secure.symantec.com/connect/articles/s...

Thanks In Advance

Ashish Sharma

 

 

_Brian's picture

Check the database schema for 12.1.2 which will help to create SQL queries

Symantec Endpoint Protection Manager 12.1.2 Database Schema

Article:DOC6039  |  Created: 2012-09-25  |  Updated: 2012-11-13  |  Article URL http://www.symantec.com/docs/DOC6039

 

Please click the "Mark as solution" link at bottom left on the post that best answers your question. This will benefit admins looking for a solution to the same problem.

harvansh Singh's picture

Hi All thanks for your all the comments.

Regards

Harvansh Singh

Regards

Harvansh Singh

Ashish-Sharma's picture

Hi,

Why my comments deleted ...

Thanks In Advance

Ashish Sharma

 

 

harvansh Singh's picture

Hi Ashish,

Your comment is not deleted as knowing, if it got delete by mistake so sorry..

Regards

Harvansh Singh

Regards

Harvansh Singh

Ambesh_444's picture

Hi,

Please check with these..

1) Less drive space query.

 

USE sem5
SELECT computer_name , AGENT.FREE_DISK /1048576 from SEM_AGENT as AGENT
LEFT OUTER JOIN V_SEM_COMPUTER as COMP ON AGENT.COMPUTER_ID = COMP.COMPUTER_ID
LEFT OUTER JOIN IDENTITY_MAP as ID_MAP ON ID_MAP.ID = AGENT.GROUP_ID
where agent.status = '1' and agent.agent_version <> '11.0.6100.645' and 
AGENT.FREE_DISK/1048576 < 8
 
2) Not update group count and IP range Report.
 
use sem5
select  count (*),name,left(IP_ADDR1_TEXT,Len(IP_ADDR1_TEXT)-CHARINDEX('.',Reverse(IP_ADDR1_TEXT))) as Network from SEM_AGENT as SA 
LEFT OUTER JOIN 
PATTERN PAT ON SA.PATTERN_IDX=PAT.PATTERN_IDX 
LEFT OUTER JOIN 
V_SEM_COMPUTER as COMP ON SA.COMPUTER_ID = COMP.COMPUTER_ID
LEFT OUTER JOIN 
IDENTITY_MAP as ID_MAP ON ID_MAP.ID = SA.GROUP_ID
where 
SA.AP_ONOFF!=2 and SA.DELETED='0' and MAJOR_VERSION != '5' and SA.AGENT_TYPE='105'  and patterndate < '2011-03-27 00:00:00.000'
group by
left(IP_ADDR1_TEXT,Len(IP_ADDR1_TEXT)-CHARINDEX('.',Reverse(IP_ADDR1_TEXT))),name--,ip_addr1_text
having count(*) > 10
order by count(*) desc
 
 
3) Not updating group by count.
 
Use sem5
SELECT "IDENTITY_MAP"."NAME" "Group Name" , count(*)
 FROM   (((("SEM_AGENT" "SEM_AGENT" INNER JOIN "SEM_CLIENT" "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" "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" "PATTERN" ON "SEM_AGENT"."PATTERN_IDX"="PATTERN"."PATTERN_IDX") INNER JOIN "IDENTITY_MAP" "IDENTITY_MAP" ON "SEM_CLIENT"."GROUP_ID"="IDENTITY_MAP"."ID") INNER JOIN "V_SEM_COMPUTER" "V_SEM_COMPUTER" ON "SEM_COMPUTER"."COMPUTER_ID"="V_SEM_COMPUTER"."COMPUTER_ID" and "SEM_AGENT"."DELETED"=0
and "sem_agent"."status" = '1'
and "PATTERN"."PATTERNDATE" < '2011-03-01 00:00:00.00'
group by name order by count(*) desc
 
 
4)  GUP list ,IP range and Group name.
 
use sem5
select  name,ip_addr1_text,left(IP_ADDR1_TEXT,Len(IP_ADDR1_TEXT)-CHARINDEX('.',Reverse(IP_ADDR1_TEXT))) as Network from SEM_AGENT as SA 
LEFT OUTER JOIN 
PATTERN PAT ON SA.PATTERN_IDX=PAT.PATTERN_IDX 
LEFT OUTER JOIN 
V_SEM_COMPUTER as COMP ON SA.COMPUTER_ID = COMP.COMPUTER_ID
LEFT OUTER JOIN 
IDENTITY_MAP as ID_MAP ON ID_MAP.ID = SA.GROUP_ID
where 
SA.AP_ONOFF!=2 and SA.DELETED='0' and MAJOR_VERSION != '5' and SA.AGENT_TYPE='105'  and SA.computer_id in (select computer_id from GUP_list) --and (PAT.VERSION is NULL or PAT.VERSION = (SELECT VALUE as LatestAVAS from GUIPARMS with (NOLOCK) where PARAMETER in ('latest_av_defs')))
group by
left(IP_ADDR1_TEXT,Len(IP_ADDR1_TEXT)-CHARINDEX('.',Reverse(IP_ADDR1_TEXT))),name,ip_addr1_text
 
 
5)  Full report query.
 
use bcpsem5
select pat.version as vd_version,'|',i.MAC_addr1,'|', i.CURRENT_LOGIN_USER,'|',i.computer_name,'|',i.ip_addr1_text,'|',OPERATION_SYSTEM,'|',
dateadd(s,convert(bigint,i.TIME_STAMP)/1000,'01-01-1970 00:00:00'),'|',
dateadd(s,convert(bigint,CREATION_TIME)/1000,'01-01-1970 00:00:00'),'|',i.DELETED,'|',
LAST_UPDATE_TIME,'|',agent_version,'|' as group_name from 
sem_agent as sa with (nolock) left outer join pattern pat on sa.pattern_idx=pat.pattern_idx
inner join v_sem_computer i on i.computer_id=sa.computer_id 
--inner join identity_map g on g.id=sa.group_id 
inner join identity_map p on p.id=sa.last_server_id 
inner join identity_map s on s.id=sa.domain_id 
inner join identity_map q on q.id=sa.last_site_id where 
(sa.agent_type='105' or sa.agent_type='151') and sa.deleted='0' 
and (sa.major_version >10 or sa.major_version=5) and I.DELETED = 0
 
 
I hope these query will help you.
 
 

 

 

Thank& Regards,

Ambesh

"Your satisfaction is very important to us. If you find above information helpful or it has resolved your issue. Please don't forget to mark the thread as solved."