I'm trying to create table with the top 5 results split into columns, so that I can have multiple results per line, grouped by date. Here's what I have:
|union
[search index=Firewall BlockFromBadActor| top src_ip by Date limit=5 | rename count as IPCount]
[search index=Firewall BlockFromBadActor| top dest_port by Date limit=5 | rename count as PortCount]
| stats values(*) as * by Date
| fields Date,src_ip,IPCount,dest_port,PortCount
Date src_ip IPCount dest_port PortCount
2022/11/25 | 1.1.1.1 2.2.2.2 3.3.3.3 4.4.4.4 5.5.5.5 |
5000 4000 3000 2000 1000 |
1 2 3 4 5 |
5000 4000 3000 2000 1000 |
2022/11/24 | 1.1.1.1 2.2.2.2 3.3.3.3 4.4.4.4 5.5.5.5 |
5000 4000 3000 2000 1000 |
1 2 3 4 5 |
5000 4000 3000 2000 1000 |
What I'm trying to get
Date IP 1 IP1 Count IP 2 IP 2 Count Port 1 Port 1 Count Port 2 Port 2 Count
2022/11/25 | 1.1.1.1 | 5000 | 2.2.2.2 | 4000 | 1 | 5000 | 2 | 4000 |
2022/11/24 | 1.1.1.1 | 5000 | 2.2.2.2 | 4000 | 1 | 5000 | 2 | 4000 |
I cannot seem to find any way to make the individual query results into new columns.
mvindex is the answer.
|union
[search index=Firewall BlockFromBadActor | top src_ip by Date limit=5 | rename count as "IPCount"]
[search index=Firewall BlockFromBadActor | top dest_port by Date limit=10 | rename count as "PortCount"]
| stats values(*) as * by Date
| eval "IP #1" = mvindex(src_ip,0)
| eval "IP #2" = mvindex(src_ip,1)
| eval "IP #3" = mvindex(src_ip,2)
| eval "IP #4" = mvindex(src_ip,3)
| eval "IP #5" = mvindex(src_ip,4)
| eval "Count for IP #1" = mvindex(IPCount,0)
| eval "Count for IP #2" = mvindex(IPCount,1)
| eval "Count for IP #3" = mvindex(IPCount,2)
| eval "Count for IP #4" = mvindex(IPCount,3)
| eval "Count for IP #5" = mvindex(IPCount,4)
| eval "Port #1" = mvindex(dest_port,0)
| eval "Port #2" = mvindex(dest_port,1)
| eval "Port #3" = mvindex(dest_port,2)
| eval "Port #4" = mvindex(dest_port,3)
| eval "Port #5" = mvindex(dest_port,4)
| eval "Port #6" = mvindex(dest_port,5)
| eval "Port #7" = mvindex(dest_port,6)
| eval "Port #8" = mvindex(dest_port,7)
| eval "Port #9" = mvindex(dest_port,8)
| eval "Port #10" = mvindex(dest_port,9)
| eval "Count for Port #1" = mvindex(PortCount,0)
| eval "Count for Port #2" = mvindex(PortCount,1)
| eval "Count for Port #3" = mvindex(PortCount,2)
| eval "Count for Port #4" = mvindex(PortCount,3)
| eval "Count for Port #5" = mvindex(PortCount,4)
| eval "Count for Port #6" = mvindex(PortCount,5)
| eval "Count for Port #7" = mvindex(PortCount,6)
| eval "Count for Port #8" = mvindex(PortCount,7)
| eval "Count for Port #9" = mvindex(PortCount,8)
| eval "Count for Port #10" = mvindex(PortCount,9)
| fields Date,"IP #1","Count for IP #1","IP #2","Count for IP #2","IP #3","Count for IP #3","IP #4","Count for IP #4","IP #5","Count for IP #5","Port #1","Count for Port #1","Port #2","Count for Port #2","Port #3","Count for Port #3","Port #4","Count for Port #4","Port #5","Count for Port #5","Port #6","Count for Port #6","Port #7","Count for Port #7","Port #8","Count for Port #8","Port #9","Count for Port #9","Port #10","Count for Port #10"
mvindex is the answer.
|union
[search index=Firewall BlockFromBadActor | top src_ip by Date limit=5 | rename count as "IPCount"]
[search index=Firewall BlockFromBadActor | top dest_port by Date limit=10 | rename count as "PortCount"]
| stats values(*) as * by Date
| eval "IP #1" = mvindex(src_ip,0)
| eval "IP #2" = mvindex(src_ip,1)
| eval "IP #3" = mvindex(src_ip,2)
| eval "IP #4" = mvindex(src_ip,3)
| eval "IP #5" = mvindex(src_ip,4)
| eval "Count for IP #1" = mvindex(IPCount,0)
| eval "Count for IP #2" = mvindex(IPCount,1)
| eval "Count for IP #3" = mvindex(IPCount,2)
| eval "Count for IP #4" = mvindex(IPCount,3)
| eval "Count for IP #5" = mvindex(IPCount,4)
| eval "Port #1" = mvindex(dest_port,0)
| eval "Port #2" = mvindex(dest_port,1)
| eval "Port #3" = mvindex(dest_port,2)
| eval "Port #4" = mvindex(dest_port,3)
| eval "Port #5" = mvindex(dest_port,4)
| eval "Port #6" = mvindex(dest_port,5)
| eval "Port #7" = mvindex(dest_port,6)
| eval "Port #8" = mvindex(dest_port,7)
| eval "Port #9" = mvindex(dest_port,8)
| eval "Port #10" = mvindex(dest_port,9)
| eval "Count for Port #1" = mvindex(PortCount,0)
| eval "Count for Port #2" = mvindex(PortCount,1)
| eval "Count for Port #3" = mvindex(PortCount,2)
| eval "Count for Port #4" = mvindex(PortCount,3)
| eval "Count for Port #5" = mvindex(PortCount,4)
| eval "Count for Port #6" = mvindex(PortCount,5)
| eval "Count for Port #7" = mvindex(PortCount,6)
| eval "Count for Port #8" = mvindex(PortCount,7)
| eval "Count for Port #9" = mvindex(PortCount,8)
| eval "Count for Port #10" = mvindex(PortCount,9)
| fields Date,"IP #1","Count for IP #1","IP #2","Count for IP #2","IP #3","Count for IP #3","IP #4","Count for IP #4","IP #5","Count for IP #5","Port #1","Count for Port #1","Port #2","Count for Port #2","Port #3","Count for Port #3","Port #4","Count for Port #4","Port #5","Count for Port #5","Port #6","Count for Port #6","Port #7","Count for Port #7","Port #8","Count for Port #8","Port #9","Count for Port #9","Port #10","Count for Port #10"