Use Case : a small town public library with a number of public internet pc's. Customers can use the internet free of charge, but the library staff needs to track which machines are available. There's no hard time limit but when there's a queue, 30 minutes per session is the agreed maximum, and those customers that have the longer sessions will be asked to log of and make place for newer ones. The library staff would like a more or less automated way of tracking which computers are available, and to monitor the duration of active sessions.They'd also like to be capable of making simple usage statistics, such as 'number of hours of internet usage per year' or 'average Length of an internet session'.
By using logon and logof scripts in Visual basic script we can see which computers have a user logged on. The logon/kogoff scripts create records in a database (MS Access, MySQL, ...) so that it is possible to make usage statistics. A web page serves as user interface showing the computers that are available (no one logged on), and the start time and duration of the sessions of the computers that are in use.
Logon and Logoff scripts can be added through the Microsoft Management console : Local Computer Policy : User Configuration : Windows Settings : Scripts.
The logon scripts sends an SQL statement to a database to insert a record containing values for hostname (computer name), logged on user, and current date and time (start of the session)
The Logoff script looks for a record without logof time and a matching computer name, and inserts the time at logoff (end of session). This way, each session is recorded.
You can use a Microsoft Access mdb file as database. If you put it on a network drive, it is accessible over the network so every host can write to it, and a supervisor computer can access the data for reporting and monitoring. However, using file sharing for database access is rather inefficient, and MS Access is known to be error prone when used by multiple users. Therefore, this is acceptable as a prototype, but for a production environment, a real database, such as mySQL, is a better option.
It's quite easy to install mysql on a Linux server. The main thing to remember is to set a root password, and configure the server to bind to a real address in stead of the local loopback address.
## my.cnf # listen port : bind to real address i.s.o. local loopback bind-address = 192.168.0.20
mySQL can work with nice sql front-ends or web-based admin tools such as phpMyAdmin, but to quickly hack together a simple database, you can also just use the mysql command line tool :
mysql -u root mysql -p CREATE DATABASE dbSillyNetMon CREATE TABLE dbSillyNetMon.tblSessions ( session_key DOUBLE, hostname CHAR, user_name CHAR, session_date CHAR(10), time_start CHAR(8), time_stop CHAR(8) ); -- to modify existing tables : ALTER TABLE dbSillyNetMon.tblSessions MODIFY hostname CHAR(48); ALTER TABLE dbSillyNetMon.tblSessions MODIFY user_name CHAR(48); -- account for application : account creation + privs in 1 statement GRANT SELECT,INSERT,UPDATE ON dbSillyNetMon.* TO 'netmon'@'%' IDENTIFIED BY 'netmon_password';
Note that we use 'strings' for date and time. This is to workaround date and time format issues. The strings can be converted back to real date and time values in the program.
The "GRANT' statement creates a user and sets that user's privileges. Note the username and password - these need to be re-used in the connection strings in your scripts. The @'%' means : 'from any host' and enables remote access.
Rename the scripts (extentension .vbs) and install them as logon and logof script on your internet pc's.
Note the Connection strings, and how they differ between MS Access (path to a file !) and mysql (tcp connection and database login). Also note that the connection string for mysql includes the user name and password for the mysql database user.
To let the Windows PC's talk to the mySQL database, you need to download and install a mySQL ODBC driver and set up ODBC --> Control Panel : ODBC -->
The interaction with the database is quite simple : the scripts just send a few SQL statement (INSERT, UPDATE) to the database. This keeps network traffic low, and most processing is done by the database.
If you use a MS Access database, you might want to put it on a network drive so it can be accessed easily through a driveletter:\path. This is best done in a startup script, because they run as SYSTEM / Administrator, before the user logs on. This allows you to restict the user's access and still keep things working. Startup scripts are added in Management console : Local Computer Policy : Computer Configuration : Windows Settings : Scripts.
To keep a uniform time across your network, you might want to synchronize the time of all the hosts with a time server. this can also be achieved in a startup script - e.g. a .bat file :
:: startup script :: synchronize time net time /setsntp:192.168.0.20 net stop w32time net start w32time
To allow the library staff to see which computers are available and which ones are in use (and for how long they have been in use), we need a user interface. We choose to create a web page for this purpose, because it's a convenient way to provide a GUI to VB scripts, and can be used from any PC with a web browser, without any additional software installation. Throw in a nice style sheet to make a good appearance.
This report is a simple web page with some javascript and visual basic script ( view source ) and a style sheet. Again, it consists mainly of some SQL statements and a bit of processing.
'get machines that are in use qryUSAGE = "SELECT * from dbSillyNetMon.tblSessions " & _ "WHERE time_stop is NULL " & _ "ORDER BY hostname ;" 'get machines that are available qryFREE = "SELECT DISTINCT hostname FROM dbSillyNetMon.tblSessions " & _ "WHERE hostname NOT IN " & _ "(SELECT DISTINCT hostname " & _ " FROM dbSillyNetMon.tblSessions " & _ " Where time_stop is NULL )" & _ "ORDER BY hostname ;"
Note that these scripts are executed client-side, i.e in the browser. By means of asp or php, it can be easily turned into a server-side script, which is indeed a better solution because otherwise the connection string is visible on the client side, and it contains some security sensitive information.
It is also possible to use the ODBC datasource in MS Access or another database front-end so that you can view and query the database in an environment your comfortable with.
|
Silly Software Company
-=oOo=- A poor man's way of doing things is still a way to get things done |