dbconnect results as a subquery

[tzvikaz]

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.

[tzvikaz]

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.

[aasabatini]

Hi @tzvikaz 

try this:

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

[tzvikaz]

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.

[aasabatini]

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?

[tzvikaz]

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" ) )

[tzvikaz]

Any idea then?

[aasabatini · Accepted solution]

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]

[splunk0]

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!

[aasabatini]

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 ]

[tzvikaz]

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.

[aasabatini]

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