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.
Since your LoggInAt is in string format, we need to convert it to epoch so that you can do your operation. Try like this
EventDescription=AuthSuccessful LoggedInAt="2017-04-13*"
| iplocation IP_Address
| eval LoggedInAt=strptime(LoggedInAt,"%Y-%m-%d %H:%M:%S")
| stats values(Country) as Country min(LoggedInAt) as min max(LoggedInAt) as max by EmailAddress
| eval TimeDelta=max-min | fields - max min
| eval Countries=mvcount(Country)
| sort - Countries
Since your LoggInAt is in string format, we need to convert it to epoch so that you can do your operation. Try like this
EventDescription=AuthSuccessful LoggedInAt="2017-04-13*"
| iplocation IP_Address
| eval LoggedInAt=strptime(LoggedInAt,"%Y-%m-%d %H:%M:%S")
| stats values(Country) as Country min(LoggedInAt) as min max(LoggedInAt) as max by EmailAddress
| eval TimeDelta=max-min | fields - max min
| eval Countries=mvcount(Country)
| sort - Countries
@somesoni2, once you've converted it, is there a reason you're not using stats ... range(LoggedInAt) as TimeDelta ...
? I've seen @woodcock do that a few times in this situation.
Excellent! This is precisely what I was looking for! Thanks a lot, @somesoni2
This returns the time delta in seconds, correct? Is there a way to convert the delta back to human readable form? Like in minutes or hours?
Yes and Yes. Just replace | eval TimeDelta=max-min
with | eval TimeDelta=tostring(max-min,"duration")
.