Getting Data In

Compare CSV file and indexed data

vulnfree
Loves-to-Learn Everything

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.

Tags (2)
0 Karma

to4kawa
SplunkTrust
SplunkTrust
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

0 Karma

vulnfree
Loves-to-Learn Everything

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.

0 Karma

to4kawa
SplunkTrust
SplunkTrust

please provide this result.

result count
index 0
csv 20

?

0 Karma

manjunathmeti
Champion

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
0 Karma

vulnfree
Loves-to-Learn Everything

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?

0 Karma

manjunathmeti
Champion

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
0 Karma

to4kawa
SplunkTrust
SplunkTrust

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

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

0 Karma

vulnfree
Loves-to-Learn Everything

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.

0 Karma