joining multiple select statements in dbxquery

[kishen2017]

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.

[sandyIscream]

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)

[kishen2017]

Thanks a lot for your input

[davebrooking]

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

[cmerriman]

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"

[kishen2017]

Thanks a lot for your input