Splunk Search

How do I place two time values from two seperate logs as distinct variables per field name that they share?

Toshbar
Explorer

Let's say I have a search query that pulls up multiple logs and there are two logs for each JOBNAME. one that contains a started time and one that contains an ended time. I want to pull up numerous JOBNAME logs then make a table that contains the started and ended times as unique columns on a single row per jobname

Example:

Log 1

 DATETIME:   2017-08-21 22:26:45.92 -0700   
 JOBNAME:    CIBF593D   
 MSGTXT:     CIBF593D - ENDED - TIME=22.26.45   

Log 2

 DATETIME:   2017-08-21 22:26:42.02 -0700   
 JOBNAME:    CIBF593D   
 MSGTXT:     CIBF593D - STARTED - TIME=22.26.42 

I know I can do:
| stats values(DATETIME) by JOBNAME but that will give me both times on a single column.
What I want is something like below:

JOBNAME         Start Time          End Time
CIBF593D        2017-08-21 22:26:42.02 -0700    2017-08-21 22:56:45.92 -0700

Should I do a search for messages that contain start time, grab the DATETIME from it and rename it to a Start Time variable, do another search doing the same for end time and append the results together?
Is there a way to do an if/else statement where i say "if MSGTXT has started time, then grab DATETIME and rename it as started time / else grab DATETIME and rename it as ended time" per JOBNAME?

I'm just not sure which way is the best way to go about this

0 Karma
1 Solution

woodcock
Esteemed Legend

Like this:

| makeresults 
| eval raw="DATETIME:     2017-08-21 22:26:45.92 -0700
JOBNAME:     CIBF593D
MSGTXT:     CIBF593D - ENDED - TIME=22.26.45::DATETIME:     2017-08-21 22:26:42.02 -0700
JOBNAME:     CIBF593D
MSGTXT:     CIBF593D - STARTED - TIME=22.26.42" 
| makemv raw delim="::" 
| mvexpand raw 
| rename raw AS _raw

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| rex "(?ms)DATETIME:\s+(?<_time>[^\r\n]+).*?JOBNAME:\s+(?<JOBNAME>\S+).*?MSGTXT:\s+(?<MSGTXT>.*)"
| eval _time=strptime(_time, "%Y-%m-%d %H:%M:%S.%2N %Z")
| eval startTime=if(match(MSGTXT, "- STARTED -"), _time, null())
| eval endTime=if(match(MSGTXT, "- ENDED -"), _time, null())
| stats earliest(startTime) AS startTime latest(endTime) AS endTime by JOBNAME
| convert ctime(*Time)

View solution in original post

0 Karma

woodcock
Esteemed Legend

Like this:

| makeresults 
| eval raw="DATETIME:     2017-08-21 22:26:45.92 -0700
JOBNAME:     CIBF593D
MSGTXT:     CIBF593D - ENDED - TIME=22.26.45::DATETIME:     2017-08-21 22:26:42.02 -0700
JOBNAME:     CIBF593D
MSGTXT:     CIBF593D - STARTED - TIME=22.26.42" 
| makemv raw delim="::" 
| mvexpand raw 
| rename raw AS _raw

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| rex "(?ms)DATETIME:\s+(?<_time>[^\r\n]+).*?JOBNAME:\s+(?<JOBNAME>\S+).*?MSGTXT:\s+(?<MSGTXT>.*)"
| eval _time=strptime(_time, "%Y-%m-%d %H:%M:%S.%2N %Z")
| eval startTime=if(match(MSGTXT, "- STARTED -"), _time, null())
| eval endTime=if(match(MSGTXT, "- ENDED -"), _time, null())
| stats earliest(startTime) AS startTime latest(endTime) AS endTime by JOBNAME
| convert ctime(*Time)
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...