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.