topic Re: How to create a chart that will display the open and resolved tickets over time? in Splunk Search

How to create a chart that will display the open and resolved tickets over time?

dojiepreji — Tue, 29 Sep 2020 23:39:41 GMT

I need to create a chart that will display the open and resolved tickets over time.

Here is my current code:

| eval myTime = _time 
| eval restoredDate = restored_on_epoch
| eval eventTime = split(myTime."|".restoredDate, "|") 
| mvexpand eventTime 
| eval status = if(eventTime = _time, "Open", "Resolved") 
| eval _time = eventTime 
| timechart span=1mon count by status

My field '_time' is the creation date of ticket and 'restored_on_epoch' the resolved date. If a ticket hasn't been resolved yet, the 'restored_on_epoch' field will have a null value.

The search above works just fine. However, it only creates a timechart for tickets that has been solved. The tickets with null values in the restored_on_epoch (those tickets that are not solved yet) are not included in the timechart. I want to include these as well in my "Open" field in my timechart.

How should I achieve this? Any help would be appreciated. Thank you.

Re: How to create a chart that will display the open and resolved tickets over time?

nickhills — Fri, 08 Mar 2019 09:54:31 GMT

I think the problem lies in this snippet: if(eventTime = _time I cant quite understand what that is trying to eval.

Would the following give the desired results?

 | eval myTime = _time 
 | eval restoredDate = restored_on_epoch
 | eval status = if((restoredDate>myTime), "Resolved", "Open") 
 | eval _time =coalesce(restoredDate,_time)
 | timechart span=1mon count by status

The logic here, is that a resolved issue has a resolved date later than the creation date, so do the eval based on that.
Then if there is a restoredDate, use that for the _time filed, otherwise use the ticket open time.

Re: How to create a chart that will display the open and resolved tickets over time?

dojiepreji — Fri, 08 Mar 2019 11:13:58 GMT

if(eventTime = _time

This is to assign each ticket a status of Open and Resolved.

name         date created                           date resolved            status
ticket_132 2019-03-07 23:12:12  03/07/2019 11:40:23 PM  Open
ticket_132 2019-03-07 20:40:42  03/07/2019 12:00:12 PM  Resolved

Each ticket will have corresponding 'open' and 'resolved' statuses.

What I want to happen is something like this:
For example, a ticket was created on december and resolved on february. I want it to add the ticket on 'open' on months december, january and then to 'resolved' on february on my timechart.

Re: How to create a chart that will display the open and resolved tickets over time?

nickhills — Fri, 08 Mar 2019 11:24:27 GMT

Surely it only has a resolved date, if the issue is resolved??
In your table you have two different resolved dates for the same ticket.

Re: How to create a chart that will display the open and resolved tickets over time?

dojiepreji — Fri, 08 Mar 2019 11:52:08 GMT

Yes, that is what happens after the mvexpand command. It will create two entries per ticket, one for 'Open' and one for 'Resolved'. This is so that I could timechart it by open and resolved.

Re: How to create a chart that will display the open and resolved tickets over time?

nickhills — Fri, 08 Mar 2019 11:59:30 GMT

Maybe its a typo in the ticket number, but your table has two rows for ticket 132.
Each row has a different created and resolved date, and even though the top row is 'Open' it still has a resolved date.

Is that the results your query (from the question) produces?

Re: How to create a chart that will display the open and resolved tickets over time?

dojiepreji — Mon, 11 Mar 2019 03:24:40 GMT

It's what the result looks like after the mvexpand command. There is originally only one table entry per ticket, but after the mvexpand command, it produces another copy of the ticket so that I can tally the tickets' as Open and Resolved.