How to search for a pair of substrings in a subsearch to filter my results?

[gcusello]

Hi at all,
I have a lookup with two fields:

• field1
• field2

I have to filter a search using the pairs of the two fields:

• aaa bbb
• aaa ccc
• ddd eee
• fff ggg
• hhh iii
• hhh jjj
• hhh kkk

My problem is that in my search I don't have fields in which to search for the two values, but I have to search them as strings

2016-12-06 13:04:27,819 133063049 [pool-8-thread-2] ERROR loggerinformation.internal.it.copergmps ? - Code <5017> Type <1> descr < [WebContainer : 45] EJBException{HASERVICES}it.coper.soa.agg_v03.serv.aaa03Bean :

I'm trying to search for the strings aaa and bbb in the last line. I know how to search using a subsearch as substring but I don't know how to search two substrings.

Someone can help me?

Bye.
Giuseppe

[gokadroid · Accepted solution]

Scenario looks similar to one I answered here where you need to match the text in a lookup with the events first and then also table out the values which were used to match in the events together with entire event:

Logic being:

• Outer search matches your lookup strings in events
• Rename _raw as rawText so not to lose it downstream
• Take out all the strings in your lookup in a field called foo
• Split foo as multivalue field
• Expand the field foo and match it piecemeal in your rawText.
• When matched table it out with rawText and foo. 

You can modify it accordingly, specifically this comment query.

[gcusello]

I have an error in eval command: expression malformed

| eval foo=[ | inputlookup funz.csv | search funz="Anagrafe" | eval query=serv | eval query=mvjoin(query,",") | fields query | format "" "" "" "" "" "" ]

where I'm going wrong?
Bye.
Giuseppe

[gcusello]

Ok the problem is that I forgot to use stats command to aggregate results that are very many!
Every way now runs, slowly, but runs!
Thank you.
Bye.
Giuseppe

[sundareshr]

See if this helps...

index=foo sourcetype=bar [| inputlookup somecsv.csv | eval search=field1." OR ".field2 | return search] | rest of your search

[gcusello]

Thank you sundareshr ,
I already tryed using query

| eval query="*"+lookup_field1+"* *"+lookup_field2+"*" | fields query

And in this way I find results in my main search, but the problem is that I need the query values of each event.
Is there a way to pass the query value bot as value and as a field from a subsearch?
Bye.
Giuseppe

[sundareshr]

Not sure I understand. Can you share some sample? You want eval x_{lookup_field1}=lookup_field1 | rename x_* AS *?

[gcusello]

this is my search

index=syslog
[ | inputlookup funz.csv 
   | eval query="*"+serv+"* *"+oper+"*" 
   | fields query
    ]
| stats values(serv) AS serv values(oper) AS oper count by field1 fields2

where "serv" and "oper" are lookup fields that I have to use to search in text search in my main search.
My problem is that before stats command there isn't a field called "query" to show.

I need to show a stat by field1 and field2, but I have to show also serv and oper and I don't know how to take them.

Thank you.

Bye.
Giuseppe

[sundareshr]

Does the lookup file have field1 and/or field2? If not, how do you decide which serv/oper pair maps to which event in syslog?

I'm thinking, something like this

index=syslog
 [ | inputlookup funz.csv 
    | eval query="*"+serv+"* *"+oper+"*" 
    | fields query
     ]
| lookup funz.csv field1 OUTPUT serv oper
| stats values(serv) AS serv values(oper) AS oper count by field1 fields2

[gcusello]

my lookup has both the fields.
The problem is that I use serv and oper in text search not in a field search so to do this I have to use query field, and in this case query value isn't stored in a field.
In other words I don't know how to have query value in a field.
I have to use both of them, but there is the problem also with one of them.
In your example I haven't field1 in my search result because serv and oper are a substring of my row, see the following example
2016-12-06 13:04:27,819 133063049 [pool-8-thread-2] ERROR loggerinformation.internal.it.copergmps ? - Code Type descr < [WebContainer : 45] EJBException{HASERVICES}it.coper.soa.agg_v03.serv.aaa03Bean :
serv=aaa oper=bbb
as you can see aaa is a part of it.coper.soa.agg_v03.serv.aaa03Bean and bbb is a part of
aggbbb but they arent fields of my result.
In other words if I could use the "query" field after subsearch I'd solve my problems.
Or if it could be possible to store a value in a variable setted in my subsearch and used after pipe.

Bye.
Giuseppe

[sundareshr]

How about this?

| inputlookup funz.csv | fields serv oper | map search="index=syslog $serv$ $oper$ | eval oper=\"$oper\"$ | eval serv=\"$serv$\""

[gcusello]

I have no results and this message in Process Inspector
The search result count (493) exceeds maximum (10), using max. To override it, set maxsearches appropriately.
Unable to run query 'index=....
Bye.
Giuseppe

[sundareshr]

Add maxsearches option to the max command.

 | inputlookup funz.csv | fields serv oper | map maxsearches=500 search="index=syslog $serv$ $oper$ | eval oper=\"$oper\"$ | eval serv=\"$serv$\""

[jmallorquin]

Hi cusello,

Mayby you should use the rename command, review this part of the documentation if helps:

http://docs.splunk.com/Documentation/Splunk/6.5.1/Search/Changetheformatofsubsearchresults

hope i help you

[gcusello]

Hi jmallorquin,
my problem is that I have fields in lookup but not in search.
I have to search using only values not fields and I did it using query.
But the problem is that I need to know the query values (there are two values for each lookup row) that are satisfied by each result.
But query value isn't recorded in any field.
Bye.
Giuseppe

[rjthibod]

I am little unclear what you mean.

Are you saying that the two fields will appear in the raw events as a concatenated string like "aaabbb", "dddeee", "hhhjjj", etc?