how to query v_user_session table within a certain time frame

fzw · ‎08-24-2016

Greetings!

I want to know how to get total transactions time data(How long they stay in serviceNow) for my users on weekly basis. It seems to me that v_user_session table only contain data on certain time point, right?

What is the best table to query it? I know that sys_user table has the last login time, but it is not enough at this moment. Duration is also needed and it seems to me that only v_user_session table has the duration.

Any thoughts would be appreciated.

Thanks.

Mwatkins · ‎08-24-2016

Hello,

You are correct, the v_user_session table only has point in time data. The v_user_session table is a virtual table, meaning it is not actually stored in the database but results are created on-the-fly, every time the table is requested. I'm not sure what your business need is, but there is a set of graphs that come out-of-the-box that will track the number of logged in users over time. You cannot view details about what users were logged in, but it is helpful if you are trying to establish trends about aggregate information.

Performance Metrics - ServiceNow Wiki

Another option might be to look in the Transactions (all users) module (displays the syslog_transaction table). The table has a hidden field called "Session" that is not displayed by default. You can add this field and group by it to see sessions with active transactions over a period of time. This table is very large. For most customers it is not feasible to look at more than 1 hour of time. Also, note that until Geneva most web service transactions were not tracked in syslog_transaction table. See the below KB article for some more details about this very useful table.

Troubleshooting Guide: Using the Transaction Logs

What are you trying to accomplish?

View solution in original post

Mwatkins · ‎08-24-2016

Hello,

You are correct, the v_user_session table only has point in time data. The v_user_session table is a virtual table, meaning it is not actually stored in the database but results are created on-the-fly, every time the table is requested. I'm not sure what your business need is, but there is a set of graphs that come out-of-the-box that will track the number of logged in users over time. You cannot view details about what users were logged in, but it is helpful if you are trying to establish trends about aggregate information.

Performance Metrics - ServiceNow Wiki

Another option might be to look in the Transactions (all users) module (displays the syslog_transaction table). The table has a hidden field called "Session" that is not displayed by default. You can add this field and group by it to see sessions with active transactions over a period of time. This table is very large. For most customers it is not feasible to look at more than 1 hour of time. Also, note that until Geneva most web service transactions were not tracked in syslog_transaction table. See the below KB article for some more details about this very useful table.

Troubleshooting Guide: Using the Transaction Logs

What are you trying to accomplish?

fzw · ‎08-24-2016

Thank you for the responses.

We have a large group in training. It is online though. So management groups wants to know if everyone stay in training longer enough in order to understand the whole content they should learn.

I appreciate your help.

Thank you again.