How to group all events by date?

[johnnykhoueiry]

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

[gcusello · Accepted solution]

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]

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

[493669]

not understand what are you expecting here as output?

[johnnykhoueiry]

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.

[493669]

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

[johnnykhoueiry]

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