topic Re: How to include additional field from inputlookup in results? in Splunk Search

How to include additional field from inputlookup in results?

luc_k — Wed, 20 Sep 2017 07:38:54 GMT

Hi,

I have a lookup table errors.csv ,which contains Error and Source columns.I have a query the returns log entries containing Error column values :

[|inputlookup errors.csv | rename Error AS query | fields query ]

How do I add the Source column to the results?

Thanks,

Luc

Re: How to include additional field from inputlookup in results?

HiroshiSatoh — Wed, 20 Sep 2017 14:27:37 GMT

Try this!

|inputlookup errors.csv
|map search="search (your search) \"$Error$\"|eval Error=\"$Error$\", Source=\"$Source$\""

Re: How to include additional field from inputlookup in results?

gcusello — Wed, 20 Sep 2017 14:30:02 GMT

Hi
as the previous adding lookup command, try something like this

your_search [ | inputlookup your_lookup.csv | rename Error AS quesry | fields query ]
| rename _raw as rawText
| eval foo=[
    | inputlookup your_lookup.csv 
    | eval query="%"+Error+"%" 
    | stats values(query) AS query 
    | eval query=mvjoin(query,",") 
    | fields query 
    | format "" "" "" "" "" ""
    ]
| eval foo=split(foo,",") 
| mvexpand foo 
| where like(rawText,foo)
| rex field=foo "\%(?<Error>[^\%]*)\%"
| lookup errors.csv Error OUTPUT Source
| ...

Bye.
Giuseppe

Re: How to include additional field from inputlookup in results?

sbbadri — Wed, 20 Sep 2017 14:38:48 GMT

try this,

[|inputlookup errors.csv where Source=* | rename Error AS query | fields query Source | table query Source]

Re: How to include additional field from inputlookup in results?

DalJeanis — Wed, 20 Sep 2017 20:36:46 GMT

@sbbadri - The user didn't say so, but the brackets indicate that this is a subsearch, so this solution will not work. if Source got passed back at all, it would act as a limit on the main search, rather than giving extra information.

Re: How to include additional field from inputlookup in results?

DalJeanis — Wed, 20 Sep 2017 20:48:42 GMT

@cusello - Not precisely the way I would do it, but it should work for moderate numbers of events and moderate numbers of records in the lookup. For larger numbers of records, I'd replace the mvexpand with a rex that pulls out those error values directly, rather than multiplying the number of records.

Something like this...

 your_search
    [ | inputlookup your_lookup.csv 
    | rename Error AS query 
    | fields query ]
| rex field=_raw [ | inputlookup your_lookup.csv 
    | table Error 
    | sort 0 - Error 
    | rex mode=sed field=Error "s/ /!!!!/g" 
    | format "(?i)(<Error>" "" "" "" "|" ")" 
    | rex mode=sed field=search "s/[ \"]//g s/^\(/\"(/g s/\)$/)\"/g s/!!!!/ /g"]
 | lookup your_lookup.csv Error OUTPUT Source

Note that this is air code. I would test that rex-build subsearch with this first, to make sure the regular expression was well formed.

 | inputlookup your_lookup.csv 
    | table Error 
    | sort 0 - Error 
    | rex mode=sed field=Error "s/ /!!!!/g" 
    | format "(?i)(<Error>" "" "" "" "|" ")" 
    | rex mode=sed field=search "s/[ \"]//g s/^\(/\"(/g s/\)$/)\"/g s/!!!!/ /g"

Re: How to include additional field from inputlookup in results?

DalJeanis — Wed, 20 Sep 2017 20:50:20 GMT

@HiroshiSatoh - This will work, but I'd only do it for very small numbers of error messages (no more than 10, or at most 20). map is very expensive for what you get.

Re: How to include additional field from inputlookup in results?

luc_k — Sun, 24 Sep 2017 09:52:06 GMT

Thanks a lot for your replies!

I receive this error:

Error in 'rex' command: The regex '(?i)(Error=not starting because the task should|Error=Error getting data)' does not extract anything. It should specify at least one named group. Format: (?...).