Endpoint Protection

I am trying to get some basic monitoring in place for SEP 11. I dont have any commercial software so I am trying to do this with a script. Basically I want to retrieve all the information from the "Security Status" -> "More Details" page. Optimally if I could get an XML or access the DB for this information I would be in great shape, otherwise if I could just get that page emailed to me daily that would work too (Just not as nice).

Does anyone know if what I am asking is possible? I have looked for some time and I am not finding any way to do it.

Not possible to do this currrently within the logs/scheduled reports in SEPM.

You would need to use a query analyzer on the DB and pull the info that way.

Oh, and sorry Brian, I do appreciate your response even if it was not what I wanted to hear.  Thanks again!

That is SO annoying because the data is there and should be so easy to get!

SYMANTEC, take note, making it hard for users to monitor your software makes me reluctant to re-up my contracts. We are a small IT team for a large infrastructure and logging in every day to check status IS NOT EFFICIENT. Please give us a way to get this information. I mean give me a break... the data is there yet there is not a report that can be generated that will email the SAME data in one email. Not cool Symantec, not cool.

I guess I am done ranting, I have just wasted so much time on something that should be so easy... especially for "Enterprise" software.

I agree though, this should be included in the logs/scheduled reports.

http://localhost:8014/reporting 

you can use this.

Rafeeq,

The problem is I need this information to be automated. I am required to provide a report daily for auditing purposes, and logging into the console is not efficient. Optimally I would use Powershell to read a logfile or get the data from a database (As I do with other Symantec products). Or at the very least have SEP email me the SAME report from the "Security Status" -> "More Details" page.

This functionality is not available.

Hi cochran242,

If you want, I can give you the database schema so you can run your own SQL queries to get what you want. If so, I just need the SEPM version you are using.

John,

Yes, that would be helpful. I am running Version 11.0.5002.333

Also, SEP does not appear to be using MS SQL, but some other DB. The DB file name is sem5.db and it looks like the DB application runs under the executable dbsrv9.exe. How would I go about accessing the DB (command line tools, Powershell, Etc...)?

Here is the 11.0.5 schema

Attachment(s)

Schema_Reference_Guide_SEP11.0.5_0.pdf   1.21 MB 1 version

You can use DBISQLC.exe, I believe this is located in the X:\...\SEPM\ASA\Win32 folder.

When you open this file it will give you connection options, enter your database login which should be "dba" and whatever password. At the botton in the Data Source Name field choose SymantecEndpointSecurityDSN.

The next screen should be fairly familiar, type commands at bottom, click execute, results at top.

You can create a query and save it as an .SQL file to be ran at any time you want.

Here's some further details:

.sql File considerations:

• A .sql file contains a list of SQL commands separated by semicolons.
• After the list of SQL commands, you can specify output parameters.
• These will be necessary when outputting the results of the SQL queries to a text file.

Examples of .sql files for different types of data:

• Outputting log data:
  • Log data will be held in the database in an alphanumeric text format that can be easily outputted to an ASCII file:
    SELECT * FROM SEM_AGENT;
    OUTPUT TO “file.txt”
    FORMAT ASCII
• Outputting .xml data:
  • Several configuration files for the SEPM are stored as binary data in the database, but are actually xml files. These can be outputted as xml enclosed in single quotes (‘’):
    SELECT * FROM BASIC_METADATA WHERE TYPE = ‘SemClientGroupTree’;
    OUTPUT TO “file.txt”
    FORMAT ASCII
    HEXADECIMAL OFF

Please be aware that this is outside the scope of support for our SEP technicians to assist you with.

Hopefully you wont have too many other questions cause that's about the extent of my knowledge on this :P

We do know reporting is not where it needs to be, this is why we are giving ITAnalytics out to our SEP customers. It's not perfect but its a step in the right direction for now.

What is needed to get ITAnalytics going? My understanding is you need Altiris in place?

Symantec provides a basic (decent) level of reporting in the product. If you ever used SAV or another vendor you would agree!!

They also make another product called Symantec Security Information Manager that is a very good event correlation engine.

