topic Re: How do I group events by date range? in Splunk Search

How do I group events by date range?

stocksltd — Fri, 05 Feb 2016 20:54:56 GMT

I am trying to combine the STB field by date, but if there is another event within +-1 day, I would like to group those STB into the first date. My results look like this:

LAST_REPORT_DATE    STB           TEL           count
1/31/2016          160767276        5551234567   4
                    160987956       
                    161689512       
                    M91542SI9196        

1/21/2016          M91151EJ4964  5551234567  3
                    M91151EJ4980        
                    M91151EJ4992        

1/22/2016          M91148FA0104  5551234567     1

I'm trying to get it to look like this

LAST_REPORT_DATE    STB           TEL           count
1/31/2016          160767276        5551234567   4
                    160987956       
                    161689512       
                    M91542SI9196        

1/21/2016          M91151EJ4964     5551234567   4
                    M91151EJ4980        
                    M91151EJ4992        
                    M91148FA0104

Re: How do I group events by date range?

somesoni2 — Fri, 05 Feb 2016 22:37:21 GMT

It would be tough to suggest something without knowing your current query/data. Give this a try

Your current search producing above output. | streamstats current=f window=1 values(LAST_REPORT_DATE) as prev | eval LAST_REPORT_DATE=if(abs(strptime(prev,"%m/%d/%Y")-strptime(LAST_REPORT_DATE ,"%m/%d/%Y"))=86400,prev,LAST_REPORT_DATE ) | stats values(STB) as STB values(TEL) as TEL sum(count) as count by LAST_REPORT_DATE

Re: How do I group events by date range?

stocksltd — Tue, 29 Sep 2020 08:41:28 GMT

here are the results before i start grouping

TEL LAST_REPORT_DATE STB STB_TIME
5551234567 1/31/2016 161689512 Wed Feb 03 17:03:08 CST 2016
5551234567 1/31/2016 160987956 Wed Feb 03 18:28:22 CST 2016
5551234567 1/31/2016 160767276 Thu Feb 04 13:49:19 CST 2016
5551234567 1/31/2016 M91542SI9196 Thu Feb 04 12:17:59 CST 2016
5551234567 1/21/2016 M91151EJ4992 Fri Jan 22 20:44:35 CST 2016
5551234567 1/21/2016 M91151EJ4964 Sat Jan 23 02:00:06 CST 2016
5551234567 1/22/2016 M91148FA0104 Mon Jan 25 04:03:42 CST 2016
5551234567 1/21/2016 M91151EJ4980 Sat Jan 23 03:24:47 CST 2016