I'm fairly new to Splunk and its query language. I have this data that I'd like to search through and visualize in a certain way. Below is what the data looks like
EmailAddress, LoggedInAt, EventDescription, IP_Address
abc@abc.com, 2017-04-13 07:00:00, AuthSuccessful, 1.1.1.1
abc@abc.com, 2017-04-13 07:10:00, AuthFailure, 2.2.2.2
abc@abc.com, 2017-04-13 07:20:00, AuthSuccessful, 3.3.3.3
def@abc.com, 2017-04-13 07:00:00, AuthSuccessful, 4.4.4.4
def@abc.com, 2017-04-13 07:10:00, AuthFailure, 5.5.5.5
def@abc.com, 2017-04-13 07:20:00, AuthSuccessful, 6.6.6.6
ghi@abc.com, 2017-04-13 07:00:00, AuthSuccessful, 7.7.7.7
ghi@abc.com, 2017-04-13 07:10:00, AuthFailure, 8.8.8.8
ghi@abc.com, 2017-04-13 07:20:00, AuthSuccessful, 8.8.8.8
Here's the query that I have created:
EventDescription=AuthSuccessful LoggedInAt="2017-04-13*"
| iplocation IP_Address
| stats values(Country) as Country by EmailAddress
| eval Countries=mvcount(Country)
| sort - Countries
My output looks like this:
EmailAddress, Country, Countries
abc@abc.com, SomeCountry, 2
SomeOtherCountry
def@abc.com, SomeCountry, 2
SomeOtherCountry
ghi@abc.com, SomeCountry, 2
SomeCountry2
What I'd like to see is this:
EmailAddress, Country, Countries, TimeDelta
abc@abc.com, SomeCountry, 2, 20
SomeOtherCountry
def@abc.com, SomeCountry, 2, 20
SomeOtherCountry
ghi@abc.com, SomeCountry, 2, 20
SomeOtherCountry
where TimeDelta is the difference in the earliest and latest LoggedInAt timestamps for a particular EmailAddress. I've tried 'delta' command with no success. If someone could help me figure this out, I'd be very grateful.
... View more