Measuring license utilisation

[tristram]

While we can measure license allocation in ServiceNow, it seems to us we don't have a way to measure actual utilisation of those allocated licenses. Our organisation is made up of a loosely affiliated set of units and the demand for licenses is high. We want to be able to validate allocation by monitoring utilisation.

There must be other folk who have faced this issue and (hopefully) solved it. If so, we would very much appreciate learning how.

What do we mean by 'utilisation'? At it's simplest, utilisation could be defined as how many times peopled logged in, when and for how long. This could also extend to "what or how much they did" while there. Of course, we would be very happy to learn how others have defined utilisation.

Thanks in advance for your help.

[nikita_mironov]

I implemented offline answer the question "how many times people logged in, when" with some BI reporting on the top of that (e.g. average logins counter per week / month / year, licenses utilized by disabled users and so on) and it was sufficient to detect idle license holders and initiate talks with them.

The key field in sys_user record to look at is "Last Logon Time" or similar. Auditing this field value changes should help to answer the question "how many times people logged in, when". By default it is audited meaning that you may try to extract data related to that field modifications from sys_audit (may affect system performance if sys_audit is big, the way to retrieve data from service-now instance should be carefully assessed). Alternative would be to create your own "Last Login Time Log" and have a business rule that fires onChange of "Last Logon Time" and logs that event to that table for further either off-line or embedded reporting analysis. This is recommended approach as you will be able to define for how long data is kept in that log and keep that table at required size to be queried fast enough and contain data that you need, e.g. last 3-6 months.

Answering question "...and for how long" is tricky as system keeps the user session open for defined timeout period. Probably to address this question we need to dive into Transactions log but it would be definitely VERY resource consuming. Probably some automation on user session termination event logging would help.

As for the question "what or how much they did" - it is again either on transaction log which is very huge and fast growing or specific "events" logging that you can program yourself (e.g. incident creation event triggers log entry, incident update triggers log entry, approval action triggers log entry, etc.).

[zschneider]

We have a report that runs monthly to see who hasn't logged in in the last 90 days. That helps with utilization.

Next we created a table that logs an entry every time someone makes an insert or update to the Task table (where 99% of our users do their work). Then we have scheduled jobs that associate that License Use table weekly into weekly use per licensed user. Then monthly that information is put into another table that calculates their monthly usage and gives some statistics based on the parameters of a license within our Service-Now contract.

If gives us a good feel of who is doing what and allows us to track individual users use from a daily, weekly, and monthly perspective.

[tnelson7]

If you by chance have more details on how you guys completely went about this it would great. We are looking to implement something very simialr to what you guys have here.

Tony

[zschneider]

It was a considerable amount of work.

First we created a table called "task update count" and a business rule on the task table that created an entry in the task update count table with the name and ticket number of the insert/update:

if (gs.getUser().hasRoles()) {
   var gr = new GlideRecord("u_task_update_count");
   gr.u_user = gs.getUserID();
   gr.u_task = current.sys_id;
   gr.insert(); 
}

Then we created a second table called "License Use" that totals up each user's weekly use and loads them into another table via a scheduled job:

getUserCount();

function getUserCount() {
  var uNames = new Array(); //Holds string to be split into array.
  var tuc = new GlideRecord('u_task_update_count');

var bolw = gs.beginningOfLastWeek(); //timespan you want to query..
var eolw = gs.endOfLastWeek();


  tuc.addQuery('sys_created_on', '>=', bolw);
  tuc.addQuery('sys_created_on', '<', eolw);
  tuc.addNotNullQuery('u_user'); //ensure automated entries are ignored
  tuc.query();
  var userCount = {}; // store user sys_id => number of records
  while (tuc.next()) {
    if (!userCount[tuc.u_user]) {
      userCount[tuc.u_user] = 1;
    } else {
      userCount[tuc.u_user]++;
    }
  }
var userArray = getRoledUsers(); //compare users that created an entry to all users with a license.
for(var u in userArray){
if(!userCount[userArray<u>]){
   userCount[userArray<u>] = 0;
}
}
  for (var j in userCount) {
    var lu = new GlideRecord('u_license_use'); //create license use entry for each individual user for that week
    lu.u_week_of = bolw;
    lu.u_user = j;
    lu.u_total_count = userCount[j];
    lu.u_notes = j + ": " + userCount[j];
    //lu.u_notes = userArray.length;
    lu.insert();
  }

}

Finally we created a third table named Average License Use that calculates each user's monthly use based on the language stipulated for a license in our contract. I've edited this script a bit, since i'm not sure if the specific license agreement we have with Service-Now is confidential or not.

getAvgCount();

function getAvgCount() {
  var uNames = new Array(); //Holds string to be split into array.
  var tuc = new GlideRecord('u_license_use');

  var bolw = gs.beginningOfLastWeek(); //timespan you want to query..
  var eolw = gs.endOfLastWeek();
  tuc.addQuery('u_week_of', '>=', bolw);
  tuc.addQuery('u_week_of', '<', eolw);
  tuc.query();
  var userCount = {}; // store user sys_id => number of records
  var tucCount = "";
  var utc = "";
  while (tuc.next()) {
    tucCount = tucCount + 1; //store all 5 values needed in an object: User, WeekCount, weekly use, total use, process user
    if (!userCount[tuc.u_user]) { // if object doesn't exist, create it.
      userCount[tuc.u_user] = {};
      userCount[tuc.u_user].weekCount = 1;
      userCount[tuc.u_user].useCount = tuc.u_total_count;
      userCount[tuc.u_user].processUser = checkProcessUser(tuc.u_total_count);
      userCount[tuc.u_user].utc = tuc.u_total_count + "(" + checkProcessUser(tuc.u_total_count) + ") ";
    } else { //if object already exists, add to it.
      userCount[tuc.u_user].weekCount++;
      userCount[tuc.u_user].useCount = userCount[tuc.u_user].useCount + tuc.u_total_count;
      if(userCount[tuc.u_user].processUser == true){
      userCount[tuc.u_user].processUser = checkProcessUser(tuc.u_total_count);
      }
      userCount[tuc.u_user].utc += tuc.u_total_count + "(" + checkProcessUser(tuc.u_total_count) + ") "; //add notes to break down weekly use

    }
  }
  var monthCount = 0; //check to see the maximum number of weeks in your timespan
  for (var i in userCount) {
    if (userCount<i>.weekCount > monthCount) {
      monthCount = userCount<i>.weekCount;
    }

  }
  for (var j in userCount) { //if user doesn't meet maximum weeks in span, not a process user
    if (userCount[j].weekCount < monthCount) {
      userCount[j].processUser = false;
    }
    //lu.u_notes += j + ": weeks: " + userCount[j].weekCount + " totalUse: " + userCount[j].useCount + " monthCount: " + monthCount + "\n";
    var lu = new GlideRecord('u_average_license_use');
    lu.u_month_of = eolw;
    lu.u_user = j;
    lu.u_total_average = userCount[j].useCount / monthCount;
    lu.u_total_use = userCount[j].useCount;
    lu.u_process_user = userCount[j].processUser;
    lu.u_notes = "Span of:" + bolw + " " + eolw + "\n Total Weeks: " + userCount[j].weekCount + "\n Weeks in Span: " + monthCount +"\n " +userCount[j].utc;
    lu.insert();
  }
}

function checkProcessUser(u_user) {
//run code that defines process user and returns if true or false
}

There's likely a more efficient way of handling this. I didn't really like having to create three separate tables to calculate one thing, but was the solution we ended up having to use to combine different little projects that were already written.