Video Screencast Help
Search Video Help Close Back
to help

Custom Report in SEPM 12.1 with SQL query

Created: 08 Jan 2013 | Updated: 12 Jan 2013 | 9 comments
harvansh Singh's picture
harvansh Singh
0 0 Votes
Login to vote

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.

Discussion Filed Under:
, , , , , , ,
Group Ownership:

Comments 9 Comments • Jump to latest comment

Brian81's picture
Brian81 Trusted Advisor
Custom Report in SEPM 12.1 with SQL query - Comment:08 Jan 2013 : Link

You can just use the monitors/reports tabs in SEPM

SEP Knowledge Base

Endpoint SWAT

0
Login to vote
harvansh Singh's picture
harvansh Singh
Custom Report in SEPM 12.1 with SQL query - Comment:08 Jan 2013 : Link

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

0
Login to vote
Ashish-Sharma's picture
Ashish-Sharma
Custom Report in SEPM 12.1 with SQL query - Comment:08 Jan 2013 : Link

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

SEPM Knowledgebase Documents  

 

0
Login to vote
Brian81's picture
Brian81 Trusted Advisor
Custom Report in SEPM 12.1 with SQL query - Comment:08 Jan 2013 : Link

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

 

SEP Knowledge Base

Endpoint SWAT

0
Login to vote
harvansh Singh's picture
harvansh Singh
Custom Report in SEPM 12.1 with SQL query - Comment:10 Jan 2013 : Link

Hi All thanks for your all the comments.

Regards

Harvansh Singh

Regards

Harvansh Singh

0
Login to vote
Ashish-Sharma's picture
Ashish-Sharma
Custom Report in SEPM 12.1 with SQL query - Comment:10 Jan 2013 : Link

Hi,

Why my comments deleted ...

Thanks In Advance

Ashish Sharma

SEPM Knowledgebase Documents  

 

0
Login to vote
harvansh Singh's picture
harvansh Singh
Custom Report in SEPM 12.1 with SQL query - Comment:10 Jan 2013 : Link

Hi Ashish,

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

Regards

Harvansh Singh

Regards

Harvansh Singh

0
Login to vote
Ambesh_444's picture
Ambesh_444 Partner Accredited
Custom Report in SEPM 12.1 with SQL query - Comment:18 Mar 2013 : Link

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

Please mark your thread as 'SOLVED' with the answer that helps you.

+2
Login to vote
Rafeeq's picture
Rafeeq
Custom Report in SEPM 12.1 with SQL query - Comment:18 Mar 2013 : Link

Thumbs up :) 

 

Please don't forget to mark your thread solved with whatever answer helped you : ) Rafeeq

0
Login to vote