All Apps and Add-ons

How to group all events by date?

johnnykhoueiry
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

gcusello
SplunkTrust
SplunkTrust

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

gcusello
SplunkTrust
SplunkTrust

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

johnnykhoueiry
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

493669
Super Champion

not understand what are you expecting here as output?

0 Karma

johnnykhoueiry
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

493669
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

johnnykhoueiry
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...