KODOS: stay alive

Frankly, I was overcome by doubts about the usefulness of this text. However, having remembered the maxim “it is better to do and regret, than not to do and regret,” I decided nevertheless to write.
It will be about another (along with office telephony) which is very beloved by all system administrators (sarcasm) area of ​​responsibility - access control systems (ACS).

Disclaimer: it will be about ACS Kodos version 1.10.8.0. Perhaps in later versions, gaps in functionality and performance have been eliminated.

The beauty of working with such software of a decade-old sample stems from a bit of extraterrestrial ergonomics of the interface, a little inhuman logic of working with the front-end of the operator part and from other fruits of creativity of people who worked under the guidance of people in uniform (I can't imagine the ideologist of developing an ACS without shoulder straps in the past ).

The main delight is caused by three things:

1. The report on passes is made more than 10 minutes.
2. Limit report length to 1000 entries.
3. No export and import accounts.

If this delight is familiar to you, I ask under kat.
')
Actually, apart from doubts, I was also motivated. The motive for writing was still the complete lack of information on these our Internet sites and, as a result, some hope that the information presented below would be useful to those who, like me, used to swear at every need to deal with Kodos.

It will be about how to receive data from the access control system, while at the same time interacting with the shell of Kodos itself and completely bypassing its limitations.

Disclaimer: all operations will have to be performed in a “live” database, so you have every chance to fill up the ACS with your careless actions. Make backups, perform group operations with caution.

To the point

Yes, stop whining. In essence, 99.9% of the interaction with the access control system is receiving reports on employee passes through control points, entering accounts into the access database and retrieving them from there.

Kodos is a client-server application. Server and DBMS are on the same machine. Data is stored in Firebird. I would venture to suggest that these statements are true for all old versions of Kodos.

Well, shake old.

We will need:

• IBExpert is a GUI shell designed for the development and administration of InterBase and Firebird databases, as well as for selecting and modifying data stored in databases. ( wiki ). Free full-featured version - Download link for a special license for the former USSR.
• firebird.msg - DBMS message code translation file in human-readable messages. IbExpress this file by default searches in a directory higher than its executable file. Therefore, IBExpress itself will be quite reasonable to unpack into a nested directory that is on the same level with firebird.msg .
• The gds32.dll library from the Firebird distribution. The path to it is specified when setting up the connection to the database.

Connect to the database
We will assume that our Kodos lives at 192.168.1.1
The database file on this machine is located at C: \ SSA \ SKD \ CODOS_DB \ CODOS.GDB
Default user and password for Firebird: sysdba and masterkey respectively.


To connect, select the following settings:
Server / Protocol: Remote, TCP / IP
Server name: 192.168.1.1
Port: gds_db
Database File: C: \ SSA \ SKD \ CODOS_DB \ CODOS.GDB
Server version: Firebird 2.0
Database Alias: arbitrary string, alias to connect, I entered Codos
User Name: sysdba
Password: masterkey
Client Library File: path to gds32.dll , in my case C: \ IBExpress \ IBE \ gds32.dll


Double-click on the newly created registration. If the settings have been entered correctly and the user credentials with administrative rights in the DBMS coincide with the entered ones, the tree of objects will open.


Of course, we are interested in the Tables branch and its child objects - tables with data.
The following tables are of the greatest practical interest:
LOGTAB - event logs on control points. This is where events of passage through turnstiles, doors, and so on are stored. The ideal starting point for generating reports.
CLITAB - user registration cards, including their full name, their electronic pass codes in decimal and hexadecimal notation, and so on. Ideal point to export credentials.

For the import we need two more tables:
CLIDOORACCESS - second lieutenant, do not laugh, - this is a table with data on tolerances to pass through access control points.
CLI_EXPIRE is a table with dates when a user's pass expires.

To call the SQL query editor, click F12 or go to the menu of the main IBExpress window and follow Tools → SQL Editor.

That's where the fun ends and the SQL query routine begins.

Get the data

Triumphant remark: if you sample directly in IBExpress, then evaluate the speed of the SQL query, especially after long periods of waiting for the same data in the Kodos shell itself.

I am not particularly good at writing beautiful SQL queries, so here are the women! and got everything you need, and weed out the unnecessary, and even in Firebird. In addition, the data still requires hairstyles, which, essno, performs Excel.
For this reason (and due to natural laziness) a vba-macro was written, which receives raw data from Kodos without any SQL tricks, and then all this data is passed through the business logic grinder, yielding neatly decorated lists of malicious intruders labor discipline.

I will not bore the reader with listings of scripts, demonstrating in addition to not very good knowledge of the language of SQL queries, also not very convincing programming skills. My task is to direct the shortest path to obtaining the results of sampling data from Kodos straight to the sheet in Excel.

The sequence of steps is as follows:

1. Install the ODBC driver to connect to Firebird.
2. Configuring ODBC connection to Kodos.
3. Using this connection, we execute the query to Kodos in the magic VBA.

Now more.
Driver. Despite the seeming abundance of ODBC drivers, the open source driver was actually the only one that immediately earned the option.

Note! The bit width of the ODBC driver must match the bit depth of the DBMS to which the connection is made. With a probability approaching 100%, Kodos is spinning under a 32-bit Firebird, respectively, and 32-bit drivers should be chosen.

ODBC connection. The sequence of steps for setting up an ODBC connection to Firebird can be read by everyone here: Connecting to InterBase or Firebird from Excel via ODBC . Manual exhaustive and without sudden plot twists. Connection settings are completely similar to those in the IBExpress connection settings. The only thing - I recommend to indicate the symbolic table explicitly: WIN1251 .


Request to Kodos. To make a connection from a VBA project, you need to add a link to the Microsoft Office 16.0 Object Library to the project. Of course, instead of 16.0 there could be anything else, depending on the version of your office.


Pay attention to the syntax of the SQL query. All Cyrillic lines should begin with a character declaration:

SELECT * FROM TableName WHERE Field1 LIKE _win1251'' 

Import Accounts

As noted above, each account is scattered across three tables: the card of the holder of the electronic pass, the period of validity of the pass, and the access rights of this pass at checkpoints.

And if there are no difficulties with the INSERT operator in most tables in IBExpress, then not everything is intuitive with the CLIDOORACCESS table.

Some non-obviousness is that the data type of the DOOR field is BLOB, and the subtype of stored values ​​is binary. In other words, by eye it is impossible to estimate the access level of a pass when viewing its record in this table.

However, IBExpress when double-clicking on such a value opens the Blob Viewer / Editor tool, which provides the ability to view it as it is stored in the database, as well as the ability to change it by loading the value from the file and saving its value to the file .

Thus, the import task is reduced to writing text values ​​to the CLITAB , CLI_EXPIRE and CLIDOORACCESS tables with loading BLOB values ​​from the file.

For a reason that is not completely clear to me, the execution of the script in the Script Executive:

 SET BLOBFILE 'doors1.lob'; INSERT INTO CLIDOORACCESS (CLI_N, DOORS) VALUES (2, :h00000000_00000016); 

Results in the creation of corresponding records in the table, but with zero BLOB values.
And the execution of the script using the UPDATE statement also does not lead to the appearance of data loaded from a file. However, if you execute UDPATE from SQL Editor, then after requesting the path to the blob file, the values ​​in the DOOR field are updated successfully.

It should be noted that multiple INSERT, UPDATE operations in one script are performed only in the Script Executive (called by pressing Ctrl + F12), the SQL Editor does not provide this feature.

In addition, when running scripts in Script Executive, do not forget to check the Use current connection box, otherwise you will get a connection error with the database.

All the charm of this solution can be felt already from 10 new user registrations per day. And if registrations go in packs of 7-8 dozens every three days, then it will not be possible to overestimate the decision for good reason.

Conclusion

I would like to think that this short howto, bypassing some of the unpleasant features of Kodos, will not close the topic, but will open it on the contrary. I hope that in the comments there will be additions to other access control systems. But just do not be surprised if the topic fades.

I would appreciate comments on the merits, inaccuracies found and a description of personal experience.