topic Re: Extract date from the Splunk log in Getting Data In

Extract date from the Splunk log

karthi25 — Mon, 07 May 2018 09:18:38 GMT

I have splunk log which looks like below:

||pool-2-thread-1|| INFO  com.tmobile.sfdc.reports.batch.listener.OrderJobListener - ORDER_JOB: SUCCESSFULLY COMPLETED at END_TIME: 2018-05-07T06:05:17.475Z

||pool-2-thread-1|| INFO  com.tmobile.sfdc.reports.batch.listener.OpportunityJobListener - OPPORTUNITY_JOB: ACTIVE at START_TIME: 2018-05-07T06:04:44.981Z

||pool-2-thread-1|| INFO  com.tmobile.sfdc.reports.batch.listener.OrderJobListener - ORDER_JOB: SUCCESSFULLY COMPLETED at END_TIME: 2018-05-09T07:10:17.475Z

||pool-2-thread-1|| INFO  com.tmobile.sfdc.reports.batch.listener.OpportunityJobListener - OPPORTUNITY_JOB: ACTIVE at START_TIME: 2018-05-09T07:08:44.981Z

I want to get the start date and end date from the log. So, My output would be like:

START_DATE                                                          END_DATE
---------------------------------------------------------------------------------
2018-05-09T07:08:44.981Z                    2018-05-09T07:10:17.475Z
2018-05-07T06:04:44.981Z                    2018-05-07T06:05:17.475Z

I have tried the below query , but its return nothing:

base search| rex field=_raw "ACTIVE at START_TIME:\[(?[^ ]+)"| rex field=_raw "SUCCESSFULLY COMPLETED at END_TIME:\[(?[^ ]+)"|table START_DATE,END_DATE

can anyone please suggest me the solution and what am doing wrong here.

Re: Extract date from the Splunk log

FrankVl — Mon, 07 May 2018 09:27:35 GMT

Your regex seems wrong. What is that \[ doing in there? There is no [ in your log on that position. There is a space after the : though, which you are not matching.

So should be (also adding field names to the capture groups):

base search| rex field=_raw "ACTIVE at START_TIME:\s+(?<START_DATE>[^\s]+)"| rex field=_raw "SUCCESSFULLY COMPLETED at END_TIME:\s+(?<END_DATE>[^\s]+)"|table START_DATE,END_DATE

PS: are those start and end logs separate events? Then you might first need to combine these somehow, to actually get start and end date on a single line in your results.

Re: Extract date from the Splunk log

karthi25 — Mon, 07 May 2018 09:34:21 GMT

@FrankVI they are seperate events, I need to get it by order basis like the first occured startdate with first occured enddate , second occured startdate with second occured enddate,... and so on.Can you please suggest what else I can do?

Re: Extract date from the Splunk log

FrankVl — Mon, 07 May 2018 09:57:25 GMT

I guess there are a couple of approaches to combine data from 2 events into 1. The transaction command is one option (but perhaps not the best performing one), alternatives could be to use something like streamstats or autoregress.

Re: Extract date from the Splunk log

xpac — Mon, 07 May 2018 10:13:55 GMT

Just a hint: Use regex101.com. You can put it in your regex and example data, and any regex that works there (and extracts named capture groups) most likely also works in Splunk.
Entering your regex there would've shown you that you're missing the named capture groups, for example. 🙂