topic Re: How to create a regex that extracts date and time from the description field? in Splunk Search

How to create a regex that extracts date and time from the description field?

mayank101 — Wed, 07 Aug 2019 18:08:00 GMT

I have 1000 of text entities under the description field, and I want to write a regex for it and put to a different entity which I will call time
or eg :

 event         description
 a                 Message: Job failed at  Aug 4 2019 8:01AM with exit code 3 and has been set to success 
 b                 Message: Job failed at Aug 1 2019 8:01AM with exit code 7 and has been set to success
 c                  Message: Job failed at Aug  2019 8:01AM with exit code 2 and has been set to success 
And so on, many entries...

So I want regex that extracts date and time from the description field(eg Aug 4 2019 8:01AM ) and put it to a separate field called time.
Can anyone please help?

Re: How to create a regex that extracts date and time from the description field?

richgalloway — Wed, 07 Aug 2019 18:18:11 GMT

There probably are many ways to do this. Here's one you can use at search time.

... | rex "at\s+(?<time>.*)\swith" | ...

Re: How to create a regex that extracts date and time from the description field?

michael_schmidt — Wed, 07 Aug 2019 19:33:52 GMT

I'd do it a little more like this personally: rex field=_raw "(?:.+at\s+)(?<time>.*(AM|PM))"

Re: How to create a regex that extracts date and time from the description field?

prabhakar_ps — Wed, 07 Aug 2019 21:22:18 GMT

Try this if you want to have deep analysis based on year,month,date,time etc,

| rex field=_raw "at\s+(?<time>(?<month>\w+)\s(?<date>\d+)\s(?<year>\d+)\s(?<hour>\d+)\S(?<minutes>\d*)(?<clock_set>\w\w))\swith"

It will create time ,month,date,year,hour,minutes,clock_set fields

time as Aug 1 2019 8:01AM , month as Aug, date as 1 , year as 2019 and so on.. Thought this search is costly as it produces more fields, it can be used for analysis/reports etc..

Re: How to create a regex that extracts date and time from the description field?

mayank101 — Thu, 08 Aug 2019 16:02:30 GMT

I am getting error while running the regex:

       index="xxxxxx" 
        publisher="xxxx" entity="**boot*" 
| rex field=_raw "at\s+(?<time>(?<month>\w+)\s(?<date>\d+)\s(?<year>\d+)\s(?<hour>\d+)\S(?  <minutes>\d*)(?<clock_set>\w\w))\swith"
        event="FAIL-ALERT" 
        state="*"
        |search resource="*"
        |search entity="***"

       |table  state entity resource event description

Re: How to create a regex that extracts date and time from the description field?

prabhakar_ps — Thu, 08 Aug 2019 20:07:06 GMT

You do have space before minutes,remove those extra spaces.. it should work if your events are same..

Re: How to create a regex that extracts date and time from the description field?

mayank101 — Thu, 08 Aug 2019 20:13:24 GMT

Hi Prabhakar,
My events are different ,I have named them as a,b,c for example purpose :
event description
2. a Message: Job failed at Aug 4 2019 8:01AM with exit code 3 and has been set to success
3. b Message: Job failed at Aug 1 2019 8:01AM with exit code 7 and has been set to success
4. c Message: Job failed at Aug 2019 8:01AM with exit code 2 and has been set to success
5. And so on, many entries...

Re: How to create a regex that extracts date and time from the description field?

prabhakar_ps — Thu, 08 Aug 2019 20:34:16 GMT

Please do add "pipe and search" after rex command, like below

|search event="Fail-Alert" state="**"|table state entity resource event description minutes year month

you have started searching for event="Fail Alert" without any pipe and also it is good to have all search before first pipe itself ..