Cover V10, I05


Application Usage Accounting

Francisco Mancardi

In the UNIX environment, the software vendors use a license daemon (LD) to limit the number of simultaneous instances of an application or features accessible to the users. These different license daemons have the potential to gather data about usage (starting and ending time, user, host, feature, etc.). Some of this data can be stored in an ASCII file, but more information is stored using a binary format only readable with a tool sold by each license daemon vendor (LDV).

However, this creates a couple of problems:

  • If you choose to pay for the tools, how do you integrate the data from different vendors?
  • If you try to use the ASCII files, how many different formats do you have to parse?
  • What happens if the vendor changes the format?
  • Is the vendor providing documentation about file structure?
  • Is the information that you need recorded in these files?

At my job, we have faced these problems and decided to develop a system. We considered the use of the Resource Accounting System that was already present in our platforms (Solaris 2.6), but we found it very difficult to process that data to obtain what we wanted.

We then switched to a solution using the same principle used by the LDs. We need to generate a record every time a user launches an application and another record when the application is closed. The LDs have this information because the applications are compiled using libraries provided by the LDV, who contact the LD to request and to release a license. We have no access to the application code and little ability to persuade different software vendors to include code developed by our company.

Generally, every software application is launched by calling a script that sets the right environment (PATH, special paths, variables, etc.) and then calls the application. We then add a call to a start_recording script just before calling the application, and to the stop_recording script when the application returns. By using this mechanism, you can record usage information for any script you want.

We wanted a simple but powerful system capable of gathering the data in a centralized location, and we decided to use the standard method to report in UNIX syslogd through the command logger. Because we have to deal with historical information, we decided it's not a good solution to process and reprocess the generated text files every time we want a report. Thus, we decided to import the data into a database system.

We wanted to start with a freeware database engine, but maintain the possibility of migrating to other database engines. Another important condition was to avoid a massive rewrite during the switch between engines. We choose Perl because of the wide availabity of modules (we need date/time arithmetic, graphics, etc.).

Our first database engine was msql, but we switched to MySQL because it supports aggregate functions (AVG, SUM) in the queries, and there is a graphical client to make queries for various operating systems (Windows, Linux, Solaris).

The process is simple -- via syslog, we register the start and stop of every instance of an application in a text file located in the syslog host. During the night we stop syslogd, backup the logfile, re-start syslogd, and process it to insert the data into the database.

System Requirements

1. Perl 5.005_03 or later.

2. Time-modules -- We use (Julian Day conversions).

3. DBI -- The Perl Database Interface by Tim Bunce.

4. The msql-mysql-modules -- DBD::mSQL/DBD::mysql and mSQL and mysql drivers for the Perl 5 Database Interface (DBI).

5. Additional software -- gd-chart and the gd lib if you want to generate nice charts.


The system has been developed following a modular approach:   the main script   constants DEFinitions   functions to calculate application usage. :   functions that deals with date and time aritmethic.
cut_liclog:   the cron job
A sample text file generated using syslogd looks like the following:

1. Nov 20 07:47:40 wks12 LIC_ACC: app1 START 20001120-0747 fiore PID: 599
2. Nov 20 08:11:14 wks12 LIC_ACC: app1 STOP 20001120-0811 fiore PID: 599
3. Nov 20 08:11:57 wks12 LIC_ACC: app1 START 20001120-0811 fiore PID: 810
4. Nov 20 08:12:27 wks3 LIC_ACC: app2 START 20001120-0812 burma PID: 732
5. Nov 20 08:12:50 wks3 LIC_ACC: START 20001120-0812 burma PID: 761
6. Nov 20 08:23:58 wks10 LIC_ACC: app4 START 20001120-0823 ckent PID: 755
7. Nov 20 08:32:20 wks14 LIC_ACC: app5 START 20001120-0832 norbi PID: 942
8. Nov 20 08:37:08 wks1 LIC_ACC: run_app6 START 20001120-0837 orre PID: 437
9. Nov 20 09:09:04 wks8 LIC_ACC: app5 START 20001120-0909 zuinni PID: 13998 
10. Nov 20 09:09:25 wks5 LIC_ACC: app7 START 20001120-0909 brink PID: 454 
11. Nov 20 09:12:18 wks5 LIC_ACC: app7 START 20001120-0912 brink PID: 688 
12. Nov 20 09:12:53 wks5 LIC_ACC: app5plus START 20001120-0912 brink PID: 717
13. Nov 20 09:13:04 wks5 LIC_ACC: app5plus STOP 20001120-0913 brink PID: 717
14. Nov 20 13:34:24 wks12 LIC_ACC: app1 STOP 20001120-1334 fiore PID: 810 
We have included the process i.d. (PID) to account for the simultaneous use of the same application in the same host by the same user. We consider each line composed by fields separated by a blank. For example, field #6 is the application name and field #8 is a time stamp in an easier format to process.

We calculate the usage by looking for the start tag (for example, see line 3) and then for the stop tag having the same hostname, application, user, and PID (see line 14).

