Count of logins for last 30 days.

[HARSHITSING]

I am working on a BR on a custom table which has user data (field name: user) and information about the last time they logged into the instance (field name: last login).
Last login field is a date/time format field, and it updates every time user login in the instance.

I need to make a BR for a count flag, which will increase by 1 every time user logins in. This flag can be increased more than once/day, if user logs in multiple time during the day.

The challenge here is I need the count of logins only for last 30 days and flag should automatically exclude any data which is older than 30 days (Attaching a workflow for better understanding).

Please let me know how to proceed on the requirement. 

[Maddysunil]

@HARSHITSING 

Could you share your script, if you have not yet written you can try with below sample BR code and update :

(function executeRule(current, previous /*previous is not used in this script*/) {
    // Define the date range for the last 30 days
    var thirtyDaysAgo = new GlideDateTime();
    thirtyDaysAgo.addDays(-30); // Subtract 30 days

    // Query for login records within the last 30 days for the current user
    var loginCountQuery = new GlideRecord('YourLoginTable'); // Replace 'YourLoginTable' with the actual table name
    loginCountQuery.addQuery('user', current.user); // Assuming 'user' is the field name for the user
    loginCountQuery.addQuery('last_login', '>=', thirtyDaysAgo); // Assuming 'last_login' is the field name for the last login timestamp
    loginCountQuery.query();

    // Count the number of login records
    var loginCount = 0;
    while (loginCountQuery.next()) {
        loginCount++;
    }

    // Update the count flag field on the user record
    current.user.sys_domain_count_flag = loginCount; // Assuming 'sys_domain_count_flag' is the field name for the count flag

})(current, previous);

Please Mark ✅Correct if this solves your query and also mark 👍Helpful if you find my response worthy based on the impact.

Thanks

[Sohail Khilji]

HI @HARSHITSING,

You did not highlight what your trying to do with the data are you trying to create a report or anyting else. As per your requirement i assume that you man need to create a custom field that tracks users login per day/month. If you can provide more insight on what end result your expecting accordingly i can suggest from solutions.. for now i guess the below info must help you

There are two log tables that store this information. The instance's administrator can retrieve this information from these log tables.

(Opening the below table may slower the perfomance so take necessary action on lowerinstace first/business hours etc in check)

1. syslog_transaction table:
   This table is not available/recommended for reporting (more details on Transaction Logs).
   
   https://<instancename>.service-now.com/syslog_transaction_list.do?sysparm_query=urlSTARTSWITH%2Flogin.do%3F&sysparm_view=
   
   *** This query can be used and can be really slow on your instance. Make sure you are doing this during low activity hours, in your instance.
   
   Search for "login.do" in the above URL, and if there is an SSO login on your instance; the URL will be different and that is what you need to search for as the string parameter. Created By field will be the username who has logged in.
   
   
2. sysevent table
   The information you are looking for is captured on the sysevent table which is a system table. This table is not available/recommended for reporting and there is an out-of-the-box auto-rotation enabled on this every 7 days.
   
   More info on the events table is in this documentation.
   Your internal developers can implement a custom solution that can be set up for historical reporting from a custom table.

For historical custom reporting:

The sysevent table will only have the last 7 days of data. To get the information you are looking for, you must create a customized script on a sysevent table to capture the required information to a custom table of your choice and use that table for reporting. 

If you use a real-time Business Rule, it can create a lot of load on the instance as this is one of the busiest/frequently accessed system tables of the instance platform. 

You can plan for a scheduled custom script that pulls the required information once a day during your NON-business hours. This can pull the required information (columns/data required) daily into the custom table of your choice and be used for reporting. 

https://instancename.service-now.com/sysevent_list.do?sysparm_query=name%3Dlogin%5EORname%3Dlogout

• Name ==> login/logout
• Instance==> Session ID of the user
• Parm 1==> Username
• User id ==> sys_id of the user
• Username
• Claimed By ==> Node on which the user was logged.
• Created ==> timestamp of Login/logout activity

I hope this helps 🙂