topic Re: How to create a Splunk DB Connect 2 lookup with query parameters? in All Apps and Add-ons

How to create a Splunk DB Connect 2 lookup with query parameters?

cmccormick — Tue, 22 Dec 2015 20:15:58 GMT

Hello,

I have a table in my database that records changes to a record in my people table. I have a trigger that inserts the new data into the historical table when the record changes and timestamps it. I need to do a lookup on that table based on username and the date on the event in Splunk.

I am trying to create a dblookup using DB Connect that will run a query like the following:

SELECT TOP 1 Meta_LogDate, FirstName, LastName, Region
FROM People_Historical
WHERE UserName = $UserName$
AND Meta_LogDate <= $LogDate$
ORDER BY Meta_LogDate DESC

How can I do this with Splunk DB Connect 2? The interface does not allow me to create/add parameters to the advanced query when creating the lookup.

Re: How to create a Splunk DB Connect 2 lookup with query parameters?

woodcock — Tue, 22 Dec 2015 23:40:08 GMT

Do not use the interface; just go to Settings -> Data inputs -> Splunk DB Connect Input Service -> Add New.

Re: How to create a Splunk DB Connect 2 lookup with query parameters?

cmccormick — Wed, 23 Dec 2015 21:37:12 GMT

This does not really answer my question...

I am wanting to create a DB lookup that allows me to pass in to input values to an advanced query, but I am not able to figure out how I need to format my query in Splunk. I have tried the format like I originally included and also this format.

 SELECT TOP 1 Meta_LogDate, FirstName, LastName, Region
 FROM People_Historical
 WHERE UserName = {{UserName}}
 AND Meta_LogDate <= {{LogDate}}
 ORDER BY Meta_LogDate DESC

Then tried to use the following search, without success.

source=actionlog | lookup db_connect_HistoricalPeople UserName, LogDate OUTPUT FirstName, LastName, Region

How would I format the query to allow me to pass the inputs from the search?

Re: How to create a Splunk DB Connect 2 lookup with query parameters?

woodcock — Tue, 16 Feb 2016 02:19:32 GMT

I have not used dblookup but I know that dbxquery (V2) is different from dbquery (V1) in that you have to encode your SLQ statement ("SELECT string") so perhaps this is required for dblokup, too. Try that; I use this web tool:

http://meyerweb.com/eric/tools/dencoder/

Re: How to create a Splunk DB Connect 2 lookup with query parameters?

cmccormick — Thu, 14 Apr 2016 21:40:47 GMT

Unfortunately, that did not work. However, I have switched to indexing the data and just using a join.

Re: How to create a Splunk DB Connect 2 lookup with query parameters?

woodcock — Tue, 26 Apr 2016 13:49:53 GMT

Always avoid using join and try to use stats instead.

Re: How to create a Splunk DB Connect 2 lookup with query parameters?

raghu0463 — Thu, 10 Aug 2017 03:04:23 GMT

Hello woodcock,
How to use stasts instead of join pls

Thanks