Splunk Search

How to define time period?

simon1524
Explorer

For example, the "SUBMIT_DATE" is split by date and time. Then define some period of time as a value(A/B/C). Can this be achieved?

(SUBMIT_DATE="2021-03-09 14:30:48.0")  ==> Split to "2021-03-09" and "14:30:48.0"

0:00:00 - 8:00:00 = A

8:00:00 - 16:00:00 = B

16:00:00 - 0:00:00 = C

 
 

 

 

Labels (1)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

If I understand this correctly, you want to define a function that outputs "A", "B", or "C" according to which 8-hour interval the time of day in SUBMIT_DATE falls into.  In other words, you are looking for a case function.

| eval SUBMIT_DATE = split(SUBMIT_DATE, " ")
| eval date = mvindex(SUBMITE_DATE, 0), time_of_day = strptime(mvindex(SUBMIT_DATE, 1), "%H:%M:%S")
| eval shift = case(time_of_day < 28800, "A", 28800 <= time_of_day AND time_of_day < 57600, "B", true(), "C")

Does this help? Note that time_of_day is in number of seconds from 00:00:00 whereas date is still in string format.  If you want to convert that to epoch, use strptime again.

Also note that is perhaps not the most elegant to split SUBMIT_DATE into substrings before converting to numeric.  But the formula should work.

View solution in original post

Tags (1)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

Can you explain what "Then define some period of time as a value(A/B/C)" means?  You have already defined A, B, and C in your description.  What is missing?

To split SUBMIT_DATE is simple,

| eval SUBMIT_DATE = split(SUBMIT_DATE, " ")
| eval part1 = mvindex(SUBMIT_DATE, 0), part2 = mvindex(SUBMIT_DATE, 1)

 

Tags (2)
0 Karma

simon1524
Explorer

I want to split (SUBMIT_DATE) into “date” and “time”.
Then define a value for 8 hours period, such as below.
0:00:00 - 8:00:00 = A

8:00:00 - 16:00:00 = B

16:00:00 - 0:00:00 = C

0 Karma

yuanliu
SplunkTrust
SplunkTrust

Still unclear what "a value for 8 hours period" is supposed to mean.  In number of seconds?  An time (epoch) value starting from that "date" with offset of 8 hours? Some sort of string (as you have already defined)?  There can be millions of interpretations of this phrase.

0 Karma

simon1524
Explorer

According to the data on the system, it should be accurate to the second.

| eval SUBMIT_DATE = split(SUBMIT_DATE, " ")
| eval part1 = mvindex(SUBMIT_DATE, 0), part2 = mvindex(SUBMIT_DATE, 1)


According to the function you give

"00:00:00 > part2 >= 08:00:00" as A

"08:00:00 > part2 >= 16:00:00" as B

"16:00:00 > part2 >= 00:00:00" as C

I wonder how to define it...

0 Karma

yuanliu
SplunkTrust
SplunkTrust

If I understand this correctly, you want to define a function that outputs "A", "B", or "C" according to which 8-hour interval the time of day in SUBMIT_DATE falls into.  In other words, you are looking for a case function.

| eval SUBMIT_DATE = split(SUBMIT_DATE, " ")
| eval date = mvindex(SUBMITE_DATE, 0), time_of_day = strptime(mvindex(SUBMIT_DATE, 1), "%H:%M:%S")
| eval shift = case(time_of_day < 28800, "A", 28800 <= time_of_day AND time_of_day < 57600, "B", true(), "C")

Does this help? Note that time_of_day is in number of seconds from 00:00:00 whereas date is still in string format.  If you want to convert that to epoch, use strptime again.

Also note that is perhaps not the most elegant to split SUBMIT_DATE into substrings before converting to numeric.  But the formula should work.

Tags (1)
0 Karma

simon1524
Explorer

Thank you!

Got the first step done by your query.

| eval SUBMITED_DATE = split(SUBMITED_DATE, " ")
| eval date = mvindex(SUBMITED_DATE, 0), time_of_day = strptime(mvindex(SUBMITED_DATE, 1), "%H:%M:%S")
| eval shift = case(time_of_day < 1664258400, "A", 1664258400 <= time_of_day AND time_of_day < 1664287200, "B", true(), "C")

The next step is to count the number of each month

| timechart span = 1mon count(eval(shift = "A")) as first_shift, count(eval(shift = "B")) as second_shift, count(eval(shift = "C")) as third_shift
| tail 12
| sort _time

As you see my query, I want to count every shift in every month of the last year. But there is something wrong with above query...

 
 

 

 

0 Karma

yuanliu
SplunkTrust
SplunkTrust

Without an illustration, it is unclear what "count every shift in every month" really means.  If you want to know how many events per month are in each shift, i.e., count by shift, you can use

| timechart span=1mon count by shift
| tail 12

 Is this what you require?

0 Karma

simon1524
Explorer

Thank you very much! The result is showing now~

 
 

 

 

0 Karma
Get Updates on the Splunk Community!

Security Highlights | November 2022 Newsletter

 November 2022 2022 Gartner Magic Quadrant for SIEM: Splunk Named a Leader for the 9th Year in a RowSplunk is ...

Platform Highlights | November 2022 Newsletter

 November 2022 Skill Up on Splunk with our New Builder Tech Talk SeriesCan you build it? Yes you can! *play ...

Splunk Education - Fast Start Program!

Welcome to Splunk Education! Splunk training programs are designed to enable you to get started quickly and ...