Topic
  • 20 replies
  • Latest Post - ‏2015-01-27T13:15:11Z by Taylor.Osmun (IBM)
PaulaFernandes
PaulaFernandes
9 Posts

Pinned topic Custom Columns

‏2014-10-21T12:01:06Z | aql query select
I'm trying to create an external tool that to create custom selects using QRadar Json API. As specified in documentation, I can do a ariel search using query_expression parameter, and for some queries I was able to do:
 
SELECT sourceIp from events 
SELECT * from events 
 
For that expressions, the API worked very well. I would like to create custom queries based on my customs columns, like "transaction (custom)" custom column (Used in the company that I work for. This column was create using regex expressions, and extract data from our custom syslog entries). Is there any way to do that using the QRadar API? I'm asking because we are able to do this kind of custom query using "log activity" page (in QRadar Web Console).
 
I would like to do something like this:
SELECT transaction (custom) from events

 

We are using QRadar 7.2.3.

 

Thanks

  • Taylor.Osmun (IBM)
    Taylor.Osmun (IBM)
    55 Posts
    ACCEPTED ANSWER

    Re: Custom Columns

    ‏2014-10-22T17:19:26Z  

    Taylor,

    Thanks for your reply.

    • Replace " " by "_"

    When I replaced " " with "_" it worked. 

    • Column names with special characters

    I thought I would not be able to do this kind of search directly... Even though, I can't understand why in the version of "web console" it works. You see, when I access the web console, the column is shown with the special characters (For example transação column name). I investigated the HTTP request send to the server while I use the GUI Interface, and saw that the special character is sent in the request in the "value(customColumns)" form field.

    In the example I'm using the "IdenticaUsuário" custom column with the á special character.

    1. value%28customColumns%29:
      qid%03ASSIGN%03true%02device%03ASSIGN%03true%02eventCount%03ASSIGN%03true%02startTime%03ASSIGN%03true%02category%03ASSIGN%03true%02sourceIP%03ASSIGN%03true%02sourcePort%03ASSIGN%03true%02destinationIP%03ASSIGN%03true%02destinationPort%03ASSIGN%03true%02userName%03ASSIGN%03true%02magnitude%03ASSIGN%03true%02identicausu%C3%A1rio%03ASSIGN%03true%02Event+Name%03Log+Source%03Event+Count%03Start+Time%03Category%03Source+IP%03Source+Port%03Destination+IP%03Destination+Port%03Username%03Magnitude%03IdenticaUsu%C3%A1rio+%28custom%29

    Some way, the programming language that is executing in the server side (I would guess java) is able to identify this special characters and using some technique execute in database or in some other kind of storage. So my question is:

    1. Is there a mapping table that make the "IdenticaUsuário" column another string to identify this column or
    2. You are using another encoding that can handle the special characters? 

    I could access this column using the column with the transformed value?

     

    The API layer uses AQL, which is the query expression language for our databases, while the User Interface layer (console) does not.

    The API layer correctly uses UTF-8, so the String is passed to the grammar correctly; you can verify this by the error message returned, it should contain the query expression. The gap experienced here are limitations of AQL, specifically the grammar. The grammar used does not recognize unicode characters, and so can not properly parse the names.

    1. Yes there is a mapping. Then name of your property is tied to an ID but there is no way to pass the ID into the query instead of the property name.

    2. The encoding is indeed UTF-8, but the parser which reads the encoded String does not recognize unicode characters.

    - Taylor

  • PaulaFernandes
    PaulaFernandes
    9 Posts

    Re: Custom Columns

    ‏2014-10-21T13:01:14Z  

    I'm having problem with columns named with special characters, specially "Á", "ç". I have tried to use UTF-8 encoding, but it didn't work.

  • KateM (IBM)
    KateM (IBM)
    43 Posts

    Re: Custom Columns

    ‏2014-10-21T18:57:37Z  

    Hi PaulaFernandes,

    I will take a look at this and get back to you.

    Thanks,

    Kate

  • PaulaFernandes
    PaulaFernandes
    9 Posts

    Re: Custom Columns

    ‏2014-10-22T12:39:52Z  

    Hi PaulaFernandes,

    I will take a look at this and get back to you.

    Thanks,

    Kate

    Kate,

    I was able to understand what I was doing wrong. The problem it was that I was using " instead of ' to write my select statement.

    Wrong way:
    select "transação" from events (Using UTF-8 encoding)

    Rigth way:
    select 'transação' from events (Using UTF-8 encoding)

  • PaulaFernandes
    PaulaFernandes
    9 Posts

    Re: Custom Columns

    ‏2014-10-22T13:07:52Z  

    Hi PaulaFernandes,

    I will take a look at this and get back to you.

    Thanks,

    Kate

    Using special characteres, the query is executed by the results show me some problems.

    if I execute the following query using API the result is not exactly correct:

    query:  
    select sourceIp, 
    'transação' from events (Using UTF-8 encoding)

    return for example:
    {{"sourceIp":"10.0.0.1", "transação":"transação"},{"sourceIp":"10.0.0.2", "transação":"transação"}}

    By analysing the result, it's obviously that what is going on is that "transação" is being transformed in String, not in the column name.

    When a use QRadar Web Console, the events are shown correctly.

    Event 1: Source IP = 10.0.0.1, Transação = DEBIT
    Event 2: Source IP = 10.0.0.2, Transação = TRASFER

    How can I use the API to return the correct result?

     

  • Taylor.Osmun (IBM)
    Taylor.Osmun (IBM)
    55 Posts

    Re: Custom Columns

    ‏2014-10-22T14:25:06Z  

    Kate,

    I was able to understand what I was doing wrong. The problem it was that I was using " instead of ' to write my select statement.

    Wrong way:
    select "transação" from events (Using UTF-8 encoding)

    Rigth way:
    select 'transação' from events (Using UTF-8 encoding)

    Hi Paula,

    Using single quotes (' ') will cause the value to become a String literal. For example:

    select 'test' from events

    ->

    {
      "events": [
        {
          "test": "test"
        },
        {
          "test": "test"

        },

        ...

    }

    This is likely not your desired query.

     

    There are currently issues with special characters such as ç. Some special characters will cause your query to fail, and there is no suitable workaround at the moment. It is advisable that you use basic ASCII characters only for the time being.

    Additionally, '(' and ')' are special characters which represent functions, and can not be used.

    Finally, spaces may be used in your property names, but must be replaced with '_' when performing queries. So "transaction custom" becomes "transaction_custom".

     

    We are taking all of these issues into account and will be looking at resolving them in a future release.

    - Taylor

     

  • PaulaFernandes
    PaulaFernandes
    9 Posts

    Re: Custom Columns

    ‏2014-10-22T15:46:42Z  

    Hi Paula,

    Using single quotes (' ') will cause the value to become a String literal. For example:

    select 'test' from events

    ->

    {
      "events": [
        {
          "test": "test"
        },
        {
          "test": "test"

        },

        ...

    }

    This is likely not your desired query.

     

    There are currently issues with special characters such as ç. Some special characters will cause your query to fail, and there is no suitable workaround at the moment. It is advisable that you use basic ASCII characters only for the time being.

    Additionally, '(' and ')' are special characters which represent functions, and can not be used.

    Finally, spaces may be used in your property names, but must be replaced with '_' when performing queries. So "transaction custom" becomes "transaction_custom".

     

    We are taking all of these issues into account and will be looking at resolving them in a future release.

    - Taylor

     

    Taylor,

    Thanks for your reply.

    • Replace " " by "_"

    When I replaced " " with "_" it worked. 

    • Column names with special characters

    I thought I would not be able to do this kind of search directly... Even though, I can't understand why in the version of "web console" it works. You see, when I access the web console, the column is shown with the special characters (For example transação column name). I investigated the HTTP request send to the server while I use the GUI Interface, and saw that the special character is sent in the request in the "value(customColumns)" form field.

    In the example I'm using the "IdenticaUsuário" custom column with the á special character.

    1. value%28customColumns%29:
      qid%03ASSIGN%03true%02device%03ASSIGN%03true%02eventCount%03ASSIGN%03true%02startTime%03ASSIGN%03true%02category%03ASSIGN%03true%02sourceIP%03ASSIGN%03true%02sourcePort%03ASSIGN%03true%02destinationIP%03ASSIGN%03true%02destinationPort%03ASSIGN%03true%02userName%03ASSIGN%03true%02magnitude%03ASSIGN%03true%02identicausu%C3%A1rio%03ASSIGN%03true%02Event+Name%03Log+Source%03Event+Count%03Start+Time%03Category%03Source+IP%03Source+Port%03Destination+IP%03Destination+Port%03Username%03Magnitude%03IdenticaUsu%C3%A1rio+%28custom%29

    Some way, the programming language that is executing in the server side (I would guess java) is able to identify this special characters and using some technique execute in database or in some other kind of storage. So my question is:

    1. Is there a mapping table that make the "IdenticaUsuário" column another string to identify this column or
    2. You are using another encoding that can handle the special characters? 

    I could access this column using the column with the transformed value?

     

  • Taylor.Osmun (IBM)
    Taylor.Osmun (IBM)
    55 Posts

    Re: Custom Columns

    ‏2014-10-22T17:19:26Z  

    Taylor,

    Thanks for your reply.

    • Replace " " by "_"

    When I replaced " " with "_" it worked. 

    • Column names with special characters

    I thought I would not be able to do this kind of search directly... Even though, I can't understand why in the version of "web console" it works. You see, when I access the web console, the column is shown with the special characters (For example transação column name). I investigated the HTTP request send to the server while I use the GUI Interface, and saw that the special character is sent in the request in the "value(customColumns)" form field.

    In the example I'm using the "IdenticaUsuário" custom column with the á special character.

    1. value%28customColumns%29:
      qid%03ASSIGN%03true%02device%03ASSIGN%03true%02eventCount%03ASSIGN%03true%02startTime%03ASSIGN%03true%02category%03ASSIGN%03true%02sourceIP%03ASSIGN%03true%02sourcePort%03ASSIGN%03true%02destinationIP%03ASSIGN%03true%02destinationPort%03ASSIGN%03true%02userName%03ASSIGN%03true%02magnitude%03ASSIGN%03true%02identicausu%C3%A1rio%03ASSIGN%03true%02Event+Name%03Log+Source%03Event+Count%03Start+Time%03Category%03Source+IP%03Source+Port%03Destination+IP%03Destination+Port%03Username%03Magnitude%03IdenticaUsu%C3%A1rio+%28custom%29

    Some way, the programming language that is executing in the server side (I would guess java) is able to identify this special characters and using some technique execute in database or in some other kind of storage. So my question is:

    1. Is there a mapping table that make the "IdenticaUsuário" column another string to identify this column or
    2. You are using another encoding that can handle the special characters? 

    I could access this column using the column with the transformed value?

     

    The API layer uses AQL, which is the query expression language for our databases, while the User Interface layer (console) does not.

    The API layer correctly uses UTF-8, so the String is passed to the grammar correctly; you can verify this by the error message returned, it should contain the query expression. The gap experienced here are limitations of AQL, specifically the grammar. The grammar used does not recognize unicode characters, and so can not properly parse the names.

    1. Yes there is a mapping. Then name of your property is tied to an ID but there is no way to pass the ID into the query instead of the property name.

    2. The encoding is indeed UTF-8, but the parser which reads the encoded String does not recognize unicode characters.

    - Taylor

  • Jason Keirstead (IBM)
    16 Posts

    Re: Custom Columns

    ‏2014-12-02T14:18:23Z  

    The API layer uses AQL, which is the query expression language for our databases, while the User Interface layer (console) does not.

    The API layer correctly uses UTF-8, so the String is passed to the grammar correctly; you can verify this by the error message returned, it should contain the query expression. The gap experienced here are limitations of AQL, specifically the grammar. The grammar used does not recognize unicode characters, and so can not properly parse the names.

    1. Yes there is a mapping. Then name of your property is tied to an ID but there is no way to pass the ID into the query instead of the property name.

    2. The encoding is indeed UTF-8, but the parser which reads the encoded String does not recognize unicode characters.

    - Taylor

    Just to follow up on Taylors response... in case it wasn't clear, this is an issue that should be raised as a PMR for tracking.

  • Rahul0807
    Rahul0807
    4 Posts

    Re: Custom Columns

    ‏2015-01-20T07:37:59Z  

    Using special characteres, the query is executed by the results show me some problems.

    if I execute the following query using API the result is not exactly correct:

    query:  
    select sourceIp, 
    'transação' from events (Using UTF-8 encoding)

    return for example:
    {{"sourceIp":"10.0.0.1", "transação":"transação"},{"sourceIp":"10.0.0.2", "transação":"transação"}}

    By analysing the result, it's obviously that what is going on is that "transação" is being transformed in String, not in the column name.

    When a use QRadar Web Console, the events are shown correctly.

    Event 1: Source IP = 10.0.0.1, Transação = DEBIT
    Event 2: Source IP = 10.0.0.2, Transação = TRASFER

    How can I use the API to return the correct result?

     

    Hi All,

     

    I am having the same difficulty in getting the AQL query output using a custom property.Sample query below.

     

    Here is what I am doing:

    /opt/qradar/bin/arielClient -start 2015:01:20-10:20:00 -end 2015:01:20-10:40:00 -f csv -execute "select userName,"Target_Account",category,sourceIP,destinationIP,device,sum(eventCount)from events where device=1962 and category=3034 group by userName"> /tmp/Password_changed.csv

     

    Target Account is the custom property.

    It's not working based on above responses.

     

  • PaulaFernandes
    PaulaFernandes
    9 Posts

    Re: Custom Columns

    ‏2015-01-20T15:40:13Z  
    • Rahul0807
    • ‏2015-01-20T07:37:59Z

    Hi All,

     

    I am having the same difficulty in getting the AQL query output using a custom property.Sample query below.

     

    Here is what I am doing:

    /opt/qradar/bin/arielClient -start 2015:01:20-10:20:00 -end 2015:01:20-10:40:00 -f csv -execute "select userName,"Target_Account",category,sourceIP,destinationIP,device,sum(eventCount)from events where device=1962 and category=3034 group by userName"> /tmp/Password_changed.csv

     

    Target Account is the custom property.

    It's not working based on above responses.

     

    Hello,

     

    Try to use "Target_Account" without the double quotes. That is:

     

    /opt/qradar/bin/arielClient -start 2015:01:20-10:20:00 -end 2015:01:20-10:40:00 -f csv -execute "select userName,Target_Account,category,sourceIP,destinationIP,device,sum(eventCount)from events where device=1962 and category=3034 group by userName" > /tmp/Password_changed.csv

     

    If you have a space character between the columns name, you should replace this character by "_" (underscore) (Just like you are doing)

    The problem is that when you use double quotes, you are transforming this column name in a simple string.

     

  • Taylor.Osmun (IBM)
    Taylor.Osmun (IBM)
    55 Posts

    Re: Custom Columns

    ‏2015-01-20T16:19:05Z  

    Hello,

     

    Try to use "Target_Account" without the double quotes. That is:

     

    /opt/qradar/bin/arielClient -start 2015:01:20-10:20:00 -end 2015:01:20-10:40:00 -f csv -execute "select userName,Target_Account,category,sourceIP,destinationIP,device,sum(eventCount)from events where device=1962 and category=3034 group by userName" > /tmp/Password_changed.csv

     

    If you have a space character between the columns name, you should replace this character by "_" (underscore) (Just like you are doing)

    The problem is that when you use double quotes, you are transforming this column name in a simple string.

     

    Hi Paula,

    Thanks for the response. Indeed, by using "Target_Account" instead of Target_Account, it is treated as a String literal, not a column name. However, I do not believe that is the case with Rahul's query, as bash will not include the double quotes in that particular command.

    - Taylor

     

  • Taylor.Osmun (IBM)
    Taylor.Osmun (IBM)
    55 Posts

    Re: Custom Columns

    ‏2015-01-20T16:23:42Z  
    • Rahul0807
    • ‏2015-01-20T07:37:59Z

    Hi All,

     

    I am having the same difficulty in getting the AQL query output using a custom property.Sample query below.

     

    Here is what I am doing:

    /opt/qradar/bin/arielClient -start 2015:01:20-10:20:00 -end 2015:01:20-10:40:00 -f csv -execute "select userName,"Target_Account",category,sourceIP,destinationIP,device,sum(eventCount)from events where device=1962 and category=3034 group by userName"> /tmp/Password_changed.csv

     

    Target Account is the custom property.

    It's not working based on above responses.

     

    Hi Rahul,

    I believe you are missing a space here: sum(eventCount)from events
    It should be: sum(eventCount) from events

    Additionally, the quotes around Target_Account will not persist to the query, as bash will not treat them as such. If you wanted to use quotes, it would need to be constructed like so:

    /opt/qradar/bin/arielClient -start 2015:01:20-10:20:00 -end 2015:01:20-10:40:00 -f csv -execute "select userName,"'"'Target_Account'"'",category,sourceIP,destinationIP,device,sum(eventCount) from events where device=1962 and category=3034 group by userName"> /tmp/Password_changed.csv

    However, like Paula said, this will not have the desired affect. It will instead treat "Target_Account" as a String, not a pointer to the column Target_Account. This is being looked into and we hope to have it fixed in a future release. The likely solution would be to use single quotes to allow for special characters in column names, similar to how many common databases work.

    - Taylor

  • PaulaFernandes
    PaulaFernandes
    9 Posts

    Re: Custom Columns

    ‏2015-01-20T16:28:43Z  

    Hi Paula,

    Thanks for the response. Indeed, by using "Target_Account" instead of Target_Account, it is treated as a String literal, not a column name. However, I do not believe that is the case with Rahul's query, as bash will not include the double quotes in that particular command.

    - Taylor

     

    Yeah, that is not exacly the case.

    In this particular example, the quote between the Target_Account is ending the -execute next parameter. But, if he tries to use the ', this column name is going to be interpreted as string.

    That is:

    /opt/qradar/bin/arielClient -start 2015:01:20-10:20:00 -end 2015:01:20-10:40:00 -f csv -execute "select userName,"  Target_Account" (Target_Account is not expected),category,sourceIP,destinationIP,device,sum(eventCount)from events where device=1962 and category=3034 group by userName"> /tmp/Password_changed.csv

     

  • Rahul0807
    Rahul0807
    4 Posts

    Re: Custom Columns

    ‏2015-01-21T10:31:47Z  

    Hi Rahul,

    I believe you are missing a space here: sum(eventCount)from events
    It should be: sum(eventCount) from events

    Additionally, the quotes around Target_Account will not persist to the query, as bash will not treat them as such. If you wanted to use quotes, it would need to be constructed like so:

    /opt/qradar/bin/arielClient -start 2015:01:20-10:20:00 -end 2015:01:20-10:40:00 -f csv -execute "select userName,"'"'Target_Account'"'",category,sourceIP,destinationIP,device,sum(eventCount) from events where device=1962 and category=3034 group by userName"> /tmp/Password_changed.csv

    However, like Paula said, this will not have the desired affect. It will instead treat "Target_Account" as a String, not a pointer to the column Target_Account. This is being looked into and we hope to have it fixed in a future release. The likely solution would be to use single quotes to allow for special characters in column names, similar to how many common databases work.

    - Taylor

    Hi All,

     

    Thanks Paula & Taylor for the responses.

    However, the issue remains the same, If I try the query with double quotes like: "_", it says the below message.

     

    Unable to execute query: com.q1labs.core.shared.ariel.ArielUtils$UnknownPropertyException: No property 'Target_Account' exists in set:

     

    And If I try with the single quotes, the query gets executed but it takes the field as string & does not treat it as column to give the required value.

    One more thing I want to ask is if at all AQL supports the custom fields to be run in the query or not.

     

    Looking forward to get this fixed in the next release.

    Thanks

    - Rahul

  • sree_ibm
    sree_ibm
    21 Posts

    Re: Custom Columns

    ‏2015-01-21T13:47:00Z  
    • Rahul0807
    • ‏2015-01-21T10:31:47Z

    Hi All,

     

    Thanks Paula & Taylor for the responses.

    However, the issue remains the same, If I try the query with double quotes like: "_", it says the below message.

     

    Unable to execute query: com.q1labs.core.shared.ariel.ArielUtils$UnknownPropertyException: No property 'Target_Account' exists in set:

     

    And If I try with the single quotes, the query gets executed but it takes the field as string & does not treat it as column to give the required value.

    One more thing I want to ask is if at all AQL supports the custom fields to be run in the query or not.

     

    Looking forward to get this fixed in the next release.

    Thanks

    - Rahul

    Hi Rahul,

    Just to double check, Is the property 'Target_Account' defined in the system? If so is the casing correct?? Custom Properties are case sensitive.

    Regards,

    Sree

  • Taylor.Osmun (IBM)
    Taylor.Osmun (IBM)
    55 Posts

    Re: Custom Columns

    ‏2015-01-21T15:55:37Z  
    • Rahul0807
    • ‏2015-01-21T10:31:47Z

    Hi All,

     

    Thanks Paula & Taylor for the responses.

    However, the issue remains the same, If I try the query with double quotes like: "_", it says the below message.

     

    Unable to execute query: com.q1labs.core.shared.ariel.ArielUtils$UnknownPropertyException: No property 'Target_Account' exists in set:

     

    And If I try with the single quotes, the query gets executed but it takes the field as string & does not treat it as column to give the required value.

    One more thing I want to ask is if at all AQL supports the custom fields to be run in the query or not.

     

    Looking forward to get this fixed in the next release.

    Thanks

    - Rahul

    Hi Rahul,

    Yes, custom properties can be used in AQL queries.

    The behaviour with 'arielClient' is different from that of the API. We are deprecating arielClient and the suggested interaction will be with the restful API.

    With arielClient, it will only accept the lowercase version of your custom properties, but does not require the '_' in place of whitespace. Instead double quotes can be used.

    /opt/qradar/bin/arielClient -start 2015:01:20-10:20:00 -end 2015:01:20-10:40:00 -f csv -execute 'select userName,"target account",category,sourceIP,destinationIP,device,sum(eventCount) from events where device=1962 and category=3034 group by userName' > /tmp/Password_changed.csv

    Again, this is for arielClient only.

    - Taylor

  • Rahul0807
    Rahul0807
    4 Posts

    Re: Custom Columns

    ‏2015-01-22T07:47:40Z  

    Hi Rahul,

    Yes, custom properties can be used in AQL queries.

    The behaviour with 'arielClient' is different from that of the API. We are deprecating arielClient and the suggested interaction will be with the restful API.

    With arielClient, it will only accept the lowercase version of your custom properties, but does not require the '_' in place of whitespace. Instead double quotes can be used.

    /opt/qradar/bin/arielClient -start 2015:01:20-10:20:00 -end 2015:01:20-10:40:00 -f csv -execute 'select userName,"target account",category,sourceIP,destinationIP,device,sum(eventCount) from events where device=1962 and category=3034 group by userName' > /tmp/Password_changed.csv

    Again, this is for arielClient only.

    - Taylor

    Hi Taylor,

     

    Thanks so much for the solution.

    Now I am stuck with other thing of converting the unix timestamp into a standard date format. I have used the DATEFORMAT function in the query to convert the date but not getting the desired output.

     

    Please advice.

     

    Thanks

    -Rahul

  • Taylor.Osmun (IBM)
    Taylor.Osmun (IBM)
    55 Posts

    Re: Custom Columns

    ‏2015-01-22T15:34:39Z  
    • Rahul0807
    • ‏2015-01-22T07:47:40Z

    Hi Taylor,

     

    Thanks so much for the solution.

    Now I am stuck with other thing of converting the unix timestamp into a standard date format. I have used the DATEFORMAT function in the query to convert the date but not getting the desired output.

     

    Please advice.

     

    Thanks

    -Rahul

    Hi Rahul,

    Unfortunately, dateformat is one of the functions that is not supported by arielClient. You will need to use the restful API for this purpose. I would recommend doing this regardless however, since arielClient will be removed some time in the future.

    dateformat takes two arguments. The first is a number which represents the epoch (milliseconds since Jan 1st 1970). The second is the date format string; since this implemented in Java, you should reference Java's date format: http://docs.oracle.com/javase/6/docs/api/java/text/SimpleDateFormat.html

    Example:

    curl -X POST -k -H 'SEC:myToken' -d "query_expression=select dateformat(startTime,'yyyy/MM/dd HH:mm') from events" https://localhost/restapi/api/ariel/searches

    curl -X GET -k -H 'SEC:myToken' https://localhost/restapi/api/ariel/searches/123456/results

    If you wish to continue to use csv, you can specify your desired MIME type via the Accept header:

    curl -X GET -k -H 'SEC:myToken' https://localhost/restapi/api/ariel/searches/123456/results -H accept:application/csv

    -Taylor

  • Rahul0807
    Rahul0807
    4 Posts

    Re: Custom Columns

    ‏2015-01-27T11:16:36Z  

    Hi Rahul,

    Unfortunately, dateformat is one of the functions that is not supported by arielClient. You will need to use the restful API for this purpose. I would recommend doing this regardless however, since arielClient will be removed some time in the future.

    dateformat takes two arguments. The first is a number which represents the epoch (milliseconds since Jan 1st 1970). The second is the date format string; since this implemented in Java, you should reference Java's date format: http://docs.oracle.com/javase/6/docs/api/java/text/SimpleDateFormat.html

    Example:

    curl -X POST -k -H 'SEC:myToken' -d "query_expression=select dateformat(startTime,'yyyy/MM/dd HH:mm') from events" https://localhost/restapi/api/ariel/searches

    curl -X GET -k -H 'SEC:myToken' https://localhost/restapi/api/ariel/searches/123456/results

    If you wish to continue to use csv, you can specify your desired MIME type via the Accept header:

    curl -X GET -k -H 'SEC:myToken' https://localhost/restapi/api/ariel/searches/123456/results -H accept:application/csv

    -Taylor

    Hi Taylor,

     

    Thanks so much for redirecting me. I tried using the REST api through. I ran a simple query select * from events in the query_expression box

    https://consoleipaddress/restapi/doc#!/

    I tried running the ariel query there & the response code shows 201.

    But the response body shows status as wait & no results came out.

     

    Please help.

  • Taylor.Osmun (IBM)
    Taylor.Osmun (IBM)
    55 Posts

    Re: Custom Columns

    ‏2015-01-27T13:15:11Z  
    • Rahul0807
    • ‏2015-01-27T11:16:36Z

    Hi Taylor,

     

    Thanks so much for redirecting me. I tried using the REST api through. I ran a simple query select * from events in the query_expression box

    https://consoleipaddress/restapi/doc#!/

    I tried running the ariel query there & the response code shows 201.

    But the response body shows status as wait & no results came out.

     

    Please help.

    Hi Rahul,

    As the documentation for POST /ariel/searches states:
    "Searches are executed asynchronously. A reference to the search_id is returned and should be used in subsequent API calls to determine the status of the search and retrieve the results once it is complete."

    You can then use the search ID here to see the status of your search:
    GET /ariel/searches/{search_id}

    And when the query is marked as completed, fetch the results here:
    GET /ariel/searches/{search_id}/results

    - Taylor