The reports you can get out of that are really just up to your skills and imagination

In addtion the database schema is available and you can then write your own queries or portal front ends as required.

http://www.symantec.com/business/support/index?page=content&id=DOC2411&actp=search&viewlocale=en_US&searchid=1289350176199

The SEP IT Analytics is a stand alone program that works by copying the SEPM DB and running reports off of it. You do not need any other software deployed in your environment for this to work.

I will want to check the installation info for it, I believe there are a few things you will need on the server itself that the analytics program resides on.

SEP IT Analytics does not sound automated to me if you have to copy the DB.

The route I chose is to have a scheduled task nightly dump the contents of the tables I needed:

1. CREATED SEM_AGENT.sql in "C:\Program Files\Symantec\Symantec Endpoint Protection Manager\ASA\win32\" that contains: 
   SELECT * FROM SEM_AGENT;
   OUTPUT TO "SEM_AGENT.CSV"
   FORMAT ASCII
   HEXADECIMAL OFF
2. SCHEDULED TASK RUNS:  
   C:\Program Files\Symantec\Symantec Endpoint Protection Manager\ASA\win32\dbisqlc.exe -c "DSN=SymantecEndpointSecurityDSN;UID=DBA;PWD=PASSWORD" SEM_AGENT.sql -q

Then I just have powershell import the csv:

1. $SEM_Agent = import-csv "\\SERVERNAME\c$\Program Files\Symantec\Symantec Endpoint Protection Manager\ASA\win32\SEM_AGENT.csv" -Header "Agent_ID","Agent_Type","R_OS_Type","Computer_ID","Domain_ID","Group_ID","Agent_Version","Profile_Version","Profile_Serial_NO","Profile_Checksum","IDS_Version","IDS_Serial_NO","IDS_Checksum","HI_Status","HI_ReasonCode","HI_REASONDESC","CREATION_TIME","STATUS","LAST_UPDATE_TIME","LAST_SERVER_ID","LAST_SITE_ID","ATTRIBUTE_EXTENSION","FULL_NAME","EMAIL","JOB_TITLE","DEPARTMENT","EMPLOYEE_NUMBER","EMPLOYMENT_STATUS","OFFICE_PHONE","MOBILE_PHONE","HOME_PHONE","USN","TIME_STAMP","DELETED","RESERVED_INT1","RESERVED_INT2","RESERVED_BIGINT1","RESERVED_BIGINT2","RESERVED_CHAR1","RESERVED_CHAR2","RESERVED_varchar1","PATTERN_IDX","AP_ONOFF","INFECTED","WORSTINFECTION_IDX","LAST_SCAN_TIME","LAST_VIRUS_TIME","CONTENT_UPDATE","AVENGINE_ONOFF","TAMPER_ONOFF","MAJOR_VERSION","MINOR_VERSION","REBOOT_REQUIRED","REBOOT_REASON","LICENSE_STATUS","LICENSE_EXPIRY","TIMEZONE","FIREWALL_ONOFF","FREE_MEM","FREE_DISK","LAST_DOWNLOAD_TIME","CURRENT_CLIENT_ID"

Then do with the data as I need.

Thanks James for getting me the info I needed. I just wish this all was part of SEM to begin with. Like an automated report containing the "Security Status" -> "More Details" data that could  drop in XML so I could use that in my monitoring scripts... would be much simpler.

If I get the time I could modify the Select statement to grab only the fields I needed (LAST_SCAN_TIME,COMPUTER_ID,PATTERN_IDX), I just did not get to that yet (as I am not a SQL guy... just need to look up syntax).

Any info on this would be great. I have a server just sitting around so I could try it out.

Correct, I don't believe IT Analytics is automated. That comment was just for the user that asked about it.

For automation I believe the route you took is unfortunately the best there is at this point.

Thanks John for the SQL script sharing, I appreciate that.

Sorry to bump an old thread, but I was wondering where in the DB schema the current virus definition installed on a client is located? I've scoured the db and have been unable to find anything. I would like the date of the release and the revision #.

Thanks,

Jim