How to list only distinct values from the listed results?

[rakeshyv0807]

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

[thambisetty]

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.

[rameshnaik]

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)

[mayurr98]

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

let me know if this helps!

[JanniktheOne]

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

[rakeshyv0807]

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.

[mayurr98]

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