topic Re: Stats Distinct Count (X) + X[Values] in Splunk Search

Stats Distinct Count (X) + X[Values]

JRamirezEnosys — Fri, 21 Apr 2017 06:08:03 GMT

Hi Splunkers,

I have a query that gives me the following fields I want to work with.

username
Country (after using iplocation on src_ip)

Some usernames have login from more than 2 countries.

I want to see it on a chart that shows me the top 5 usernames based on how many countries they have log in and which countries are those.

I believe I need to use dc(Country) at some point

This is my search string

index=main event=sso status=success ip!=10.0.0.0/8  | regex subject=^\D\d+$ | iplocation ip | stats dc(Country) by subject

Re: Stats Distinct Count (X) + X[Values]

andrey2007 — Fri, 21 Apr 2017 12:37:53 GMT

Try this

index=main event=sso status=success ip!=10.0.0.0/8  | regex subject=^\D\d+$ | iplocation ip | stats dc(Country)  as Country_count values(Country) as Countries by subject | sort 5 - Country_count

Re: Stats Distinct Count (X) + X[Values]

DalJeanis — Fri, 21 Apr 2017 14:10:31 GMT

Based on your description and code, I have to assume that the field "subject" is holding the username...

Re: Stats Distinct Count (X) + X[Values]

somesoni2 — Fri, 21 Apr 2017 15:02:20 GMT

If your end goal is to see the chart, try this version and select a stacked column/bar chart.

index=main event=sso status=success ip!=10.0.0.0/8  | regex subject=^\D\d+$ | iplocation ip | stats dc(Country)  as Country_count values(Country) as Countries by subject | sort 5 - Country_count
| mvexpand Countries | chart count over subject by Countries