How do you make a table based on a match between field and lookup?

[dhirendra761]

Hi All,

My base search has a "tags" field, which contains 10 values. Another lookupfile has the the same column tags with total 24 values inside.

Now, I have to match tags field values into the lookup file.

I need a table with tags and status. tags contains all the values(24) and status will be OK or Not OK based on match result with tags field values in my search.

Could you please suggest?

Thanks in advance.
Example:tags in my search :
Please refer SS for more info.

Dhirendra

[sdchakraborty · Accepted solution]

Hi Dhirendra,

If I understood your question correctly you need to keep your tags_export lookup tags in your output irrespective of whether those values are present in index right? Here is the run anywhere code, you need to replace the data preparation part with your base search,

| inputlookup tags_export
| join max=0 type=left tags
    [| makeresults count=2
| streamstats count
| eval tags = case(count=1,"SysImpSPDE_Status",count=2,"SysImpFRDE_Status")
 | dedup tags
| stats values(tags) as tags_mv
| eval tags = tags_mv
| mvexpand tags]
| eval tags_mv=split(tags_mv," ")
| eval tag_exist = mvfind(tags_mv,tags)
| eval status = if(tag_exist >=0 , "OK","Not OK")
| table tags,status

basically you need to replace the below portion with your base search,

| makeresults count=2
| streamstats count
| eval tags = case(count=1,"SysImpSPDE_Status",count=2,"SysImpFRDE_Status")

Sid

[dhirendra761]

Excellent!!.. On the first try, Query generated exact expected result. Thank you Sid. 🙂