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

[russell120]

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!

[kmaron · Accepted solution]

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

[russell120]

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!