Splunk Add-on for Oracle Database: Why are we unable to retrieve lookup values to add to one of our alerts on Splunk 5.0.3?

[scottl2k2]

We are attempting to add lookup values to one of our alerts on a 5.0.3 installation. We do not have access to the file system, so all of our work needs to be done through the Web UI.

I’ve copied the values found at http://docs.splunk.com/Documentation/AddOns/released/Oracle/Lookups and saved them as .CSV files.

I then went to Manager > Lookups > Lookup Table Files and uploaded them in our Oracle “App Context”.

I then visited “Permissions”, and set it to “Read” for “Everyone” in “This app only”.

Back in the app context, if I search for

index="oracle*" sourcetype="os_audit" action="17"
I get many results that look like this:

Apr 23 08:28:05 dvl8-oracle-01 Oracle Audit[8135]: LENGTH: "342" SESSIONID:[8] "26756899" ENTRYID:[1] "2" STATEMENT:[2] "22" USERID:[10] "SHARED_REF" USERHOST:[5] "XXXXXXX" TERMINAL:[7] "unknown" ACTION:[2] "17" RETURNCODE:[1] "0" OBJ$CREATOR:[10] "SHARED_REF" OBJ$NAME:[20] "XXXXXX" OBJ$PRIVILEGES:[16] "---------Y------" AUTH$GRANTEE:[6] "PUBLIC" OS$USERID:[4] "XXXXXX" DBID:[10] "3479291889"
If I change that to

index="oracle*" sourcetype="os_audit" action="17"

| lookup oracle_returncode.csv RETURNCODE as RETURNCODE OUTPUT result as RC
I get the message “Assuming implicit lookup table with filename ‘oracle_returncode.csv’”, but the results are no different. Our goal is to get a human-readable value (RC) to include with our alerts.

It seems that my syntax is correct. If I change the first occurrence of “RETURNCODE” to something else, I get “Error in ‘lookup’ command: Could not find all of the specified lookup fields in the lookup table.” If I change “result”, I get the same message.

I’m new to Splunk - I’ve done quite a bit of searching, and I’ve tried everything that I can think of. Any guidance would be greatly appreciated.

[Yasaswy · Accepted solution]

Hi
index="oracle*" sourcetype="os_audit" action="17" | lookup oracle_returncode.csv RETURNCODE as RETURNCODE OUTPUT result as RC

If the above search is returning results for you then search works. I am assuming "result" is a valid field in your lookup table

You can pull up your lookup table to check:
| inputlookup oracle_returncode.csv (in your app context)

1. Click on "more fields" and select "All Fields". Look for "RC" here and check it. It should now show up in your "Selected Fields"
2. If you do not find the output field then very likely "RETURNCODE as RETURNCODE" match might be incorrect. Verify you are matching the right field from the result set to the correct field in lookup table. Eg: index="oracle*" sourcetype="os_audit" action="17" | lookup oracle_returncode.csv FieldInLookUpTable as FieldInResultset OUTPUT lookuptableMatchField as RC

Hope it helps.

[scottl2k2]

I'm hoping that I understand everything correctly...

The search does return results. The problem we're having is that it does not include the "RC" field.

When I pull up the lookup table as you suggest, we can confirm that "result" is there, and valid. In fact, if I change it to something invalid, I receive an error right away.

"Click on "more fields" and select "All Fields"." - the closest thing I see is "View all 30 fields" in the left column. Clicking that gives me a dialog and RC is nowhere to be found on that list.

Did I misunderstand something?

Thanks!

[Yasaswy]

Might be dependent on the permissions. When you click "View all 30 fields" do you see an option on the top beside the filter for "Coverage" ... if you do the you can select "All Fields".
What do you get if you run:

index="oracle*" sourcetype="os_audit" action="17"

| lookup oracle_returncode.csv RETURNCODE as RETURNCODE OUTPUT result as RC|stats values(RC)

[scottl2k2]

I'll try this first thing in the morning... thanks!

[scottl2k2]

I had some difficulty figuring out how to get an image up here. I hope this works...

1) This is what I get when I try to show "All Fields":

2) When I run your search, I get:

3) Clicking "inspect" gives me:

[Yasaswy]

Can't see the images Scotts 🙂 ... assuming you didn't get any results back for stats values(RC) ...correct?

[scottl2k2]

That's odd - I can see the images. I'm not sure why others cannot. I don't have enough "karma" to post images, unfortunately.

No, I did not get any results for the stats values(RC).

