Compare list of used credentials in events with list of total credentials in lookup

[parkz]

I have a lookup of all active credentials from tenable called tio_credentials.csv. I have a search that lists unique credentials used, like so:

`tenable` `io` earliest=-15d pluginID=19506
| rex field=plugin_output "'(?<domain>.*\\\)?(?P<Credentialed_Checks>.*)'"
| stats dc(host-ip) as count by Credentialed_Checks

How do I compare the list of credentials from Splunk events with the lookup in a way that shows all the credentials in the lookup that aren't showing up in events? I'm new to splunk and trying to see if there's any credentials we can remove from our credentials list.

[richgalloway]

The general method is to read the lookup file using a where clause that excludes credentials that are indexed.  The SPL looks something like this:

| inputlookup tio_credentials.csv where NOT [ search `tenable` `io` earliest=-15d pluginID=19506
  | rex field=plugin_output "'(?<domain>.*\\\)?(?P<Credentialed_Checks>.*)'"
  | stats dc(host-ip) as count by Credentialed_Checks
  | rename Credentialed_Checks as <<equivalent field name in the lookup>>
  | fields - count
  | format ]

[ITWhisperer · Accepted solution]

Depending on what your credentials field is in your csv

`tenable` `io` earliest=-15d pluginID=19506
| rex field=plugin_output "'(?<domain>.*\\\)?(?P<Credentialed_Checks>.*)'"
| stats dc(host-ip) as count by Credentialed_Checks
| append
  [| inputlookup tio_credentials.csv
   | rename credential as Credentialed_Checks
   | eval csv="True"
   | fields csv Credentialed_Checks]
| stats count values(csv) as csv by Credentialed_Checks
| where count = 1 AND csv="True"