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

[cmccormick]

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.

[woodcock]

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/

[cmccormick]

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

[woodcock]

Always avoid using join and try to use stats instead.

[raghu0463]

Hello woodcock,
How to use stasts instead of join pls

Thanks

[woodcock]

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

[cmccormick]

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?