How to calculate time elapsed since now from IIS logs

[DaClyde]

I need to be able to show how long it has been since a user uploaded or downloaded a specific type of data based on the content of IIS logs. Basically I'm trying to set up a dashboard that will show the users, and the the number of days since they last posted or downloaded data. I can build the query to show the data, or count the bytes of how much was uploaded or downloaded, but can't figure out the time calculation. Can anyone help? I'm stuck using 4.0.11 for now. Thanks!

I need results that look something like:

USER      DOWNLOAD     UPLOAD
user1       0d           3d
user2       1d           17d
user5       8d           8d

[hexx]

The tostring(X,Y) function of the eval command might also work here when using the "duration" mode :

tostring(X,"duration") : converts seconds X to readable time format HH:MM:SS.

For full reference and examples, consult the following documentation links :

http://www.splunk.com/base/Documentation/latest/SearchReference/CommonEvalFunctions
http://www.splunk.com/base/Documentation/latest/SearchReference/Eval#Example_5

[dwaddle · Accepted solution]

Generally speaking, you can compute the elapsed time (in seconds) since any indexed event using something like this:

my_search | eval timedelta=now()-_time

Converting that value from seconds to days/hours/minutes is mostly an exercise in division and remainders.

(Now that I see your example)

my_search | eval timedelta=now()-_time 
| eval time_delta_days=floor(timedelta/86400 )
| eval time_delta_hours=floor((timedelta % 86400) / 3600)

To clarify a little further, "_time" is the timestamp extracted from the event by Splunk's datetime parser. There is also "_indextime" which is the timestamp of when the Splunk stored the event. (Thanks hexx!)

[DaClyde]

Ok, this is getting me there (I've gone from completely ignorant to almost completely ignorant, so that's progress): sourcetype="iis" cs_username!="-" POST cs_bytes>1024 | eval timedelta=now()-_time | eval time_delta_days=floor(timedelta/86400) | eval time_delta_hours=floor((timedelta % 86400) / 3600) |stats first(time_delta_days) as time by cs_username

[dwaddle]

My first guess would be that "top" is stripping off the time information before eval gets a chance to see it. You may need to rearrange your order of operations a bit to get to what you desire.

[DaClyde]

Thanks for the response! So far, all it returns in the results table is a list of usernames, but nothing in the timedelta or time_delta_days fields. Here's my search:

sourcetype="iis" cs_username!="-" sc_status="200" POST cs_bytes>1024 | top cs_username limit="100" | eval timedelta=now()-_time | eval time_delta_days=floor(timedelta/86400) | eval time_delta_hours=floor((timedelta % 86400) / 3600) | fields cs_username,time_delta_days,timedelta