topic Re: dbconnect results as a subquery in Splunk Search

dbconnect results as a subquery

tzvikaz — Thu, 22 Apr 2021 07:41:30 GMT

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.

Re: dbconnect results as a subquery

aasabatini — Thu, 22 Apr 2021 08:54:59 GMT

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

Re: dbconnect results as a subquery

tzvikaz — Thu, 22 Apr 2021 09:05:32 GMT

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.

Re: dbconnect results as a subquery

aasabatini — Thu, 22 Apr 2021 09:23:10 GMT

Hi @tzvikaz

try this:

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

Re: dbconnect results as a subquery

tzvikaz — Thu, 22 Apr 2021 09:48:21 GMT

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.

Re: dbconnect results as a subquery

aasabatini — Thu, 22 Apr 2021 10:24:20 GMT

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?

Re: dbconnect results as a subquery

tzvikaz — Thu, 22 Apr 2021 11:30:13 GMT

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

Re: dbconnect results as a subquery

aasabatini — Thu, 22 Apr 2021 12:22:06 GMT

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 ]

Re: dbconnect results as a subquery

tzvikaz — Thu, 22 Apr 2021 12:28:50 GMT

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.

Re: dbconnect results as a subquery

tzvikaz — Sun, 25 Apr 2021 08:02:19 GMT

Any idea then?

Re: dbconnect results as a subquery

aasabatini — Mon, 26 Apr 2021 07:45:34 GMT

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]

Re: dbconnect results as a subquery

splunk0 — Mon, 26 Apr 2021 13:13:41 GMT

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!