Display a users time in portal by day

[matthew_foos]

Splunkers,

I'm attempting to display how long a user as spent in our training portal over the last 30 days.

Search string:

index=blah
| stats earliest(_time) as login, latest(_time) as logout by user
| eval diff=logout-login
| eval diff=tostring(diff, "duration")
| convert timeformat="%B %d %Y %I:%M:%S %p" ctime(login)
| convert timeformat="%B %d %Y %I:%M:%S %p" ctime(logout)
| rename user as User, login as Login, logout as Logout, diff as "Time in Portal"

Any advice would be great.

Thanks!

[matthew_foos · Accepted solution]

Answered my own question:

index=something
| eval day=strftime(_time, "%B %d %Y")
| eventstats range(_time) AS duration BY username day
| stats values(duration) as duration by username day
| eval duration=tostring(duration, "duration")

[SplunkNovice202]

This worked great, but how do I display also in the same search, what the first record was and the last record was for the durration.

Something like a table below

username, day, Duration, First, Last

[damien_chillet]

You could try the following:

index=blah 
| bucket _time as day span=1d
| stats earliest(_time) as login, latest(_time) as logout by user, day
| eval diff=logout-login
| stats sum(diff) as tip by user
| eval tip=tostring(tip, "duration")
| rename user as User, tip as "Time in Portal"

That should retrieve time in Portal per user per day, then sums it to get Time in Portal per user last 30 days

[matthew_foos]

No results for the Time in Portal field

[damien_chillet]

Hey i made a mistake, i've edited the SPL, could you try one more time?

[adonio]

hello there,
seems like your query will calculate 1 long session for each user for 30 days.
do you have an event that indicates a logon / logout?
can you share some masked sample data?

[matthew_foos]

Hi,

I do not have an event that indicates a login / logout. I'm calculating those fields with this:

| stats earliest(_time) as login, latest(_time) as logout by user
| eval diff=logout-login
| eval diff=tostring(diff, "duration")

This gives me a login, logout, and diff(how long they spent in the portal).

Unfortunatly, this is all I have to work with..

[adonio]

hmmm,
not sure how to approach this Rubiks cube. if for example user A logs in in day 1 and logs out that same day and also logs in and out on day 29, your query will capture login in day 1 and logout on day 29 and therefore calculate 28+ days on portal...
can you shed some more light by sharing some masked sample data?