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!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...