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.
STEP 4
Log in SEPM using web console.
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 |
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
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)
Now we add simple one line of code in our file.
rep_infectedclients.php before changes:
and after (I added echo $query;):
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.
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.