Splunk Search

How to dedup a combination of two fields and get the count of unique values per host?

New Member

We have devices that generate thousands of a particular entry. I created a daily search to summarize. I combined the srcint and destint into a single field labeled interfaces. What my boss wants is to see the total number of events per host, but only unique to the new field. The problem is he also wants to dedup the interfaces field even if the srcint and destint are reversed like this:
alt text

Here is the current search:

index=network  sourcetype="cisco:ios*"  (key_word="MAC_MOVE-SP-4-NOTIF" OR key_word="MAC_MOVE-SW1-4-NOTIF") 
|  eval Interfaces = src_int + "," + dest_int |  table host, Interfaces | sort host | dedup Interfaces 

What I need is to somehow dedup the "Interfaces" field even if the 2 fields that make up the eval are reversed. I then need to add a count of unique "Interfaces" entries per host after the dedup.

Thanks!

0 Karma

SplunkTrust
SplunkTrust

Try something like this

index=network  sourcetype="cisco:ios*"  (key_word="MAC_MOVE-SP-4-NOTIF" OR key_word="MAC_MOVE-SW1-4-NOTIF") 
 |  eval Interfaces = mvsort(split(src_int + "," + dest_int,","))| nomv interfaces |  table host, Interfaces | sort host | dedup Interfaces 

Explorer

Ah. I gotcha. This works though for the count. Thanks so much for your help!

0 Karma

Explorer

I had tried that but it gives the same total for each line:

Host Interfaces Count
ns-s-972brus-6509c Gi7/37 47
Po246

ns-s-972brus-6509c Gi7/48 47
Po246

ns-s-972brus-6509c Gi4/25 47
Po246

ns-s-972brus-6509c Gi4/23 47
Po246

What I need is the count of the number of events for each pair of interfaces. For this search the total number of events was 47 so I would expect the count for each pair to add up to 47 total.

Thanks!

0 Karma

SplunkTrust
SplunkTrust

I should've pointed this one out, after dedup, there will be only 1 entry available for each interface, so I guess you need the count of events what was before the dedup. Try this:

index=network  sourcetype="cisco:ios*"  (key_word="MAC_MOVE-SP-4-NOTIF" OR key_word="MAC_MOVE-SW1-4-NOTIF") 
   |  eval Interfaces = mvsort(split(src_int + "," + dest_int,","))| nomv interfaces | stats count latest(host) as host by Interfaces | sort host 
0 Karma

New Member

actually it would be the # of events AFTER the dedup

0 Karma

SplunkTrust
SplunkTrust

Try this

 index=network  sourcetype="cisco:ios*"  (key_word="MAC_MOVE-SP-4-NOTIF" OR key_word="MAC_MOVE-SW1-4-NOTIF") 
  |  eval Interfaces = mvsort(split(src_int + "," + dest_int,","))| nomv interfaces |  table host, Interfaces | sort host | dedup Interfaces  | eventstats count 
0 Karma

New Member

OK. Almost there. This is awesome. All I need now is to add a column with the count of the number of events before the dedup.

thanks!

0 Karma