Merge Multiple Values into a Single Field
Hello,
I am looking for a way to merge several values into one field (or cell) in order to reduce the clutter in a table view.
For example, when parsing my Firewall logs I want to be able to pair a single IP address with all the destination ports for that IP.
I would look like this:
IP Address | Ports
---------------------------------
8.8.8.8 | 80, 443
192.168.1.1 | 22, 443, 25
I can't seem to figure this out but I imagine it has to be doable in Sumologic. Any thoughts??
-
I got this figured out and I'll post the answer here in case it helps someone else.
_sourceCategory=<YourSrcCategoryHere>
| count dst_ip, dst_port // this dedupes the values
| now() as _messagetime //Need this for transactionize
| transactionize dst_ip (merge dst_ip takeFirst, dstport join with ", ") //Formats the table
| fields - _messagetime -
Hi Joseph,
I am struggling with the same scenario. However, dstport join with ", " displays all the duplicate values as well. To qoute your example here.It gives us the result as -
IP Address | Ports
---------------------------------
8.8.8.8 | 80, 443, 443, 80, 80, 80
192.168.1.1 | 22, 443, 25, 22, 22In short, it does not remove the duplicates from the result. Do you know how we can remove these duplicate values and have only unique values here?
-
Yes, in order to remove duplicates you can do a couple things. First, you can 'count' the ports before the transactionize OR you can use the 'takeFirst' param in the transactionize statement.
If you are still having trouble post your query and I will look at it.
-
Ahh yeah I see what you're saying. You need to do a count before the transactionize to dedup the ports.
It is possible I do have this working, it just isn't clean.
-
| _sourceCategory=<srcCat>
| count dst_ip, dst_port
| now() as _messagetime
| transactionize dst_ip (merge dst_ip takeFirst, dst_port join with ", ")
| fields - _messagetime// Output will be:
dst_ip | dst_port
1.1.1.1 | 443, 80
Please sign in to leave a comment.
Comments
8 comments