Splunk Search

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

Motivator

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.

0 Karma
1 Solution

Path Finder

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.

View solution in original post

Path Finder

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.

View solution in original post

Motivator

This works great. Thank you for your answer.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!