Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
×
Are you a member of the Splunk Community?
Sign in or Register with your Splunk account to get your questions answered, access valuable resources and connect with experts!
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
- Find Answers
- :
- About Wheresmydata
Wheresmydata
Explorer
Member since:
06-18-2020
11-25-2021
Community Statistics
| Posts | 19 |
| Solutions | 0 |
| Karma Given | 2 |
| Karma Received | 1 |
| Member Since | 06-18-2020 |
Activity Feed
- Posted Re: Timechart showing trend of missed SLOs against total on Dashboards & Visualizations. 07-14-2020 04:41 AM
- Posted Re: Timechart showing trend of missed SLOs against total on Dashboards & Visualizations. 07-07-2020 06:40 AM
- Posted Re: Timechart showing trend of missed SLOs against total on Dashboards & Visualizations. 07-06-2020 12:32 AM
- Posted Re: Timechart showing trend of missed SLOs against total on Dashboards & Visualizations. 07-03-2020 07:44 AM
- Posted Re: Timechart showing trend of missed SLOs against total on Dashboards & Visualizations. 07-03-2020 03:16 AM
- Posted Re: Timechart showing trend of missed SLOs against total on Dashboards & Visualizations. 07-03-2020 02:07 AM
- Posted Re: Timechart showing trend of missed SLOs against total on Dashboards & Visualizations. 07-02-2020 06:27 AM
- Posted Re: Timechart showing trend of missed SLOs against total on Dashboards & Visualizations. 07-02-2020 05:45 AM
- Posted Re: Timechart showing trend of missed SLOs against total on Dashboards & Visualizations. 07-02-2020 04:55 AM
- Posted Re: Timechart showing trend of missed SLOs against total on Dashboards & Visualizations. 06-29-2020 03:01 AM
- Tagged Re: Timechart showing trend of missed SLOs against total on Dashboards & Visualizations. 06-29-2020 03:01 AM
- Tagged Re: Timechart showing trend of missed SLOs against total on Dashboards & Visualizations. 06-29-2020 03:01 AM
- Tagged Re: Timechart showing trend of missed SLOs against total on Dashboards & Visualizations. 06-29-2020 03:01 AM
- Got Karma for Re: How to show data from different regions as Today's. 06-26-2020 02:26 PM
- Posted Re: Timechart showing trend of missed SLOs against total on Dashboards & Visualizations. 06-25-2020 08:53 AM
- Posted Timechart showing trend of missed SLOs against total on Dashboards & Visualizations. 06-24-2020 03:27 AM
- Posted Re: How to show data from different regions as Today's on Splunk Search. 06-24-2020 01:48 AM
- Karma Re: How to show data from different regions as Today's for DalJeanis. 06-24-2020 01:45 AM
- Posted How to show data from different regions as Today's on Splunk Search. 06-23-2020 01:36 AM
- Posted Re: Count working hours after midnight on Splunk Search. 06-22-2020 02:37 AM
Topics I've Started
| Subject | Karma | Author | Latest Post |
|---|---|---|---|
| 0 | |||
| 0 | |||
| 0 |
07-14-2020
04:41 AM
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
... View more
07-07-2020
06:40 AM
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 ...
... View more
07-06-2020
12:32 AM
@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
... View more
07-03-2020
07:44 AM
hi @to4kawa , I don't know how to do that. Would you please show me how to? Thank you
... View more
07-03-2020
03:16 AM
hi @to4kawa , can you please be a little be more specific? where do I add that?
... View more
07-03-2020
02:07 AM
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?
... View more
07-02-2020
06:27 AM
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,
... View more
07-02-2020
05:45 AM
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?
... View more
07-02-2020
04:55 AM
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!
... View more
06-29-2020
03:01 AM
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?
... View more
06-25-2020
08:53 AM
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!
... View more
06-24-2020
03:27 AM
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: 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!
... View more
Labels
- Labels:
-
timechart
06-24-2020
01:48 AM
1 Karma
Thank you @DalJeanis ! Didn't think it was that simple 🙂 By the way, this is what I did and works great: | 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 Thank you!!
... View more
06-23-2020
01:36 AM
Hi Splunkers, I have different queries that get the age of a ticket only counting the business hours. I need to do different queries as business hours are not the same for different regions. For example, this is the one for the Americas: | 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
| 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
| 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,"%H")
| eval myDay = strftime(_time,"%A")
| where myDay != "Saturday" AND myDay != "Sunday" AND (myHour >= 13 OR myHour < 1)
| stats count as durationInMinutes by reported_time, processed_time
| eval duration = tostring(durationInMinutes*60, "duration") Instead, for Europe I have this line different: | where myDay != "Saturday" AND myDay != "Sunday" AND (myHour >= 7 OR myHour < 17) The output for all of them is to show if today the SLO of 1 hour was missed by doing the following: | eval SLO=if(durationInMinutes>60,"SLO Fail","SLO Achieved")
| chart count by SLO I'm working with UTC time. And it works great. The problem here is that when I get my charts, it will show some events from the Americas as today's (because it's from 13 UTC to 1 UTC) but instead I'd like to see that as yesterdays. Any idea how to get some sort of offset in my query? Thank you all! Wheresmydata
... View more
Labels
- Labels:
-
chart
06-22-2020
02:37 AM
A ha!!! I see what happened, I was missing that 1 hour of Monday from 0 to 1 AM! That makes sense now 🙂 THANK YOU!!!!!!!!!
... View more
06-22-2020
01:14 AM
Hi @javiergn , I think we're almost there! I checked many of the entries and they seem to be all ok - but found one that cannot get my head around. Look at the data below: ticket reported_time processed_time duration SLO Comments ticket01 JUN 07 2020 11:21:43 JUN 09 2020 19:54:52 19:54:00 SLO Failed Jun 7th was a Sunday. Counting 12h for Monday + 6h54m on Monday should be 18:54 ticket02 JUN 01 2020 15:23:13 JUN 02 2020 11:28:46 09:37:00 SLO Failed Jun 1st Monday. This one is correct! ticket03 JUN 15 2020 15:51:27 JUN 16 2020 13:12:35 09:21:00 SLO Failed Jun 15th Monday. This one is correct! ticket04 JUN 08 2020 22:46:51 JUN 09 2020 19:55:32 09:09:00 SLO Failed Jun 9th Monday. This one is correct! ticket05 JUN 01 2020 18:17:04 JUN 02 2020 11:28:49 06:43:00 SLO Failed Jun 1st Monday. This one is correct! ticket06 JUN 17 2020 18:34:51 JUN 18 2020 00:25:11 05:51:00 SLO Failed Jun 17th Wednesday. This one is correct! ticket07 JUN 15 2020 19:24:19 JUN 16 2020 13:12:27 05:48:00 SLO Failed Jun 15th Wednesday. This one is correct! ticket08 JUN 17 2020 18:56:50 JUN 18 2020 00:26:17 05:30:00 SLO Failed Jun 17th Wednesday. This one is correct! I just can't understand why ticket01 has 1 extra hour than expected - am I counting the hours wrong? Sunday - no hours counted there Monday: 12 hours (from 13 to 1) Tuesday: from 13 to 19:54 = 6h 54m Total = 12h + 6h 54m = 18h 54 m .... why is the duration showing 19:54? Thanks so much! you're amazing 🙂
... View more
- Tags:
- stats
06-18-2020
08:32 AM
Hi @javiergn , Thanks for your efforts! really appreciated. Some of the results look ok but others not really. For example look at this one (ticket on the left, omitted in the screenshot) here's my current query: | eval start=strptime(reported_time,"%b %d %Y %H:%M:%S") | eval end=strptime(processed_time,"%b %d %Y %H:%M:%S") | eval startAt1pm = relative_time(start, "@d+13h") | eval endAt1am = relative_time(end, "@d+1h") | eval newStart = if(start > startAt1pm, start, startAt1pm) | eval newEnd = if(end < endAt1am, end, endAt1am) | eval durationInSecs = newEnd - newStart | eval durationHours = floor(durationInSecs/3600) | eval durationMins = floor((durationInSecs - (durationHours*3600))/60) | eval durationString = if(durationHours<10, "0" . toString(durationHours), toString(durationHours)) . ":" . if(durationMins<10, "0" . toString(durationMins), toString(durationMins)) |eval SLO=if(durationInMinutes>60,"SLO Fail","SLO Achieved") | stats first(durationString) as durationString by ticket, reported_time, processed_time,SLO | sort by - durationString thank you, Wheresmydata
... View more
06-18-2020
02:42 AM
Thanks for your quick answer @javiergn but unfortunately that would not do everything I need as I don't want to count hours during the weekends and I also need the exact time (not rounded up). For example, if ticket01 arrives at 19:10 UTC and gets processed next day at 10.05 AM, I want the result to be: ticket01 | JUN 16 2020 19:10:00 | JUN 17 2020 10:05:00 | 05:50:00 | SLO Fail because I would count only from 19:10 to 1 AM Hope I explained myself clearly. thank you again!
... View more
06-18-2020
01:31 AM
Hi Splunkers, hope you guys are all well. I'm trying to do an adaptation of the search in this post (thanks to @elliotproebstel and @javiergn !) https://community.splunk.com/t5/Getting-Data-In/How-to-calculate-total-Business-hours-in-between-weekend-days/td-p/304838 I'm working in UTC and in my case I'm interested in counting the the hours between 1 PM and 1 AM (next day). It works great for other teams where hours are in the same day, but I'm finding the next day tricky. This is what I have so far: | eval start=strptime(reported_time,"%b %d %Y %H:%M:%S") | eval end=strptime(processed_time,"%b %d %Y %H:%M:%S") | eval minute = mvrange(0, (end - start), 60) | mvexpand minute | eval _time = start + minute | eval myHour = strftime(_time,"%H") | eval myMinute = strftime(_time,"%H") | eval myDay = strftime(_time,"%A") | eval myMonth = strftime(_time,"%b") | where myDay != "Saturday" AND myDay != "Sunday" AND myHour >= 13 AND myHour <=1 | stats count as durationInMinutes by ticket,reported_time,processed_time | eval duration = tostring(durationInMinutes*60, "duration") | eval SLO=if(durationInMinutes>60,"SLO Fail","SLO Achieved") | table ticket,reported_time,processed_time,duration,SLO | sort by - duration I want my table to show: ticket number, reported time (when it was reported), processed time (when it got worked by an engineer), duration (time between reported time and processed time, counting only hours between 1 PM and 1 AM next day) and whether the SLO was met or not. Thanks for the help!! Wheresmydata.
... View more
Labels
- Labels:
-
stats
