Hi, I've merged two lookup files using this query:
|join type=inner IP
The (condensed/sanitized) table looks like this:
IP tool1_ip tool2_ip date location tool3_mac tool4_mac
188.8.131.52 20181029 Krypton abcd1234
184.108.40.206 220.127.116.11 18.104.22.168 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
22.214.171.124 0 abcd1234 1
126.96.36.199 2 5678hijk 2
33.33.333.333 1 wxyz1111 1
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!