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.
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:
Unfortunately, that did not work. However, I have switched to indexing the data and just using a join.
Always avoid using join
and try to use stats
instead.
Hello woodcock,
How to use stasts instead of join pls
Thanks
Do not use the interface; just go to Settings
-> Data inputs
-> Splunk DB Connect Input Service
-> Add New
.
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?