Simple Lookup via file not working

[tyronetv]

I am attempting to translate system response codes to natural language for business reasons.
I have 7 codes that relate to user issues. For Example: 1 = bad password, 2 = expired password, etc., etc.

I have done the following:
Manager->Lookup->Lookup Table Files->Add new

-Destination App - Search

-Lookupfile - Upload CSV from my system in format of:

"response_code","response" (my input has response code with an underline but I don't see it in the preview table)

1,"Bad Password"

2,"Expired Password"

-Destination File Name->login_codes.csv

Permission = All Apps, everyone read, admin write.

Manager->Lookup->Lookup Definitions->Add new

-App - Search
-Type - File Based

-Name - LoginCodes

-Lookup File - login_codes.csv
Permission=This App(search), everyone read, admin write.

Manager->Lookup->Automatic Lookups->Add new

- App - Search

- Name - FailedLogin

- LookupTable - LoginCodes

- Apply to - Source ...index...

- lookup input fields

response_code =

Permission=This App(search), Everyone Read, Admin write

My data has the identifier of "loginResonse" and "return>" so I set up a field extraction of:
Manager->Field->Field Extraction->New

- Destination App - Search

- Name - LoginResponse

- Apply to - source ...index...

- Type - inline

- Extract/Transform - loginResponse[^<]+<return>(?\d)

Permissions=This App(Search), Everyone Read, Admin write

I then created an event type to ease the serach:

Manager->Event Types->New

- Destination App - Search

- Name - Login_Resonse
- Search String - loginResponse AND "return>"

Permissions=This App(Search), Everyone Read, Admin Write

I then do a seach:

eventtype=Login_Response - and this works.

I then do a timechart:

| timechart count by reason -- and this gives me a proper count but every field is returned "null"

I add 'usenull=f" so my full search is "eventtype=Login_Response | timechart count by reason usenull=f" and I get, everytime, no results found.

What am I doing wrong?

[sowings · Accepted solution]

It sounds like your field extraction isn't working properly--let's start there.

If your log line looks like this (as quoted):

loginResponse return>1

Then the regular expression you've quoted won't quite work. I realize that the end is probably correctly entered in your config as "\d", but it shows up here as just d. That's not where I had trouble, though. I had to change the regex to read

loginResponse[^<+]return>(?<response_code>\d)

Once this was done, the eventtype search turned up my sample records, as well as identifying a field called "response_code" containing the numerical value from my sample. To be clear, an eventtype search can "work" without necessarily hitting all of your field extractions, unless the eventtype search mentions the field explicitly, e.g. "loginResponse AND response_code=*".

With the field extraction working, I moved on to the lookup table. It looks like you put the string "...index..." in the source field for the lookup definition. This means that Splunk will look for a source (which is most commonly thought of as filename) containing the string "index". You might consider changing the lookup definition to match your sourcetype.; in my test, this is what I did. Also, your input / output clauses might be wrong. Based upon your transcription, I got a $SPLUNK_HOME/etc/apps/search/local/props.conf entry like this:

[source::...index...]
LOOKUP-LoginResponse = LoginCodes response_code OUTPUTNEW

When it should look more like this:

[source:...index...]
LOOKUP-LoginResponse = LoginCodes response_code OUTPUTNEW response

(Note that in my test I had also changed the source:: part to read just [syslog] as discussed above).

This tells the lookup to use the response_code (from your field extraction) as the lookup to figure out the "response" (since this is the other field in your CSV) and to create a new field to store this value. Now the eventtype search returns response_code and response together.

Finally, we reach the timechart call. All this time you've been identifying the human readable response string as "response", but the timechart call labels it as "reason". So far, there's been nothing to populate such a field, so naturally it's going to be null. I'm assuming that you meant to label it "response" in the timechart call, and that's what all of the above double-checking is about.

But maybe you did all of the transforms and field extractions right, and only had a think-o when it came time to call timechart.