topic Re: Display a users time in portal by day in Splunk Search

Display a users time in portal by day

matthew_foos — Tue, 29 Sep 2020 19:03:15 GMT

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!

Re: Display a users time in portal by day

adonio — Tue, 17 Apr 2018 15:41:02 GMT

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?

Re: Display a users time in portal by day

matthew_foos — Tue, 29 Sep 2020 19:03:21 GMT

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

Re: Display a users time in portal by day

adonio — Tue, 17 Apr 2018 15:59:04 GMT

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?

Re: Display a users time in portal by day

damien_chillet — Tue, 17 Apr 2018 16:11:48 GMT

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

Re: Display a users time in portal by day

matthew_foos — Tue, 17 Apr 2018 18:17:27 GMT

No results for the Time in Portal field

Re: Display a users time in portal by day

damien_chillet — Wed, 18 Apr 2018 10:29:31 GMT

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

Re: Display a users time in portal by day

matthew_foos — Tue, 29 Sep 2020 19:13:00 GMT

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")

Re: Display a users time in portal by day

SplunkNovice202 — Tue, 17 Oct 2023 18:26:29 GMT

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