I was able to get the content using archive.org Wayback Machine.
Reporting on Service Now License Usage danspada.com
Here is the text if the article in case the above link stops working, too.
Reporting on Service Now License Usage
Whenever I do a product implementation one of the questions that always comes up is "How can we report on our license usage for this product?" Most products have a way to view current usage yet no functional way of looking at license history.
I have developed this small customisation in Service Now to look at not only currently licensed users, but also the maximum logged in users by day, and concurrent connections by day. It tracks it by day so that we can see license usage over time so that we can plan for growth, as well as see actual usage of licensed users.
The result is a graph in the reporting tab to show Licensed vs. Logged in vs. Unique.
Key:
"Licensed" is the number of user accounts that consume a license.
"Logged In" is the number of licensed sessions connected.
"Unique" is the number of unique licensed user accounts that are connected.
How it works?
A script include runs every 15 mins via a scheduled job to work out the current number of connections for each of the metrics above, if the value is higher than the currently stored value for than metric on that day, the value is updated in the "user reporting" table.
A report is then run against that table to produce the graph shown above. The data can also be viewed directly within the "User Reporting" table, which shows the time that the peak for that day was first reached.
How to build it?
Create a table called "User Reporting" (table name u_user_reporting)
- Field Name: "AuditDate" Type: "Date"
- Field Name: "Type" Type: "String 40″
- Field Name: "Value" Type: "Integer"
Forms and Lists
- Create a list view which contains the AuditDate, Type, Value and Updated field.
- Create a detail form which has the AuditDate, Type and Value down one side, and Created and Updated on the other.
Script Include
Create a script include called "RunUserAudit" with the following script:
var RunUserAudit = Class.create();
RunUserAudit.prototype = {
initialize: function() {
},
type: 'RunUserAudit',
execute : function() {
this._insertValue("Licensed", this._getCount("sys_user", "sys_id=javascript:getRoledUsers()"));
this._insertValue("Logged In", this._getCount("v_user_session", "user!=guest^EQ"));
this._insertValue("Unique Logins", this._getAggregate("v_user_session", 'user', "user!=guest^EQ"));
},
_getCount : function(table, query) {
var gr = new GlideRecord(table);
gr.addEncodedQuery(query);
gr.query();
return gr.getRowCount();
},
_getAggregate : function(table,field,query) {
var gr = new GlideAggregate('v_user_session');
gr.addEncodedQuery(query);
gr.groupBy('user');
gr.query();
return gr.getRowCount();
},
_insertValue : function (type, value) {
var currentVal = this._getValue(type);
if ((currentVal == null) || (currentVal == "null")) {
var userRecord = new GlideRecord('u_user_reporting');
userRecord.initalize();
var d = new GlideDateTime(gs.now());
userRecord.u_auditdate = d.getDate();
userRecord.u_type = type;
userRecord.u_value = value;
userRecord.insert();
} else if (value >= currentVal) {
var rec = new GlideRecord('u_user_reporting');
var d = new GlideDateTime(gs.now());
rec.addEncodedQuery("u_auditdate=" + d.getDate() + "^u_type="+type);
rec.query();
while (rec.next()) {
rec.u_value = value;
rec.update();
}
} else {
// No update required.
}
},
_getValue : function(type) {
var rec = new GlideRecord('u_user_reporting');
var d = new GlideDateTime(gs.now());
var q = "u_auditdate=" + d.getDate() + "^u_type="+type;
rec.addEncodedQuery(q);
rec.query();
var value = null;
while (rec.next()) {
value = rec.u_value;
}
return value;
}
}
Scheduled Job
Create a scheduled job to run every 15 mins with the following script:
gs.include('RunUserAudit');
var userAudit = new RunUserAudit();
userAudit.execute();
Report
Create a report using these parameters:
- Type: Line Chart
- Table: User Reporting
- Group By: Type
- Trend Field: AuditDate per Date
- Aggregation: Sum Value
