I have gotten myself confused and I can't seem to find the answer I need to resolve the question I have in my head about "DB Lookup". I have DB Connect running and able to perform SQL database queries as I need them and able to pull data into an index and build searches and visualisations from the data. All of this is with an DB Input which means data coming from the database and being indexed in SPLUNK. So that I can call the data when I need to, I started looking at DB Lookup so that I can look up the data when I run a query. I also seem to get stuck at step 3 in the DB Lookup (Choose the Splunk fields to base the lookup on). I presume that this lookup is meant to go and reference other indexes that are already in SPLUNK and then map the fields into the database to the fields in the index.
However what I am trying to do is build a search that does a lookup using DB Lookup but the index or fields are not yet known. So for example I have a query that looks for say non-administrative accounts on workstations. The database that holds this information is a SQL box. If I run my SQL query I get the following results (for example)
user1, non-admin account, member of local administrators, on workstation ABC
user2, admin account, member of local administrators, on workstation XYZ
So this search is part of a governance search and so there is no pre-built query/search for it. Do I have to create an index first with the relevant information and save it as a DB Input. Then do I then save the search from the DB Input? Do I then run a query based on the DB Input saved search and do a DB lookup even though the queries are exactly the same.
The problem is I could use a DB Input but the problem is there is no database time stamp in the query and the only time stamp is the import time in SPLUNK. Consequently this is pulled in in a batch and every time the query is run information is duplicated and I have to use dedup as part of my search query (the negative side to this is the index keeps growing and tailing the database I don't think works because there is no time stamp to tail to).
So can I use DB Lookup with DB Connect when the query is being run as an ad-hoc query without relying on any other indexed data in SPLUNK?
