how stats count any fields , top 3 valus

[siv]

for example 
i have this fields and valus:
stats count by username . i got this:

username
root | 102
admin | 71
yara | 34

this is the same for src
src
168.172.1.1 | 132
10.10.0.1 | 60
168.0.8.1 | 12

i want to see it one table
but the i want it to check all fields , like dst , port , mail... it could be any thing on the event
the goal is to get for each event the top field that have the most values that are repeated with the same value

[tscroggins]

Hi @siv,

The fieldsummary command summarizes field values and counts as a JSON array. We can use that to return the top three values for each field. In the case of a tie, all tied values are returned:

```
index=foo
```
| fieldsummary maxvals=0 username src dst port mail etc
| fields field values
| eval values=json_array_to_mv(values)
| eval count=mvindex(mvdedup(mvmap(values, spath(values, "count"))), 0, 2)
| mvexpand values
| mvexpand count
| where spath(values, "count")==count
| eval value=spath(values, "value")
| fields field value count

The use of mvexpand makes this a suboptimal solution, but we can build on this with better use of JSON and multivalue eval functions.

[tscroggins]

If we don't care about ties, we can filter the pre-sorted values field in place:

```
index=foo
```
| fieldsummary maxvals=0 username src dst port mail etc 
| fields field values
| eval values="[".mvjoin(mvindex(json_array_to_mv(values), 0, 2), ",")."]"

EDIT: See @PickleRick's answer re: maxvals=3. My only caution here is that distinct_count will no longer be exact. We haven't used the field in this result, but its behavior changes nonetheless.

[PickleRick]

You can just use maxvals=3 argument to fieldsummary.

[ITWhisperer]

Please could you give an example of what your desired output would look like?

[siv]

Username | count _username | src | src_count

root | 102 | 168.172.1.1 | 132

admin | 71 | 10.10.0.1 | 60

yara | 34 | 168.0.8.1 | 12

And if there is more fields search for the top three fields with the top three values

[tscroggins]

Here's an example using fieldsummary. Replace the base search and field list as needed. To summarize all fields, remove the field list. Note, however, that SplunkWeb doesn't handle results with many columns as well as it does results with many rows.

index=_internal source=*splunkd.log*
``` get top 3 values discarding ties ```
| fieldsummary maxvals=3 host log_level component
| fields field values
| eval values=json_array_to_mv(values)
``` use stats as an alternative to mvexpand ```
| stats count by field values
| fields - count
| spath input=values
| fields - values
``` uncomment expensive sort to show values in decreasing count order ```
``` | sort 0 - count ```
``` beware of field name conflicts ```
``` field names that conflict with values or counts will be overwritten ```
| eval {field}=value
 ``` using count_field instead of field_count helps sort columns ```
| eval {field}_count=count
``` replace row_key with another name if you already have a field named row_key ```
| streamstats global=f count as row_key by field
| fields - field value count
| stats values(*) as * by row_key
| fields - row_key