topic Re: How to list only distinct values from the listed results in Splunk Search

How to list only distinct values from the listed results?

rakeshyv0807 — Thu, 21 Jul 2022 15:26:10 GMT

Hi I have a query which runs and results me the list of Ip's in a table format grouped by username.

In my table of results there might be different IP's for the same username which are listed down in the single IP cell. Please find below the example of my result table:

Username-------------------------------------------Ipaddress------------------------application---------------------------city-----------------------------country
1) abcd--------------------------------------------------123.123.123.12---------------------xyz---------------------------------asdf-----------------------------zxcvb
123.123.123.12 xyz asdf zxcvb
234.456.677.22 ghj ghjk fghjk

2) dfgh--------------------------------------------------234.123.12.345----------------------ssss------------------------------dfggh----------------------------ghjhjkk

As shown above for one username there will be list of ip's and corresponding city and country info are displayed. What i want to achieve here is that I need to display only distinct ip's for each username. How can I do it?

To display my results in above table I am using the following search:

mysearch
| iplocation clientip1
| streamstats count as occuranceCount list(clientip1) as client_IP, list(applicationid) as application list(Country) as Country, list(City) as City by subject
| sort - occuranceCount
| dedup subject
| table subject occuranceCount client_IP connectionid City Country

Please help!
Thanks in advance

Re: How to list only distinct values from the listed results

mayurr98 — Tue, 27 Mar 2018 17:42:52 GMT

Instead of list try values? i.e. values(clientip1) as client_IP

let me know if this helps!

Re: How to list only distinct values from the listed results

rakeshyv0807 — Tue, 27 Mar 2018 17:58:42 GMT

I have tried the values(clientip1) as client_IP but since I am deleting duplicates by username using dedup so the different Ip's of same username are not being displayed.

Re: How to list only distinct values from the listed results

mayurr98 — Tue, 27 Mar 2018 18:18:34 GMT

well you are doing by subject which is already distinct so dedup subject will not make any sense.

can you try this?

mysearch 
| iplocation clientip1 
| streamstats count as occuranceCount by subject clientip1 applicationid Country City 
| table subject occuranceCount clientip1 connectionid City Country 
| sort - occuranceCount 
| dedup subject

Re: How to list only distinct values from the listed results

rameshnaik — Tue, 24 Nov 2020 11:50:07 GMT

I want list out all values of variable ban in the string "ban":12897

Using below command but nothing is listed.

index=k8_bm* "jeopardyType" "Prepaid-Service-Error" "ban" | stats values(ban)

Re: How to list only distinct values from the listed results

thambisetty — Tue, 24 Nov 2020 12:53:57 GMT

you can apply any stats functions like values/list/avg/median etc to only field names. based on your query ban must be field in your index.

Re: How to list only distinct values from the listed results

JanniktheOne — Wed, 20 Jul 2022 08:13:30 GMT

values is what I was searching for. It only shows distinct lists.