need to update values of a lookup search by count

[pkharbanda1021]

Splunk Query
index="abc" source=def
[| inputlookup ABC.csv | table text_strings count | rename text_strings as search]

Problem: 
I need to count the text_string values but when I run the above search which searches the text_strings but I dont find a field called search with which I can count 

So need help
@somesoni2 if you can help please

[ITWhisperer]

Does index="abc" source=def already contain fields called search and count?

[pkharbanda1021]

NO the field comes via lookup column which I am converting to search so that it can search keywords in splunk but I wanted to use that field and i dont see in the data but search works.

[ITWhisperer]

If the fields don't exist in the events found in the index, perhaps that is why the search can't find them?

[pkharbanda1021]

I am not sure if you are following what I am saying 
let me explain
I have a lookup which has 2 columns 
Col1=xyz col2=count
xyz has values which I want to search in my data and that works fine and searches the keywords via lookup without the field existing in the data by converting into search ...goal is I need to count those results by search keywords which is what I am trying to solve.

[ITWhisperer]

If I am understanding you correctly, your search is finding all the events which have at least one match to the keywords (Col1 from your lookup)? Now you want to find the count (Col2 from your lookup) associated with the first keyword that each event matched to? Or do you want to find all the possible matches of keywords with their associated counts? Or, do you want to count the number of matches from the lookup that each event matches?

[pkharbanda1021]

last one 
count the number of matches from the lookup that each event matches

[ITWhisperer]

This has the potential for memory explosion and you may need to do this multiple times with different subsets of your lookup depending on the size of the lookup. Essentially, for every event, you add (using eventstats) a multivalue field with the full (or partial) contents of your lookup file, then you filter the field based on whether _raw (or whatever field you are look to compare with) matches each of the values in the mvfield, and finally count the number of matching strings that are left. This even has the bonus of identifying what the matches were.

index="abc" source=def
[| inputlookup ABC.csv | table text_strings | rename text_strings as search]
| append
  [| inputlookup ABC.csv
  | stats values(text_strings) as text_strings]
| table _raw text_strings
| eventstats values(text_strings) as text_strings
| where isnotnull(_raw)
| eval text_strings=mvmap(text_strings,if(match(_raw,text_strings),text_strings,null())
| eval count=mvcount(text_strings)

[pkharbanda1021]

this solves for one value only but when you have multiple values in your table like 

text_string 

value1
value2 
value3 

this will always pick the top value and shows the count where as the goal is to count all values/counts then overwrite the table with outputlookup ...I tried with 2 values and it only picks the first one and removes the otherone.

[ITWhisperer]

This is the count you asked for

last one 
count the number of matches from the lookup that each event matches

[pkharbanda1021]

I agree but the whole point of lookup is to have multiple values and when you run the above query with even 2 values in the lookup table this wont give you the right result.

[ITWhisperer · Accepted solution]

It looks like it is your filtering search - try it this way

index="abc" source=def
[| inputlookup ABC.csv | table text_strings | rename text_strings as query]
| append
  [| inputlookup ABC.csv
  | stats values(text_strings) as text_strings]
| table _raw text_strings
| eventstats values(text_strings) as text_strings
| where isnotnull(_raw)
| eval text_strings=mvmap(text_strings,if(match(_raw,text_strings),text_strings,null())
| eval count=mvcount(text_strings)

[pkharbanda1021]

I found a small issue after changing the search to query its not returning any results for keywords which has special characters and regex in that like 

"* not found"
and similar 
I believe query is unformatted search in terms of splunk please let confirm if I am wrong. @ITWhisperer  

[ITWhisperer]

Well you could try not having the filter on the search and instead filter later based on the mvcounts being non-zero

[pkharbanda1021]

Apologies for reaching out again ...couple of things 
I verified by running individual searches for the strings with regex like "* not found" and I found results so I tweaked the query but it seems like match function doesnt like the keyword and not returning any result. 

Please let me know if there is way to get it to work..thank you for your help !!

[ITWhisperer]

"* not found" is not a wildcard search in regex. You don't need the *. Match will find the remaining string anywhere in the field. Actually, so would search. Can you remove the * from your list of strings?

[pkharbanda1021]

that works ..thank you so much !!