topic Re: joining multiple select statements in dbxquery in Splunk Search

joining multiple select statements in dbxquery

kishen2017 — Tue, 29 Sep 2020 17:13:24 GMT

How to join multiple select statements in dbxquery
Need to display output as
Total Defects 532
Open defects 147
Closed defect 385
I have individual select statements for each row
select count(bug_id) as "Total Defects" from bug
select count(bug_status) as "Open defects" from bug where bug_status='Open'
select count(bug_status) as "Open defects" from bug where bug_status='Close'

Need to know how to join these individual select statements on dbxquery to produce output as mentioned.

Re: joining multiple select statements in dbxquery

cmerriman — Tue, 12 Dec 2017 13:20:55 GMT

can you try something like this:

|dbxquery connection=db maxrows=0 shortnames=t query="select bug_id, bug_status from bug where bug_status in('Open','Close')"|eval open_defects=if(bug_status="Open",1,0)|eval closed_defects=if(bug_status="Close",1,0)|stats count(bug_id) as "Total Defects" sum(open_defects) as "Open Defects" sum(closed_defects) as "Closed Defects"

Re: joining multiple select statements in dbxquery

sandyIscream — Tue, 29 Sep 2020 17:13:32 GMT

Hi Kishen2017,

This is possible in Splunk. Try to construct your query like the below format.

| dbxquery query="select count(bug_id) as "Total Defects" from bug" connection="your connection name" | appendcols [| dbxquery query="select count(bug_status) as "Open defects" from bug where bug_status='Open'" connection="your connection name" ] | appendcols [| dbxquery query="select count(bug_status) as "Open defects" from bug where bug_status='Close'" connection="your connection name"]

You can try by replacing append with appendcols also. (this is as per your convenient)

Re: joining multiple select statements in dbxquery

kishen2017 — Tue, 12 Dec 2017 13:45:36 GMT

Thanks a lot for your input

Re: joining multiple select statements in dbxquery

kishen2017 — Tue, 12 Dec 2017 13:46:01 GMT

Thanks a lot for your input

Re: joining multiple select statements in dbxquery

davebrooking — Tue, 12 Dec 2017 13:54:35 GMT

Another alternative would be to use the SQL UNION operator. So you'd have a single dbxquery that executed the SQL statement

select count(bug_id) as "Total Defects" from bug
UNION
select count(bug_status) as "Open defects" from bug where bug_status='Open'
UNION
select count(bug_status) as "Open defects" from bug where bug_status='Close'

Regards

Dave