Calculate delta for range of time

[gauravnj1]

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.

[somesoni2 · Accepted solution]

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

[DalJeanis]

@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.

[gauravnj1]

Excellent! This is precisely what I was looking for! Thanks a lot, @somesoni2

[gauravnj1]

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?

[somesoni2]

Yes and Yes. Just replace | eval TimeDelta=max-min with | eval TimeDelta=tostring(max-min,"duration").