How to edit my search to combine multiple rows into one output row in my results?

[szimmer661]

I am taking numerous log entries and trying to produce an output report that shows the earliest logon time and the latest logoff or disconnect time by day and user. I've got the earliest logon time and latest logoff or disconnect time part working, but the output comes out as a row per event type. I've seen answers suggesting the use of stats or chart to display the results, but when I try that, I get one row for each date and userid combination, but no times are shown.

Any help is greatly appreciated. My search is below:

index = ironstream (MSGNUM=IEF125I OR MSGNUM=IEF126I OR MSGNUM=IEF450I) JOBID=TSU* | rename JOBNAME as UserID |  eval EventDate=strftime(_time,"%Y%m%d") | eval EventTime=strftime(_time,"%H:%M:%S") | stats earliest(EventTime) as start latest(EventTime) as end by UserID EventDate MSGNUM | eval LogonTime=case(MSGNUM="IEF125I",start) | eval LogoffTime=case(MSGNUM="IEF126I",end) | eval DisconnectTime=case(MSGNUM="IEF450I",end) | lookup TAM_Information.csv UserID OUTPUT FULNAME as Name AD_DEPARTMENT as Department | table EventDate UserID Name Department LogonTime LogoffTime DisconnectTime

[somesoni2 · Accepted solution]

Try this
Updated Was supposed to use if instead of case

index = ironstream (MSGNUM=IEF125I OR MSGNUM=IEF126I OR MSGNUM=IEF450I) JOBID=TSU* 
| rename JOBNAME as UserID | eval EventDate=strftime(_time,"%Y%m%d") 
| eval LogonTime=if(MSGNUM="IEF125I",strftime(_time,"%H:%M:%S"),null()) 
| eval LogoffTime=if(MSGNUM="IEF126I",strftime(_time,"%H:%M:%S"),null()) 
| eval DisconnectTime=case(MSGNUM="IEF450I",strftime(_time,"%H:%M:%S"),null())
| stats earliest(LogonTime) as LogonTime latest(LogoffTime) as LogoffTime latest(DisconnectTime) as DisconnectTime by UserID EventDate 
 | lookup TAM_Information.csv UserID OUTPUT FULNAME as Name AD_DEPARTMENT as Department | table EventDate UserID Name Department LogonTime LogoffTime DisconnectTime

[szimmer661]

This worked very nicely. Splunk didn't like the null() argument in the case expressions, but once I removed those, the results were exactly as desired.

Thank you very much.

[szimmer661]

Is there any advantage to using the if instead of the case? Or is it just a matter of personal preference?

[somesoni2]

I meant to change case to if, but forgot (if is the one which uses three parameter).

[sundareshr]

See if this is what you're looking for

index = ironstream (MSGNUM=IEF125I OR MSGNUM=IEF126I OR MSGNUM=IEF450I) JOBID=TSU* 
| rename JOBNAME as UserID 
| eval EventDate=strftime(_time,"%Y%m%d") 
| eval EventTime=strftime(_time,"%H:%M:%S") 
| stats earliest(EventTime) as start latest(EventTime) as end by UserID EventDate MSGNUM 
| eval LogonTime=case(MSGNUM="IEF125I",start) 
| eval LogoffTime=case(MSGNUM="IEF126I",end) 
| eval DisconnectTime=case(MSGNUM="IEF450I",end) 
| lookup TAM_Information.csv UserID OUTPUT FULNAME as Name AD_DEPARTMENT as Department 
| table EventDate UserID Name Department LogonTime LogoffTime DisconnectTime
| stats values(*) as * by UserID EventDate

[szimmer661]

This worked, but the result totally ignored the column order specified in the table clause.