How to save the results of a function and reuse them in a search and have a clause based on count of >1

[wrangler2x]

New to splunk but getting somewhere with learning to construct complex searches. The goal of the search below is to look for instances where multiple login credential failures via a central web-based authentication mechanism are associated with one non-U.S. IP address (client) and that these multiple failures involve more than one user-id (field is called webauth_ucinetid). The approach I took was to use the dc() function to get a count of unique user-ids, and the list() function to put them together as multivalues associated with their given, common, IP address. Here is the search:

index="syslogs_webauth" webauth_action="login" webauth_success="N" geoip | search client_country_code!="US" | stats dc(webauth_ucinetid) List(webauth_ucinetid) AS "UCInetID" count by webauth_ip client_country_code| rename client_country_code AS "CC" | sort -dc(webauth_ucinetid) | head 20

An example of what the search returns is shown here:

webauth_ip	CC	dc(webauth_ucinetid)	UCInetID	Count
114.143.187.210	CN	3	kbaeki cseunggyk rhsung	3
120.87.95.70	IN	2	esalvi sameshr esalvi	3
121.246.27.18	CN	2	zonglial zonglail	2
220.137.253.128	TW	2	curun is\\curun	2
58.186.87.156	VN	2	echcao ecao	2

So the first thing I wonder about is how I could avoid using this dc(webauth_ucinetid) function twice. Is there a way to do the function the first time and save it and reuse it in the sort?

The second thing I wonder about is that if there are few enough of these that the top 20 will include instances where the dc count is 1, and I don't really want to see anything that is dc count of 1 because it is not interesting. But I can't figure out how to say that! Also, I'd like to turn this into an alert where it only sends an email if there are results and that the results are where dc count is >1. Can't figure-out how to do this.

the geoip is a macro for a lookup using the GeoIP lite database for country codes, etc., and simply uses the lookup command to use a python script to do this and create the client_country_code field you see being used as 'CC'. This macro has a leading pipe in it.

[eelisio2 · Accepted solution]

Edit the stats and sort commands as below. Add the search command.

| stats dc(webauth_ucinetid) AS DCOUNT List(webauth_ucinetid) AS "UCInetID" count by webauth_ip client_country_code| rename client_country_code AS "CC" | search DCOUNT > 1 | sort -DCOUNT | head 20

When you create the alert through the Search App, you can trigger on number of events greater than zero.

[wrangler2x]

This works great. Thank you for your answer.