IBM QRadar SOAR

IBM QRadar SOAR

Join this online user group to communicate across Security product users and IBM experts by sharing advice and best practices with peers and staying up to date regarding product enhancements.

 View Only
Back to discussions
Expand all | Collapse all

Qradar search from offense ID

  • 1.  Qradar search from offense ID

    Posted Tue December 10, 2019 08:13 AM
    Hello,

    Does anyone know why is it making an error on this query? It is working with all the incidents, but when I execute on this event (in the same organization) it gaves me an error, thats the strange thing:

    2019-12-10 11:22:29,493 ERROR [actions_component] <task[functionworker] (<function _call_the_task at 0x7f41ec0366e0>, <qradar_search[functions.qradar_search] (id=203, workflow=qradar_events_search_using_offense_id, user=resilientuser@company.com) 2019-12-10 10:54:27.527000> qradar_query={u'content': u'SELECT %param1% FROM events WHERE INOFFENSE(%param2%) LAST %param3% MINUTES', u'format': u'text'}, qradar_query_range_end=5, qradar_query_range_start=0, qradar_query_param3=u'18000', qradar_query_param2=u'38422', qradar_query_param1=u"DATEFORMAT(starttime, 'YYYY-MM-dd HH:mm') as StartTime, DOMAINNAME(domainid), QIDNAME(qid), STR(sourceip), STR(destinationip), STR(sourceport), STR(destinationport), CATEGORYNAME(category), STR(magnitude), LOGSOURCENAME(logsourceid), PROTOCOLNAME(protocolid), UTF8(payload), RULENAME(creeventlist)")> (<class 'resilient_circuits.action_message.FunctionException_'>):
    Traceback (most recent call last):
      File "/usr/lib/python2.7/site-packages/fn_qradar_integration/components/qradar_search.py", line 83, in _qradar_search_function
        timeout=timeout)
      File "/usr/lib/python2.7/site-packages/fn_qradar_integration/util/qradar_utils.py", line 253, in ariel_search
        response = ariel_search.perform_search(query)
      File "/usr/lib/python2.7/site-packages/fn_qradar_integration/util/SearchWaitCommand.py", line 95, in perform_search
        raise SearchFailure(search_id, status)
    SearchFailure: Query [cab96e8a-0dd9-4ae3-ab85-ca00d83b79f8] failed with status [1]

    Has anyone any resolution about this error? I would appreciate your help.

    Thank you.



    ------------------------------
    Aitor Vivanco Sata Cruz
    ------------------------------


  • 2.  RE: Qradar search from offense ID

    Posted Tue December 10, 2019 03:58 PM
    Hello Aitor,

    Could you please explain what it means by "when I execute on this event"? What event is that please?
    As you can see, the query is printed out in the log file. It looks like your query is

    SELECT %param1% FROM events WHERE INOFFENSE(%param2%) LAST %param3% MINUTES', u'format': u'text'

    param1 is ""DATEFORMAT(starttime, 'YYYY-MM-dd HH:mm') as StartTime, DOMAINNAME(domainid), QIDNAME(qid), STR(sourceip), STR(destinationip), STR(sourceport), STR(destinationport), CATEGORYNAME(category), STR(magnitude), LOGSOURCENAME(logsourceid), PROTOCOLNAME(protocolid), UTF8(payload), RULENAME(creeventlist)", it looks pretty standard.

    param2 is 38422. Is this a valid QRadar offence id?

    param3 is 18000. Not sure if it is the default, but it seems big. You are looking for 300 hours of data?

    So I would like to check param2 first, make sure 38422 is a good QRadar offence id.

    If it is good, the next to do it to replace all those tokens with the params, and then post the query into the Advanced Search window of QRadar, then QRadar can tell us what is wrong in the query.

    Thanks,

    Yongjian

    Now there are several parameters/tokens in this query. param2 for example needs to be a valid offence_id.

    ------------------------------
    Yongjian Feng
    ------------------------------

    Original Message


  • 3.  RE: Qradar search from offense ID

    Posted Wed December 11, 2019 02:29 AM
    The query starts with this message, as I can see the param2 on the query and on the error message are not the same. But why? 

    2019-12-10 11:19:13,915 INFO [qradar_search] qradar_query: SELECT %param1% FROM events WHERE INOFFENSE(%param2%) LAST %param3% MINUTES
    2019-12-10 11:19:13,917 INFO [qradar_search] qradar_query_param1: DATEFORMAT(starttime, 'YYYY-MM-dd HH:mm') as StartTime, DOMAINNAME(domainid), QIDNAME(qid), STR(sourceip), STR(destinationip), STR(sourceport), STR(destinationport), CATEGORYNAME(category), STR(magnitude), LOGSOURCENAME(logsourceid), PROTOCOLNAME(protocolid), UTF8(payload), RULENAME(creeventlist)
    2019-12-10 11:19:13,920 INFO [qradar_search] qradar_query_param2: 38783
    2019-12-10 11:19:13,921 INFO [qradar_search] qradar_query_param3: 18000
    2019-12-10 11:19:13,923 INFO [qradar_search] qradar_query_param4: None
    2019-12-10 11:19:13,925 INFO [qradar_search] qradar_query_param5: None
    2019-12-10 11:19:13,926 INFO [qradar_search] qradar_query_range_start: 0
    2019-12-10 11:19:13,928 INFO [qradar_search] qradar_query_range_end: 5
    2019-12-10 11:19:13,930 INFO [qradar_search] Running query: SELECT DATEFORMAT(starttime, 'YYYY-MM-dd HH:mm') as StartTime, DOMAINNAME(domainid), QIDNAME(qid), STR(sourceip), STR(destinationip), STR(sourceport), STR(destinationport), CATEGORYNAME(category), STR(magnitude), LOGSOURCENAME(logsourceid), PROTOCOLNAME(protocolid), UTF8(payload), RULENAME(creeventlist) FROM events WHERE INOFFENSE(38783) LAST 18000 MINUTES
    2019-12-10 11:19:13,932 INFO [decorators] [qradar_search] StatusMessage: starting...
       



    Original Message


  • 4.  RE: Qradar search from offense ID

    Posted Wed December 11, 2019 07:23 AM
    The AQL query here looks fine:
    SELECT DATEFORMAT(starttime, 'YYYY-MM-dd HH:mm') as StartTime, DOMAINNAME(domainid), QIDNAME(qid), STR(sourceip), STR(destinationip), STR(sourceport), STR(destinationport), CATEGORYNAME(category), STR(magnitude), LOGSOURCENAME(logsourceid), PROTOCOLNAME(protocolid), UTF8(payload), RULENAME(creeventlist) FROM events WHERE INOFFENSE(38783) LAST 18000 MINUTES

    You might be running more than one query at the same time? Do you mind showing us the entire log file?

    Thanks,
    Yongjian

    ------------------------------
    Yongjian Feng
    ------------------------------

    Original Message


  • 5.  RE: Qradar search from offense ID

    Posted Wed December 11, 2019 08:43 AM
    Edited by System Admin Thu November 11, 2021 11:15 AM

    Sorry, we were executing more than one query... wasn't taking on account. Here is the whole log. 


    2019-12-11 13:04:08,961 INFO [actions_component] Event: <qradar_search[] (id=203, workflow=s21_qradar_events_search_using_offense_id, user=user@company.com) 2019-12-11 13:04:08.635000> Channel: functions.qradar_search

    2019-12-11 13:04:08,963 DEBUG [client] Received heart-beat

    2019-12-11 13:04:09,065 DEBUG [decorators] decorated

    2019-12-11 13:04:09,173 DEBUG [actions_component] Task: <function _call_the_task at 0x7fa940f16b18>

    2019-12-11 13:04:09,176 DEBUG [decorators] Thread-3: _call_the_task

    2019-12-11 13:04:09,178 INFO [qradar_search] qradar_query: SELECT %param1% FROM events WHERE INOFFENSE(%param2%) LAST %param3% MINUTES

    2019-12-11 13:04:09,179 INFO [qradar_search] qradar_query_param1: DATEFORMAT(starttime, 'YYYY-MM-dd HH:mm') as StartTime, DOMAINNAME(domainid), QIDNAME(qid), STR(sourceip), STR(destinationip), STR(sourceport), STR(destinationport), CATEGORYNAME(category), STR(magnitude), LOGSOURCENAME(logsourceid), PROTOCOLNAME(protocolid), UTF8(payload), RULENAME(creeventlist)

     

    2019-12-11 13:04:09,180 INFO [qradar_search] qradar_query_param2: 38783

    2019-12-11 13:04:09,180 INFO [qradar_search] qradar_query_param3: 18000

    2019-12-11 13:04:09,181 INFO [qradar_search] qradar_query_param4: None

    2019-12-11 13:04:09,182 INFO [qradar_search] qradar_query_param5: None

    2019-12-11 13:04:09,183 INFO [qradar_search] qradar_query_range_start: 0

    2019-12-11 13:04:09,183 INFO [qradar_search] qradar_query_range_end: 5

    2019-12-11 13:04:09,184 DEBUG [qradar_search] Connection to 172.31.4.206 using user

    2019-12-11 13:04:09,185 INFO [qradar_search] Running query: SELECT DATEFORMAT(starttime, 'YYYY-MM-dd HH:mm') as StartTime, DOMAINNAME(domainid), QIDNAME(qid), STR(sourceip), STR(destinationip), STR(sourceport), STR(destinationport), CATEGORYNAME(category), STR(magnitude), LOGSOURCENAME(logsourceid), PROTOCOLNAME(protocolid), UTF8(payload), RULENAME(creeventlist) FROM events WHERE INOFFENSE(38783) LAST 18000 MINUTES

    2019-12-11 13:04:09,186 INFO [decorators] [qradar_search] StatusMessage: starting...

     

     

    2019-12-11 13:05:44,030 ERROR [qradar_search] 'ascii' codec can't encode character u'\xf3' in position 894: ordinal not in range(128)

    2019-12-11 13:05:44,112 ERROR [actions_component] <task[functionworker] (<function _call_the_task at 0x7fa940f16b18>, <qradar_search[functions.qradar_search] (id=203,
    workflow=s21_qradar_events_search_using_offense_id, user=user@company.com) 2019-12-11 13:04:08.635000> qradar_query={u'content': u'SELECT %param1% FROM events WHERE INOFFENSE(%param2%) LAST %param3% MINUTES', u'format': u'text'}, qradar_query_range_end=5, qradar_query_range_start=0, qradar_query_param3=u'18000', qradar_query_param2=u'38783', qradar_query_param1=u"DATEFORMAT(starttime, 'YYYY-MM-dd HH:mm') as StartTime, DOMAINNAME(domainid), QIDNAME(qid), STR(sourceip), STR(destinationip), STR(sourceport), STR(destinationport), CATEGORYNAME(category), STR(magnitude), LOGSOURCENAME(logsourceid), PROTOCOLNAME(protocolid), UTF8(payload), RULENAME(creeventlist)")> (<class 'resilient_circuits.action_message.FunctionException_'>):

    Traceback (most recent call last):

    File "/usr/lib/python2.7/site-packages/fn_qradar_integration/components/qradar_search.py", line 83, in _qradar_search_function

    timeout=timeout)

    File "/usr/lib/python2.7/site-packages/fn_qradar_integration/util/qradar_utils.py", line 253, in ariel_search

    response = ariel_search.perform_search(query)

    File "/usr/lib/python2.7/site-packages/fn_qradar_integration/util/SearchWaitCommand.py", line 114, in perform_search

    result = self.get_search_result(search_id)

    File "/usr/lib/python2.7/site-packages/fn_qradar_integration/util/qradar_utils.py", line 165, in get_search_result

    events = function_utils.fix_dict_value(events)

    File "/usr/lib/python2.7/site-packages/fn_qradar_integration/util/function_utils.py", line 39, in fix_dict_value

    event[key] = str(event[key])

    UnicodeEncodeError: 'ascii' codec can't encode character u'\xf3' in position 894: ordinal not in range(128)




    Original Message


  • 6.  RE: Qradar search from offense ID

    Posted Wed December 11, 2019 10:10 AM
    This is something else. This time it looks like the function integration has trouble to handle some unicode or something.

    When the function was developed, we took a short cut. We need to display the events we get back from QRadar in a datatable, so we need to convert any non-string object in the event dictionary into str. The short cut we took has trouble to handle a non-string object that contains some unicode content.

    A jira ticket will be created to track this issue.

    Thanks,

    Yongjian

    ------------------------------
    Yongjian Feng
    ------------------------------

    Original Message


  • 7.  RE: Qradar search from offense ID

    Posted Wed December 11, 2019 05:02 PM
    @Yongjian Feng please see my DM.

    I have a significant revision of the integration for Resilient and the community that redoes how the search query works. Fixing this issue would be rather simple I believe, and I can add this into my​​​ revision. The app would need to be rebuilt by Resilient with updated examples, however.

    ------------------------------
    Jared Fagel
    Cyber Security Analyst Intern
    Public Utility
    ------------------------------

    Original Message


  • 8.  RE: Qradar search from offense ID

    Posted Thu December 12, 2019 08:44 AM
    Cool. I will take a look. Do you mind pasting a link here please?

    Yongjian

    ------------------------------
    Yongjian Feng
    ------------------------------

    Original Message


  • 9.  RE: Qradar search from offense ID

    Posted Tue January 07, 2020 07:27 AM
    Hi Jared
    Any update on this issue?
    Is there available a new version for the qradar_function?
    Best regards
    Oscar

    ------------------------------
    Oscar López
    ------------------------------

    Original Message


  • 10.  RE: Qradar search from offense ID

    Posted Tue January 07, 2020 05:13 PM
    Edited by Jared Fagel Tue January 07, 2020 05:13 PM
    Hi @Oscar López,

    Resilient pushed an update to the QRadar app today solving a multi-threading issue.

    I will be adding this into what I have, pushing to my GitHub, and updating back. I will have this done by early next week.

    Best,
    ------------------------------
    Jared Fagel
    Cyber Security Analyst I
    Public Utility
    ------------------------------

    Original Message


  • 11.  RE: Qradar search from offense ID

    Posted Tue January 07, 2020 05:26 PM
    @Oscar López,

    It did not fix the unicode issue, only an issue with threading (multi-instance execution).

    Best,
    Jared​

    ------------------------------
    Jared Fagel
    Cyber Security Analyst Intern
    Public Utility
    ------------------------------

    Original Message


  • 12.  RE: Qradar search from offense ID

    Posted Wed January 08, 2020 01:19 AM
    Hi Jared
    Thank you very much for this information.
    Best,
    Oscar

    ------------------------------
    Oscar López
    ------------------------------

    Original Message


  • 13.  RE: Qradar search from offense ID

    Posted Tue January 07, 2020 05:07 PM
    Hi Yongjian 

    An update on Qradar integration was released on 24 Dic 2019. Does it solve the issue of unicode?
    https://exchange.xforce.ibmcloud.com/hub/extension/a9bcc3eaebf2a6efc04258b4964a48a4

    I am not sure on the steps to follow to perform an upgrade of the app. 

    Best regards
    Oscr

    ------------------------------
    Oscar López
    ------------------------------

    Original Message


  • 14.  RE: Qradar search from offense ID

    Posted Wed January 08, 2020 02:37 AM
    For Oscar's original problem with Radar returning an HTTP 422 and the stack below this should be fixed by the latest version.

    Traceback (most recent call last):
    File "/usr/lib/python2.7/site-packages/fn_qradar_integration/components/qradar_search.py", line 83, in _qradar_search_function
    timeout=timeout)
    File "/usr/lib/python2.7/site-packages/fn_qradar_integration/util/qradar_utils.py", line 253, in ariel_search
    response = ariel_search.perform_search(query)
    File "/usr/lib/python2.7/site-packages/fn_qradar_integration/util/SearchWaitCommand.py", line 95, in perform_search
    raise SearchFailure(search_id, status)
    SearchFailure: Query [cab96e8a-0dd9-4ae3-ab85-ca00d83b79f8] failed with status [1]

    For the unicode problem there is a defect logged but as a work around I have sent you in the form of a case the following:

    cp /usr/lib/python2.7/site-packages/fn_qradar_integration/util/function_utils.py /tmp

    cd /usr/lib/python2.7/site-packages/fn_qradar_integration/util/

    vi function_utils.py

    Change from

    if not isinstance(value, str):    event[key] = str(value)

    Change to  if (not isinstance(value, str)) and (not isinstance(value,unicode)):    event[key] = str(value)

    Restart Resilient Circuits (sudo systemctl restart resilient_circuits) and test again.



    ------------------------------
    BEN WILLIAMS
    ------------------------------

    Original Message


  • 15.  RE: Qradar search from offense ID

    Posted Wed January 08, 2020 03:15 AM
    Hi Ben
    Which version do you recomend us to install
    24/12/2019 https://exchange.xforce.ibmcloud.com/hub/extension/a9bcc3eaebf2a6efc04258b4964a48a4

    (or the one in the github which is more recent? I am not sure how to install this one)

    I suposse just resinstall the new version should be ok
    sudo pip install <fn_qradar_integration version>.tar.gz

    Thanks again.
    Oscar
    Best

    ------------------------------
    Oscar López
    ------------------------------

    Original Message


  • 16.  RE: Qradar search from offense ID

    Posted Wed January 08, 2020 03:25 AM
    Hi Oscar,

    Please use the one from the App Exchange which will include documentation on how to install it. 

    As you have a support case open please use that as a way to communicate on this matter.

    ------------------------------
    BEN WILLIAMS
    ------------------------------

    Original Message


  • 17.  RE: Qradar search from offense ID

    Posted Wed January 08, 2020 03:36 AM
    Thanks again
    Best,
    Oscar

    ------------------------------
    Oscar López
    ------------------------------

    Original Message


  • 18.  RE: Qradar search from offense ID

    Posted Mon February 10, 2020 07:03 AM
    In the end we updated /usr/lib/python2.7/site-packages/fn_qradar_integration/util/function_utils.py as follows?

       for event in events:
       # event is a dict
       if isinstance(event, dict):
           for key in event:
               if not isinstance(event[key], str):
                   event[key] = u"{}".format(event[key])

   return events​


    ------------------------------
    BEN WILLIAMS
    ------------------------------

    Original Message


  • 19.  RE: Qradar search from offense ID

    Posted Mon February 10, 2020 10:12 AM
    Edited by Jared Fagel Mon February 10, 2020 10:14 AM
    Hey @Oscar López, I have uploaded a revised version of the app here, apologies for dropping the ball on this.

    I took a slightly different approach than @BEN WILLIAMS -- you'll see I used unicodedata.normalize() in that file, which may avoid issues if working with the values later on.

    The qradar_search function in my revision takes a few different params in the pre-processor including the actual AQL as a single string ("qradar_query"). Also, in addition to uploading a CSV of the events (or pushing it to a network share), it also returns them to the post-processor in a way that a Resilient data table could be built. There are a few other changes you will observe looking through the function code.

    I did not do a great job adding documentation/comments in, so let me know if you have questions.


    ------------------------------
    Jared Fagel
    Cyber Security Analyst I
    Public Utility
    ------------------------------

    Original Message


Related Content