Dashboards & Visualizations

## Which is the best visualization or chart to use to find open tickets until that week?

New Member

Hello Guys,

I have a requirement and i am finding it difficult to show it in a line chart. Below are the details.

I have a list of tickets for past one year. Ticket details has ticket number, state, reported date, resolved date etc. Now i have to show per week count of tickets for last 20 weeks in a line chart.
The count is the number of open tickets till that week (i.e., ticket should not have resolved date or resolved week of that ticket is greater than opened week of that ticket).

Can anyone help me in solving this problem?

Many Thanks in advance.

Tags (2)
SplunkTrust

Here's how I solve this kind of problem:

Turn each ticket into two transactions. The first, with _time at the open date, has a +1 "deltaCount" value. The second, with _time at the close date, has a -1 "deltaCount" value. Therefore, the open tickets at any point are the running sum of all the deltaCount values. You also produce one _time record for each day, with a zero deltaCount value, so that you will get a running sum for each day even if there are no records opened or closed on that day.

You have to decide, and this is a personal preference matter, how you want to report items that are opened and closed in the same period, and whether you want to count the max number of open items in the period, or the average.

Here's a similar thread with an example of the technique -

New Member

index=abc sourcetype=xyz "Assignment group"!="Assignment group" (Priority="1 - Critical" OR Priority="2 - High" OR Priority="3 - Moderate" OR Priority="4 - Low") | table Number "Assignment group" State Priority time Updated Opened Categorization |dedup Number |search "Assignment group"!=xyz123 AND (Categorization="Correction" OR Categorization="Incident") | eval Opened1=strptime(Opened, "%Y-%m-%d") |eval Opened2=strftime(Opened1, "%Y-%m-%d")|eval week=strftime(relativetime(Opened1,"@w5+7d"),"%Y-%m-%d") | eval week2=strftime(relativetime(Opened1,"@w5"),"%Y-%m-%d") | eval week=if(Opened2=week2, week2,week)
|stats count(eval(State!="Cancelled")) as New, count(eval(State!="Closed" AND State!="Resolved" AND State!="Cancelled")) as New2 by week | streamstats current=t sum(New2) as Active |fields - New2
| tail 20|sort week |
append [search index=abc sourcetype=xyz "Assignment group"!="Assignment group" (Priority="1 - Critical" OR Priority="2 - High" OR Priority="3 - Moderate" OR Priority="4 - Low") | table Number "Assignment group" State Priority _time Updated Opened Resolved Categorization| search Resolved=* | search State!="Cancelled"
| dedup Number| search "Assignment group"!=xyz123 AND (Categorization="Correction" OR Categorization="Incident")| eval Resolved1=strptime(Resolved, "%Y-%m-%d") |eval Resolved2=strftime(Resolved1, "%Y-%m-%d")|eval week=strftime(relative
time(Resolved1,"@w5+7d"),"%Y-%m-%d") | eval week2=strftime(relative_time(Resolved1,"@w5"),"%Y-%m-%d") | eval week=if(Resolved2=week2, week2,week)| stats count as Closed by week | tail 20 | sort week ]
| stats first(*) as * by week

New Member

Below is the query i have used, here the Active count is not showing the required count. Basically Active count should be the number of
open tickets(not resolved) till that week. But when i check in the graph for Active count till last month first week, the active count is 0,
because i am checking the latest status. And when i check now those tickets are resolved, but i want to see the number of tickets
which were open till that week.

How to check the Active count of tickets till that particular week?

Splunk Employee

@Chinmai - Can you provide the search that you used? That would be helpful for users attempting to assist you in how you got your initial output and how you want your output to look like.

In general, your question has a greater chance of being answered by experts in the Answers community when when you provide as much information and context as possible. Thanks.

New Member

Currently i have below query, but the Active field will not give required count because if i check the count of Active on first week of last month, obviously it will show zero because i am checking today and i know some of the tickets are open on that week of the month. My question is how to calculate this?

I am finding difficulty in calculating active count.

Active should be the count of open tickets(not resolved) till that week. WE have resolved date column also

``````index=abc  sourcetype=xyz   "Assignment group"!="Assignment group"   (Priority="1 - Critical" OR Priority="2 - High" OR Priority="3 - Moderate" OR Priority="4 - Low") | table Number "Assignment group"  State Priority _time Updated Opened Categorization  |dedup Number |search "Assignment group"!=*xyz123*  AND (Categorization="Correction" OR  Categorization="Incident") | eval Opened1=strptime(Opened, "%Y-%m-%d") |eval Opened2=strftime(Opened1, "%Y-%m-%d")|eval week=strftime(relative_time(Opened1,"@w5+7d"),"%Y-%m-%d") | eval week2=strftime(relative_time(Opened1,"@w5"),"%Y-%m-%d") | eval week=if(Opened2=week2, week2,week)
|stats  count(eval(State!="Cancelled")) as New, count(eval(State!="Closed" AND State!="Resolved" AND State!="Cancelled")) as New2 by week | streamstats current=t sum(New2) as Active |fields - New2
| tail 20|sort week |
append [search index=abc sourcetype=xyz   "Assignment group"!="Assignment group"   (Priority="1 - Critical" OR Priority="2 - High" OR Priority="3 - Moderate" OR Priority="4 - Low") | table Number "Assignment group"  State Priority _time Updated Opened Resolved Categorization| search Resolved=*  | search State!="Cancelled"
| dedup Number| search "Assignment group"!=*xyz123*   AND (Categorization="Correction" OR  Categorization="Incident")| eval Resolved1=strptime(Resolved, "%Y-%m-%d") |eval Resolved2=strftime(Resolved1, "%Y-%m-%d")|eval week=strftime(relative_time(Resolved1,"@w5+7d"),"%Y-%m-%d") | eval week2=strftime(relative_time(Resolved1,"@w5"),"%Y-%m-%d") | eval week=if(Resolved2=week2, week2,week)| stats count as Closed  by week | tail 20 | sort week  ]
| stats first(*) as * by week
``````