Splunk Search

How to create a bar chart that counts completed tickets and averages

tmanuel1
New Member

Hi guys! I am pretty new to this and in researching I have not found what I am looking for or did not recognize the answer when it was in front of me. Anyway, I am trying to create a bar chart that counts how many completed tickets, Average days it too to assign the ticket, Average days to complete the ticket after it was assigned and the Total Average Days per month. So what I am 'trying to do' is have a chart that shows...

example:

Month Completed Avg_Days_to_Assign Avg_Days_to_Complete_after_Assign Total_Avg_Days
JAN 1 12 10 22
FEB 1 10 10 20
MAR 1 2 2 4
etc etc

Below is what I have done:

| dedup Work_Order_ID 
| eval Days_to_Assign=round((Actual_Start_Date-Submit_Date)/86400,0)
| eval Days_to_Complete_after_Assign=round((Completed_Date-Actual_Start_Date)/86400,0)
| eval Total_Days_from_Submit_to_Completion=round((Completed_Date-Submit_Date)/86400,0) 
| stats count(Status) as Completed, avg(Days_to_Assign) as Avg_Days_to_Assign, avg(Days_to_Complete_after_Assign) as Avg_Days_to_Complete_after_Assign, avg(Total_Days_from_Submit_to_Completion) as Avg_Total_Days_from_Submit_to_Completion by Completed_Date | fieldformat Completed_Date=strftime(Completed_Date,"%b")

What I get is each ticket listed separately by month instead of getting just totals for each month.

I get...ignore the numbers, I just plugged those in for an example
Month Completed Avg_Days_to_Assign Avg_Days_to_Complete_after_Assign Total_Avg_Days
JAN 1 12 10 22
JAN 1 10 10 20
FEB 1 9 10 19
FEB 1 4 4 8
MAR 1 1 1 2

Any help is greatly appreciated!

0 Karma

tmanuel1
New Member

Apologies, I did not copy the entire thing....

| dedup Work_Order_ID
| eval Days_to_Assign=round((Actual_Start_Date-Submit_Date)/86400,0)
| eval Days_to_Complete_after_Assign=round((Completed_Date-Actual_Start_Date)/86400,0)
| eval Total_Days_from_Submit_to_Completion=round((Completed_Date-Submit_Date)/86400,0)
| stats count(Status) AS Completed, avg(Days_to_Assign) as Avg_Days_to_Assign, avg(Days_to_Complete_after_Assign) as Avg_Days_to_Complete_after_Assign, avg(Total_Days_from_Submit_to_Completion) as Avg_Total_Days_from_Submit_to_Completion by Completed_Date
| fieldformat Completed_Date=strftime(Completed_Date,"%b")

0 Karma

tmanuel1
New Member

I came up with this solution...

My Search
| dedup Work_Order_ID
| eval Avg_Days_to_Assign=round((Actual_Start_Date-Submit_Date)/86400,0)
| eval Avg_Days_to_Complete_after_Assign=round((Completed_Date-Actual_Start_Date)/86400,0)
| eval Avg_Total_Days_from_Submit_to_Completion=round((Completed_Date-Submit_Date)/86400,0)
| eval Month=strftime(Completed_Date,"%Y-%m (%b)")
| stats count(eval(Status="5")) AS Completed, avg(Avg_Days_to_Assign) as Avg_Days_to_Assign, avg(Avg_Days_to_Complete_after_Assign) as Avg_Days_to_Complete_after_Assign, avg(Avg_Total_Days_from_Submit_to_Completion) as Avg_Total_Days_from_Submit_to_Completion by Month
| eval Avg_Days_to_Assign=round(Avg_Days_to_Assign,0)
| eval Avg_Days_to_Complete_after_Assign=round(Avg_Days_to_Complete_after_Assign,0)
| eval Avg_Total_Days_from_Submit_to_Completion=round(Avg_Total_Days_from_Submit_to_Completion,0)

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!