Archive

joining multiple select statements in dbxquery

Path Finder

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.

Tags (1)
0 Karma

Communicator

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)

Path Finder

Thanks a lot for your input

0 Karma

Contributor

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

0 Karma

Super Champion

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"
0 Karma

Path Finder

Thanks a lot for your input

0 Karma
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes
and swag!