I have data where I am calculating the difference between two timestamps and showing the difference in days:hh:mm:ss ...But in some cases if the the duration is greater than 99 days its not showing 100 .It shows something like 99+04:47:11
I am looking something like...if the duration is 103 days..the it should be 103+04:47:11..Is this possible on Splunk.
Thanks in Ad
Please post your duration field calculation
@bowesmana Below is my search.I want the "Estimated_Installed_Time" to show days greater than 99 instead of 99+
Index=xyz .....| eval ltime = Estimated_End_Time
| eval Estimated_End_Time = strftime(Estimated_End_Time,"%Y-%m-%d %H:%M:%S")
| eval ltime = if(Estimated_End_Time != "", ltime, nowtime)
| eval nowtime = strftime(nowtime,"%Y-%m-%d %H:%M:%S")
| eval Estimated_End_Time = if(Estimated_End_Time != "", Estimated_End_Time, nowtime)
| eval duration = ltime - etime
| eval duration_days = round(duration/86400,2)
| eval Estimated_Installed_Time =tostring(duration,"duration")
| table Row id host SN Start_Date Start_Time Estimated_End_Time Estimated_Installed_Time etime ltime duration_days
| stats max(Estimated_End_Time) as Estimated_End_Time max(Estimated_Installed_Time) as Estimated_Installed_Time max(duration_days) as Estimated_Installed_Days by host SN Start_Date Start_Time
.
Thanks for posting the search - the reason you are getting 99+ as your max is this
| eval Estimated_Installed_Time =tostring(duration,"duration")
...
| stats ... max(Estimated_Installed_Time) as Estimated_Installed_Time
You are converting the duration to a string then doing max() on the string value, not the number, so 99 is greater then 100 (9 is higher alphabetically than 1.
Your search should
| stats max(Estimated_End_Time) as Estimated_End_Time max(duration_days) as Estimated_Installed_Days by host SN Start_Date Start_Time
| eval Estimated_Installed_Days = round(Estimated_Installed_Days/86400,2)
| eval Estimated_Installed_Time =tostring(Estimated_Installed_Days,"duration")
i.e.
@bowesmana I tried your search but I dont see any results . I think there is no value for duration days...in my searvh we get the value from | eval duration_days = round(duration/86400,2)
stats max(Estimated_End_Time) as Estimated_End_Time max(duration_days) as Estimated_Installed_Days by host SN Start_Date Start_Time
Sorry, my bad - you're absolutely right, instead of duration_days, use duration, so you carry duration through and then calculate days after the stats.
| stats max(Estimated_End_Time) as Estimated_End_Time max(duration) as duration by host SN Start_Date Start_Time
| eval Estimated_Installed_Days = round(duration/86400,2)
| eval Estimated_Installed_Time =tostring(Estimated_Installed_Days,"duration")
Another (more elegant in my opinion) solution is to use fieldformat instead of eval to keep the nummerical value internally but present it in a nicer way.
@PickleRick Yes, fieldformat has its uses, but I'm not a fan given its somewhat confusing behaviour, e.g.
| makeresults
| fields - _time
| eval n=now()
| fieldformat a=strftime(n, "%F %T")
| fieldformat n=strftime(n, "%F %T")
| eval a_max=max(a,1)
| eval n_max=max(n,1)
| eval a_type=if(isnum(a),1,0)
| eval n_type=if(isnum(n),1,0)
so the a assignment works but is not really a field and is not a number and if you transpose that, field a does not get included in the transposed results.
It also does not work in foreach [] statements oddly.
Let's say that it is perfectly understandable but indeed can be a bit confusing.
Hi @power12
It should work, maybe share how you are doing it and remember that the time diff should be in seconds.
Here's a run anywhere example demonstrating it should work OK if the value is more than 100 days (tested on v8.2.7)
| makeresults | eval timediff=tostring((103 * 86400)+12345, "duration")
Hope that helps
Hello @yeahnah
Below is my search.I want the "Estimated_Installed_Time" to show days greater than 99 instead of 99+
Index=xyz .....| eval ltime = Estimated_End_Time | eval Estimated_End_Time = strftime(Estimated_End_Time,"%Y-%m-%d %H:%M:%S") | eval ltime = if(Estimated_End_Time != "", ltime, nowtime) | eval nowtime = strftime(nowtime,"%Y-%m-%d %H:%M:%S") | eval Estimated_End_Time = if(Estimated_End_Time != "", Estimated_End_Time, nowtime) | eval duration = ltime - etime | eval duration_days = round(duration/86400,2) | eval Estimated_Installed_Time =tostring(duration,"duration") | table Row id host SN Start_Date Start_Time Estimated_End_Time Estimated_Installed_Time etime ltime duration_days | stats max(Estimated_End_Time) as Estimated_End_Time max(Estimated_Installed_Time) as Estimated_Installed_Time max(duration_days) as Estimated_Installed_Days by host SN Start_Date Start_Time