How to count the number of opened and closed incidents?

[madhav_dholakia]

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

[to4kawa · Accepted solution]

| makeresults
| eval _raw="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"
| multikv forceheader=1
| table dateRaised, IncID, Location, Status, closedDate
| eval dateRaised=strptime(dateRaised,"%d-%m-%y")
| fieldformat dateRaised=strftime(dateRaised,"%d-%m-%y")
| eventstats count(eval(dateRaised>=strptime("06-05-20","%d-%m-%y"))) as RaisedCount count(eval(isnotnull(closedDate))) as ClosedCount
| where dateRaised=strptime("06-05-20","%d-%m-%y")

[madhav_dholakia]

Thank you very much @to4kawa for quick response on this.

The search you have suggested works fine. But how can I make it dynamic - like at a later stage, I will need to create a rolling report (using Summary Index) which shows daily closed and raised count for last 30 days.

in this case, I can set time picker as "yesterday" and schedule my search to run daily.

How can I make these changes in your search so that it filters data based on what is selected in Time Picker drop-down.

Thank you.

Madhav

[to4kawa]

How to count the number of opened and closed incidents?
There is not your condition.

I am answering your question.
Is it in good faith to change the terms you are offering in the first place?

[madhav_dholakia]

Thanks, I wanted to understand how I can use _time in my scenario so just used a sample data and output (i.e. Open and Closed Count for 6th May) in my question. I think I will need to post a more generic question and not with this sample data. Thanks for your help.

[punithgubbi]

@madhav_dholakia were you able to resolve this, i am looking for similar solution to show both closed and opened data in the single visual