About joea9

joea9 · ‎10-26-2015

This is just a dummy example to illustrate a problem I'm having with my DB Lookup... Within my Splunk search results, I have two fields, person_id1 and person_id2. In my database, I have the fields user_id, user_name and member_type. My 'database lookup' matches the person_id1 and person_id2 fields in the Splunk search results with the user_id field in my database, and then returns the related user_name and member_type fields. The SQL in my 'database lookup' (myDbLookup) looks like this: SELECT user_name, member_type FROM myTable WHERE $person_id1$ = user_id OR $person_id2$ = user_id My Splunk search string looks like this: sourcetype="events" | lookup myDbLookup person_id1, personid2 as user_id OUTPUT user_name, member_type | table person_id1, person_id2, user_name, member_type Some id's in my search results will return names, and some won't, sometimes both id's could have a linked name. So the problem is that when I do have a name returned, how do I know whether the name matched with person_id1 or person_id2? How do I handle the instance where both id's have matching names in the database? Is there a better way to be doing this? James

joea9 · ‎10-25-2015

I got this working...because I had a hard time finding any decent info regarding this, I'll explain what I had to do. Install DB connect Go to the Splunk DB Connect app in Splunk Go to "database connections in Splunk manager" --> New Database type - choose the type of database you are using, you will need to find and install the relevant driver for your database if you don't have it already, but Splunk will give you all of the info you need to do this. Transaction Isolation Level - I had this set to 'DATABASE_SETTING' The rest of the options here are self explanatory, once you have saved that you should have an external database connection listed. You should now be able to run a dbquery command to pull in data from your database: | dbquery list "select * from myTable" In the above query, 'list' is the name of the database connection you've just set up Next you need to set up the Lookup so that data can be inserted from your database Go to Manager menu --> Database Lookups --> New Database - The name of the database connection you just created will appear here Database Table - The name of the table that you will be pulling data from Lookup Fields - Choose all of the fields that you want to pull into Splunk, including the field you are matching on You should now be able to run a lookup: host="coffee" | lookup iplookup myhost as host OUTPUT myip, metadata | table host, myip, metadata In the above query, 'lookup' just means that you're running a lookup query. iplookup is the name of my lookup in Manager-->Database lookup. myhost is the field in my database that I want to match with a field in my Splunk search results. I then want to OUTPUT the related fields from my database, in this case I want to enrich my results with the myip field and the metadata field. I then pipe the results into a table within Splunk, showing me the host, myip and metadata fields.

joea9 · ‎10-22-2015

I've probably looked at that page 300 times over the last 2 days! I've created a 'database lookup' although I'm not sure sure I've done it correctly without an example, the documentation seems really light on information. The example query on the page you linked looks like this: index=test | lookup local=1 mysql_table ip_address as clientip OUTPUT host | table clientip, host My best guess at interpreting that for my set up is: source="/var/log/audit/audit.log" | lookup myiplookup myhost as host OUTPUT myip | table myip, host (Where myiplookup is my 'database lookup', myhost is the hostname field in my database, myip is the ip field in my database, and host is the host field in my search results). A table is returned, but it's not inserting anything from the database (the search also returning 'error code 1').

joea9 · ‎10-22-2015

