how to query time spent by user in a console

[anikeshp7]

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

[anikeshp7]

login and logout

[ITWhisperer]

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?

[anikeshp7]

-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

[ITWhisperer]

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

[anikeshp7]

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

[isoutamo]

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

eval dispTime = tostring(totalsessiontime, "duration")

r. Ismo

[anikeshp7]

@isoutamothe dispTime column is  coming as empty using this

eval dispTime = tostring(totalsessiontime, "duration")

[ITWhisperer]

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

[anikeshp7]

@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 != ""

[ITWhisperer]

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

[anikeshp7]

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

[ITWhisperer]

%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

[anikeshp7]

@ITWhispereryou are awesome . Thanks

[anikeshp7]

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

[ITWhisperer]

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

[anikeshp7]

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

[ITWhisperer]

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

[isoutamo]

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

[ITWhisperer]

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