Join 3 Queries into one for usage in Dashboard

[kishorksudha]

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.

[kishorksudha]

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

[echalex]

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.

[martin_mueller · Accepted solution]

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.

[kishorksudha]

Thanks Martin
It was very helpful and the assumption was right.

Kishor