How do I search and compare fields from two different CSV files?

[tp92222]

I have two CSV files: dummy1 dummy2

dummy1 contains

server ip    apps running
10.1.1.1     Firefox, oracle, skypee
10.2.2.2     outlook, chrome
10.2.1.1     Firefox, msoffice

dummy2 contains

Vulnerability_id    apps affected
1                   Firefox,chrome
2                   Skype

This is my expected output:

server ip     vern_apps         vern_id
10.1.1.1      Firefox,skype     1,2
10.2.2.2      skype             2

[lguinn2]

Assuming that the field names are "server ip","apps running",Vulnerability_id, and "apps affected"
Try this

source=dummy2
| eval applist=`apps affected`
| makemv  delim="," applist
| mvexpand applist
| eval appname=applist
| join appname max=0 [ search source=dummy1
        | eval  applist=`apps running`
        | makemv  delim="," applist
        | mvexpand applist
        | eval appname=applist ]
| stats count(Vulnerability_id) as count list(Vulnerability_id) list("apps running" ) by "server ip"
| where count > 0

[somesoni2 · Accepted solution]

Try something like this

| inputlookup dummy1.csv | makemv app_running delim="," | mvexpand app_running | rename app_running as app | join type=inner app [| inputlookup dummy2.csv | makemv app_affected delim="," | mvexpand app_affected | rename app_affected as app ] | table server_ip apps Vulnerability_id 

[tp92222]

Thank you. query solved my problem