 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 Time::JulianDay.pm (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.
Details
The system has been developed following a modular approach:
al_main.pl: the main script
al_defs.pl: constants DEFinitions
al_calc.pl: functions to calculate application usage.
al_fct.pl : 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: app3.sh 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 'qTool.sh' ,'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.
Conclusion
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.
Acknowledgements
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: fman@uyr.com.ar.
|
