do count of a field name with condition in search query

[man03359]

Hi All,

Below is my query-

index=idx-sec-cloud sourcetype=rubrik:json  NOT summary="*on demand backup*"
(custom_details.eventName="Snapshot.BackupFailed" NOT (custom_details.errorId="Oracle.RmanStatusDetailsEmpty")) 
OR (custom_details.eventName="Mssql.LogBackupFailed") 
OR (custom_details.eventName="Snapshot.BackupFromLocationFailed" NOT (custom_details.errorId="Fileset.FailedDataThresholdNas" OR custom_details.errorId="Fileset.FailedFileThresholdNas" OR custom_details.errorId="Fileset.FailedToFindFilesNas")) 
OR (custom_details.eventName="Vmware.VcenterRefreshFailed")
OR (custom_details.eventName="Hawkeye.IndexOperationOnLocationFailed")
OR (custom_details.eventName="Hawkeye.IndexRetryFailed")
OR (custom_details.eventName="Storage.SystemStorageThreshold")
OR (custom_details.eventName="ClusterOperation.DiskLost")
OR (custom_details.eventName="ClusterOperation.DiskUnhealthy")
OR (custom_details.eventName="Hardware.DimmError")
OR (custom_details.eventName="Hardware.PowerSupplyNeedsReplacement")
| eventstats earliest(_time) AS early_time latest(_time) AS late_time

I am trying to write a condition that excludes all custom_details.eventName="Mssql.LogBackupFailed" (check line 3) unless it the count is greater than 2.

Now sure how to proceed.

Thanks in advance.

[richgalloway · Accepted solution]

One of these should work, depending on which count must be greater than 2

index=idx-sec-cloud sourcetype=rubrik:json  NOT summary="*on demand backup*"
(custom_details.eventName="Snapshot.BackupFailed" NOT (custom_details.errorId="Oracle.RmanStatusDetailsEmpty")) 
OR (custom_details.eventName="Snapshot.BackupFromLocationFailed" NOT (custom_details.errorId="Fileset.FailedDataThresholdNas" OR custom_details.errorId="Fileset.FailedFileThresholdNas" OR custom_details.errorId="Fileset.FailedToFindFilesNas")) 
OR (custom_details.eventName="Vmware.VcenterRefreshFailed")
OR (custom_details.eventName="Hawkeye.IndexOperationOnLocationFailed")
OR (custom_details.eventName="Hawkeye.IndexRetryFailed")
OR (custom_details.eventName="Storage.SystemStorageThreshold")
OR (custom_details.eventName="ClusterOperation.DiskLost")
OR (custom_details.eventName="ClusterOperation.DiskUnhealthy")
OR (custom_details.eventName="Hardware.DimmError")
OR (custom_details.eventName="Hardware.PowerSupplyNeedsReplacement")
| eventstats count
| where (count > 2 AND (custom_details.eventName="Mssql.LogBackupFailed")) OR count <=2 OR OR (custom_details.eventName!="Mssql.LogBackupFailed") 
| eventstats earliest(_time) AS early_time latest(_time) AS late_time

index=idx-sec-cloud sourcetype=rubrik:json  NOT summary="*on demand backup*"
(custom_details.eventName="Snapshot.BackupFailed" NOT (custom_details.errorId="Oracle.RmanStatusDetailsEmpty")) 
OR (custom_details.eventName="Snapshot.BackupFromLocationFailed" NOT (custom_details.errorId="Fileset.FailedDataThresholdNas" OR custom_details.errorId="Fileset.FailedFileThresholdNas" OR custom_details.errorId="Fileset.FailedToFindFilesNas")) 
OR (custom_details.eventName="Vmware.VcenterRefreshFailed")
OR (custom_details.eventName="Hawkeye.IndexOperationOnLocationFailed")
OR (custom_details.eventName="Hawkeye.IndexRetryFailed")
OR (custom_details.eventName="Storage.SystemStorageThreshold")
OR (custom_details.eventName="ClusterOperation.DiskLost")
OR (custom_details.eventName="ClusterOperation.DiskUnhealthy")
OR (custom_details.eventName="Hardware.DimmError")
OR (custom_details.eventName="Hardware.PowerSupplyNeedsReplacement")
| eventstats count(eval(custom_details.eventName="Mssql.LogBackupFailed"))
| where (count > 2 AND (custom_details.eventName="Mssql.LogBackupFailed")) OR count <=2 OR OR (custom_details.eventName!="Mssql.LogBackupFailed") 
| eventstats earliest(_time) AS early_time latest(_time) AS late_time

[man03359]

Hi @richgalloway ,

Thanks a lot for the help! the query worked for me 🙂

 Good day!