How to automate reporting of license usage

[jasoncleo]

This was something we wanted setup fairly early on, as we had more QA staff than licenses, and our previous automation framework had previously had no user limits, so staff were not accustomed to ensuring that they released their licenses when not in use.

We ran the license server on a central VM, but didn't want to have to constantly connect to it to open the License Manager and check usage. We wanted an automated process that would monitor it and send out an email if licenses were being hogged, or if we hit the threshold of floating premium licenses.

The Ranorex License Service doesn't have an API, but they do use a SQL Server Compact database (.sdf) which is accessible. So we found that a simple .Net application that ran in the background, could read from this database to check how many licenses are currently allocated, and to which machines/IPs they were currently allocated.

We could also monitor how long they had been held for.

The Ranorex License Server keeps a template sdf in its install folder, but the one you want to access for the live data is in %programdata%\RanorexLicenseServer\.

This allowed us to be able to provide automated gentle reminders to staff to release licenses when they went home for the day, and also track how frequently we were hitting our license limit.

Hope this helps others wanting to monitor Ranorex license usage in their own organisation.

[odklizec]

Hi,

Thank you for sharing your find. A very useful one! Any chance you can share also your sdf monitoring app? Or at least a rough sample how to read the appropriate info from sdf file? Thank you in advance

[jasoncleo]

The application is something that was thrown together in about as much time as this post, so it really isn't fit for sharing as we've seen plenty of opportunity for extending it with WCF for integration with some of our other reporting and test management tools.

However, I'll cover my core routine and it is easy to adapt that to a simple utility for your own purposes.

Firstly, you'll need Microsoft's SQL Server CE 3.5 libraries installed. But actually you can shortcut that, as the Ranorex license server does install them all under it's bin folder. The one you need to reference in your code is System.Data.SqlServerCe.dll. The other SQL DLLs there are needed for execution.

I referenced the System.Data.SqlServerCe.dll as a local copy and then just situated the utility in the same folder as the Ranorex license server so it leveraged the same copy of libraries.

Some note about the design:

• We had a single pack of Premium licenses, and a single pack of Runtime. This meant that grouping the DB instances by license type reflected the packs of licenses we had, I don't have to check the key

• I didn't care about runtime license consumption so ignored it.

• I also know next to nothing about SQL, so don't ask me exactly how they work. I picked these off from StackOverflow and they work fine for us at the moment. In terms of efficiency, I have no idea how fast they are.

The core routine is similar to below. As a note, we parameterized all of this, but its listed as hardcoded below for clarity.

Code: Select all

using (var conn = new SqlCeConnection(@"Data Source= c:\programdata\ranorexlicenseserver\licensemanager.sdf")) {
    conn.Open();
    // This groups by LicenseType and then gets the last item (by primary key: Id) for each group.
    // The purpose of this is to check our license consumption for a particular type.
    using (var comm = new SqlCeCommand("select a.* from LeaseLog a inner join " +
                                       "(select LicenseType, max(Id) as maxid from LeaseLog group by LicenseType) " +
                                       "as b on a.Id = b.maxid", conn))
    {
        comm.CommandType = CommandType.Text;
        var rs = comm.ExecuteResultSet(ResultSetOptions.Scrollable);
        if (rs.HasRows) {
            // The rows I want are:
            //    rs.GetOrdinal("LeasedCount") - this is for the number of currently used licenses for this type.
            //    rs.GetOrdinal("MaxLeases") - gives me the max number of concurrent users this license type supports.
            //    rs.GetOrdinal("LicenseType") - license type, for us to check if it is 'Premium' the one I want.
            bool cont = true;
            rs.ReadFirst();
            do {
                if (rs.GetString(rs.GetOrdinal("LicenseType")) == "Premium") {
                    // Do the stuff I need to here to collect metrics for license consumption.
                    cont = false;
                } else
                    if (!rs.Read())
                        cont = false;
           } while (cont);
       }
   }

    // This groups by ComputerName and then gets the last item (by primary key: Id) for each group.
    // The purpose of this is to assess which computers still have a lease open (i.e. still holding a license).
   using (var comm = new SqlCeCommand("select a.* from LeaseLog a inner join " +
                                     "(select ComputerName, max(Id) as maxid from LeaseLog group by ComputerName) " +
                                     "as b on a.Id = b.maxid", conn))
    {
        comm.CommandType = CommandType.Text;
        var rs = comm.ExecuteResultSet(ResultSetOptions.Scrollable);

        if (rs.HasRows) {
            // The rows I want are:
            //    rs.GetOrdinal("TimeStamp") - Tells me when this action occurred for monitoring how long a user may have been holding the license.
            //    rs.GetOrdinal("Action") - To determine if the last action was 'lease' or 'release'. We're only interested in 'lease'
            //    rs.GetOrdinal("ComputerName") - I log the computer names so I can follow up with machines that seem to be holding a license constantly, or even holding multiple licenses.
            //    rs.GetOrdinal("LicenseType") - license type, for us to check if it is 'Premium' the one I want.
            rs.ReadFirst();
            do {
                if ((rs.GetString(rs.GetOrdinal("LicenseType")) == "Premium") &&
                    (rs.GetString(rs.GetOrdinal("Action")) == "Lease")) {
                    // Record machine name: rs.GetString(rs.GetOrdinal("ComputerName"))
                    // Record the timestamp of the last lease: rs.GetSqlDateTime(rs.GetOrdinal("TimeStamp"))
                }
            } while (rs.Read());
        }
    }
} // End of DB connection.