My ultimate goal is to have a table that displays the "Term" describing the login span, # of users that fall under the respective category, and showing the Country, City.
So basically it groups the amount of user logins by a certain term, and then groups them again by the Country/City.
What I have so far is:
| stats earliest(_time) as first_login latest(_time) as last_login by IP_address User
| eval term=last_login-first_login
| eval term=case(term<86400, "Very Short", term>86400 AND term<(86400*7), "Short", term>(86400*7), "Long")
| chart dc(User) as usercount over IP_address by term
| iplocation IP_address
| table Country City "Very Short" Short Long
| where NOT Country="United States"
I'm pretty new to Splunk and not sure where to go from this to get the results I'm looking for.
Any guidance would be much appreciated.
Are you looking for output like this
Country City Term #Users
If yes then try this
| stats earliest(_time) as first_login latest(_time) as last_login by IP_address User
| eval term=last_login-first_login
| eval term=case(term<86400, "Very Short", term>86400 AND term<(86400*7), "Short", term>(86400*7), "Long")
| stats dc(User) as usercount by IP_address term
| iplocation IP_address | where NOT Country="United States"
| table Country City term usercount | rename term as Term usercount as "#Users"
Like this:
| stats range(_time) AS term BY IP_address User
| eval term=case(term<86400, "Very Short",
term>86400 AND term<(86400*7), "Short",
term>(86400*7), "Long",
true(), "IMPOSSIBLE!")
| iplocation IP_address
| where NOT Country="United States"
| stats dc(User) AS usercount BY Country City term
| table Country City term usercount
If there is not a value for both Country
and City
, then the event will not be counted. If this is undesirable, then you can use fillnull
to force values of Unknown
.
This should be considerably more efficient than the accepted solution.
Are you looking for output like this
Country City Term #Users
If yes then try this
| stats earliest(_time) as first_login latest(_time) as last_login by IP_address User
| eval term=last_login-first_login
| eval term=case(term<86400, "Very Short", term>86400 AND term<(86400*7), "Short", term>(86400*7), "Long")
| stats dc(User) as usercount by IP_address term
| iplocation IP_address | where NOT Country="United States"
| table Country City term usercount | rename term as Term usercount as "#Users"
That is what i'm looking for! (I think)
Is there a way to completely filter out the Country and City when theres no value for one or both of them? I could probably figure out a way to just put "Unknown" for when it can't locate a login as a last resort.
Give this a try
| stats earliest(_time) as first_login latest(_time) as last_login by IP_address User
| eval term=last_login-first_login
| eval term=case(term<86400, "Very Short", term>86400 AND term<(86400*7), "Short", term>(86400*7), "Long")
| stats dc(User) as usercount by IP_address term
| iplocation IP_address | where (isnotnull(Country) AND isnotnull(City) AND NOT Country="United States")
| table Country City term usercount | rename term as Term usercount as "#Users"
Still getting a lot of rows with no Country or City values, and some only missing City values. I think its okay to leave the unknown City values if there is a Country, but if theres neither, I'd like to just not show those results.
I tried using OR instead of AND and played around with that command line a little bit and still can't seem to exclude those unknown locs.
Give this a try
| stats earliest(_time) as first_login latest(_time) as last_login by IP_address User
| eval term=last_login-first_login
| eval term=case(term<86400, "Very Short", term>86400 AND term<(86400*7), "Short", term>(86400*7), "Long")
| stats dc(User) as usercount by IP_address term
| iplocation IP_address | where (isnotnull(Country) AND isnotnull(City) AND NOT Country="United States" AND trim(Country)!="" AND trim(City)!="")
| table Country City term usercount | rename term as Term usercount as "#Users"
That worked!
Seems like it'd be such an easy thing to fix but it was a bit more tricky I guess haha.
Thanks a ton for your help. Now to learn visualization 🙂