topic Join 3 Queries into one for usage in Dashboard in Splunk Search

Join 3 Queries into one for usage in Dashboard

kishorksudha — Mon, 28 Sep 2020 18:48:46 GMT

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.

Re: Join 3 Queries into one for usage in Dashboard

martin_mueller — Sat, 31 Jan 2015 22:59:58 GMT

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.

Re: Join 3 Queries into one for usage in Dashboard

kishorksudha — Mon, 28 Sep 2020 18:45:50 GMT

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

Re: Join 3 Queries into one for usage in Dashboard

echalex — Sun, 01 Feb 2015 11:30:57 GMT

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.

Re: Join 3 Queries into one for usage in Dashboard

kishorksudha — Fri, 13 Feb 2015 02:16:43 GMT

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

Kishor