topic Re: Compare CSV file and indexed data in Getting Data In

Compare CSV file and indexed data

vulnfree — Fri, 17 Apr 2020 17:31:58 GMT

How can I compare CSV file test.csv with one column, lets name the column"DNS" to index=myindex with field name "host"? Preferably I'd like to output the results in a pie chart where the indexed data is the total and I can do a dif against the test.csv DNS column.

Re: Compare CSV file and indexed data

to4kawa — Fri, 17 Apr 2020 20:19:41 GMT

see https://docs.splunk.com/Documentation/Splunk/latest/Knowledge/ConfigureCSVlookups

Pie Chart display numeric value.
compare
What do you mean this?

Re: Compare CSV file and indexed data

vulnfree — Mon, 20 Apr 2020 14:25:26 GMT

Thanks that was helpful, but I still don't know how to get the dif displayed. Meaning, I want items in my test.csv displayed that no events are matching.

Step 2 would be to compare the amount of hosts in test.csv that are not matched with events, to the total number of host names reporting for a certain index and sourcetype.

Re: Compare CSV file and indexed data

manjunathmeti — Mon, 20 Apr 2020 14:40:59 GMT

Try this:

index=myindex | append [ |inpulookup  test.csv | rename DNS as host] | stats count by host | stats count(eval(if(count=1, 1, null))) as no_event_hosts, count(eval(if(count>1, 1, null))) as hosts_with_events

Re: Compare CSV file and indexed data

vulnfree — Mon, 20 Apr 2020 17:06:29 GMT

Thanks for helping out, but this didnt produce the outcome I'm looking for.

Is it possible to output the test.csv DNS column and host column from the indexed data and then do a dif?

Re: Compare CSV file and indexed data

to4kawa — Mon, 20 Apr 2020 20:44:46 GMT

index=myindex| stats count by host | rename host as DNS |eval result="index"|append [| inputlookup test.csv |eval result="csv" ]|stats count(DNS) as tmp dc(DNS) as unique|eval duplicated = tmp - unique | eval diff = unique - duplicated | table duplicated diff  |transpose column_name=result| rename "row 1" as count

Viz>>Pie Chart

Re: Compare CSV file and indexed data

vulnfree — Mon, 20 Apr 2020 21:12:51 GMT

It doesnt give me the diff. It displays the number of entries in my CSV not the difference between whats in the CSV and my indexed data.

Re: Compare CSV file and indexed data

to4kawa — Mon, 20 Apr 2020 21:17:05 GMT

please provide this result.

result count
index 0
csv 20

Re: Compare CSV file and indexed data

manjunathmeti — Mon, 20 Apr 2020 21:17:31 GMT

Then you can simply do this:

 index=myindex | stats count by index | appendcols [ |inpulookup  test.csv | stats count as csv_count ] | eval diff = count - csv_count

Re: Compare CSV file and indexed data

templets — Tue, 12 Oct 2021 00:05:38 GMT

I do this by using stats values to combine two searches, then look for differences. The basic method follows:

The key is to make sure that the fields you want to compare have the same and not contain null values. After, you can check the SRC field for unique values, and it will tell you which search is was from. You can also filter for what is in common (match_cnt==2) or different.

For your search something like this: