Dashboards & Visualizations

Timechart showing trend of missed SLOs against total

Wheresmydata
Explorer

Hi Splunkers,

I have some issues creating a time chart from the below report. Pie chart looks great but for some reason I can't get a time chart working:

 

| eval start_time_epoch = strptime(reported_time,"%b %d %Y %H:%M:%S") 
| eval start_time_second = strftime(start_time_epoch,"%S") 
| eval start_time_epoch_rounded = start_time_epoch - start_time_second - 5*3600 
| fields - start_time_epoch, start_time_second 
| eval close_time_epoch = strptime(processed_time,"%b %d %Y %H:%M:%S") 
| eval close_time_second = strftime(close_time_epoch,"%S") 
| eval close_time_epoch_rounded = close_time_epoch - close_time_second - 5*3600 
| fields - close_time_epoch, close_time_second 
| eval minute = mvrange(0, (close_time_epoch_rounded - start_time_epoch_rounded), 60) 
| mvexpand minute 
| eval _time = start_time_epoch_rounded + minute 
| eval myHour = strftime(_time,"%H") 
| eval myMinute = strftime(_time,"%M") 
| eval myDay = strftime(_time,"%A") 
| where myDay != "Saturday" AND myDay != "Sunday" AND (myHour >= 8 AND myHour < 20) 
| eval ReportedTime = strftime(start_time_epoch_rounded,"%b %d %Y %H:%M:%S") 
| eval ProcessedTime = strftime(close_time_epoch_rounded,"%b %d %Y %H:%M:%S") 
| stats count as durationInMinutes by ticket,ReportedTime,ProcessedTime 
| eval duration = tostring(durationInMinutes*60, "duration") 
| eval SLO=if(durationInMinutes>60,"SLO Fail","SLO Achieved") 
| table ticket,ReportedTime,ProcessedTime,duration,SLO 
| chart count by SLO

 

This is how the pie chart looks like:

Screenshot 2020-06-24 at 11.26.29.png

I'd like to get a timechart showing a line for the missed SLOs against the total. If there are no SLOs missed then the line should be at 100%. This is what I tried and it's not working:

 

| eval start_time_epoch = strptime(reported_time,"%b %d %Y %H:%M:%S") 
| eval start_time_second = strftime(start_time_epoch,"%S") 
| eval start_time_epoch_rounded = start_time_epoch - start_time_second - 5*3600 
| fields - start_time_epoch, start_time_second 
| eval close_time_epoch = strptime(processed_time,"%b %d %Y %H:%M:%S") 
| eval close_time_second = strftime(close_time_epoch,"%S") 
| eval close_time_epoch_rounded = close_time_epoch - close_time_second - 5*3600 
| fields - close_time_epoch, close_time_second 
| eval minute = mvrange(0, (close_time_epoch_rounded - start_time_epoch_rounded), 60) 
| mvexpand minute 
| eval _time = start_time_epoch_rounded + minute 
| eval myHour = strftime(_time,"%H") 
| eval myMinute = strftime(_time,"%M") 
| eval myDay = strftime(_time,"%A") 
| where myDay != "Saturday" AND myDay != "Sunday" AND (myHour >= 8 AND myHour < 20) 
| eval ReportedTime = strftime(start_time_epoch_rounded,"%b %d %Y %H:%M:%S") 
| eval ProcessedTime = strftime(close_time_epoch_rounded,"%b %d %Y %H:%M:%S") 
| stats count as durationInMinutes by ticket,ReportedTime,ProcessedTime 
| eval duration = tostring(durationInMinutes*60, "duration") 
| eval SLO=if(durationInMinutes>60,"SLO Fail","SLO Achieved") 
| table ticket,ReportedTime,ProcessedTime,duration,SLO 
| timechart count(SLO), count(SLO Fail) as Missed | eval Percent=round(Missed*100/SLO,2)

 

Any ideas what's missing?

thank you!

Labels (1)
0 Karma

Wheresmydata
Explorer

I just tried something else that didn't work, but at least it's a different error:

| timechart count(eval(durationInMinutes>60 AS Achieved) count(durationInMinutes>=60 as Fail))

and the error is:

Error in 'timechart' command: The eval expression 'count(eval(durationInMinutes>60 AS Achieved) count(durationInMinutes>=60 as Fail))' must be renamed.

 

