Below is the props.conf at $SPLUNK_HOME/etc/system/local:
[Test_Log]
lookup_table = namelookup memberId OUTPUT memberName
Below is the transforms.conf at $SPLUNK_HOME/etc/system/local:
[namelookup]
external_cmd = namelookup.py memberId memberName
external_type = python
fields_list = memberId, memberName
Script location :
$SPLUNK_HOME/etc/system/bin/namelookup.py
# File namelookup.py
# ------------------------------
import os,csv
import sys
import logging
import logging.config
import pyodbc
# Given a id, find its name
def lookupName(idf, cur):
#logger.debug("====Inside Lookup idf====="+idf)
try:
selString = "SELECT first_name FROM emp where Member_ID="
cur.execute (selString + idf )
row = cur.fetchone()
return row[0]
except:
return []
def main():
logging.config.fileConfig("logging.conf")
logger = logging.getLogger("name_lookup")
if len(sys.argv) != 3:
print "Usage: python name_lookup.py [id field] [name field]"
sys.exit(0)
idf = sys.argv[1]
namef = sys.argv[2]
#logger.debug("====idf===="+idf)
#logger.debug("====namef====="+namef)
r = csv.reader(sys.stdin)
w = None
header = []
first = True
conn = pyodbc.connect('DSN=IBM_DB')
cursor = conn.cursor()
for line in r:
if first:
header = line
if idf not in header or namef not in header:
print "Host and IP fields must exist in CSV data"
sys.exit(0)
csv.writer(sys.stdout).writerow(header)
w = csv.DictWriter(sys.stdout, header)
first = False
continue
# Read the result
result = {}
i = 0
while i < len(header):
if i < len(line):
result[header[i]] = line[i]
else:
result[header[i]] = ''
i += 1
# Perform the lookup for city to country if necessary
if len(result[idf]) and len(result[namef]):
w.writerow(result)
elif len(result[idf]):
result[namef] = lookupName(result[idf], cursor)
if len(result[namef]):
w.writerow(result)
cursor.close()
conn.close()
main()
the results displayed on stdout are as desired
C:\Splunk\etc\system\bin>db_lookup.py memberId memberName < memberInput.csv
produces following output retrieving memberName from database
memberId,memberName
006,RANDY
007,LEONY
009,RANDOLPH
However i still have following issues
The script doesn't output similar results in the CSV file. Wondering why it doesn't output in CSV file when the desired results are displayed in STDOUT. So what i am missing here?
The script then outputs from the CSV file and returns it to Splunk, which populates the memberName field in your results
source="Test_Log.txt" | xmlkv entry | lookup namelookup memberId OUTPUT memberName | table memberId, memberName
Please let me know how to populate CSV file with the results
Yes i am looking for script which reads from the context of Splunk Searching instead of taking the input from CSV file from stdin
Here is the script which works from standalone but not from context of splunk search
# File namelookup.py
# ------------------------------
import os,csv
import sys
import logging
import logging.config
import pyodbc
FIELDS = [ "memberId", "memberName" ]
# Given a id, find its name
def lookupName(idf, cur):
try:
selString = "SELECT first_name FROM emp where Member_ID="
cur.execute (selString + idf )
row = cur.fetchone()
return row[0]
except:
return []
def main():
if len(sys.argv) != 3:
print "Usage: python name_lookup.py [id field] [name field]"
sys.exit(0)
idf = sys.argv[1]
namef = sys.argv[2]
# THIS IS NOT DESIRED
#r = csv.reader(open('C:\\Splunk\\etc\\system\\bin\\memberInput.csv'))
#r = csv.reader(sys.stdin)
result = {}
w = csv.DictWriter(sys.stdout, FIELDS)
header = []
first = True
conn = pyodbc.connect('DSN=IBM_DB')
cursor = conn.cursor()
if len( idf):
result[namef] = lookupName(idf, cursor)
print("Result of"+ namef)
print(result[namef])
if len(result[namef]):
w.writerow(result)
cursor.close()
conn.close()
main()
Pl let me know how to rewrite the script to read/write from the context of splun search
Right, so if you ensure that your script is in the right location for your given app or implementation (it appears that you just want to use the system level implementation -- I highly recommend doing this within an app space). Therefore, according to the docs you should be putting it in $SPLUNK_HOME/etc/searchscripts
.
Once you get your script in the right location you should be good to go. Your transforms.conf
looks reasonable.
external_cmd = <string>
* Command and arguments to invoke to perform lookups.
* This string is parsed like a shell command.
* The first argument is expected to be a python script located in
$SPLUNK_HOME/etc/<app_name>/bin (or ../etc/searchscripts)
However, having read more into what you're trying to do here I would make an additional suggestion. Instead of trying to output your script to another CSV that you would have to create another lookup to OUTPUT your memberName, just use this to do your lookup and return your memberName dynamically as you search.
Here's what you would do for that:
transforms.conf
[namelookup]
external_cmd = namelookup.py memberId memberName
external_type = python
fields_list = memberId memberName
Your Search
source="Test_Log.txt" | xmlkv entry | lookup namelookup memberId | table memberId, memberName
This should create the memberName
field for you.
Without having your data to test this I'm flying a bit blind, let me know how this works out for you.
You read my mind. Thats exactly what i plan to do i.e. return memberName dynamically as you search. But wasn't sure how to do so always wonder why example lookup scripts (viz external_lookup.py) read CSV file from stdin/stdout. I am looking for an example which reads the value of memberId from the context of Splunk Search and not from CSV file. As i cannot post the code snippet here i will post under "Add An Answer" section
Yes you understood the problem correctly i.e. scripted lookup execute perfectly from the command-line as shown in my posting
The questions are
**- Where do i mention the name of CSV file so that the script outputs to CSV file and then returns it to Splunk. So i would like to know the location of CSV file . Is it $SPLUNK_HOME/etc/system/lookups/ . Do i need to refer it in transforms.conf
-Next is HOW Scripted LookUp uses these CSV file to populate the results from Database. I haven't referenced in my Scripted Lookup. So where is the magic done? Right Now my scripted LookUp doesnt write to CSV file just print to stdout
- Finally how does CSV file returns the results to Splunk. What is the Splunk Search Command . The "lookup memberId OUTPUT memberName" doesnt seem to be working**
My apologies if I'm not understanding what problem you're having. But, from what I've read it sounds as if Splunk is not executing the lookup as designed via a search that you perform within the search interface or CLI. It does however, execute appropriately if you execute it from the command-line.
I can address this fairly simply -- You've placed your script in the wrong location within the Splunk directory structure.
Please refer to the transforms.conf
documentation link below on where you need to place your lookup scripts. You'll be looking for the "Lookup Tables" section and the external_cmd
area within that.
Hope that helps!
http://www.splunk.com/base/Documentation/4.1.6/Admin/Transformsconf