Hey Splunksters,
How can I go about getting to the next hour and 15 min - when min is 15 min past the hour for a timestamp of an event? So far I have gotten clarity for when the min is at 00 on the hour and will keep that line of code. I have tried different methods, but still at square 1.
(index="123" level=pdf ) OR (index="456" )
| eval latestSub=case(level="pdf", eventTimeStamp)
| eval Ingestion_Time=strftime(strptime(latestSub, "%Y-%m-%d %H:%M:%S.%3N") + 4500, "%Y-%m-%d %H:%M:%S.%3N")
| stats stats dc(index) as idx values(index) as indexes values(level) as level latest(latestSub) as latestSub latest(Ingestion_Time) as Ingestion_Time by letterSubmission | where idx=1 AND indexes!="456" | fields - idx
Above code only has the logic for when the min is 00 on the hour, but need to include for when latestSub is 15 min past the hour. Any guidance on the approach is greatly appreciated.
Example:
2021-02-19 13:16:43.349028
Desired result when min is past 15 of the hour:
2021-02-19 14:15:43.349028
Hi @Mary666,
I used a temporary variable for epoch and changed if to case statement by adding your conditions. I hope I got them right 😀. Please try below;
| eval latestSub_epoch=strptime(latestSub, "%Y-%m-%d %H:%M:%S.%6N")
| eval latestSub_hour=tonumber(strftime(latestSub_epoch, "%H"))
| eval latestSub_min=tonumber(strftime(latestSub_epoch, "%M"))
| eval latestSub_sec=round(latestSub_epoch%100,6)
| eval Ingestion_Time=strftime(case(latestSub_hour=23 OR latestSub_min>15,latestSub_epoch-latestSub_epoch%3600+4500+latestSub_sec,latestSub_min=0,latestSub_epoch+900,1=1,latestSub_epoch),"%Y-%m-%d %H:%M:%S.%6N")
| fields - latestSub_*
Hi @Mary666,
Sure, let me explain;
I used a case statement to calculate the Ingestion_Time value according to your scenarios below. Inside the case statement, each pair is a comparison and action (if .. then). The latest 1=1 is for the ELSE scenario.
We are making all time calculations using seconds.
Three comparisons are required to cover your scenarios, we can divide the case into three part;
if latestSub_hour=23 OR latestSub_min>15 then
latestSub_epoch%3600 --> Finds remainder of the timestamp by diving by hour
latestSub_epoch-latestSub_epoch%3600 --> Substarcts from the time to find the exact hour
4500 --> adds 1 hour and 15 minutes to go the next hour 15 minutes.
latestSub_sec --> puts the seconds part of the time
if latestSub_min=0 then
latestSub_epoch+900 --> since we are for min=00 so just ad 15 minutes (900 seconds)
1=1,latestSub_epoch --> Since we already covered all cases keep the latestSub as it is for else
Hi scelikok,
I apricate you for being so through with your explanation. I was testing out the code for another time range lets say every 7th min past the half hour - it ingest every half hour past the 7th min. Example if event comes in at 16:02 it will be in the ingested at 16:07 and if event comes in at 16:15 it will be ingested at 16:37 - if event comes in at 16:30 ingestion should be 16:37 too. So far I have the code below, but the issue is when event comes in at lets say 16:30 - it gives me 17:07 instead of 16:37 and when event comes in 16:02 it gives me 16:37 instead of 16:07. Would you please be able to help?
| eval Ingestion_Time=strftime(case(latestSub_hour=23 OR latestSub_min>7,latestSub_epoch-latestSub_epoch%1800+2220+latestSub_sec,latestSub_min=0,latestSub_epoch+420,1=1,latestSub_epoch),"%Y-%m-%d %H:%M:%S.%6N")
Hi @Mary666,
Thank you for your nice comments 🙂
My explanations are below;
1. We are calculating the seconds in the latestSub timstamp with 6 decimal precision. % is modulus operator. It %100 divides the epoc time by 100 and outputs the remainder part. ,6 is round parameter for keeping 6 decimals on seconds.
Actually I noticed that it should be divided by 60 not 100, because what we are trying to get is seconds. Your second values may not be correct. Please update your query with %60,6
| eval latestSub_sec=round(latestSub_epoch%60,6)
2. On CASE statements 1=1 means ELSE, it will always result true. We use this as latest comparison if any of previous comparisons fails, this will catch.
Hi scelikok,
Thanks for catching the error seconds for me. I would like to further understand. Would you mind shedding some light on what these commands are doing.
Why is latestSub_epoch being subtracted from latestSub_epoch%3600+4500+latestSub_sec?
latestSub_epoch-latestSub_epoch%3600+4500+latestSub_sec
Also, why start with latestSub_min=0, and why adding 900 to latestSub_epoc?
latestSub_min=0,latestSub_epoch+900
Thanks for your time.
Hi @Mary666,
I used a temporary variable for epoch and changed if to case statement by adding your conditions. I hope I got them right 😀. Please try below;
| eval latestSub_epoch=strptime(latestSub, "%Y-%m-%d %H:%M:%S.%6N")
| eval latestSub_hour=tonumber(strftime(latestSub_epoch, "%H"))
| eval latestSub_min=tonumber(strftime(latestSub_epoch, "%M"))
| eval latestSub_sec=round(latestSub_epoch%100,6)
| eval Ingestion_Time=strftime(case(latestSub_hour=23 OR latestSub_min>15,latestSub_epoch-latestSub_epoch%3600+4500+latestSub_sec,latestSub_min=0,latestSub_epoch+900,1=1,latestSub_epoch),"%Y-%m-%d %H:%M:%S.%6N")
| fields - latestSub_*
Hi scelikok,
| eval latestSub_sec=round(latestSub_epoch%100,6)
| eval Ingestion_Time=strftime(case(latestSub_hour=23 OR latestSub_min>15,latestSub_epoch-latestSub_epoch%3600+4500+latestSub_sec,latestSub_min=0,latestSub_epoch+900,1=1,latestSub_epoch),"%Y-%m-%d %H:%M:%S.%6N")
The code works perfectly fine, but have the following questions:
1. Could you please let me know what this is doing? I see it is rounding, but not sure why you used %100,6 on the first line of code.
2. On the second to the last line you have 1=1 - I know 1=1 means true in some cases. Can you please let me know your thought process here?
You're an Angel! Thanks so much. I am learning from your recommendations and this solution works perfectly. Have an awesome week.
Hi @Mary666,
You can use conditional eval like below, I converted latestSub the hour (with minutes) and check if it is higher than 1500. If it is, finding the beginning of the hour, than add 4500 seconds.
(index="123" level=pdf ) OR (index="456" )
| eval latestSub=case(level="pdf", eventTimeStamp)
| eval latestSub=strptime(latestSub, "%Y-%m-%d %H:%M:%S.%3N")
| eval Ingestion_Time=strftime(if(tonumber(strftime(latestSub, "%M%S"))>1500,round(latestSub/3600,0)*3600+4500,latestSub),"%Y-%m-%d %H:%M:%S.%3N")
| stats stats dc(index) as idx values(index) as indexes values(level) as level latest(latestSub) as latestSub latest(Ingestion_Time) as Ingestion_Time by letterSubmission
| where idx=1 AND indexes!="456"
| fields - idx
Hi scelikok,
Thanks for getting back to me. I seem to be having two issues from the code below:
1. The code below returns the time in epoch. Is there a way this can show in UNIX time?
| eval latestSub=strptime(latestSub, "%Y-%m-%d %H:%M:%S.%3N")
2. I see the results in your code and it works well for certain time slots, but generally what needs to occur is based on the below scenarios. I have tried adding if eval with less than and equal to logic, but not getting anywhere... Can you please orient me on how to approach this? These are the scenarios?