2 Replies Latest reply on Jun 19, 2013 9:49 AM by senorp3450

    ePO 4.6.3 Scripting type issues with S-expression operators

      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

        • 1. Re: ePO 4.6.3 Scripting type issues with S-expression operators

          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-using-any-external-library

                    """

                    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

          • 2. Re: ePO 4.6.3 Scripting type issues with S-expression operators

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