Splunk Search

How to correlate index with dbxquery with condition or interation?

LearningGuy
Motivator

How to correlate index with dbxquery with condition or interation?

See the sample below.   Thank you for your help.
index=company

CompanyIDCompanyNameRevenue
ACompanyA3,000,000
BCompanyB2,000,000
CCompanyC1,000,000


|  dbxquery query="select * from employee where companyID in (A,B,C)"
OR 
Iteration:
|  dbxquery query="select * from employee where companyID ='A'
|  dbxquery query="select * from employee where companyID ='B'
|  dbxquery query="select * from employee where companyID ='B'

CompanyIDEmployeeNameEmployeeEmail
AEmployeeA1empA1@email.com
AEmployeeA2empA2@email.com
AEmployeeA3empA2@email.com
BEmployeeB1empB1@email.com
BEmployeeB2empB2@email.com
BEmployeeB3empB3@email.com
CEmployeeC1empC1@email.com
CEmployeeC2empC2@email.com
CEmployeeC3empC3@email.com


Expected result:

CompanyIDCompanyNameRevenueEmployeeNameEmployeeEmail
ACompanyA3,000,000EmployeeA1empA1@email.com
ACompanyA3,000,000EmployeeA2empA2@email.com
ACompanyA3,000,000EmployeeA3empA2@email.com
BCompanyB2,000,000EmployeeB1empB1@email.com
BCompanyB2,000,000EmployeeB2empB2@email.com
BCompanyB2,000,000EmployeeB3empB3@email.com
CCompanyC1,000,000EmployeeC1empC1@email.com
CCompanyC1,000,000EmployeeC2empC2@email.com
CCompanyC1,000,000EmployeeC3empC3@email.com


OR 

CompanyIDCompanyNameRevenueEmployeeNameEmployeeEmail
ACompanyA3,000,000EmployeeA1, EmployeeA2, EmployeeA3empA1@email.com, empA2@email.com, empA2@email.com
BCompanyB2,000,000EmployeeB1, EmployeeB2, EmployeeB3empB1@email.com, empB2@email.com, empB3@email.com
CCompanyC1,000,000EmployeeC1, EmployeeC2, EmployeeC3empC1@email.com, empC2@email.com, empC3@email.com




Labels (2)
0 Karma

LearningGuy
Motivator

Hello @richgalloway ,
Thanks for your help.   It's odd that I didn't receive notification when you responded.
1) It looks like it also works if I do the index first, then DBX query. 
2) How do I put company ID in the brackets on DBX query dynamically?
    eval variable = .....   A, B, C, ...   Z  (Company ID) 
    where companyID in $variable$

index=company
| append
[ | dbxquery query="select * from employee where companyID in (A,B,C)"
| stats values(*) as * by CompanyID

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Run both queries then combine the results using stats.

| dbxquery query="select * from employee where companyID in (A,B,C)"
| append [search index=company]
| stats values(*) as * by Company ID

 

---
If this reply helps you, Karma would be appreciated.
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...