Splunk Search

How to create new columns from results?

CyberMage
Engager

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.

Labels (2)
0 Karma
1 Solution

CyberMage
Engager

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"

View solution in original post

0 Karma

CyberMage
Engager

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"

0 Karma
Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...