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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...