I want to be able to enrich my Splunk search results using data in a MySQL database. Where the 'hostname' field in my Splunk search results matches the hostname field in my database, I want to insert the IP address from the database. So far I have set up the database connection within Splunk using DB Connect 1, and have returned the contents of my database using dbquery in the search bar. But how do I now create the database lookup that inserts the IP address into my search results when the hostname is found? (I've read all of the documentation but it isn't helping me to understand, I'd appreciate it if someone could provide an explanation with an example).

joea9 · ‎07-18-2015

I am using a CSV lookup that adds additional fields to my Splunk search results... Search string: date_month=october OR date_month=june | lookup mylookup month as date_month CSV file: month,shortname july,jul june,jun october,oct So it's matching the date_month field in my search results with the month column in my CSV file and returning the 'shortname' as a new field. QUESTION However, I want to use data in my CSV file to initiate the search instead. I want to do a lookup that will return all of the months in the month column of my CSV file, and then do a search on them, while including the additional 'shortname' field in the search results? I got this far: | inputlookup myiplookup | fields month Which returns the list of values in my CSV 'month' column, but it doesn't actually search on them, and doesn't return the 'shortname' field.

joea9 · ‎07-18-2015

I've submitted a question but it's 'awaiting moderation'. Is there a way that I can contact a moderator to make sure that it hasn't gone in to a black hole? None of my previous questions have had this happen, so perhaps it's hit a spam filter of some kind.

joea9 · ‎07-17-2015

I'd tried lookups before but I didn't get very far, and I then started to look at alternatives, ie custom python scripts that pull in a CSV file etc. Although re-reading the lookup documentation that you linked to, it is clearly the method that I should be using. Thanks for pointing me back down the correct track. I'll give it another go and if I have issues again I'll create a new Question. Just as an aside question, does the configuration method described in that documentation produce the same outcome as creating lookups via the GUI? I had only tried via the GUI but the transform.conf instructions seem clearer.

joea9 · ‎07-17-2015

I want to know how people would go about solving this problem... In my Splunk search results I have a field called 'Name', which holds peoples names. I also have a CSV file on the server which holds the following columns: Name Age Date of Birth Location I want to use this CSV file to enrich my splunk data. So that when I do a search, and Fred Bloggs appears in the results, I also want his Age, DOB and Location fields to be included in the Splunk results. I also want to be able to drill down on those Age, Date of Birth and Location fields from within the Splunk results. Can anyone help with this? I really want to know what the best and cleanest solution is, so I can focus on doing it that way. I have read some things about lookups and tags, and custom search scripts. But it's not clear to me how I would implement them to do what I need.

joea9 · ‎05-26-2015

Option 2 worked for me, thanks!

joea9 · ‎05-21-2015

I managed to get the test working at least, which in effect works similarly to what you've suggested: [| script myscript| return 2 $ip] This returns both IPs, but in my real script, there will be any number of IPs, could be 10, or 1000, I really just want to return all of them, I'll never know the exact number beforehand. The only workaround I can imagine at the moment is to just put a ridiculously high number in the search string so that I will always get all of them, but that's a very ugly way of doing it. I'll edit this in to the main post.

joea9 · ‎05-20-2015

I have a custom search script (Python) which is nothing more than a dummy script at the moment, as I want to get the very basics working before building any logic into it. I have created the following static string in my python script (I've also added the relevant lines to command.py): searchstring = [{"ip":"192.168.1.10","hostname":"coffee"},{"ip":"192.168.1.15","hostname":"tea"}] and then returning the string with: splunk.Intersplunk.outputResults(searchstring) I can then run the following from the search bar: [| script myscript| return $ip] This returns the '192.168.1.10' and searches for related events,. But, how do I get it to also return the second IP address in the array, and search related events for both? At the moment it is just returning the first ip value and ignoring the rest. EDIT- It seems I can return two IPs like this: [| script myscript| return 2 $ip] But in my real script there could be 10 IPs or 1000 IPs, I'll never know beforehand. So I really need a way of returning all of them, rather than a specific number of them. In theory I could put in a ridiculously high number after 'return', which would hopefully catch everything, but that seems like an ugly solution.

Posts	11
Solutions	1
Karma Given	7
Karma Received	2
Member Since	‎05-20-2015

Online Status	Offline
Date Last Visited	‎06-05-2020 02:03 AM

DB Lookup - If I have two database inputs of the s...

Using Splunk DB Connect 1, how can I enrich my sea...

How to use a column of data in a CSV lookup to ini...

Question is awaiting moderation - how do I contact...

How would you go about including CSV data in your ...

Custom search script returning only first value in...