Hi There,
I have got incidents data in below format:
dateRaised, IncID, Location, Status, closedDate
05-05-20, 12345, ABC, Closed, 06-05-20
05-05-20, 23456, DEF, In Progress,
06-05-20, 3...
See more...
Hi There,
I have got incidents data in below format:
dateRaised, IncID, Location, Status, closedDate
05-05-20, 12345, ABC, Closed, 06-05-20
05-05-20, 23456, DEF, In Progress,
06-05-20, 34567, GHI, Closed, 06-05-20
24-04-20, 45678, JKL, On Hold,
28-04-20, 56789, MNO, Closed, 06-05-20
Here, column closedDate is NULL for all the incidents which are not yet closed.
And, _time is set to dateRaised.
I want to calculate raised and closed incident counts for a particular time period (say, for 06-05-20) and expected results in below tabular format:
Date RaisedCount ClosedCount
06-05-20 1 3
I have tried solutions given for some of the similar questions on this site but to no joy.
I have tried below solutions with selecting date range between 06-05-20 00:00:00 and 06-05-20 24:00:00 in time picker.
Trial 1:
source="Inc_Open_Closed_Test.csv" host="xyz" sourcetype="csv"
| eval _time=strptime(dateRaised, "%Y-%m-%d %H:%M:%S.%N")
| stats dc(dateRaised) as RaisedCount
| table RaisedCount
| appendcols [search source="Inc_Open_Closed_Test.csv" host="xyz" sourcetype="csv"
| eval _time=strptime(closedDate, "%Y-%m-%d %H:%M:%S.%N")
| stats dc(closedDate) as ClosedCount
| table ClosedCount]
| table RaisedCount ClosedCount
Trial 2:
source="Inc_Open_Closed_Test.csv" host="xyz" sourcetype="csv"
| eval dateRaised=strptime(dateRaised, "%Y-%m-%d %H:%M:%S")
| eval closedDate=strptime(closedDate, "%Y-%m-%d %H:%M:%S")
| eval status=mvappend("opened","closed")
| mvexpand status
| eval _time=case(status="opened", dateRaised, status="closed", closedDate)
| timechart span=1d count by status
Any help would be greatly appreciated.
Thank you.
Madhav