I am getting the feeds from Database into Splunk every 15 minutes; using 3 Queries to get the desired results; can you please help me to combine all these three into a single query
Step 1: Find all the Unique IDS (PAY_TXN_ID)
index=foo source="dbmon-tail://Dev/test-DEV" | dedup PAY_TXN_ID | table PAY_TXN_ID
Step2: For each of the Unique IDS obtained from previous query; get the latest Time stamp (AUDIT_TIME)
index=foo source="dbmon-tail://Dev/test-DEV" | search PAY_TXN_ID="3225958" | table AUDIT_TIME | sort -AUDIT_TIME | head 1
Step3: For each of the Unique IDS PAY_TXN_ID (step1)and the latest Time Stamp for that ID(step 2) get all the required details - Desired Results
index=foo source="dbmon-tail://Dev/test-DEV" | search PAY_TXN_ID="3225958" | search AUDIT_TIME="1345618062.375" | table PAY_TXN_ID, SOURCE, STATUS, DEBIT_CCY, DEBIT_AMNT, REMIT_CCY, REMIT_AMNT
Can someone help me to convert these three queries into a single one.
Try this:
index=foo source="dbmon-tail://Dev/test-DEV" | eventstats max(AUDIT_TIME) as max_audit_time by PAY_TXN_ID | where AUDIT_TIME = max_audit_time | table PAY_TXN_ID, SOURCE, STATUS, DEBIT_CCY, DEBIT_AMNT, REMIT_CCY, REMIT_AMNT
NB: I have assumed that AUDIT_TIME
values are unique per PAY_TXN_ID
. If that's not the case you will get all events with the latest AUDIT_TIME
for one PAY_TXN_ID
.
Thank you very much Martin; it was very helpful.
your assumption was right; AUDIT_TIME values are unique per PAY_TXN_ID.
AUDIT_TIME is the rising_column when the datas are being pulled from the Database.
Thanks
Kishor
Hi kishorksudha. If Martin's answer was helpful, I suggest you click on "accept answer" and possibly vote up the answer as well. Furthermore, the preferred way of replying would be to comment on the answer, rather than posting a new answer.
Try this:
index=foo source="dbmon-tail://Dev/test-DEV" | eventstats max(AUDIT_TIME) as max_audit_time by PAY_TXN_ID | where AUDIT_TIME = max_audit_time | table PAY_TXN_ID, SOURCE, STATUS, DEBIT_CCY, DEBIT_AMNT, REMIT_CCY, REMIT_AMNT
NB: I have assumed that AUDIT_TIME
values are unique per PAY_TXN_ID
. If that's not the case you will get all events with the latest AUDIT_TIME
for one PAY_TXN_ID
.
Thanks Martin
It was very helpful and the assumption was right.
Kishor