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.
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)
Thanks a lot for your input
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
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"
Thanks a lot for your input