Splunk Search

Scripted Lookup Script doesn't output to a CSV file

bansi
Path Finder

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

bansi
Path Finder

LookUp Script NOT To Read from CSV File

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

0 Karma

Lamar
Splunk Employee
Splunk Employee

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.

bansi
Path Finder

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

0 Karma

bansi
Path Finder

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** 
0 Karma

Lamar
Splunk Employee
Splunk Employee

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...