thanks for your help!

 

0 Karma

Wheresmydata
Explorer

Another (failed) attempt:

| eval start_time_epoch = strptime(reported_time,"%b %d %Y %H:%M:%S") 
| eval start_time_second = strftime(start_time_epoch,"%S") 
| eval start_time_epoch_rounded = start_time_epoch - start_time_second - 5*3600 
| fields - start_time_epoch, start_time_second 
| eval close_time_epoch = strptime(processed_time,"%b %d %Y %H:%M:%S") 
| eval close_time_second = strftime(close_time_epoch,"%S") 
| eval close_time_epoch_rounded = close_time_epoch - close_time_second - 5*3600 
| fields - close_time_epoch, close_time_second 
| eval minute = mvrange(0, (close_time_epoch_rounded - start_time_epoch_rounded), 60) 
| mvexpand minute 
| eval _time = start_time_epoch_rounded + minute 
| eval myHour = strftime(_time,"%H") 
| eval myMinute = strftime(_time,"%M") 
| eval myDay = strftime(_time,"%A") 
| where myDay != "Saturday" AND myDay != "Sunday" AND (myHour >= 8 AND myHour < 20) 
| eval ReportedTime = strftime(start_time_epoch_rounded,"%b %d %Y %H:%M:%S") 
| eval ProcessedTime = strftime(close_time_epoch_rounded,"%b %d %Y %H:%M:%S") 
| stats count as durationInMinutes by ticket,ReportedTime,ProcessedTime
| timechart span=1d count(eval(if(durationInMinutes>60,SLOFail,SLOAchieved))) by ticket

No output at all in the visualisations. How do you actually troubleshoot timecharts?

Tags (3)
0 Karma

robinsonalex88
Explorer

Try this:

 

| timechart count as SLO count(eval(durationInMinutes<60)) as Achieved
| eval Percent=round(Achieved*100/SLO, 2)
| timechart Percent

 

The original example didn't work as expected because the SLO field in the denominator no longer existed after the |timechart command.  You would need count(SLO) as SLO.

The second example didn't work because the as clause needs to be outside whatever stats command you're using and the second count() was missing an eval.  For example:

 

| timechart count(eval(durationInMinutes<60)) AS Achieved count(eval(durationInMinutes>=60)) as Fail

 

 

0 Karma

Wheresmydata
Explorer

Hi @robinsonalex88 , thanks so much for your help here. Apologies for the late response but my Splunk account was locked and had to reach out to support to unlock it.

Unfortunately, this still does not work. I believe the first proposal is missing some function for the timechart:

 

Error in 'timechart' command: The specifier 'Percent' is invalid. It must be in form <func>(<field>). For example: max(size).

 

 

Second suggestion does not give any errors:

 

| timechart count(eval(durationInMinutes<60)) AS Achieved count(eval(durationInMinutes>=60)) as Fail

 

However, it does not output any results 😞 

I think I like the idea of the first proposal better, because I just need a line showing how the SLO attainment is. So let me go through this one better.

I modified your first 'timeline' with stats and I can see the total number of tickets vs the ones that achieved SLO:

 

| stats count as Total count(eval(durationInMinutes<60)) as Achieved

Results:
Total	Achieved
1391	1160

 

that's good. now with that data I want to build my timechart - but I think that with the last |command I lost my _time values and I think that's why I can't create a timechart. How can I bring my ProcessedTime value all the way here?

thanks!

 

0 Karma

Wheresmydata
Explorer

Just tried something else to bring the _time but didn't work either:

| stats count as durationInMinutes by cs3,ReportedTime,ProcessedTime 
| stats count as Total count(eval(durationInMinutes<60)) as Achieved by ProcessedTime 
|timechart count(round(Achieved*100/Total, 2))

No output. What am I missing?

0 Karma

to4kawa
Ultra Champion

timechart needs _time field.

0 Karma

Wheresmydata
Explorer

thanks @to4kawa that's what I thought - but how do I get it there? this is my query so far:

