Adding increments of time to an event

[Mary666]

Hi Splunkers,

I have gotten help on this type of problem and it has been very useful. However, I still stuck, but almost there, need some guidance.  

Scenario:

Ingestion_Time_Logged  which is the field I created should occur twice within 30 min, at min 7th and then min 37th. 

If event occurs at 6:00  Ingestion_Time_Logged should be 6:07 and if event occurs at 6:30 Ingestion_Time_Logged should be 6:37. The min should always land on the next exact 7th min or the next exact  37th. min. 

This is what I have, there is an issue when min is before the 7th min and when min is shy from the 37 th min. 

I am  open to any suggestions, perhaps I need a new approach here. 

(index=foo Type="black") OR (index="boo") 

| eval CreationTime=case(Type="creation", loggedEventTime)

| eval CreationTime_epoch=strptime(CreationTime, "%Y-%m-%d %H:%M:%S.%6N")

| eval latestCreated_hour=tonumber(strftime(CreationTime_epoch, "%H"))

| eval latestCreated_min=tonumber(strftime(CreationTime_epoch, "%M"))

| eval latestCreated_sec=round(CreationTime_epoch%60,6)


| eval Ingestion_Time_Logged=strftime(case(latestCreated_hour=23 OR latestCreated_min>07,CreationTime_epoch-CreationTime_epoch_epoch%1800+2220+latestCreated_sec,CreationTime_epoch=0,CreationTime_epoch+420,1=1,CreationTime_epoch),"%Y-%m-%d %H:%M:%S.%6N")

[scelikok · Accepted solution]

Hi @Mary666,

It was working work between 30 and 37 minutes , I made a change on first case condition to cover the minutes between  0-7 and 30-37, please try below;

(index=foo Type="black") OR (index="boo") 
| eval CreationTime=case(Type="creation", loggedEventTime)
| eval CreationTime_epoch=strptime(CreationTime, "%Y-%m-%d %H:%M:%S.%6N") 
| eval latestCreated_hour=tonumber(strftime(CreationTime_epoch, "%H")) 
| eval latestCreated_min=tonumber(strftime(CreationTime_epoch, "%M")) 
| eval latestCreated_sec=round(CreationTime_epoch%60,6) 
| eval Ingestion_Time_Logged=strftime(case(latestCreated_min%30 < 7, CreationTime_epoch-CreationTime_epoch%1800+420+latestCreated_sec, latestCreated_min!=37 AND latestCreated_min!=7, CreationTime_epoch-CreationTime_epoch%1800+2220+latestCreated_sec,1=1,CreationTime_epoch),"%Y-%m-%d %H:%M:%S.%6N")

[Mary666]

Thank You! You have been very helpful. If you don't mind would like to pick your brain a bit so I can understand your changes:

We are looking for in between 30 min and less than 7 min? Also, what is the % doing here ?

latestComposed_min%30 < 7

Why are we excluding min 37 and 7  -- and why the use of AND here instead of OR? 

latestComposed_min!=37 AND latestComposed_min!=7

[scelikok]

Sure 😀

% is the modulo operator which is a way to determine the remainder of a division operation. Instead of returning the result of the division, the modulo operation returns the whole number remainder.

latestComposed_min%30 will divide the minutes by 30 and result in the reminder. I used it to find if the minute is between 0 - 7 or 30 - 37.  It is the same as below but much easier and efficient.

if(latestComposed_min < 7 OR (latestComposed_min>30 AND latestComposed_min < 37))

I excluded 37 and 7 to keep them as they are since they are already ok, if we do not exclude them, they will be added 37 minutes. Since both are NOT comparisons AND should be used between them otherwise with OR the result will be always true (which is wrong)

I hope I could explain and help you.

[Mary666]

Thanks so much for your explanation it has been very helpful.

[bowesmana]

This is a run anywhere, but the last 2 eval statements will do what you want I believe

| makeresults count=10
| eval _time=_time - random() % 14400
| sort _time
| eval mins=floor(tonumber(strftime(_time, "%M"))/30)*30*60
| eval Ingestion_Time_Logged=strftime(relative_time(_time, "@h+7m")+mins, "%F %T")
| table _time Ingestion_Time_Logged

[Mary666]

Hi bowesmana,

Thanks for your suggestion. I was not able to manipulate the numbers here quite yet. It seems to work, but it does not go up to the next hour - lets say if its 3:37 I need it then to go to 4:07. I see with your code this can easily be manipulated. Perhaps if I understand this a bit more I can get it to work. Would you be able to explain what these two lines of code are doing here?

| eval mins=floor(tonumber(strftime(_time, "%M"))/30)*30*60
| eval Ingestion_Time_Logged=strftime(relative_time(_time, "@h+7m")+mins, "%F %T")

[bowesmana]

I don't think I totally understood your progression from time A to time B, so this may be wrong when you say you want to go from 13:37 to 14:07. What about 13:59 or 14:11?

[bowesmana]

@Mary666 

| eval mins=floor(tonumber(strftime(_time, "%M"))/30)*30*60

• takes the minute part of the event time and converts it to a number
• Divides that by 30 and rounds down to the nearest integer - result will always be 0 or 1
• Multiplies by 30 then 60 (1800) - result will always be 0 or 1800

So this is saying if

• time is 14:58 then floor(58/30) = 1 * 1800 = 1800 (seconds)
• time is 14:03 then floor (3/30) - 0 * 1800 - 0 

which is essentially a way of calculating which half of the hour the current time is in

| eval Ingestion_Time_Logged=strftime(relative_time(_time, "@h+7m")+mins, "%F %T")

and this now says

• round the current hour down to the 00 minutes and then add 7 minutes and then the number of seconds from the first line above (0 or 1800)
• Format it as YYYY-MM-DD HH:MM:SS.sss

so it knows if the time is between xx:00 and xx:29:59 and will do

• number of seconds from first line is 0
• relative time calculation is xx:07 + 0 seconds, so resultant time is xx:07

if time is between xx:30 and xx:59:59 it will

• number of seconds from first line is 1800
• relative time calculation is xx:07 + 1800 seconds, so resultant time is xx:37

Hope this helps and is useful

[scelikok]

Hi @Mary666,

There are some confusion with variables in the case statement. But you are close;

I added 7 minutes if minutes is less than 7, otherwise 37 minutes. Also check if minutes is already 7 OR 37 to keep them as they are. 

Please try below;

(index=foo Type="black") OR (index="boo") 
| eval CreationTime=case(Type="creation", loggedEventTime)
| eval CreationTime_epoch=strptime(CreationTime, "%Y-%m-%d %H:%M:%S.%6N")
| eval latestCreated_hour=tonumber(strftime(CreationTime_epoch, "%H"))
| eval latestCreated_min=tonumber(strftime(CreationTime_epoch, "%M"))
| eval latestCreated_sec=round(CreationTime_epoch%60,6)
| eval Ingestion_Time_Logged=strftime(case(latestCreated_min<7, CreationTime_epoch-CreationTime_epoch%1800+420+latestCreated_sec,latestCreated_min!=37 AND latestCreated_min!=7 , CreationTime_epoch-CreationTime_epoch%1800+2220+latestCreated_sec,1=1,CreationTime_epoch),"%Y-%m-%d %H:%M:%S.%6N")

[Mary666]

Hi scelikok, 

I see one small issue with the time if its 2:30 it gives me 3:07 instead of  2:37 or if its 00:36 it will give me 1:07 instead of 00:37, everything else looks good. I tried playing with the numbers, but no luck yet.