All Apps and Add-ons

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

cmccormick
Explorer

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
Esteemed Legend

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/

0 Karma

cmccormick
Explorer

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

0 Karma

woodcock
Esteemed Legend

Always avoid using join and try to use stats instead.

0 Karma

raghu0463
Explorer

Hello woodcock,
How to use stasts instead of join pls

Thanks

0 Karma

woodcock
Esteemed Legend

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

0 Karma

cmccormick
Explorer

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?

0 Karma
Get Updates on the Splunk Community!

Developer Spotlight with William Searle

The Splunk Guy: A Developer’s Path from Web to Cloud William is a Splunk Professional Services Consultant with ...

Major Splunk Upgrade – Prepare your Environment for Splunk 10 Now!

Attention App Developers: Test Your Apps with the Splunk 10.0 Beta and Ensure Compatibility Before the ...

Stay Connected: Your Guide to June Tech Talks, Office Hours, and Webinars!

What are Community Office Hours?Community Office Hours is an interactive 60-minute Zoom series where ...