| eval start_time_epoch = strptime(reported_time,"%b %d %Y %H:%M:%S") 
| eval start_time_second = strftime(start_time_epoch,"%S") 
| eval start_time_epoch_rounded = start_time_epoch - start_time_second - 5*3600 
| fields - start_time_epoch, start_time_second 
| eval close_time_epoch = strptime(processed_time,"%b %d %Y %H:%M:%S") 
| eval close_time_second = strftime(close_time_epoch,"%S") 
| eval close_time_epoch_rounded = close_time_epoch - close_time_second - 5*3600 
| fields - close_time_epoch, close_time_second 
| eval minute = mvrange(0, (close_time_epoch_rounded - start_time_epoch_rounded), 60) 
| mvexpand minute 
| eval _time = start_time_epoch_rounded + minute 
| eval myHour = strftime(_time,"%H") 
| eval myMinute = strftime(_time,"%M") 
| eval myDay = strftime(_time,"%A") 
| where myDay != "Saturday" AND myDay != "Sunday" AND (myHour >= 8 AND myHour < 20) 
| eval ReportedTime = strftime(start_time_epoch_rounded,"%b %d %Y %H:%M:%S") 
| eval ProcessedTime = strftime(close_time_epoch_rounded,"%b %d %Y %H:%M:%S") 
| stats count as durationInMinutes by ticket,ReportedTime,ProcessedTime 
| stats count as Total count(eval(durationInMinutes<60)) as Achieved by ProcessedTime 
| timechart count(round(Achieved*100/Total, 2))

thanks,

0 Karma

to4kawa
Ultra Champion

I don't know your log. so I can't make the query.

0 Karma

Wheresmydata
Explorer

hi @to4kawa ,

My data contains:

reported_time (in format "%b %d %Y %H:%M:%S")
processed_time (same format "%b %d %Y %H:%M:%S")
ticket: this is a unique URL
from my query above, you can see that I only count working hours, from 8 AM to 8 PM, excluding weekends.
The difference between processed_time and reported_time (considering the working hours above) should be less than 60 mins, otherwise = SLO Fails

I've already tested that the durationInMinutes work perfectly, now need to make a timechart to track the SLO attainment over time. I thought that this would work: 

| stats count as Total count(eval(durationInMinutes<60)) as Achieved by ProcessedTime 
| timechart count(round(Achieved*100/Total, 2))

 

but I get no output. Any ideas?

0 Karma

to4kawa
Ultra Champion

make _time

0 Karma

Wheresmydata
Explorer

hi @to4kawa , can you please be a little be more specific? where do I add that?

0 Karma

to4kawa
Ultra Champion

timechart needs _time, so you should make _time field.

0 Karma

Wheresmydata
Explorer

hi @to4kawa , I don't know how to do that. Would you please show me how to?

Thank you

0 Karma

to4kawa
Ultra Champion

if your field is already epoch time or time formated(e.g. XX:XX:XX , XX/XX/XX, XX-XX-XX): use RENAME
other: use eval and make _time

 

0 Karma

Wheresmydata
Explorer

@to4kawa ,

I know you're trying to help me but unfortunately I don't think I'm able to understand where to place your suggestions in my query. I'm quite new to Splunk and it's taken me a while to put together this query below. I believe I have a _time value just before the timechart because if I do a table including the ProcessedTime (that's the value I want for _time) I get data. So, why is it not there for the timechart?

Again, this is my query:

| eval start_time_epoch = strptime(reported_time,"%b %d %Y %H:%M:%S") 
| eval start_time_second = strftime(start_time_epoch,"%S") 
| eval start_time_epoch_rounded = start_time_epoch - start_time_second - 5*3600 
| fields - start_time_epoch, start_time_second 
| eval close_time_epoch = strptime(processed_time,"%b %d %Y %H:%M:%S") 
| eval close_time_second = strftime(close_time_epoch,"%S") 
| eval close_time_epoch_rounded = close_time_epoch - close_time_second - 5*3600 
| fields - close_time_epoch, close_time_second 
| eval minute = mvrange(0, (close_time_epoch_rounded - start_time_epoch_rounded), 60) 
| mvexpand minute 
| eval _time = start_time_epoch_rounded + minute 
| eval myHour = strftime(_time,"%H") 
| eval myMinute = strftime(_time,"%M") 
| eval myDay = strftime(_time,"%A") 
| where myDay != "Saturday" AND myDay != "Sunday" AND (myHour >= 8 AND myHour < 20) 
| eval ReportedTime = strftime(start_time_epoch_rounded,"%b %d %Y %H:%M:%S") 
| eval ProcessedTime = strftime(close_time_epoch_rounded,"%b %d %Y %H:%M:%S") 
| stats count as durationInMinutes by ticket,ReportedTime,ProcessedTime 
| stats count as Total count(eval(durationInMinutes<60)) as Achieved by ProcessedTime 
| timechart count(round(Achieved*100/Total, 2))

