Need help in displaying results in column

[nicksrulz]

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 :

status	Date	Time	REASON_CODE
FAILED	25/04/2023	25/04/2023 20:33	Z910
FAILED	25/04/2023	25/04/2023 20:11	Z910
FAILED	25/04/2023	25/04/2023 3:38	Z911
FAILED	25/04/2023	25/04/2023 3:37	Z911
FAILED	25/04/2023	25/04/2023 3:37	Z911
FAILED	25/04/2023	25/04/2023 3:36	Z911

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

Status	Date	Start Time	End Time	REASON_CODE	Count
FAILED	25/04/2023	25/04/2023 20:11	25/04/2023 20:33	Z910	2
FAILED	25/04/2023	25/04/2023 3:36	25/04/2023 3:38	Z911	4

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

[gcusello]

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