Hi, I have a question about MARS queries: I run queries using 'custom columns' and I continually hit over 5000 entries. I was wondering if there is a way to show the following:
- event type set
- source IP address
- destination IP address, port, and protocol
- <NEW FIELD> 'Hit-count'
The reason I posit the 'Hit-count' field is that this would help me see everything that happened on the first three columns and not limit me when MARS says 'only the first 5000 entries will be displayed'.
If there is any way to count the number of times it happened in a hit-count field, versus counting the number of times it happened and then limiting the displayed results, I would think that would be tremendously useful.
Please let me know if there is already a way to do this, or if there are any plans to add this! Thanks!
I'm not aware of any way to do that. The custom report is at the session level and no aggregation is possible. Well, beyond the aggregation that occurs within the available columns.
Actually, after looking at a query, I'm a little confused by what you're asking (and what I said above is not quite right). Every row in the above query should already be unique, so adding a "hit-count" field isn't going to lessen the rows, it will just add a counter. For example, change the columns to:
event type set, source address, destination address
And you should have a unique row for each event type, src ip, and dst ip.
To your response, I do have a unique row for each event type, source IP, and destination IP.
However, if the sessions exceed 5000, not all unique sessions are displayed. Also, why is there no way to tell how many times a unique session occurred (i.e. hitcount)?
"However, if the sessions exceed 5000, not all unique sessions are displayed"
You're not looking at unique sessions, but I think you get that. Are you trying to say that not all unique sessions are represented in the aggregate data returned? I had always assumed that what MARS really meant with that popup is that the resulting aggregated rows exceeded 5000, not that the number of session rows to be aggregated exceeded 5000. I never really thought about it too much because I use that functionality so rarely.
"Also, why is there no way to tell how many times a unique session occurred (i.e. hitcount)?"
I agree, there probably should be a column that adds a counter.
ugh. I'm trying to run a query that would definitely tell me whether the limitation is 5000 rows pre or post aggregation. It's taking f-o-r-e-v-e-r. It hasn't come back with the popup message, so perhaps it is the number of rows in the results that matter. That makes the most sense when I think about it.
Exactly that - not everything is displayed because the aggregate ends up exceeding 5000.
For example, "EVENT A: SOURCE IP A: DEST IP A" happens 3000 times, one aggregate is displayed. Then, "EVENT B: SOURCE IP B: DEST IP B" happens 3000 times, one aggregate is displayed. I know for a fact that there are 40 more events, IPs, etc., but in the query output, only "EVENT A: SOURCE IP A: DEST IP A" and "EVENT B: SOURCE IP B: DEST IP B" is displayed.
I would want a list of the rest of these *unique* aggregates and not be cut off because the first two aggregates happened 5000+ times within my scoped timeframe.
Thanks for your replies!
If it really works this way, well that would suck. I can't think of a way to work around that within the system. I'm trying to run a query to prove this out on one of my MARS boxes, but it's dog slow.
Don't know about queries, but you define 'Count' in MARS rules, so you could clone the built-in rule and perhaps modify the count value to suit your needs. I know this is not exactly what you are looking for but it might get you going in the right direction. You also have the following variables to play with to further suit your needs:
ANY-(Default). Signifies that the IP address for each count is any IP address.
SAME-Signifies that the IP address for each count is the same IP address. This variable is local to its offset.
DISTINCT- Signifies that the IP address for each count is a unique IP address. This variable is local to its offset.
$Target01 to $Target20-The same variable in another field or offset signifies that the IP address for each count is the same IP address.
Have a look at:
Also on strange idea, but it might work, in the "Maximum Number of Rows Returned" why don't you try and put 1000, does the MARS accept that? I seriously doubt it would work, but worth a try. I think they used to have an even lower limit in older version (1000).
Another workaround could be done like this:
You run the initial query and find numerous entries for IP 220.127.116.11 (1000 entries), IP 18.104.22.168 (2000 Entries), you could then modify your query to add these IPs with the "Not Equal" operator, this way they would not be part of this query anymore, making room for more meaningful display, but then again this is just a band-aid solution :)
That's true, I could do that.
I usually end up having to run multiple queries filtering by source subnets, etc.
Thanks for the reply.