topic Re: How to define time period in Splunk Search

How to define time period?

simon1524 — Tue, 27 Sep 2022 13:33:09 GMT

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

Re: How to define time period

yuanliu — Tue, 27 Sep 2022 05:57:33 GMT

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)

Re: How to define time period

simon1524 — Tue, 27 Sep 2022 06:02:36 GMT

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

Re: How to define time period

yuanliu — Tue, 27 Sep 2022 06:08:09 GMT

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.

Re: How to define time period

simon1524 — Tue, 27 Sep 2022 06:28:16 GMT

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...

Re: How to define time period

yuanliu — Tue, 27 Sep 2022 07:13:50 GMT

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.

Re: How to define time period

simon1524 — Wed, 28 Sep 2022 01:29:48 GMT

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...

Re: How to define time period

yuanliu — Wed, 28 Sep 2022 05:51:19 GMT

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?

Re: How to define time period

simon1524 — Wed, 28 Sep 2022 05:59:52 GMT

Thank you very much! The result is showing now~