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
Solved! Go to 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
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
Thank you for this excellent and timely method... Regards, Tom Smith
Corporate Headquarters
6220 America Center Drive
San Jose, CA 95002 USA