topic Re: How to edit my search to combine multiple rows into one output row in my results? in Splunk Search

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

szimmer661 — Tue, 25 Oct 2016 21:21:51 GMT

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

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

sundareshr — Tue, 25 Oct 2016 21:27:18 GMT

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

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

somesoni2 — Tue, 25 Oct 2016 21:30:42 GMT

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

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

szimmer661 — Tue, 25 Oct 2016 21:41:42 GMT

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

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

szimmer661 — Tue, 25 Oct 2016 21:43:02 GMT

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.

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

somesoni2 — Tue, 25 Oct 2016 21:44:47 GMT

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

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

szimmer661 — Tue, 25 Oct 2016 22:02:25 GMT

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