Splunk Search

dbconnect results as a subquery

tzvikaz
Explorer

trying to do something like:

index=someindex action=someaction | where city_id in ([search dbxquery query="select city_id from Cities  where Country="USA" connection="SQLserver" ])

The dbxquerry will return 1 or more results

obviously, this is a malformed syntax.

Any idea how I pull this off?

 

Thanks.

 

Labels (1)
0 Karma
1 Solution

aasabatini
Builder

Hi @tzvikaz 

sorry for the late reply, I think the best way is use the rename outside the subsearch, try like this.

(sorry if the previous search doesn't works but is not really easy understand the request without see the subset of data)

index=someindex action=someaction | rename event_city_id_field as city_id | search [search dbxquery query="select city_id from Cities  where Country="USA" connection="SQLserver" | fields city_id]

View solution in original post

tzvikaz
Explorer

Thanks,

 

Im not looking to join. I do want to search the results of dbxquery on splunk index.

But I am not sure your example is good enough.

the "translated" Splunk SPL should look something like:

 

index=someindex action=someaction | WHERE city_id IN(10, 3, 223,2324 12323)

 

 

10,3,223, ... are the results returned from the DBquery.

So I want to search a specific field (for several options, hence the WHERE clause) in the events and not in all fields.

I hope I am making sense.

 

0 Karma

aasabatini
Builder

Hi @tzvikaz 

try this:

index=someindex action=someaction [search dbxquery query="select city_id from Cities  where Country="USA" connection="SQLserver" | fields city_id ]
0 Karma

tzvikaz
Explorer

I get no results. 

how does this compare the event fields to the db field? in no point to I write in this SPL the field name in the splunk event.

 

 

0 Karma

aasabatini
Builder

Hi @tzvikaz 

 

 

are you sure you have the city_id info on the :

index=someindex

Also if you run 

dbxquery query="select city_id from Cities  where Country="USA" connection="SQLserver" | fields city_id | format

 what is the results?

0 Karma

tzvikaz
Explorer

Your comment made me realize I should do a field rename to match the fields so I added a rename directive.

Still didn't help

running only the query with format gives something like

( ( city_id="259186" ) OR ( city_id="259732" ) OR ( city_id="259733" ) OR ( city_id="259734" ) OR ( city_id="259735" ) OR ( city_id="259768" ) OR ( city_id="259846" ) OR ( city_id="259851" ) OR ( city_id="259864" ) OR ( city_id="260088" ) OR ( city_id="260827" ) OR ( city_id="260831" ) OR ( city_id="261115" ) OR ( city_id="261116" ) OR ( city_id="261916" ) OR ( city_id="262012" ) OR ( city_id="262390" ) OR ( city_id="262394" ) OR ( city_id="262501" ) OR ( city_id="262620" ) OR ( city_id="262655" ) OR ( city_id="262661" ) OR ( city_id="262877" ) OR ( city_id="262878" ) OR ( city_id="262906" ) OR ( city_id="262907" ) OR ( city_id="263650" ) OR ( city_id="263945" ) OR ( city_id="263971" ) OR ( city_id="265786" ) OR ( city_id="265805" ) OR ( city_id="266123" ) OR ( city_id="266231" ) OR ( city_id="266246" ) OR ( city_id="269376" ) OR ( city_id="270357" ) OR ( city_id="272283" ) OR ( city_id="272384" ) OR ( city_id="275371" ) OR ( city_id="277011" ) OR ( city_id="277208" ) OR ( city_id="277500" ) OR ( city_id="277652" ) OR ( city_id="277673" ) OR ( city_id="278081" ) OR ( city_id="278116" ) OR ( city_id="279034" ) OR ( city_id="279144" ) OR ( city_id="279487" ) OR ( city_id="279515" ) OR ( city_id="279677" ) OR ( city_id="279977" ) OR ( city_id="279991" ) OR ( city_id="280287" ) OR ( city_id="280291" ) OR ( city_id="280602" ) OR ( city_id="280690" ) OR ( city_id="280695" ) OR ( city_id="280921" ) OR ( city_id="281026" ) OR ( city_id="281414" ) OR ( city_id="281481" ) OR ( city_id="282798" ) OR ( city_id="282917" ) OR ( city_id="283024" ) OR ( city_id="283088" ) OR ( city_id="283094" ) OR ( city_id="283132" ) OR ( city_id="283133" ) OR ( city_id="283148" ) OR ( city_id="283149" ) OR ( city_id="283175" ) OR ( city_id="283266" ) OR ( city_id="283734" ) OR ( city_id="283802" ) OR ( city_id="284024" ) OR ( city_id="284122" ) OR ( city_id="284453" ) OR ( city_id="284473" ) OR ( city_id="285711" ) OR ( city_id="285777" ) OR ( city_id="286354" ) OR ( city_id="286479" ) OR ( city_id="286507" ) OR ( city_id="286649" ) OR ( city_id="286652" ) OR ( city_id="286653" ) OR ( city_id="286654" ) OR ( city_id="286655" ) OR ( city_id="286656" ) OR ( city_id="286657" ) OR ( city_id="286829" ) OR ( city_id="286857" ) OR ( city_id="287004" ) OR ( city_id="287073" ) OR ( city_id="287272" ) OR ( city_id="287430" ) OR ( city_id="287809" ) OR ( city_id="288027" ) OR ( city_id="288298" ) OR ( city_id="288716" ) OR ( city_id="289860" ) OR ( city_id="289984" ) OR ( city_id="290007" ) OR ( city_id="290055" ) OR ( city_id="290435" ) OR ( city_id="290625" ) OR ( city_id="290686" ) OR ( city_id="290787" ) OR ( city_id="290789" ) OR ( city_id="290904" ) OR ( city_id="290989" ) OR ( city_id="291061" ) OR ( city_id="291108" ) OR ( city_id="291216" ) OR ( city_id="291222" ) OR ( city_id="291405" ) OR ( city_id="291434" ) OR ( city_id="291449" ) OR ( city_id="291463" ) OR ( city_id="291997" ) OR ( city_id="292187" ) OR ( city_id="292625" ) OR ( city_id="292884" ) OR ( city_id="293188" ) )

 

 

0 Karma

tzvikaz
Explorer

Any idea then?

0 Karma

aasabatini
Builder

Hi @tzvikaz 

sorry for the late reply, I think the best way is use the rename outside the subsearch, try like this.

(sorry if the previous search doesn't works but is not really easy understand the request without see the subset of data)

index=someindex action=someaction | rename event_city_id_field as city_id | search [search dbxquery query="select city_id from Cities  where Country="USA" connection="SQLserver" | fields city_id]

View solution in original post

splunk0
Path Finder

OK, got it to work like so:

 

index=someindex action=someaction |join event_city_id_field [|dbxquery query="select city_id from Cities  where Country="USA" connection="SQLserver" | rename city_id as event_city_id_field| fields event_city_id_field]

I needed to add the join directive + replace "search" with "|"

Really appreciate your help!

 

0 Karma

aasabatini
Builder

ok, in this case you need to rename the field have the same values of city_id

example:

 

 

index=someindex action=someaction | rename <field with the same cit_id values> as city_id | [search dbxquery query="select city_id from Cities  where Country="USA" connection="SQLserver"| fields city_id ]

 

 

0 Karma

tzvikaz
Explorer

I did. inside the subsearch and not like you wrote (which gives a syntax error)

 

 

index=someindex action=someaction [search dbxquery query="select city_id from Cities  where Country="USA" connection="SQLserver" | rename city_id as event_city_id_field| fields event_city_id_field]

 

 

 

If I just append the result of the formatted subsearch to the main search it works.

 

so something like this works:

 

 

index=someindex action=someaction AND ( ( event_city_id_field="259186" ) OR ( event_city_id_field="259732" ) OR ( event_city_id_field="259733" ) OR ( event_city_id_field="262012" ) OR ( event_city_id_field="262390" ) OR ( event_city_id_field="262394" ) OR ( event_city_id_field="262501" ) OR ( event_city_id_field="262620" ) OR ( event_city_id_field="262655" ) OR ( event_city_id_field="262661" ) OR ( event_city_id_field="262877" ) OR ( event_city_id_field="262878" ) OR ( event_city_id_field="262906" ) OR ( event_city_id_field="262907" ) OR ( event_city_id_field="263650" ) OR ( event_city_id_field="263945" ) OR ( event_city_id_field="263971" ) OR ( event_city_id_field="265786" ) OR ( event_city_id_field="265805" ) OR ( event_city_id_field="266123" ) OR ( event_city_id_field="266231" ) OR ( event_city_id_field="266246" ) OR ( event_city_id_field="269376" ) OR ( event_city_id_field="270357" ) OR ( event_city_id_field="272283" ) OR ( event_city_id_field="283175" ) OR ( event_city_id_field="283266" ) OR ( event_city_id_field="283734" ) OR ( event_city_id_field="283802" ) OR ( event_city_id_field="284024" ) OR ( event_city_id_field="284122" ) OR ( event_city_id_field="284453" ) OR ( event_city_id_field="284473" ) OR ( event_city_id_field="287272" ) OR ( event_city_id_field="287430" ) OR ( event_city_id_field="287809" ) OR ( event_city_id_field="288027" ) OR ( event_city_id_field="292884" ) OR ( event_city_id_field="293188" ) )

 

 

So no idea why it doesn't in the way I mentioned.

0 Karma

aasabatini
Builder

Hi @tzvikaz 

Can you explain better the scope of the search?

if  you need a join  try:

index=someindex action=someaction | join city_id [search dbxquery query="select city_id from Cities  where Country="USA" connection="SQLserver" ]

but be careful because the subsearch works better with a small subset of data.

However if you want search the results of dbxquery on splunk index you can use:

index=someindex action=someaction [search dbxquery query="select city_id from Cities  where Country="USA" connection="SQLserver" ]

but to understand better the scope I need more details

Alessandro

 

0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!