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
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
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
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
Thank you. query solved my problem