Search combined with inputlookup

[David_M]

VERY new to splunk.  I have a query that scans a vulnerability report for critical vulnerabilities:

index=vulnerability severity=critical
| eval first_found=replace (first_found, "T\S+", "")
| eval first_found_epoch=strptime(first_found, "%Y-%m-%d")
| eval last_found=replace (last_found, "T\S+", "")
| eval last_found_epoch=strptime(last_found, "%Y-%m-%d")
| eval last_found_65_days=relative_time(last_found_epoch,"-65d@d")
| fieldformat last_found_65_days_convert=strftime(last_found_65_days, "%Y-%m-%d")
| where first_found_epoch>last_found_65_days
| sort -first_found
| dedup cve
| rename severity AS Severity, first_found AS "First Found", last_found AS "Last Found", asset_fqdn AS Host, ipv4 AS IP, cve AS CVE, output AS Description
| streamstats count as "Row #"
| table Severity,"First Found","Last Found",Host,IP,CVE,Description,Reason

Which gives me output similar to this:

critical 2023-10-11 2023-11-20 host1.example.com 192.168.101.12 CVE-2021-0123 blah blah blah
critical 2023-03-25 2023-11-20 host2.example.com 192.168.101.25 CVE-2022-0219 blah blah blah
critical 2023-06-23 2023-11-20 host3.example.com 192.168.101.102 CVE-2023-0489 blah blah blah
critical 2023-08-05 2023-11-20 host4.example.com 192.168.101.145 CVE-2023-0456 blah blah blah

I also have a .csv lookup file where I keep extra information on certain hosts:
ScanHost                      ScanIP                   target-CVE            Reason
host2.example.com 192.168.101.25 CVE-2022-0219 CVE can not be mitigated

What I'm trying to do is to take the Host from the search and if it matches a ScanHost in the CSV then fill in the Reason field from the .csv.

[richgalloway · Accepted solution]

You would not be the first person to conflate the inputlookup and lookup commands.  This is a classic use case for lookup.  Insert the lookup command late in the query to pull the reason from the CSV.

index=vulnerability severity=critical
| eval first_found=replace (first_found, "T\S+", "")
| eval first_found_epoch=strptime(first_found, "%Y-%m-%d")
| eval last_found=replace (last_found, "T\S+", "")
| eval last_found_epoch=strptime(last_found, "%Y-%m-%d")
| eval last_found_65_days=relative_time(last_found_epoch,"-65d@d")
| fieldformat last_found_65_days_convert=strftime(last_found_65_days, "%Y-%m-%d")
| where first_found_epoch>last_found_65_days
| sort -first_found
| dedup cve
| lookup mylookup.csv ScanHost as asset_fqdn target-CVE as cve OUTPUT Reason
| rename severity AS Severity, first_found AS "First Found", last_found AS "Last Found", asset_fqdn AS Host, ipv4 AS IP, cve AS CVE, output AS Description
| streamstats count as "Row #"
| table Severity,"First Found","Last Found",Host,IP,CVE,Description,Reason

Pro tip: do everything you can to avoid using hyphens in field names.  Splunk sometimes interprets it as a minus operator, which can break a query.