If all I need to do is use RENAME, where would that be? how would the query look like?

thank you,

Wheresmydata

0 Karma

to4kawa
Ultra Champion

I don't know your logs, so I can't recommend. you can do it.

0 Karma

Wheresmydata
Explorer

Something that I really don't understand is the following:

If I remove the timechart and just leave the following: 

| stats count as durationInMinutes by ticket,ReportedTime,ProcessedTime 
| stats count as Total count(eval(durationInMinutes<60)) as Achieved by ProcessedTime

I get the following output:

ProcessedTime	Total	Achieved
Jul 06 2020 08:26:00	1	0
Jul 06 2020 08:27:00	6	0
Jul 06 2020 08:30:00	3	0
Jul 06 2020 08:35:00	1	0
Jul 06 2020 08:37:00	1	0
Jul 06 2020 08:38:00	11	10

so, why if I add after that my timechart like this:

| stats count as durationInMinutes by ticket,ReportedTime,ProcessedTime 
| stats count as Total count(eval(durationInMinutes<60)) as Achieved by ProcessedTime 
| timechart count(round(Achieved*100/Total, 2))

I get no visualization/stats? only events? 

I should have the time (ProcessedTime) .... why is it not working???

I need someone to enlighten me ...

0 Karma

to4kawa
Ultra Champion

I said If you don't provide the log, I can't make query. 

why do you display the log?

I told that make _time, where is it in your query?

0 Karma

Wheresmydata
Explorer

I cannot share the log as it contains a lot of confidential information. I don't think it's necessary anyway as you can see that I'm able to get a proper table with data before the timechart.

If I add make _time in my query I just get the following:

Unknown search command 'make'.

So, going back to my previous question - maybe someone else in this forum can help:

This is my current query:

| eval start_time_epoch = strptime(reported_time,"%b %d %Y %H:%M:%S") 
| eval start_time_second = strftime(start_time_epoch,"%S") 
| eval start_time_epoch_rounded = start_time_epoch - start_time_second - 5*3600 
| fields - start_time_epoch, start_time_second 
| eval close_time_epoch = strptime(processed_time,"%b %d %Y %H:%M:%S") 
| eval close_time_second = strftime(close_time_epoch,"%S") 
| eval close_time_epoch_rounded = close_time_epoch - close_time_second - 5*3600 
| fields - close_time_epoch, close_time_second 
| eval minute = mvrange(0, (close_time_epoch_rounded - start_time_epoch_rounded), 60) 
| mvexpand minute 
| eval _time = start_time_epoch_rounded + minute 
| eval myHour = strftime(_time,"%H") 
| eval myMinute = strftime(_time,"%M") 
| eval myDay = strftime(_time,"%A") 
| where myDay != "Saturday" AND myDay != "Sunday" AND (myHour >= 8 AND myHour < 20) 
| eval ReportedTime = strftime(start_time_epoch_rounded,"%b %d %Y %H:%M:%S") 
| eval ProcessedTime = strftime(close_time_epoch_rounded,"%b %d %Y %H:%M:%S") 
| stats count as durationInMinutes by ticket,ReportedTime,ProcessedTime 
| stats count as Total count(eval(durationInMinutes<60)) as Achieved by ProcessedTime 
| timechart count(round(Achieved*100/Total, 2))

If I run that, there are events but I don't get a visualization. The issue in this post is to get the Timeline working!

If I remove the timeline line I can get good data:

ProcessedTime	      Total  Achieved
Jul 13 2020 07:01:00	3	0
Jul 13 2020 07:03:00	2	0
Jul 13 2020 07:04:00	2	1
Jul 13 2020 07:11:00	2	1

 

I have time (ProcessedTime), I have values for Achieved. I have values for Total. Can anyone else help?

Thank you

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...