topic Re: Increase Time on event TimeStamp for two different times in Splunk Search

Increase Time on event TimeStamp for two different times

Mary666 — Mon, 08 Mar 2021 04:19:21 GMT

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

Re: Increase Time on event TimeStamp for two different times

scelikok — Mon, 08 Mar 2021 07:06:56 GMT

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

Re: Increase Time on event TimeStamp for two different times

Mary666 — Mon, 08 Mar 2021 23:49:19 GMT

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?

If latestSub time falls on the hour and min is 00 - add 15 min.
If latestSub time falls exactly on the hour and 15 min - leave as is.
If latestSub time min is 15 min past the hour - go to next hour and 15 min of the clock.
if latestSub time hour is 23 (11p.m.) - have time go the next day and 15 th min.
- example: 2021-02-22 23:00:37.336087 ---> 2021-02-23 00:15:37.336087

Re: Increase Time on event TimeStamp for two different times

scelikok — Tue, 09 Mar 2021 04:47:37 GMT

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_*

Re: Increase Time on event TimeStamp for two different times

Mary666 — Tue, 09 Mar 2021 05:22:51 GMT

You're an Angel! Thanks so much. I am learning from your recommendations and this solution works perfectly. Have an awesome week.

Re: Increase Time on event TimeStamp for two different times

Mary666 — Wed, 10 Mar 2021 02:19:10 GMT

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?

Re: Increase Time on event TimeStamp for two different times

scelikok — Sat, 13 Mar 2021 09:13:00 GMT

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.

Re: Increase Time on event TimeStamp for two different times

Mary666 — Mon, 15 Mar 2021 21:16:16 GMT

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.

Re: Increase Time on event TimeStamp for two different times

scelikok — Tue, 16 Mar 2021 03:53:34 GMT

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 time min is 15 min past the hour - go to next hour and 15 min of the clock.
if latestSub time hour is 23 (11p.m.) - have time go the next day and 15 th min.

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 time falls on the hour and min is 00 - add 15 min.

if latestSub_min=0 then latestSub_epoch+900 --> since we are for min=00 so just ad 15 minutes (900 seconds)

If latestSub time falls exactly on the hour and 15 min - leave as is.

1=1,latestSub_epoch --> Since we already covered all cases keep the latestSub as it is for else

Re: Increase Time on event TimeStamp for two different times

Mary666 — Tue, 16 Mar 2021 06:56:01 GMT

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")