All Apps and Add-ons

How to group all events by date?

Explorer

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

0 Karma
1 Solution

Legend

Hi johnnykhoueiry,
see this imple example taht I think could solve your need:

index=_internal 
| head 10000 
| stats count BY sourcetype component 
| streamstats count(component) AS my_sourcetype BY sourcetype 
| eval my_sourcetype=if(my_sourcetype=1,sourcetype,"") 
| table my_sourcetype component count

In other words, you should try something like this

index = mail sourcetype = imap *Europe* AND *XPOS* 
| where DATE = "13-Feb-2018"
| rex field=_raw >(?P<DEVICEDID_OFFLINE>XPOS\w+)\s-.+offline
| rex field=_raw >(?P<DEVICEDID_ONLINE>XPOS\w+)\s-.+online
| rex field=_raw \s-\s\w.+\s-\s\w.+\s\w.+-\sWent\soffline\s-\s(?P<Offline_Date>\w.+)\s-
| table DEVICEDID_OFFLINE, DATE , Offline_Date 
| sort DATE, DEVICEDID_OFFLINE 
| stats  list(Offline_Date) as "Offline Time" count(Offline_Date) as NUMBER_OFFLINE by  DATE,DEVICEDID_OFFLINE
| streamstats count(DEVICEDID_OFFLINE) AS DEVICE_count BY DATE
| streamstats count("Offline Time") AS Offline_Time_count BY DEVICEDID_OFFLINE
| eval MY_DATE=if(DEVICE_count=1,DEVICEDID_OFFLINE,"") 
| eval MY_DEVICEDID_OFFLINE=if(Offline_Time_count=1,DEVICEDID_OFFLINE,"") 
| table MY_DATE MY_DEVICEDID_OFFLINE "My Offline Time" count

Please understand the approach, I'm not sure about your fields!

Bye.
Giuseppe

View solution in original post

Legend

Hi johnnykhoueiry,
see this imple example taht I think could solve your need:

index=_internal 
| head 10000 
| stats count BY sourcetype component 
| streamstats count(component) AS my_sourcetype BY sourcetype 
| eval my_sourcetype=if(my_sourcetype=1,sourcetype,"") 
| table my_sourcetype component count

In other words, you should try something like this

index = mail sourcetype = imap *Europe* AND *XPOS* 
| where DATE = "13-Feb-2018"
| rex field=_raw >(?P<DEVICEDID_OFFLINE>XPOS\w+)\s-.+offline
| rex field=_raw >(?P<DEVICEDID_ONLINE>XPOS\w+)\s-.+online
| rex field=_raw \s-\s\w.+\s-\s\w.+\s\w.+-\sWent\soffline\s-\s(?P<Offline_Date>\w.+)\s-
| table DEVICEDID_OFFLINE, DATE , Offline_Date 
| sort DATE, DEVICEDID_OFFLINE 
| stats  list(Offline_Date) as "Offline Time" count(Offline_Date) as NUMBER_OFFLINE by  DATE,DEVICEDID_OFFLINE
| streamstats count(DEVICEDID_OFFLINE) AS DEVICE_count BY DATE
| streamstats count("Offline Time") AS Offline_Time_count BY DEVICEDID_OFFLINE
| eval MY_DATE=if(DEVICE_count=1,DEVICEDID_OFFLINE,"") 
| eval MY_DEVICEDID_OFFLINE=if(Offline_Time_count=1,DEVICEDID_OFFLINE,"") 
| table MY_DATE MY_DEVICEDID_OFFLINE "My Offline Time" count

Please understand the approach, I'm not sure about your fields!

Bye.
Giuseppe

View solution in original post

Explorer

This wasnt the exact answer i was looking for, but helped opening my eyes on the streamstats count and the match "" when =1. Thanks !

0 Karma

Super Champion

not understand what are you expecting here as output?

0 Karma

Explorer

I want to group the events by the DATE as provided in the .txt screenshot. My grouping by DATE and DEVICE is not returning the desired output. i want a single date for the output.

0 Karma

Super Champion

why dont you group by DATE only like:

...|stats  list(Offline_Date) as "Offline Time" count(Offline_Date) as NUMBER_OFFLINE values(*) as * by  DATE
0 Karma

Explorer

Not the expected result, now i dont have a matching offline time with device here was the output:

DATE                      Count                 DEVICE                Offline time
13-Feb-2018     32          XPOS20430       date1
                            XPOS20431       dat2
                            XPOS20432       dat3
                            XPOS20433       date4
                                            datex
                                            datex
                                            datex
                                            datex
                                            datex
                                            datex
                                            datex
                                            datex
                                            datex
                                            datex
                                            datex
                                            datex
                                            datex
                                            datex
                                            datex
                                            datex
                                            datex
                                            datex
0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!