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?
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
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
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?
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!
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?