count over time with open/close events

[sudo_su]

Hello Splunkers,

I would like to create a timechart for status. The data only comes when there's an update, so generally, one event when the ticket opens, and one when it closes. How should I approach visualising this?  

Data ~

Problem ID	Start Time	End Time	ManagementZone
10	09:00	null	CAT
11	09:00	null	DOG
10	09:00	09:30	CAT
12	10:00	null	CAT
13	10:00	null	DOG
11	09:00	11:30	DOG
12	10:00	11:30	CAT
13	10:00	12:00	DOG
14	15:00	null	CAT
15	15:30	null	DOG

Desired Outcome

	08:00	09:00	10:00	11:00	12:00	13:00	14:00	15:00	16:00	17:00
CAT	0	1	1	1	0	0	0	1	1	1
DOG	0	1	2	2	0	0	0	0	1	1

Thank you all in advance. 

[ITWhisperer]

Looking at the desired outcome, it looks like you are bucketing your times to the top of the next hour (hence the relative_time calculations)

| makeresults 
| eval _raw="Problem ID,Start,End,ManagementZone
10,09:00,,CAT
11,09:00,,DOG
10,09:00,09:30,CAT
12,10:00,,CAT
13,10:00,,DOG
11,09:00,11:30,DOG
12,10:00,11:30,CAT
13,10:00,12:00,DOG
14,15:00,,CAT
15,15:30,,DOG"
| multikv forceheader=1 
| eval Start="2021-04-10T".Start
| eval End="2021-04-10T".End
| eval Start=strptime(Start,"%Y-%m-%dT%H:%M")
| eval End=strptime(End,"%Y-%m-%dT%H:%M")
| fieldformat Start=strftime(Start,"%H:%M")
| fieldformat End=strftime(End,"%H:%M")
| fields - _* linecount 
| eval change=if(isnull(End),1,-1)
| eval _time=coalesce(relative_time(End,"+1h-1s@h"),relative_time(Start,"+1h-1s@h"))
| chart sum(change) as change by _time ManagementZone
| makecontinuous _time span=1h
| fillnull value=0
| untable _time ManagementZone change
| streamstats sum(change) as count by ManagementZone
| stats sum(count) as count by _time ManagementZone
| eval time=strftime(_time,"%H:%M")
| xyseries ManagementZone time count

[manjunathmeti · Accepted solution]

hi @sudo_su,

Try this:

| makeresults 
| eval _raw="ProblemID	StartTime	EndTime	ManagementZone
10	09:00	null	CAT
11	09:00	null	DOG
10	09:00	09:30	CAT
12	10:00	null	CAT
13	10:00	null	DOG
11	09:00	11:30	DOG
12	10:00	11:30	CAT
13	10:00	12:00	DOG
14	15:00	null	CAT
15	15:30	null	DOG" 
| multikv forceheader=1 
| search EndTime!=null 
| table ProblemID StartTime EndTime ManagementZone 
| eval st="04/19/2021 ".StartTime, et="04/19/2021 ".EndTime 
| eval st=strptime(st, "%m/%d/%Y %H:%M"), et=strptime(et, "%m/%d/%Y %H:%M"), mv=mvrange(st, et, "1h"), mv=strftime(mv, "%H:%M") 
| mvexpand mv 
| chart count over ManagementZone by mv

If this reply helps you, a like would be appreciated.