Splunk Search

How to edit my search to find the sum for the latest events with subsearch?

benchdba
New Member

Hi,

I am very new to Splunk and have a question about subsearch.
I have some events with the following fields and data:

REC_DATE          DB_NAME    FILE_NAME   TotalMB
20161116165944    PROD      F1          50
20161116165944    PROD      F2          40
20161116165944    PROD      F3          110

20161116170143  PROD        F1          50
20161116170143  PROD        F2          30
20161116170143  PROD        F3          90

20161116170316    DEV       F1          30
20161116170316    DEV       F2          10
20161116170316    DEV       F3          70
20161116170316    DEV       F4          80

20161116192215  DEV         F1          40
20161116192215  DEV         F2          10
20161116192215  DEV         F3          90
20161116192215  DEV         F4          90

I need to have a result like below:

DB_NAME     latest REC_DATE        sum(TotalMB)
PROD        20161116170143         1700
DEV         20161116192215         2300

I could get a sum of TotalMB for each DB_NAME, but do not know how to filter this in order to get the latest one:

index=dba_log sourcetype=space_usage | stats sum(TotalMB)  by REC_DATE DB_NAME

I think the use of subsearch should help.
Thank you for any help.

-Den

0 Karma

rjthibod
Champion

How about this?

index=dba_log sourcetype=space_usage | eventstats latest(REC_DATE) as latest_rec by DB_NAME | where latest_rec=REC_DATE | stats sum(TotalMB) as TotalMB by DB_NAME REC_DATE
0 Karma

davpx
Communicator

Try this

index=dba_log sourcetype=space_usage | stats sum(TotalMB) as TotalMB latest(REC_DATE) as REC_DATE by DB_NAME
0 Karma

benchdba
New Member

Thanks but the result returns the latest REC_DATE but the sum(TotalMB) includes all TotalMB with earlier REC_DATE for the same DB_NAME.

DB_NAME latest REC_DATE sum(TotalMB)
PROD 20161116170143 3700
DEV 20161116192215 4200

The correct result should be included only the sum(TotalMB) just for the latest REC_DATE for each DB_NAME.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...