Splunk Search

How to combine my two searches to match a field from event logs with a lookup via inputcsv to output another field?

dagnygaard
Explorer

I am trying to set up a report with a search string that works OK.
Unfortunately, only internal Ids are used in the log entries. To make the report readable, I am trying to set up a join that will exchange the internalId with a descriptive name in the final output.

To make things easier, I have dumped the database table with two columns, internalId and description to a csv file.

Search A:

| inputcsv machinesdump.csv | table internalId description

This works fine as it is, showing the two columns with reasonable values. The csv-file is read and parsed correctly.

My main search works fine too, except for the the internalId being dsiplayed instead of the description.

Search B:

index="errorIndex" 
source="*-error.log" 
Machine LogEntryEntity 
| eval temp=split(_raw,"|") | eval txt=mvindex(fields,3)| ... | eval internalId=mvindex(foo, 1)
| stats count by internalId | sort -num(count)

This also works as expected, counting failing machines with the top failing machine on the top of the list, except we show internal Id of the machines with their count.

I tried merging the two searches:

index="errorIndex" 
source="*-error.log" 
Machine LogEntryEntity 
| eval temp=split(_raw,"|") | eval txt=mvindex(fields,3)| ... | eval internalId=mvindex(foo, 1)
| join internalId [ | inputcsv machinesdump.csv | table internalId description ]
| stats count by description | sort -num(count)

No entries found.

I am currently experimenting on Splunk Light Free Version 6.3.0, but the target is a running Splunk Enterprise version. Did I botch the join setup? Am I using the wrong action?

0 Karma
1 Solution

sundareshr
Legend

I believe what you need is a lookup table. Setup your .csv as a lookup table and get the description for matching internalId.

http://docs.splunk.com/Documentation/Splunk/6.3.2/SearchReference/Lookup

View solution in original post

sundareshr
Legend

I believe what you need is a lookup table. Setup your .csv as a lookup table and get the description for matching internalId.

http://docs.splunk.com/Documentation/Splunk/6.3.2/SearchReference/Lookup

Chandras11
Communicator

Sorry, I might be too late here. I am also having a similar issue. So I defined a lookup. What if I am using ourputcsv to create the CSV.
Will lookup definition be affected if my CSV file generated by ourputcsv is changing regularly?

0 Karma

dagnygaard
Explorer

Got it!

My final search looks like this:

index="errorIndex" 
      source="*-error.log" 
      Machine LogEntryEntity 
      | eval temp=split(_raw,"|") | eval txt=mvindex(fields,3)| ... | eval internalIdString=mvindex(foo, 1)
      | eval internalId=trim(internalIdString)
      | lookup DEVICE InternalId as internalId output Description
      | stats count by Description | sort -num(count) | rename Description as Id| table Id, count | where count >= 10

[the search now also only shows the machine Ids with 10 or more errors]

The clue to the final solution is that the extraction of internalIdString padded the extracted value with a space (or the split did it or the _raw messageentry contained a extra blank like this "....id : 77".) Instead of trying to compare "77" with 77, the lookup tried to find a " 77" in the lookuptable! This "fault" may also be the reason why the join did not work.

In any case, we got a working answer using Lookup and by trimming the spacepadded internalId:

  | eval internalId=trim(internalIdString)

Thanks for you tip sundareshr!

dagnygaard
Explorer

Good idea, but I am still not able to make it work:

   index="errorIndex" 
     source="*-error.log" 
     Machine LogEntryEntity 
     | eval temp=split(_raw,"|") | eval txt=mvindex(fields,3)| ... | eval internalId=mvindex(foo, 1)
     | table internalId 
     | lookup DEVICE internalId as internalId
     | stats count by description | sort -num(count)

I set up a lookuptable DEVICE using transforms.cnf, described in the documentation. The command:

  | inputlookup DEVICE

confirms that the setup works, i.e. produces a table with all rows.

I also setup a default text for no value found. When I run the searchscript above, I get a correct DeviceId but "No value found" for each field in the lookup table.

I have unsuccessfully tried to follow a number of faultsearches:
- different types in the searchtable and in the inputtable, i.e. is DeviceId a string in the searchtable and a number in the lookuptable.
- Will lookup only work with Eventfields, i.e. not take derived fields such as those extracted with "eval"?
- Is my lookuptable-csv file placed in the wrong place for a search? [$SPLUNK_HOME/etc/apps/lookups/myfile.csv]
- Is lookup limited in Splunk Light (free) version?

0 Karma
Get Updates on the Splunk Community!

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...

Ready, Set, SOAR: How Utility Apps Can Up Level Your Playbooks!

 WATCH NOW Powering your capabilities has never been so easy with ready-made Splunk® SOAR Utility Apps. Parse ...

DevSecOps: Why You Should Care and How To Get Started

 WATCH NOW In this Tech Talk we will talk about what people mean by DevSecOps and deep dive into the different ...