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:
kbaeki cseunggyk rhsung
esalvi sameshr esalvi
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.