how to write serach query to pull details changed in last 1 or 2 days

Follow
Avatar
Katyayani Danavulapati

let us take a simple query in json file

{
    "query": "| count _sourceCategory",
    "from": "2013-01-28T12:00:00",
    "to": "2013-01-28T13:10:00",
    "timeZone": "PST"

}

 

I want to make the "from" and "to" dynamic . Eg data from last 24 hours or 48 hours as relative insted of static date and time. Can you please point me to documentation that talks about these parameters ?

 
0

Comments

11 comments

Sort by Date Votes
  • Official comment
    Avatar
    Graham

    Hi Katyayani,

    We don't currently support relative times via the search job API - here is a feature request that you can vote and comment on to ask for this to be supported. You can write a script to adjust the "from" and "to" values to be from 24 hours ago/48 hours ago to the current time.

    Thanks,

    Graham

    Comment actions Permalink
  • Avatar
    Nick

    Hi Katyayani,

    I am curious though, from where are you calling the API? Would it be possible for you to generate those value dynamically in your script?

    Thanks,

    Nick

    0
    Comment actions Permalink
  • Avatar
    Katyayani Danavulapati

    can you point me to documentation where it talks about different formatts of time stamps I can use with in sumologic ? I remember seeing it however I could not find it anymore now

    0
    Comment actions Permalink
  • Avatar
    Katyayani Danavulapati

    my case query is static but I would need to run the query everyday and gather the details past 24 hours only. so I created a .json file and would like to make it "-24hours" like in UI. however looks like it is not possible and i would need to update the ,json everytime with "from" and "to" before calling this .json file with in search API

    0
    Comment actions Permalink
  • Avatar
    Nick

    Hi Katyayani,

    I believe this article is what you're looking for: https://help.sumologic.com/Send-Data/Sources/04Reference-Information-for-Sources/Timestamps%2C-Time-Zones%2C-Time-Ranges%2C-and-Date-Formats

    Let me know if that helps. 

    0
    Comment actions Permalink
  • Avatar
    Nick

    Also, that is correct, you would have to update the query before calling the API, since as Graham mentioned, the API does not accept relative dates. But you could potentially automate that piece as well on your end with whatever scripting language you're using.

    0
    Comment actions Permalink
  • Avatar
    Katyayani Danavulapati

    Thanks Nick for the article. Yes that was the one I saw earlier and thought I even saw relative timestamps. Like you said I will take current date and time and automate the .json parameters

    0
    Comment actions Permalink
  • Avatar
    Katyayani Danavulapati

    what other timestamps are allowed with in json file ? I tried

    MMM dd yyyy HH:mm:ss

    and it reported error

    Search job created, id: { "status" : 400, "id" : "3XLIO-JOUD7-CTUOQ", "code" : "searchjob.invalid.timestamp.from", "message" : "The 'from' field (Sep 25 2017 09:02:03) cont
    ains an invalid time." }

    0
    Comment actions Permalink
  • Avatar
    Nick

    Hi Katyayani - Could you please post your JSON that generated this error?

    0
    Comment actions Permalink
  • Avatar
    Katyayani Danavulapati

    json

    {
     "query": "(_sourceName=*TC* OR _sourceName=*ET5* )and (_sourceName=*.txt) \n| parse regex \"(?<date_stamp>\\S+)\\s+(?<time_stamp>\\d{1,2}:\\d{2}:\\d{2}.\\d{3})\\s+(?<pid>\\S+)\\s+(?<tid>\\S+)\\s+(?<tag>\\S+)\\s+(?<application>\\S+)\\s+(?<message>.+)$\"\n| if ((message matches \"*Fail*\") or (message matches \"*fail*\") ,1,0) as fail\n| sum(fail) as FAIL \n| format(\"%.1f Failures\", FAIL) as errorString | fields errorString",
     "from": "2017-09-03T22:00:00",
     "to": "Sep 25 2017 09:02:03",
     "timeZone": "EST"
    }

     

    response

     

    id: { "status" : 400, "id" : "IORBE-UGXBI-VVO7S", "code" : "searchjob.invalid.timestamp.to", "message" : "The 'to' field (Sep 25 2017 09:02:03) contains
     an invalid time." }

    0
    Comment actions Permalink
  • Avatar
    Nick

    Katyayani,

    I see - I'm sorry for the confusion, but there are separate requirements for the timestamp format for the Search API than what is listed in the article I gave you. The "from" and "to" parameters for the API call need to be formatted according to ISO 8601 format: https://help.sumologic.com/APIs/Search-Job-API/About-the-Search-Job-API#Query_parameters

    Here are some other resources that may be helpful:

    http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a003169814.htm
    https://coderstoolbox.net/unixtimestamp/

    In your POST body, you have the "from" parameter set to "2017-09-03T22:00:00" which is a valid ISO 8601 format. However, your "to" parameter is "Sep 25 2017 09:02:03" which is not valid. Try "2017-09-25T09:02:03" for "to" instead.

    {
     "query": "(_sourceName=*TC* OR _sourceName=*ET5* )and (_sourceName=*.txt) \n| parse regex \"(?<date_stamp>\\S+)\\s+(?<time_stamp>\\d{1,2}:\\d{2}:\\d{2}.\\d{3})\\s+(?<pid>\\S+)\\s+(?<tid>\\S+)\\s+(?<tag>\\S+)\\s+(?<application>\\S+)\\s+(?<message>.+)$\"\n| if ((message matches \"*Fail*\") or (message matches \"*fail*\") ,1,0) as fail\n| sum(fail) as FAIL \n| format(\"%.1f Failures\", FAIL) as errorString | fields errorString",
     "from": "2017-09-03T22:00:00",
     "to": "2017-09-25T09:02:03",
     "timeZone": "EST"
    }

    Thanks,
    Nick Wilson

    0
    Comment actions Permalink

Please sign in to leave a comment.

Didn't find what you were looking for?

New post