Video Screencast Help

Extract SEPM Reports into clear SQL query - A step by step process

Created: 07 Oct 2013 • Updated: 30 Oct 2013 | 6 comments
Language Translations
Rafix's picture
+10 10 Votes
Login to vote

Hello

A few days ago my customer asked me about creating various reports depending on SEPM server.Everything sounds clear but reports have to be prepared in clear SQL query... I know that SEPM database schema is available for download but who have so much time to read that, analyze and finally make the proper SQL query. 
 
I found simple workaround/hack how to get all this information from SEPM based on default reports in the Report section in minutes, and it's working for me like a charm.

STEP 1

Install test Symantec Endpoint Protection Server with MS SQL database (in my case). You can find a lot of simple guides how doing that.

STEP 2

Identify what data you need to get clear SQL query. Let's say that for this example we need all computers at risk and the time period will be 1 year.

STEP 3

Open SEPM web console using other web browser then IE. It can be Chrome or FireFox or any other you like.

Your link to the SEPM web console should look like it:

https://sepm_server:8443 replace "sepm_server" your SEPM ip or hostname.

login.JPG

STEP 4

Log in SEPM using web console.

dashboard.JPG

After that open Report Section of SEPM Console and set proper report you want to extract. In my case it would be:

Report Type Risk
Selected report Infected and At Risk Computers
Time range Past Year
Scan Type SONAR

report.JPG

STEP 5

Now we start tricky part. Hit "Create Report" button and you should see popup windows with SEPM report.

In the popup window with URL filed you should see link look like it:

https://your_sepm_server:8445/Reporting/reports/rep_infectedclients.php

link.JPG

Ok. Now we know what PHP file is used to generate report we asked for. Let's find this file on SEPM server.

STEP 6

Open Symantec Endpoint Protection Manager folder and go to: Inetpub\Reporting\Reports

Now find rep_infectedclients.php end open it in any text editor. (remember to change file permission and uncheck "Read Only" in file properties)

file.JPG

Now we add simple one line of code in our file.

rep_infectedclients.php before changes:

before.JPG

and after (I added echo $query;):

after.JPG

After all Save and Close your rep_infectedcomputers.php file.

STEP 7

Now we back to SEPM console and reopen our report I described in STEP 4 and now we should get our clear SQL query.

query.JPG

Aaaaaaaaand yes we have got it :) now you can copy that query into your SQL Management Studio and edit in query designer. That's all folks and remember, this simple example works in case of every type of SEPM report.
I hope that this simple hack would be helpful if you have to prepare a clear SQL query based on SEPM Informations.

Comments 6 CommentsJump to latest comment

Brɨan's picture

Very well done. Thanks for the share.

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.

0
Login to vote
Chetan Savade's picture

It's a good information.

Chetan Savade
Sr.Technical Support Engineer, Endpoint Security
Enterprise Technical Support
CCNA | CCNP | MCSE | SCTS |

Don't forget to mark your thread as 'SOLVED' with the answer that best helps you.<

0
Login to vote
don_berlin's picture

I don't know why it took me until  now to find this, but THANK YOU for this simple and extremely helpful trick.

0
Login to vote
symavkn's picture

Was able to get this to work on the example above but cannot on 5 other reports I've tried. I'm running RU5 and maybe that's the issue or only certain reports can show the SQL query?

0
Login to vote