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.
index=dba_log sourcetype=space_usage | stats sum(TotalMB) as TotalMB latest(REC_DATE) as REC_DATE by DB_NAME
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.
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