Multiple Values for open ports, trying to table only the open ones

[Abass42]

I was asked to create a query that will allow the user to see only the open ports. An example log looks something like this:

10/24/2023 06:00:04,source=SXXXX-88880000,destination=10.10.100.130,DuBlIn_,11.11.119.111,port_80=True,port_443=True,port_21=False,port_22=True,port_25=False,port_53=False,port_554=False,port_139=False,port_445=False,port_123=False,port_3389=False

it looks easy enough, I want to table port_*=True.

I want destination, src_ip, and the open ports.

I asked our equivalent of Chat GPT about it, and I got this. 

index=gpss sourcetype=acl "SXXXXXXX" destination="11.11.111.11"
| eval open_ports = case(
    port_123=="True", "123",
    port_139=="True", "139",
    port_21=="True", "21",
    port_22=="True", "22",
    port_25=="True", "25",
    port_3389=="True", "3389",
    port_443=="True", "443",
    port_445=="True", "445",
    port_53=="True", "53",
    port_554=="True", "554",
    port_80=="True", "80",
    true(), null()
)
| where open_ports!=null()
| mvexpand open_ports
| table _time, destination, gpss_src_ip, open_ports

But the open_ports!=null() wasnt allowed.  I get a 

Error in 'where' command: Type checking failed. The '!=' operator received different types.

During testing, I have a baseline event, an event with three open Ports, but that search I ran only outputs the first one in the list. It hits port 22 first, since thats the first on in the case statement that is true. 

My main question is, How do I successfully tell splunk to only grab the open ports that are True? Can i even do a wildcard somewhere, and request to pull port_* WHERE True

Thank you for any help 🙂 

[Abass42]

ne in the future, this is the final query I went with. I was trying to group any event in a certain index and sourcetype. 

index=test sourcetype=test2 source=*
| rex field=test_city "(?<city>[A-Za-z]+)_$"
| eval has_true_port = case(
    port_123="True" OR port_139="True" OR port_21="True" OR port_22="True" OR port_25="True" OR port_3389="True" OR port_443="True" OR port_445="True" OR port_53="True" OR port_554="True" OR port_80="True", 
    "Yes",
    true(),
    "No"
) 
| where has_true_port = "Yes" 
| stats values(port_123) as port_123, values(port_139) as port_139, values(port_21) as port_21, values(port_22) as port_22, values(port_25) as port_25, values(port_3389) as port_3389, values(port_443) as port_443, values(port_445) as port_445, values(port_53) as port_53, values(port_554) as port_554, values(port_80) as port_80 values(city) as City by destination, test_src_ip
| eval open_ports = if(port_123="True", "123,", "") . if(port_139="True", "139,", "") . if(port_21="True", "21,", "") . if(port_22="True", "22,", "") . if(port_25="True", "25,", "") . if(port_3389="True", "3389,", "") . if(port_443="True", "443,", "") . if(port_445="True", "445,", "") . if(port_53="True", "53,", "") . if(port_554="True", "554,", "") . if(port_80="True", "80,", "")
| eval open_ports = rtrim(open_ports, ",")
| table destination, test_src_ip City open_ports

The result looks a bit like this:

Basically, this combines each open port into one row while also sorting by destination ip and source IP

[inventsekar · Accepted solution]

Hi @Abass42 ,..

tried with rex and its working good. 

| makeresults
| eval _raw="10/24/2023 06:00:04,source=SXXXX-88880000,destination=10.10.100.130,DuBlIn_,11.11.119.111,port_80=True,port_443=True,port_21=False,port_22=True,port_25=False,port_53=False,port_554=False,port_139=False,port_445=False,port_123=False,port_3389=False"
| extract
| rex max_match=5 field=_raw "port\_(?P<open_ports>\d+)\=True"
| mvexpand open_ports
| table _time, destination, gpss_src_ip, open_ports

[Abass42]

Thank you. That did the trick. Adding a 

| stats values(open_ports) by destination 

allows me to group and add them all in one row. 

Thank you again for the prompt help 🙂

[danspav]

Hi @Abass42,

You can achieve this using the foreach command.

| makeresults
| eval _raw="10/24/2023 06:00:04,source=SXXXX-88880000,destination=10.10.100.130,DuBlIn_,11.11.119.111,port_80=True,port_443=True,port_21=False,port_22=True,port_25=False,port_53=False,port_554=False,port_139=False,port_445=False,port_123=False,port_3389=False"
| extract
``` Above is to generate the test data ```

``` Iterate through each port_xxx field to pick out the open ones ```
| foreach port_* [| eval open_ports=if(<<FIELD>>=="True", mvappend(open_ports, "<<MATCHSTR>>"), open_ports)]

| mvexpand open_ports
| table _time, destination, gpss_src_ip, open_ports

We use foreach to pick out all the fields that start with port_ and test to see if they are true. If they are, we add the number part of the field name (<<MATCHSTR>>) to a new multivalue field.

Then we continue with your mvexpand and table to show the results.

The results look like this:

The Splunk Docs page for ForEach explains the use of <<FIELD> and <<MATCHSTR>>

Cheers,
Daniel

[Abass42]

Thank you. I knew there was probably some way to iterate, but couldnt figure it out. Thank you.