topic Re: how to query time spent by user in a console in Splunk Search

how to query time spent by user in a console

anikeshp7 — Tue, 29 Sep 2020 11:08:13 GMT

Hi I want to create a report to display time spent by user in a console

Being beginner doesnt know how to query .

Any suggestions ?

index="123" AND organizationId="0123000000000342" logRecordType=ailtn ("appName":"Collections_Platform" AND "appType":"Console")

Re: how to query time spent by user in a console

ITWhisperer — Tue, 29 Sep 2020 11:15:23 GMT

How do you know when a user is in a console and when they are not?

Re: how to query time spent by user in a console

anikeshp7 — Tue, 29 Sep 2020 14:57:53 GMT

Re: how to query time spent by user in a console

ITWhisperer — Tue, 29 Sep 2020 15:12:26 GMT

Cool! How do you identify which user has logged in and which has logged out? Can a user log in more than once concurrently? If concurrent, does the overlapping period get double counted? If the log in session terminates abnormally, is that also logged as an end of session? Does each event have a session id which ties all these events together?

Re: how to query time spent by user in a console

anikeshp7 — Wed, 30 Sep 2020 04:54:15 GMT

-We can identify the users using the userID.

-If a user log in more than once concurrently, overlapping period doesn't get double counted

-If the log in session terminates abnormally, then that will be logged as an end of session

-Each event do have a session id

Re: how to query time spent by user in a console

isoutamo — Wed, 30 Sep 2020 05:27:26 GMT

Can you give to us some scrambled example log entries so community could easier help you with this case?

Re: how to query time spent by user in a console

ITWhisperer — Wed, 30 Sep 2020 07:07:43 GMT

index="123" AND organizationId="0123000000000342" logRecordType=ailtn ("appName":"Collections_Platform" AND "appType":"Console") | stats values(userid) as userid, earliest(_time) as startofsession, latest(_time) as endofsession by sessionid | eval timeloggedon=endofsession-startofsession | stats sum(timeloggedon) as totalsessiontime by userid | fields userid totalsessiontime

This assumes you have already extracted userid and sessionid

Re: how to query time spent by user in a console

anikeshp7 — Wed, 30 Sep 2020 18:15:31 GMT

The events are generating but I see no data in statistics.

Re: how to query time spent by user in a console

anikeshp7 — Thu, 01 Oct 2020 11:44:39 GMT

I also want to know if the time displayed is which format and how can i format it to display in Hrs:Min

Re: how to query time spent by user in a console

isoutamo — Thu, 01 Oct 2020 12:05:30 GMT

Those are seconds as original values were epoch. Easiest way is to use tostring with duration:

eval dispTime = tostring(totalsessiontime, "duration")

r. Ismo

Re: how to query time spent by user in a console

ITWhisperer — Thu, 01 Oct 2020 12:59:51 GMT

Given that your screenshot shows timeSpentOnConsole and this is a field calculate from the statistics, what data are you not seeing?

Re: how to query time spent by user in a console

anikeshp7 — Thu, 01 Oct 2020 16:22:08 GMT

@ITWhispererThe previous query you wrote wasn't returning stats so had to tweak little bit.

Re: how to query time spent by user in a console

ITWhisperer — Thu, 01 Oct 2020 16:29:26 GMT

So what do you have now and what isn't working?

Re: how to query time spent by user in a console

anikeshp7 — Thu, 01 Oct 2020 16:38:22 GMT

@isoutamothe dispTime column is coming as empty using this

eval dispTime = tostring(totalsessiontime, "duration")

Re: how to query time spent by user in a console

ITWhisperer — Thu, 01 Oct 2020 16:45:21 GMT

Your total session times represent multiple days. Perhaps you need to calculate days, hours, and minutes as separate values or perhaps use strftime(totalsessiontime,"%j %H:%M") although this is likely to give days as 3-digits with leading zeros, but you could strip them afterwards

Re: how to query time spent by user in a console

anikeshp7 — Thu, 01 Oct 2020 17:08:40 GMT

@ITWhisperer is this what You are asking to write as

stats earliest(_time) as startofsession, latest(_time) as endofsession by sessionKey userId

| eval timeloggedon=endofsession-startofsession

|eval strftime(timeloggedon,"%j %H:%M") as temp

|stats sum(temp) as timeSpentOnConsole by userId

| lookup 2clicTest.csv UserID AS userId OUTPUT Name AS NAME

| fields NAME timeSpentOnConsole | sort -timeSpentOnConsole | where NAME != ""

Re: how to query time spent by user in a console

ITWhisperer — Thu, 01 Oct 2020 17:22:38 GMT

stats earliest(_time) as startofsession, latest(_time) as endofsession by sessionKey userId | eval timeloggedon=endofsession-startofsession | stats sum(timeloggedon) as timeSpentOnConsole by userId | lookup 2clicTest.csv UserID AS userId OUTPUT Name AS NAME | fields NAME timeSpentOnConsole | sort -timeSpentOnConsole | where NAME != "" | fieldformat timeSpentOnConsole = strftime(timeSpentOnConsole ,"%j %H:%M")

Re: how to query time spent by user in a console

anikeshp7 — Thu, 01 Oct 2020 17:47:41 GMT

Thanks for this.

One questions, multiplying days by 24 and adding it into hours would be a good approach to display time spent as hours:min

How would I do that ? Or shall i simply display as strftime(timeSpentOnConsole ,"%T")

Re: how to query time spent by user in a console

ITWhisperer — Thu, 01 Oct 2020 18:48:53 GMT

%T will take a modulus of 24. Try this:

stats earliest(_time) as startofsession, latest(_time) as endofsession by sessionKey userId | eval timeloggedon=endofsession-startofsession | stats sum(timeloggedon) as timeSpentOnConsole by userId | lookup 2clicTest.csv UserID AS userId OUTPUT Name AS NAME | fields NAME timeSpentOnConsole | sort -timeSpentOnConsole | where NAME != "" | eval hours=round(timeSpentOnConsole /(60*60)) | eval minutes=round((timeSpentOnConsole / 60) % 60) | eval timeSpentOnConsole =tostring(hours)." hours, ".tostring(minutes)." minutes" | fields - hours minutes

Re: how to query time spent by user in a console

anikeshp7 — Fri, 02 Oct 2020 07:17:18 GMT

@ITWhispereryou are awesome . Thanks