Splunk Search

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

Explorer

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
0 Karma
1 Solution

SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

Explorer

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.

0 Karma

Explorer

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

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Legend

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
0 Karma

Explorer

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

0 Karma