Splunk Search

Need help in displaying results in column

nicksrulz
Explorer

Hi Legends,

Need help in displaying start time, when error occurred and end time when it got resolved , in separate column. Currently they are getting displayed in same column like below :

statusDateTimeREASON_CODE
FAILED25/04/202325/04/2023 20:33Z910
FAILED25/04/202325/04/2023 20:11Z910
FAILED25/04/202325/04/2023 3:38Z911
FAILED25/04/202325/04/2023 3:37Z911
FAILED25/04/202325/04/2023 3:37Z911
FAILED25/04/202325/04/2023 3:36Z911

 

Please let me know how can i modify my query to display results like below:

StatusDateStart TimeEnd TimeREASON_CODECount
FAILED25/04/202325/04/2023 20:1125/04/2023 20:33Z9102
FAILED25/04/202325/04/2023 3:3625/04/2023 3:38Z9114

 

My Query :

index=test sourcetype="*" STATUS_REASON_CODE IN (U220, U902, U904, U905, Z704, Z900, Z902, Z903, Z904, Z910, Z911, Z912, Z913, Z914, Z920, Z922, Z923, Z924) STATE = FAILED | fields STATE _time STATUS_REASON_CODE | convert timeformat="%Y-%m-%d %H:%M:%S" ctime(_time) AS Time | convert timeformat="%Y-%m-%d" ctime(_time) AS TimeDay | eval FailTime=case(field_name="Failure Time", _time) | eval ReasonCode=case(field_name="Reason Code", STATUS_REASON_CODE) | eval State=case(field_name="State", STATE) | eval minTime = (min(Time)) | rename STATUS_REASON_CODE as REASON_CODE | sort - Time | table STATE TimeDay minTime REASON_CODE

Labels (2)
0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @nicksrulz,

let me understand: you want, for each REASON_CODE, the first event date and the last event date, is it correct?

if this is your requirement, please try this:

index=test sourcetype="*" STATUS_REASON_CODE IN (U220, U902, U904, U905, Z704, Z900, Z902, Z903, Z904, Z910, Z911, Z912, Z913, Z914, Z920, Z922, Z923, Z924) STATE=FAILED 
| rename STATUS_REASON_CODE as REASON_CODE 
| stats 
   values(STATE) AS Status
   earliest(_time) AS StartTime
   latest(_time) AS EndTime
   count
   BY REASON_CODE
| eval Date=strftime(StartTime,"%d/%m/%Y")
| table Status Date StartTime EndTime REASON_CODE count

Ciao.

Giuseppe

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...