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

[Toshbar]

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

[woodcock · Accepted solution]

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)