Splunk Search

How do I count how many times a value appears in other fields?

Communicator

Hi, I've merged two lookup files using this query:

|inputlookup master_inventory.csv
|join type=inner IP
  [|inputlookup personal_inventory.csv]

The (condensed/sanitized) table looks like this:

      IP           tool1_ip      tool2_ip          date      location     tool3_mac       tool4_mac
 11.11.11.11                                     20181029     Krypton                     abcd1234
 22.22.22.222     22.22.22.222   22.22.22.222    20181029    Hogwarts     5678hijk        5678hijk
 33.33.333.333    33.33.333.333                  20181029       US        wxyz1111

How do I display the count of how many times each value appears in specified fields? (The specific fields would be tool1_ip, tool2_ip, tool3_mac, and tool4_mac). Maybe using mvappend() would help with this issue? Anyway, the result should spit out a table that looks like this:

     IP           ip_appears        mac        mac_appears
11.11.11.11           0          abcd1234           1
22.22.22.222          2          5678hijk           2
33.33.333.333         1          wxyz1111           1

Important notes:
1. The solution needs to be scale-able and not limited to the 2-3 rows/events I have above. In my real data, there are hundreds of rows/events.
2. Each tool# field is actually named after different asset management software, like ServiceNow or Solarwinds.
3. All mac address values are not separated by colons (:) in my data (unfortunately).
4. Thank you for your help!

0 Karma
1 Solution

Motivator

This may be too simplified for what you want but I think it would work.

| eval tool1_ip_count = if(tool1_ip=IP,1,0)
| eval tool2_ip_count = if(tool2_ip=IP,1,0)
| eval ip_appears = tool1_ip_count + tool2_ip_count
| eval tool3_mac_count = if(len(tool3_mac)>0,1,0)
| eval tool4_mac_count = if(len(tool4_mac)>0,1,0)
| eval mac_appears = tool3_mac_count + tool4_mac_count
| eval mac = case(len(tool3_mac)>0,tool3_mac,len(tool4_mac)>0,tool4_mac)
| table IP ip_appears mac mac_appears

View solution in original post

0 Karma

Motivator

This may be too simplified for what you want but I think it would work.

| eval tool1_ip_count = if(tool1_ip=IP,1,0)
| eval tool2_ip_count = if(tool2_ip=IP,1,0)
| eval ip_appears = tool1_ip_count + tool2_ip_count
| eval tool3_mac_count = if(len(tool3_mac)>0,1,0)
| eval tool4_mac_count = if(len(tool4_mac)>0,1,0)
| eval mac_appears = tool3_mac_count + tool4_mac_count
| eval mac = case(len(tool3_mac)>0,tool3_mac,len(tool4_mac)>0,tool4_mac)
| table IP ip_appears mac mac_appears

View solution in original post

0 Karma

Communicator

This worked for me actually. I had to add some edits of course, but the main solution was the if(tool#_ip=IP) and line 7. Thanks!

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!