Splunk Search
Highlighted

how can i see more fields after counting by another field ?

Path Finder

Hi ,
I have this query :
sourcetype= Filed=X [search sourcetype= Filed=X | iplocation IPAddress | stats dc(Country) AS MultipleCountrylogin by Username | where MultipleCountrylogin > 1 | table Username ] | iplocation IPAddress | dedup UserAgent | table Username, UserAgent , IPAddress| stats count by Username | where count > 1

which obviously shows me the stats of the usernames with the count where the count is bigger than 1.
but, i want now to print also the UserAgent AND IPAddress to all those Usernames.
is this possible ? if so, how can i do it ?

Thanks Guys.

Highlighted

Re: how can i see more fields after counting by another field ?

Contributor

Simply add those fields to your split-by clause so, stats count by Username, UserAgent, IPAddress. Then further down you can filter your search by user. Stats can theoretically split by an unlimited number of fields.

0 Karma
Highlighted

Re: how can i see more fields after counting by another field ?

Contributor

I also see you are using iplocation so you may only want to split by a single field. You can concatenate the fields you want using eval

eval usercombined = Username." - ".UserAgent." - ".IPAddress | stats count by usercombined

0 Karma
Highlighted

Re: how can i see more fields after counting by another field ?

SplunkTrust
SplunkTrust

If I understand right, the nuance here is that you still want to filter to the usernames that appear more than once in the overall list, but you want to list BOTH the username and the ip address.

If that's the case, adding the IPAddress field to the stats command will cause a problem cause it'll harm your ability to do that filtering . If the filtering was left intact, ie where count>1, you would actually filter out users that accessed once from ip X and once from ip Y.

So there are a couple answers. One is to roll up all the IP addresses in values(IPAddress) as IPAddress inside the stats command, and then mvexpand them after the filter.

sourcetype= Filed=X [search sourcetype= Filed=X | iplocation IPAddress | stats dc(Country) AS Multiple_Country_login by Username | where Multiple_Country_login > 1 | table Username ] | iplocation IPAddress | dedup UserAgent | table Username, UserAgent , IPAddress| stats count values(IPAddress)  as IPAddress by Username | where count > 1 | mvexpand IPAddress

There's another way to get the same end-result, by doing by UserName IPAddress, and then instead of | where count>1, have instead | eventstats sum(count) as totalUserCountForUser by UserName | where totalUserCountForUser>1

And.... to make a side comment, the search is a little strange. I assume the sourcetype terms in the base search are just typos, btu theer's something odd about the dedup UserAgent, and I'm not sure that's doing what you think it's doing. As written, for each value of UserAgent in the whole set, you'll get only the UserName and IPAddress about the single most recent event. So if bob uses mozilla version X from IP Y, and then alice uses mozilla version X from IP Z, because of that dedup, your end report will have no information at all about alice. Which seems wrong but maybe I'm missing something.

Highlighted

Re: how can i see more fields after counting by another field ?

Path Finder

Hey Guys,
finally this query did the job so i could see each user name that appears more than once and all the ips related to him + all useragents.

sourcetype=y Filed=X [search sourcetype=y Filed=X | iplocation IPAddress | stats dc(Country) AS MultipleCountrylogin by Username | where MultipleCountrylogin>1 | table Username ] | iplocation IPAddress | table _time Username UserAgent IPAddress | stats dc(UserAgent) AS ddd by Username | where ddd>1 | fields Username | join max=100 Username [search sourcetype=y Filed=X| rename _time AS time |table time Username UserAgent IPAddress Country] | iplocation IPAddress | table Username UserAgent IPAddress Country | stats values(IPAddress) values(Country) values(UserAgent) by Username

thank you for the suggestions !

View solution in original post