All-
I am new to Splunk and trying to figure out how to return a matched term from a CSV table with inputlookup. I just researched and found that inputlookup returns a Boolean response, making it impossible to return the matched term.
With that being said, is the any way to search a lookup table and return a matching term? I would imagine eval would do the trick, but I have not been successful in making it work.
I have tried the below:
index=proxysg sourcetype=proxysg_base [|inputlookup aterms.csv | return 10000 $aterms] | eval matchedterm=if( [|inputlookup aterms.csv | return 10000 $aterms], $aterms)
Thanks for the help!
Lets say your Lookup table is "inputLookup.csv" and it is as follows:
Field1,Field2
AA,11
AB,22
AC,33
BA,21
BB,22
BC,23
You can match terms from input lookup on any of the above fields Field1 or Field2 as follows (I am matching on Field1 and displaying Field2):
|inputlookup inputLookup.csv | search Field1=A* | fields Field2
If you now want to use all the Field2 values which returned based on your match Field1=A* as subsearch then try:
yourBaseSearch [ |inputlookup inputLookup.csv | search Field1=A* | fields Field2 ]
Updating as per the comment conversation:
index=proxysg sourcetype=proxysg_base [|inputlookup aterms.csv | return 10000 $aterms]
| rename _raw as rawText
| eval foo=[|inputlookup aterms.csv |stats values(aterms) as query | eval query=mvjoin(query,",") | fields query | format "" "" "" "" "" ""]
| eval foo=split(foo,",")
| mvexpand foo
| eval foo=lower(foo)
| eval rawText=lower(rawText)
| where like(rawText,"%"+foo+"%")
| table rawText, foo
index=proxysg sourcetype=proxysg_base search [| inputlookup lookup.csv | eval search="".term."" | rename search as term |fields term(lookup columnname) ]
Lets say your Lookup table is "inputLookup.csv" and it is as follows:
Field1,Field2
AA,11
AB,22
AC,33
BA,21
BB,22
BC,23
You can match terms from input lookup on any of the above fields Field1 or Field2 as follows (I am matching on Field1 and displaying Field2):
|inputlookup inputLookup.csv | search Field1=A* | fields Field2
If you now want to use all the Field2 values which returned based on your match Field1=A* as subsearch then try:
yourBaseSearch [ |inputlookup inputLookup.csv | search Field1=A* | fields Field2 ]
Updating as per the comment conversation:
index=proxysg sourcetype=proxysg_base [|inputlookup aterms.csv | return 10000 $aterms]
| rename _raw as rawText
| eval foo=[|inputlookup aterms.csv |stats values(aterms) as query | eval query=mvjoin(query,",") | fields query | format "" "" "" "" "" ""]
| eval foo=split(foo,",")
| mvexpand foo
| eval foo=lower(foo)
| eval rawText=lower(rawText)
| where like(rawText,"%"+foo+"%")
| table rawText, foo
Sorry to post comment on an old answer;
The solution is working fine but it uses a lot of resources when the number of rows in csv file and index size grow. In my case, I have a structured data file like this:
Field-ID,Field-SourceType,Field-Substring
1,sourcetype1,Some text goes here
2,sourcetype1,Another other text with WILDCARD * here
3,sourcetype2,This is a different text for different sourcetype
...
I run the above query (returning "Field-Substring" field) against some index data/events to count the number of occurrences of substrings. As there are huge number of events and quite large number of substrings in the csv file, it takes ages to return the result. Just wondering if there's another method to expedite searching unstructured log files for all the values in my lookup csv file and return the stats/count/etc.
These unstructured indexed data/logs are only categorised based on different sourcetypes and as you can see in the lookup csv file, each line shows the substring and it's corresponding sourcetype which needs to be searched.
Great solution! Not sure if I apply it correctly. I assume mvexpand utilize a lot of memory. I hit the error "command mvexpand results will be truncated due to excessive memory usage". We are not able to modify the 500MB memory threshold. Any other method beside using mvexpand?
Hello, I have got other fields like latitude and longitude. How do I show it on the Table as well?
my lookup table is a list of hundreds of strings that I am searching against logs. The search works perfect as: index=proxysg sourcetype=proxysg_base [|inputlookup aterms.csv | return 10000 $aterms] and returns all of the events that have one of the matching strings in my lookup table.
The issue is that I need to display the input table string that was identified in the corresponding event.
Are you trying to match the strings which get returned from inputlookup to the event strings of outersearch randomly or the outer search will have the text matched randomly but the matched string will happen to be a field value in your outer search. As the latter case might be a good case for lookup command then.
Yeah, so we are looking for keywords in the outer search, I just need the inner search to identify the keyword that hit. The input lookup only returns the boolean true, so I would need the inner search to identify the keyword in the event that was returned from the input lookup.
Can you check this please and see if that's what u need:
https://answers.splunk.com/answers/469425/is-it-possible-to-highlight-a-specific-term-from-a.html
That post is simply highlighting the matched term, I am looking to add the matched term into a new field.
I put in some research and coming back with solution which does following:
Let me know if it worked so I can update in answer. Hope that is what you were looking for
index=proxysg sourcetype=proxysg_base [|inputlookup aterms.csv | return 10000 $aterms]
| rename _raw as rawText
| eval foo=[|inputlookup aterms.csv |stats values(aterms) as query | eval query=mvjoin(query,",") | fields query | format "" "" "" "" "" ""]
| eval foo=split(foo,",")
| mvexpand foo
| eval foo=lower(foo)
| eval rawText=lower(rawText)
| where like(rawText,"%"+foo+"%")
| table rawText, foo
*Splunk Version: 6.5.1 *
I needed to do this to see what strings were matching my powershell script blocks to weed out high false positive rates, but I keep getting a eval malformed error when I try to example above.
| table rawText, foo
Powershell_Rules CSV List
PS_Commands Match
Set-MasterBootRecord Set-MasterBootRecord
Get-GPPPassword Get-GPPPassword
Get-Keystrokes Get-Keystrokes
Get-TimedScreenshot Get-TimedScreenshot
Get-VaultCredential Get-VaultCredential
Get-ServiceUnquoted Get-ServiceUnquoted
Get-ServiceEXEPerms Get-ServiceEXEPerms
Get-ServicePerms Get-ServicePerms
Get-RegAlwaysInstallElevated Get-RegAlwaysInstallElevated
Get-RegAutoLogon Get-RegAutoLogon
Error I get is: Error in 'eval' command: The expression is malformed. An unexpected character is reached at ')'.
I get a similiar error when trying the command
| eval foo=[|inputlookup Powershell_Rules.csv |stats values(Powershell_Rules) as query | eval query=mvjoin(query,",") | fields query | format "" "" "" "" "" ""]
Error in 'eval' command: Fields cannot be assigned a boolean result. Instead, try if([bool expr], [expr], [expr]).
It worked perfectly!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! Thanks a million!!
See if this is what you're looking for...
index=proxysg sourcetype=proxysg_base [|inputlookup aterms.csv | rename aterms AS search | return search]