Compare CSV file and indexed data

[vulnfree]

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.

[templets]

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

<basic search>
| table <fields I care about>
| rename <myfield_orig> as <myfield> 
| eval src="<an identifier for this search>"
| append 
    [ <second search> 
      | table <fields I care about>
      | rename <myfield_other> as <myfield> 
      | eval src="<an identifier for this search>"
    ]
| fillnull value="-" <myfield>
| stats values(*) as *, dc(SRC) as match_cnt by <myfield>

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:

| inputlookup test.csv
| table DNS *
| rename DNS as myField
| eval src="TEST"
| append
    [ search index="myindex"
      | table host * 
      | rename host as myField
      | src="MYINDEX" ] 

| fillnull value="-" DNS host 
| stats values(*) as *, dc(SRC) as match_cnt by myField

[to4kawa]

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

[vulnfree]

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.

[to4kawa]

please provide this result.

result count
index 0
csv 20

?

[manjunathmeti]

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

[vulnfree]

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?

[manjunathmeti]

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

[to4kawa]

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

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

[vulnfree]

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.