[url=http://postimage.org/][img=http://s29.postimg.org/ooueja5l3/1_All_Fields.jpg][/url]

[url=http://postimage.org/][img=http://s29.postimg.org/smho8osef/2_Results.jpg][/url]

[url=http://postimage.org/][img=http://s29.postimg.org/67zzsgpmv/3_Details.jpg][/url]

Thanks Again,
Scott

[Yasaswy]

I guess there were blocked on my network. Can you also give a sample result set from just
"| inputlookup oracle_returncode.csv"
I am guessing the lookup match is not happening.

[scottl2k2]

The result is a table with two columns, and two rows.

> RETURNCODE    result
> ----------    --------            
> 0              success
> *              failure

[Yasaswy]

that is possibly the problem. From your output .... RETURNCODE:[1] "0" does not match what is in your lookup table. If you run
index="oracle*" sourcetype="os_audit" action="17" |stats values(RETURNCODE)
you should probably be getting [1] "0" rather than just 0 (which) is what is in your lookup table

[scottl2k2]

This only way that I was able to get it to display that way was by surrounding the value in "tripled double-quotes" in the CSV:

"""0"""

It didn't change anything.

If it wasn't able to match the zero, shouldn't it have matched the wildcard?

[Yasaswy]

depends on how it's set up in transforms.conf... it needs to be defined. Additionally .... if you would just like to test. Remove the wild card just use

RETURNCODE result

---

0 success

for now.
If your RETURNCODE values are like [1] "0"
then I think you should able to run something like
index="oracle*" sourcetype="os_audit" action="17"|rex field=RETURNCODE mode=sed "s/([.] \"0\"/0/g"|lookup oracle_returncode.csv RETURNCODE as RETURNCODE OUTPUT result as RC|stats values(RC)

to get a match on 0 and see success for RC (if there are matches in the result set)

[scottl2k2]

Thanks... I seem to be having some trouble with the regular expression. Could you tell me what it's trying to do?

[Yasaswy]

just trying replace the values returned for RETURNCODE with 0 ... I think the escape characters were messed in my post above... maybe

index="oracle*" sourcetype="os_audit" action="17"|rex field=RETURNCODE mode=sed "s/[.] \"0\"/0/g"|lookup oracle_returncode.csv RETURNCODE as RETURNCODE OUTPUT result as RC|stats values(RC)

or

index="oracle*" sourcetype="os_audit" action="17"|rex field=RETURNCODE mode=sed "s/.*0./0/g"|lookup oracle_returncode.csv RETURNCODE as RETURNCODE OUTPUT result as RC|stats values(RC)

[scottl2k2]

Thanks... still no luck. RC doesn't show up at all. This is incredibly frustrating, and I appreciate your patience.

I tried setting up the "advanced options" with a minimum and maximum of one, and a default match value of "success". If it's failing to make matches, shouldn't it have returned "success" for every event?

[Yasaswy]

I am not sure on that. Maybe others can offer advice. I would guess that advanced options are still limited to "matches". Meaning there still needs to be a match for anything to "tie" to the lookup table. Might be easier if you can narrow down to make sure that you can get a match first.

Run:
index="oracle*" sourcetype="os_audit" action="17"|stats values(RETURNCODE)
Depending on the output you are seeing. Update the looktptable /csv to ensure a match. An then try the full search with lookup.

[scottl2k2]

Thanks for your help... when your last suggestion returned no results, it helped me to find my problem.

The results kept showing "RETURNCODE", but if I'd been looking at the "Interesting Fields" section on the left, I would've noticed that the field was listed as "returncode".

I feel foolish, but it was as simple as changing my query to:

index="oracle*" sourcetype="os_audit" action="17"       
 | lookup oracle_returncode.csv RETURNCODE as returncode OUTPUT result as RC

Thanks!

[neelamssantosh]

Troubleshooting,
1.Hope you have restarted your Splunk instance after updating the Lookups.
2. Kindly check by replacing 'as' with 'AS'
3. Finally, just use in search bar with only "| lookup oracle_returncode.csv"

[scottl2k2]

Thank you for the suggestions...

1) The "politics" of our environment make restarting difficult. I thought that lookups defined through the Web UI did not require a restart?

2) Replacing "as" with "AS" had no effect.

3) "| lookup oracle_returncode.csv" results in the error message "Error in 'lookup' command: Must specify one or more lookup fields.
"| inputlookup oracle_returncode.csv" resulted in a table containing the values from the .CSV.

[curryRick]

1. You are correct, a restart of splunkd is not required when you add lookup tables via the Web UI.

2. I typically use CamelCase for my variables and always CAPTIALIZE select keywords in the search code (i.e. OR and AS) to match the Splunk doc and make the code easier to follow. OR is required but I don't think AS is; I've used it as 'as'.

   1. The "| inputlookup oracle_returncode.csv" command returns the CSV file header as the field names for each column. Verify that these match your code. The lookup command in your search uses the syntax of

| lookup {LookUp_Tbl_Name} {LookUp_Tbl_Field_1} AS {Event_Field_1} OUTPUT {LookUp_Tbl_Field_2} AS {Event_Field_2}

For more info on the command's format, check out the Lookup Command1 doc. If you create a Lookup Definition (second item on the Settings >> Lookups page), then you do not need to specify the lookup table's CSV file name and use a shorter name instead.