ePO 4.6.3 Scripting type issues with S-expression operators

[senorp3450]

Good Evening,

I'm looking to automate using the python API and have run into a scripting challenge.

my SQL query provides what I'm looking for

select [EPOEvents].[DetectedUTC], [EPOEvents].[TargetHostName], [EPOEvents].[ThreatName], [EPOEvents].[ThreatActionTaken], [EPOEvents].[AnalyzerIPV6], [EPOEvents].[AnalyzerMAC], [EPOEvents].[AnalyzerDetectionMethod], [EPOEvents].[ThreatCategory], [EPOEvents].[SourceProcessName], [EPOEvents].[AutoID] from [EPOEvents] where ( [EPOEvents].[DetectedUTC] between '2013-05-30T01:01:06.143' and '2013-05-31T01:01:06.143' ) order by [EPOEvents].[DetectedUTC] desc

but the python executeQuery equivalent is causing an issue.

>>> mc.core.executeQuery(target='EPOEvents', select='(select EPOEvents.DetectedUTC EPOEvents.TargetHostName EPOEvents.ThreatName EPOEvents.ThreatActionTaken EPOEvents.AnalyzerIPV6 EPOEvents.AnalyzerMAC EPOEvents.AnalyzerDetectionMethod EPOEvents.ThreatCategory EPOEvents.SourceProcessName EPOEvents.AutoID)', where="(where (between EPOEvents.DetectedUTC (timestamp \"" + oldTime2 + "\") (timestamp \"" + newTime2 + "\")))", order='( order (desc EPOEvents.DetectedUTC))');

Traceback (most recent call last):

  File "<stdin>", line 1, in <module>

  File "/usr/lib/python2.6/site-packages/mcafee.py", line 454, in __call__

    raise e

mcafee.CommandInvokerError: com.mcafee.orion.core.query.sexp.SexpString cannot be cast to com.mcafee.orion.core.query.sexp.SexpLong

>>> type(oldTime2)

<type 'str'>

>>> oldTime2

'2013-05-30T01:01:06.143'

>>> newTime2

'2013-05-31T01:01:06.143'

Have tried using time.time(), time.gmtime() and others to no avail.

Any help would be appreciated.

Tom

[jbrooks2 · Accepted solution]

Hi Tom,

So the inner workings of core.executeQuery are a bit tricky when it comes to times.  You're on the right track with trying to get to epoch time.

1) The timestamp operator just needs a long value (aka 64-bit int) as an operand (no quotes needed) that represents milliseconds since the epoch.  The where string should look similar to the following:

(where (between EPOEvents.DetectedUTC (timestamp 1369900866140) (timestamp 1369987266140)))

2)  Now, we have to get the time string into millis and with some stackoverflow help here's a sample script that should do what you want.  You'll have to tweak the times a bit for your timezone (I assumed PDT here) and append the timezone offset to your time string to use the iso_to_timestamp function.  If anybody knows an easier way to convert to millis, please share

import mcafee, calendar, datetime, time

def iso_to_timestamp(ts):

          """

          Takes ISO 8601 format(string) and converts to epoch time in millis(string).

          Adapted from http://stackoverflow.com/questions/12223284/convert-iso8601-format-to-seconds-in-python-without-usin...

          """

          dt = datetime.datetime.strptime(ts[:-7],'%Y-%m-%dT%H:%M:%S.%f') - datetime.timedelta(hours=int(ts[-5:-3]),

          minutes=int(ts[-2:]))*int(ts[-6:-5]+'1')

          seconds = calendar.timegm(dt.timetuple()) + dt.microsecond/1000000.0

          return str(int(seconds*1000))

#

# NOTE: the time strings include the TZ offset (assuming Pacific Daylight Time)

# I tweaked your example to include a 24 hour time window here

#

oldTime2 = iso_to_timestamp('2013-05-30T01:01:06.143-07:00')

newTime2 = iso_to_timestamp('2013-05-31T01:01:06.143-07:00')

#

# Here's arguments for a real simple example using the OrionAuditLog

# I successfully used this against some sample data.

#target_arg='OrionAuditLog'

#select_arg='(select OrionAuditLog.Message OrionAuditLog.StartTime OrionAuditLog.EndTime)'

#where_arg='(where (between OrionAuditLog.StartTime (timestamp ' + oldTime2 + ') (timestamp ' + newTime2 + ')))'

#order_arg='(order (desc OrionAuditLog.StartTime))'

#

# Here's arguments for yours against EPOEvents. Should work.

#

target_arg='EPOEvents'

select_arg='(select EPOEvents.DetectedUTC EPOEvents.TargetHostName EPOEvents.ThreatName EPOEvents.ThreatActionTaken EPOEvents.AnalyzerIPV6 EPOEvents.AnalyzerMAC EPOEvents.AnalyzerDetectionMethod EPOEvents.ThreatCategory EPOEvents.SourceProcessName EPOEvents.AutoID)'

where_arg='(where (between EPOEvents.DetectedUTC (timestamp ' + oldTime2 + ') (timestamp ' + newTime2 + ')))'

order_arg='(order (desc EPOEvents.DetectedUTC))'

# Here's what the where argument should look like

print 'where = ' + where_arg

#replace with your server/port and credentials

#mc = mcafee.client('my-server-name',8443,'my-username','my-password')

r = mc.core.executeQuery(target=target_arg, select=select_arg, where=where_arg, order=order_arg);

# Spit out the results (JSON)

print r

Let me know if that works for you.

-Jeremy

[senorp3450]

Thank you for this excellent and timely method... Regards, Tom Smith