Appendcols or other options for searching a sub-search on port ranges

[jlundtristate]

Here is the example in the Splunk documentation:

specific.server | stats dc(userID) as totalUsers | appendcols [ search specific.server AND "text" | addinfo | where _time >= info_min_time AND _time <=info_max_time | stats count(field) as variableA ] | eval variableB = exact(variableA/totalUsers)

My need has a difference where the (appendcols [ search) is on a different target and the target is a lookup or inputlookup...

First search is the source, destination, protocol and destinationport seen in a given time period, the second search is a lookup table that has allowed traffic rules (source, destination, protocol, allowedport(s)), if the allowedport(s) is a range it can be 580-590.

search netactivity | stats count by source, destination, protocol, destinationport | appendcols [search inputlookup allowedrules | where Source=source and Destination=destination and Protocol=protocol | eval tmpport=(Port,"-"), portcnt=mvcount(tmpport) | eval startport=mvindex(tmpport,0), endport=if(portcnt>1, mvindex(tmpport,1), mvindex(tmpport,0)) | where startport<= destinationport AND endport>=destinationport | (table/stats/fields) Source, Destination, Protocol, Port, ApprovedBy ] | table source, destination, protocol, destinationport, Source, Destination, Protocol, Port, ApprovedBy

For the second search, I am trying to return the ApprovedBy field most importantly, but for validation a testing purposes, having the information from the rule that is being found, is beneficial. So I have tried the table, stats and fields clauses none of which has returned any values. My results are just the fields from the initial netactivity file.

I settled on trying to get the appendcols to work as I read the documentation, I believe it is the correct option. Lookup tables don't let me do a where clause and if all the allowed rules were a 1-1 relationship on the port (instead of ranges) maybe that would work better, but the port ranges rule out that option. Even if the allowed rules table was recreated to have a start and end port, lookup doesn't all for <= or >= in the clause. I also looked at join, but again the port range being a single port or a range of ports makes joining by an individual field impossible.

I figured this would have been a easy search, but I didn't find an example of anyone doing this. If anyone has implemented something along these lines, I would appreciated their insight.

Thanks in advance for any assistance.
Jason

[jlundtristate]

Thanks somesoni2, that worked great, I didn't think join was the answer, but you proved it is... :-}

[somesoni2 · Accepted solution]

Give this a try

search netactivity | stats count by source, destination, protocol, destinationport 
| join type=left source, destination, protocol, destinationport 
    [ | inputlookup allowedrules | eval Port=split(Port,"-") 
| eval s=tonumber(mvindex(Port,0))| eval e=tonumber(mvindex(Port,1))
| eval destinationport=mvrange(s,e+1,1) | mvexpand destinationport
| table Source Destination Protocol destinationport ApprovedBy 
| rename Source as source Destination as destination Protocol as protocol ]

[jlundtristate]

somesoni2, is there a limit to the number of entries in the join? for the high ports range 49152-65535, because we have a number of source and destination types. I'm afraid that the join/inputlookup is growing too large for the join to match in some instances. Our inputlookup allowedrules file has over 10,000 entries... so is there a way to pass and extra filter or two from the netactivity search to the inputlookup allowed rules

something like [| inputlookup allowed rules | search Port=(destinationport from the netactivity search) |...