How can I split Splunk query into time ranges?

[coreytoast]

Hi Everyone,

If I am searching through the past 4 weeks in one query, how can I break this data into two columns, one for previous 2 weeks, and one for latest 2 weeks, then sort by Latest 2 weeks?

In general, im using stats to display the amount of objects affected by errors occurring  in a 4 week period but would like to see them displayed in two 2 week periods, sorted by the amount in the latest 2 weeks.

| stats dc(objects) as OBJ by errorMessage

| span -OBJ

CURRENT OUTPUT

ERROR MESSAGE	OBJ
message 1	1792
message 2	1210
message 3	957

DESIRED OUTPUT

ERROR MESSAGE	LATEST 2 WEEKS	PREVIOUS 2 WEEKS
message 1	967	825
message 2	872	666
message 3	103	854

Thanks all,

Corey

[bowesmana · Accepted solution]

Use something like this

...
| bin _time span=2w@w aligntime=@w
| eval t=if(_time < relative_time(now(), "-2w@w"), "Previous", "Latest")
| chart dc(objects) as OBJ over errorMessage by t
| sort - Latest

bin will segregate time into two week sections. t= will then categorise which period the event fits into, then chart will do your tabling.

[coreytoast]

This worked perfectly, thank you so much

[PickleRick]

You can also look into the | timewrap command.

[richgalloway]

Please tell us more about the use case?  What kind of data?  What should the output look like?

[coreytoast]

updated question

[richgalloway]

Use eval to break the results into 2-week periods then have stats group the results by period.

| eval period=if(_time>=relative_time(now(), "-2w"), "LATEST 2 WEEKS", "PREVIOUS 2 WEEKS")
| stats dc(objects) as OBJ by errorMessage, period

[bowesmana]

Basic way to split by _time is to use either

... search ...
| timechart span=2w

or to use an aggregation command splitting by time where you define the window, like this

... search ...
| bin _time span=2w
| stats .... by _time

depending on what you want your output to be will dictate what fits your use case

[coreytoast]

I have updated my question to give more context