How to group by src_ip and getting the same source and destination IPs repeated or duplicate entries?

[BhushanGurav]

Hi, I am working on use case which has following requirements

1. high number of connections to external DNS IPs from non-authortized internal DNS servers (i.e. end users or even servers)

2. connections have higher upload versus download bytes

I am developing query as

index=*_fw_* (src=internal_ips) NOT (dest=external_ips) AND (dest_port=53) bytes_out>0
| eventstats sum(bytes_out) AS total_bytes_out by src
| eventstats sum(bytes_in) AS total_bytes_in by src
| where total_bytes_out > total_bytes_in
| stats count by src _time dest dest_port total_bytes_out total_bytes_in sourcetype host app dstcountry ftnt_action index osname packets_out packets_in policyname product service srcmac src_translated_ip srcname subtype eventtype transport user vd vendor vendor_action _raw
| sort - total_bytes_out | uniq

But I'm getting same source and destination IPs repeated or duplicate entries.

I want to view by grouping source ips such that only unique source IPs will be displayed along with all other fields.

[yuanliu]

Pro tip: If you are going to illustrate complex code sample, illustrate and explain data first. You talk so much about source IP, but it is not even clear which field represents it. How do you expect others to help? Secondly, if the code sample does not return the result you wanted, illustrate output and explain why that's not what you want. It is totally unclear what you want from "| stats count by src _time dest dest_port total_bytes_out total_bytes_in sourcetype host app dstcountry ftnt_action index osname packets_out packets_in policyname product service srcmac src_translated_ip srcname subtype eventtype transport user vd vendor vendor_action _raw".

In your code, uniq does not operate on stats output. Even if it does, your stats groupby clause includes so many different fields many of them can have repeating values while the group can still be unique. Even worse, your groupby includes _time, even _raw. This guarantees that every entry will be unique no matter what source IP is.

If you want to source IP to be unique, only group by source IP (I assume that is src - please explain whether that is true or not). I suggest you first try

| stats values(*) as * by src

Review the results, then determine what to do next. If you must group by more than one field, make sure those extra fields are dependent on source IP, i.e., those fields can only have one value for each source IP. Obviously, _time and _raw are not dependent on source IP, nor are the majority of fields in your groupby clause.

Hope this helps.

[BhushanGurav]

hello @yuanliu ,

1. the reason behind not mentioning src as source IP is, it is very clear to what I have mentioned in query against "src" field. There is no other field indicating to what source IP field could possibly be. so there is no doubt in what source IP field is mentioned.

2. I clearly mentioned the result is giving me duplicate entries for source IPs (src) which I would want as unique results of src. To further elaborate, I want all the fields for analysis (highlighted in question) but grouping by source IP (src) for unique source IP addresses. Thus, my question was that how can I achieve that and what changes should be done in query for such advanced query .

3. The point of making sure that the extra fields should be dependent on source IP is well noted and will work on results based on that. Thanks for your inputs.

[yuanliu]

Have you examined output from

| stats values(*) as * by src