Maximiliano Cittadini

nw2esper convertion script

Discussion created by Maximiliano Cittadini on Sep 25, 2017
Latest reply on Nov 1, 2017 by Maximiliano Cittadini

I want to share with the community my very first python script (mainly based on the work found here: REST API to CSV by Rui Ataide )

The goal of this little script is to assist anyone that have to create an ESA alert. I use the esper tryout page EsperTech Esper EPL Online very often, but I don't like to create the schema and create the events manually. This script will create the schema definition of the event with the related meta and alse generate the events with time increments.

The idea is very simple. You run the script with the service that you want to use, the query (as it shows on the investigation debug) and an optional filename to output the results.

usage: nw2esper [-h] -s SERVICE -q QUERY [-u USERNAME] [-p PASSWORD]
                [-o OUTPUT]

 

Retrieve the metadata of the sessions afected by the query and format them to
use in the Esper Tryout Page (http://esper-epl-
tryout.appspot.com/epltryout/mainform.html)

 

optional arguments:
  -h, --help            show this help message and exit
  -s SERVICE, --service SERVICE
                        the service that you want to use (e.g. -s
                        http://broker:50103)
  -q QUERY, --query QUERY
                        query used to retrieve the sessions of your interest
                        (copied from NW investigation debug)
  -u USERNAME, --user USERNAME
                        the username to use in the REST API
  -p PASSWORD, --password PASSWORD
                        the password of the username to use in the REST API
  -o OUTPUT, --output OUTPUT
                        the output file. if is not specified, the output will
                        be the stderr

-O OBFUSCATE, --obfuscate OBFUSCATE
                        a space separated list of the metakeys to obfuscate
-k KEY, --key KEY

                        the key used to (de)obfuscate data
-DO DEOBFUSCATE, --deobfuscate DEOBFUSCATE
                        just deobfuscate using the key

for example:

investigation debug

nw2esper.py -s http://10.100.107.82:50105 -q "(event.source = 'mssqlserver') && (reference.id = '17177') && time=""2017-09-23 15:59:00""-""2017-09-25 15:58:59"""

(NOTE: Be sure to double quote around dates)

 

The result wild be something like:

Event={sessionid=4262634, time=1506222204, size=430, lc_cid='vlcmassi', device_host='srvsql.laboratorio.local', medium=32, device_type='mssql', device_class='Database', header_id='0009', level=4, reference_id='17177', event_source='MSSQLSERVER', event_type='Classic', event_computer='srvsql.laboratorio.local', event_desc='This instance of SQL Server has been using a process ID of 2732 since 05/09/2017 11:12:05 a.m. (local) 05/09/2017 02:12:05 p.m. (UTC). This is an informational message only; no user action is required.', event_time=1506222037, msg_id='Application_17177_MSSQLSERVER', event_cat_name='System.Errors', did='ldec', rid=3976553}


t=t.plus(86328 seconds)


Event={sessionid=4325187, time=1506308532, size=430, lc_cid='vlcmassi', device_host='srvsql.laboratorio.local', medium=32, device_type='mssql', device_class='Database', header_id='0009', level=4, reference_id='17177', event_source='MSSQLSERVER', event_type='Classic', event_computer='srvsql.laboratorio.local', event_desc='This instance of SQL Server has been using a process ID of 2732 since 05/09/2017 11:12:05 a.m. (local) 05/09/2017 02:12:05 p.m. (UTC). This is an informational message only; no user action is required.', event_time=1506308439, msg_id='Application_17177_MSSQLSERVER', event_cat_name='System.Errors', did='ldec', rid=4039106}

 

CREATE SCHEMA Event(event_cat_name string, medium short, event_source string, event_type string, event_time long, level integer, device_host string, event_computer string, device_class string, msg_id string, header_id string, sessionid long, reference_id string, device_type string, time long, lc_cid string, did string, rid long, event_desc string, size long);

that can be used in the esper page:

 

This, hopefully, will help to just focus on the rule that we want to try and not to type events in the page.

 

Please, be free to use and modify the code.

I'm also publishing it in github:

GitHub - shadkianash/nw2esper: Extract Netwitness events metadata to use in te espertech tryout page 

 

once again, this is my first python script, so you may find errors in the code.

 

I hope that you find this useful

Attachments

Outcomes