Splunk Search

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

szimmer661
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

somesoni2
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

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

szimmer661
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

szimmer661
Explorer

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

sundareshr
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

szimmer661
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

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, ...