Splunk Search
Highlighted

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

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
Highlighted

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

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
Highlighted

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

New Member

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

DBNAME latest RECDATE sum(TotalMB)
PROD 20161116170143 3700
DEV 20161116192215 4200

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

0 Karma
Highlighted

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

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