The database, named DB_license_log, has two tables:

1. syslog_line -- Where we store the data imported from the file generated using the logger command:

CREATE TABLE syslog_line (
aaaa char(4),
mm char(2),
dd char(2),
hh char(2),
mmin char(2),
app char(20),
user char(8),
pid char(6),
hostname char(20),
status char(5),
procesado char(1) );
We have added the field procesado (processed) that we use to resolve the problem of applications running overnight. Remember that we "cut" our license log every night, so it is possible to have a start tag without the corresponding stop tag. We compute the elapsed time when we get the stop, or maybe never (if the application doesn't finish in the normal way).

2. uso_diario (usage) -- Where we insert the results from processing the data contained in syslog_line:

CREATE TABLE uso_diario (
user char(8),
hostname char(20),
app char(20),
aaaa char(4),
mm char(2),
dd char(2),
hh_start char(2),
mm_start char(2),
pid char(6),
uso_en_min int(11),
day_of_week char(2) );
hh_start and mm_start are the starting time, and uso_en_min is usage in minutes during the period started at hh_start and mm_start.

We have defined prime and regular time classes and, during the processing, we generate several records for the usage corresponding to each time class in the table uso_diario. We also have chosen to record the usage separated by date (i.e., if an application is launched on the day 20001123 and is stopped on 20001125, we will have three records for the prime time corresponding to 20001123, 20001124, and 20001125, and records corresponding to the regular time for the same days).

With this system, we can detect licenses that are "locked" during the night by the users. This can be a sign of some problems like users not logging out at the end of the day or insufficient number of licenses.

Getting Useful Data (Reports)

An important issue before trying a report is to understand some internal conventions. We have divided the day in the following slices:

regular - Overnight activity : 00:00 till 07:59 
Prime Time : 08:00 till 19:00 
regular - overtime : 19:00 till 23:59
We can detect overnight usage doing sql queries on the table uso_diario using an "as where" clause something like hh_start="00" and mm_start="00". We extract the most useful data from the table uso_diario. We can construct a monthly ranking of the most used applications with an sql statement like:

select app, aaaa, mm, SUM(uso_en_min) as total_usage  
from uso_diario 
where (day_of_week !="do" and day_of_week !="sa")
group by app, aaaa, mm order by aaaa,mm, total_usage
Sample output:

# app aaaa mm total_usage (minutes) 
'app1' ,'2000' ,'06' ,313 
'app3' ,'2000' ,'06' ,456  
'app12' ,'2000' ,'06' ,890
An important issue in the installation is knowing if there is the right quantity of licenses. To answer this question, you can compute the average number of used licenses with the following equation:

AveLic=Average Number of Licenses
AveLic = Total App usage / Available Working Time
Available Working Time = 
   WorkingHoursbyDay * WorkingDays_in_a_month
We get this figure with the following sql statement:

select app,aaaa, mm, (((SUM(uso_en_min)/60)/6)/22) as num_of_lic from uso_diario 
where (day_of_week !="do" and day_of_week !="sa") 
group by app,aaaa,mm order by aaaa,mm, num_of_lic
Where the numbers mean:

60 -> Minutes in an hour  
6 -> WorkingHoursbyDay 
22 -> WorkingDays_in_a_month
Sample output:

# app aaaa mm num_of_lic
'app1' ,'2000' ,'06' ,0.751389  'app3' 
,'2000' ,'06' ,1.361995  'xsxmenu' ,'2000' 
,'06' ,1.452020  'd3dapp' ,'2000' ,'06' 
,1.662247  '' ,'2000' ,'06' ,1.744318  
'app2d' ,'2000' ,'06' ,2.003283  'app3d' 
,'2000' ,'06' ,2.097601  'MAP' ,'2000' 
,'06' ,3.398359
Another similar figure can be computed with the following equation:

AppUsage(%) = Total App usage / Available License Time
Available License Time = number of licenses * Available Working Time
By adding additional tables to the database (e.g., relation between users and projects, relation between App and App Type), you can compute usage by project or usage by App Type.


Thus, we've got a simple and practical tool to record application usage data, with the spirit of UNIX tools (i.e., combining different tools such as Perl, PerlDBI, syslogd, and MySQL). We have proof that it is possible in a database application of low complexity like this to change your database engine (from msql, to MySQL, to Oracle) with minimum (re-coding) effort thanks to PerlDBI. We have a vendor-independent solution, and the freedom to add different data if we need it. By using the MySQL GUI Client, we have a good user interface with zero development effort. We "rediscover" the power of using syslogd as a standard method for applications and scripts log generation. Further development is needed to provide a standard set of predefined reports as an integral piece to this tool. Furthermore, work is in progress to access reports through the Web.


Thanks to my wife, Gabriela, and my twins, Annabella and Sofia. Special thanks to Carolina Leon for her suggestions and testing.

Francisco Mancardi is an associate in U&R Consultores, Buenos Aires, Argentina, a consulting company focused in network management and data security. He can be reached at: