topic Retain columns in lookup table but not creating duplicates in Splunk Search

Retain columns in lookup table but not creating duplicates

tromero3 — Mon, 17 Aug 2020 18:20:46 GMT

I have a saved search which runs every month and looks at my vulnerability events and outputs the results into a lookup table. I am deduping the "Plugin ID" value so that I am only getting unique vulnerabilities in my lookup table. I have also added 3 extra columns to the lookup table, but the search results from the saved search will not have these columns . I'm struggling with how to retain the values of those columns while still appending new results to the table. The search below that I have tried, is retaining the extra columns but it is duplicating the results each time the search is run.

I've tried not using the append=t with the outputlookup but that just replaces my whole lookup table and deletes the extra columns that I need in there.

Is there any other way that I can use outputlookup and retain the extra columns but still deduping the plugin ID? Thank you!

| stats values(state) as State, values(severity) as Severity, values(tags) as "Tags", values(plugin.name) as "Plugin Name", values(plugin_publication_date) as "Plugin Publication Date", count by plugin_id | rename plugin_id as "Plugin ID", count as "Total Hosts" | eval Severity=lower(Severity) | sort num(Severity), -num("Total Hosts") | inputlookup Vulnerabilities append=t | dedup "Plugin ID" | outputlookup Vulnerabilities append=t

Re: Retain columns in lookup table but not creating duplicates

thambisetty — Mon, 17 Aug 2020 19:10:02 GMT

| stats values(state) as State, values(severity) as Severity, values(tags) as "Tags", values(plugin.name) as "Plugin Name", values(plugin_publication_date) as "Plugin Publication Date", count by plugin_id | rename plugin_id as "Plugin ID", count as "Total Hosts" | eval Severity=lower(Severity) | sort num(Severity), -num("Total Hosts") | inputlookup Vulnerabilities append=t | stats values(*) by "Plugin ID" | outputlookup Vulnerabilities append=t

stats values(*) is just to give you an idea, you can change.

upvote if my answer solves your problem

Re: Retain columns in lookup table but not creating duplicates

tromero3 — Mon, 17 Aug 2020 19:31:18 GMT

Hmm I dont understand. When I do that, all it does is add extra duplicate values in the column for Plugin ID....

Re: Retain columns in lookup table but not creating duplicates

thambisetty — Mon, 17 Aug 2020 19:44:45 GMT

can you provide csv lookup header.

Re: Retain columns in lookup table but not creating duplicates

tromero3 — Mon, 17 Aug 2020 22:31:37 GMT

Plugin ID, State, Severity, Tags, Plugin Name, Plugin Publication Date, Total Hosts, Case Number, Date Submitted, Days Since Submitted

The last 3 are the values that are not in the search, only lookup table.