How to include additional field from inputlookup in results?

[luc_k]

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

[sbbadri]

try this,

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

[DalJeanis]

@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.

[gcusello]

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

[DalJeanis]

@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"

[luc_k]

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: (?...).

[HiroshiSatoh]

Try this!

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

[DalJeanis]

@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.