KODOS: stay alive
- Tutorial
Frankly, I was overcome by doubts about the need for this text. However, remembering the maxim "it is better to do and regret than not to do and regret", I decided to write.
This will be about another area of responsibility (along with office telephony) that is very beloved by all system administrators (sarcasm) - access control and management systems (ACS).
The main delight is caused by three things:
If you are familiar with this enthusiasm, please under the cat.
Actually, besides doubt, I was also motivated. The motive for writing was still the complete lack of information on our Internet and, as a result, a certain hope for the relevance of the information presented below by those who, like me, cursed when necessary to deal with Kodos.
We will talk about how to get data from ACS, while minimizing interacting with the shell of Kodos itself and completely bypassing its limitations.
Yes, stop whining. In fact, 99.9% of interaction with access control systems is receiving reports on employees' passes through control points, entering accounts into the access base, and extracting them from there.
Kodos is a client-server application. Server and DBMS are on the same machine. Data is stored in Firebird. I venture to suggest that these statements are valid for all old versions of Kodos.
Well, shake the old days.
We will need:
Connecting to the database
We assume that our Kodos lives at 192.168.1.1.
The database file on this machine is located on the path C: \ SSA \ SKD \ CODOS_DB \ CODOS.GDB The
user and password are default 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, an alias for connection, I entered Codos
User Name: sysdba
Password: masterkey
Client Library File: path to gds32.dll , in my case C: \ IBExpress \ IBE \ gds32.dll
We double-click on the newly created registration. If the settings were entered correctly and the user credentials with administrative rights in the DBMS are the same as those entered, the object tree will open.
Of course, we are interested in the Tables branch and its child objects - tables with data.
The following tables are of most practical interest:
LOGTAB - logs of events at control points. It is here that the events of passage through turnstiles, doors and so on are stored. An ideal starting point for reporting.
CLITAB - user credentials, including their full name, codes for their electronic passes in decimal and hexadecimal notation, and so on. Ideal for exporting credentials.
For import, we will need two more tables:
CLIDOORACCESS - the second order , do not neigh - this is a table with data on permissions to pass through access control points.
CLI_EXPIRE- a table with the dates when the validity period of the user pass expires.
To invoke the SQL query editor, press F12 or in the main window menu of IBExpress follow Tools → SQL Editor.
On this, the most interesting part ends and the routine of SQL queries begins.
For this reason (and because of natural laziness), a vba macro was written that receives "raw" data from Kodos without any SQL tricks, and then passes all this data through a meat grinder business logic, giving out neatly executed lists of malicious violators labor discipline.
I will not bore the reader with script listings, demonstrating not very good knowledge of the SQL query language, but also not very convincing programming skills. My task is to direct along the shortest path to obtaining the results of sampling data from Kodos directly to a sheet in Excel.
The sequence of steps is as follows:
Now in more detail.
Driver. Despite the seeming abundance of ODBC drivers, in fact, the only open source driver in my case was the open source driver .
Request to Kodos. To connect from a VBA project, you will need to add a link to the Microsoft Office 16.0 Object Library in the project. Of course, instead of 16.0, there may be anything else, depending on the version of your office installed.
Pay attention to the syntax of the SQL query. All cyrillic strings must begin with an encoding declaration:
As noted above, each account is scattered in three tables: the card of the holder of the electronic pass, the validity period of the pass and the access rights of this pass at the control points.
And if there are no difficulties with the INSERT operator in most tables in IBExpress, then with the CLIDOORACCESS table , not everything is intuitive.
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 sight it is impossible to assess the access level of the pass when viewing its entries in this table.
However, when double clicking on this value, IBExpress opens the Blob Viewer / Editor tool, which provides the ability to view it in the form in which it is stored in the database, as well as the ability to change it by downloading the value from the file and the ability to save its value to the file .
Thus, the import task is reduced to writing text values to the tables CLITAB , CLI_EXPIRE and CLIDOORACCESS with loading BLOB values from the file.
For a reason that is not completely clear to me, running a script in Script Executive:
And the execution of the script using the UPDATE statement also does not lead to the appearance of data loaded from the file. However, if UDPATE is executed from SQL Editor, then after requesting the path to the blob-file, the values in the DOOR field are updated successfully.
I would like to think that this brief howto on circumventing some of the unpleasant features of Kodos will not close the topic, but rather open it. I hope that in the comments there will be additions to other ACS. But I also won’t be surprised if the topic fades.
I would be grateful for substantive comments, for inaccuracies found, and for a description of personal experience.
This will be about another area of responsibility (along with office telephony) that is very beloved by all system administrators (sarcasm) - access control and management systems (ACS).
Disclaimer: it will be about ACS Kodos version 1.10.8.0. Perhaps in later versions the gaps in functionality and performance have been eliminated.The charm of working with such software a decade ago arises from a bit of extraterrestrial ergonomics of the interface, a little inhuman logic of working with the front-end of the operator part and other fruits of the creativity of people who worked under the direction of people in uniform (I can not imagine the ideologist of developing access control systems without epaulets in the past )
The main delight is caused by three things:
- A pass report is compiled for more than 10 minutes.
- Limit report length to 1000 records.
- Lack of export and import of accounts.
If you are familiar with this enthusiasm, please under the cat.
Actually, besides doubt, I was also motivated. The motive for writing was still the complete lack of information on our Internet and, as a result, a certain hope for the relevance of the information presented below by those who, like me, cursed when necessary to deal with Kodos.
We will talk about how to get data from ACS, while minimizing 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 ACS with your careless actions. Make backups, perform group operations with caution.
To the point
Yes, stop whining. In fact, 99.9% of interaction with access control systems is receiving reports on employees' passes through control points, entering accounts into the access base, and extracting them from there.
Kodos is a client-server application. Server and DBMS are on the same machine. Data is stored in Firebird. I venture to suggest that these statements are valid for all old versions of Kodos.
Well, shake the old days.
We will need:
- IBExpert is a GUI shell designed for the development and administration of InterBase and Firebird databases, as well as for the selection and modification of data stored in databases. ( wiki ). Free full-featured version - Link to download a special license for the former USSR.
- firebird.msg - file for translating DBMS message codes into human-readable messages. IBExpress searches for this file by default in a directory higher than its executable file. Therefore, IBExpress itself will be quite reasonable to unpack into a subdirectory located at the same level as firebird.msg .
- The gds32.dll library from the Firebird distribution. The path to it is indicated when setting up a connection to the database.
Connecting to the database
We assume that our Kodos lives at 192.168.1.1.
The database file on this machine is located on the path C: \ SSA \ SKD \ CODOS_DB \ CODOS.GDB The
user and password are default for Firebird: sysdba and masterkey, respectively.
If it’s not immediately clear how to create a new connection
После запуска IBExpress, согласившись на дефолтовое представление рабочего пространства, видим характерный многооконный интерфейс. Слева расположен Database Explorer, выполняем по нему правый клик и выбираем Register Database, либо жмакаем Shift+Alt+R.
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, an alias for connection, I entered Codos
User Name: sysdba
Password: masterkey
Client Library File: path to gds32.dll , in my case C: \ IBExpress \ IBE \ gds32.dll
Settings screenshot
We double-click on the newly created registration. If the settings were entered correctly and the user credentials with administrative rights in the DBMS are the same as those entered, the object tree will open.
Object tree screenshot
Of course, we are interested in the Tables branch and its child objects - tables with data.
The following tables are of most practical interest:
LOGTAB - logs of events at control points. It is here that the events of passage through turnstiles, doors and so on are stored. An ideal starting point for reporting.
CLITAB - user credentials, including their full name, codes for their electronic passes in decimal and hexadecimal notation, and so on. Ideal for exporting credentials.
For import, we will need two more tables:
CLIDOORACCESS - the second order , do not neigh - this is a table with data on permissions to pass through access control points.
CLI_EXPIRE- a table with the dates when the validity period of the user pass expires.
To invoke the SQL query editor, press F12 or in the main window menu of IBExpress follow Tools → SQL Editor.
On this, the most interesting part ends and the routine of SQL queries begins.
We get the data
Triumphant remark: if you select directly in IBExpress, then evaluate the speed of executing the SQL query, especially after long periods of waiting for the same data in the Kodos program shell itself.I’m not very good at writing beautiful SQL queries so that it’s right to women! and I got everything I needed, and sifted out the unnecessary, and even in Firebird. In addition, the received data still requires a hairstyle, which, of course, is performed by Excel.
For this reason (and because of natural laziness), a vba macro was written that receives "raw" data from Kodos without any SQL tricks, and then passes all this data through a meat grinder business logic, giving out neatly executed lists of malicious violators labor discipline.
I will not bore the reader with script listings, demonstrating not very good knowledge of the SQL query language, but also not very convincing programming skills. My task is to direct along the shortest path to obtaining the results of sampling data from Kodos directly to a sheet in Excel.
The sequence of steps is as follows:
- Install the ODBC driver to connect to Firebird.
- We configure ODBC connection to Kodos.
- Using this connection, we execute a request to Kodos in magical VBA.
Now in more detail.
Driver. Despite the seeming abundance of ODBC drivers, in fact, the only open source driver in my case was the open source driver .
Note! The bit depth of the ODBC driver must match the bit depth of the DBMS to which the connection is made. With a probability approaching 100%, Kodos spins under a 32-bit Firebird, respectively, and drivers should choose 32-bit.ODBC connection. Everyone can learn the sequence of steps to configure an ODBC connection to Firebird here: Connect to InterBase or Firebird from Excel via ODBC . The manual is exhaustive and without sudden plot twists. Connection settings are completely similar to those in IBExpress connection settings. The only thing - I recommend to specify the symbol table explicitly: WIN1251 .
ODBC Settings Screenshot
Request to Kodos. To connect from a VBA project, you will need to add a link to the Microsoft Office 16.0 Object Library in the project. Of course, instead of 16.0, there may be anything else, depending on the version of your office installed.
Adding a link to a project
Tools → References
Pay attention to the syntax of the SQL query. All cyrillic strings must begin with an encoding declaration:
SELECT * FROM TableName WHERE Field1 LIKE _win1251'Кириллица'A short listing, we get the data and write the result by line on the sheet
Dim row As Long
Set conn = New ADODB.Connection
Set rst = New ADODB.RecordsetSet rst = New ADODB.Recordset
rst.Open "SELECT dt, ev_text, cli_text FROM logtab WHERE cli_text not like _win1251'%ОХРАНА%' and dt BETWEEN '01.01.2018 00:01' and '01.01.2018 23:59' order by dt asc", conn, adOpenDynamic, adLockOptimistic
With Worksheets("Лист1")
Do While Not rst.EOF
.Range("A" & CStr(row)).NumberFormat = "@"
.Range("A" & CStr(row)).Value = CStr(rst.Fields(0))
.Range("b" & CStr(row)).Value = rst.Fields(1)
.Range("c" & CStr(row)).Value = rst.Fields(2)
rst.MoveNext
row = row + 1
Loop
End WithImport Accounts
As noted above, each account is scattered in three tables: the card of the holder of the electronic pass, the validity period of the pass and the access rights of this pass at the control points.
And if there are no difficulties with the INSERT operator in most tables in IBExpress, then with the CLIDOORACCESS table , not everything is intuitive.
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 sight it is impossible to assess the access level of the pass when viewing its entries in this table.
However, when double clicking on this value, IBExpress opens the Blob Viewer / Editor tool, which provides the ability to view it in the form in which it is stored in the database, as well as the ability to change it by downloading the value from the file and the ability to save its value to the file .
Thus, the import task is reduced to writing text values to the tables CLITAB , CLI_EXPIRE and CLIDOORACCESS with loading BLOB values from the file.
For a reason that is not completely clear to me, running a script in Script Executive:
SET BLOBFILE 'doors1.lob';
INSERT INTO CLIDOORACCESS (CLI_N, DOORS) VALUES (2, :h00000000_00000016);And the execution of the script using the UPDATE statement also does not lead to the appearance of data loaded from the file. However, if UDPATE is executed from SQL Editor, then after requesting the path to the blob-file, the values in the DOOR field are updated successfully.
CHYADNT?
Возможно, хабрачитатели знают, в чем моя ошибка, и любезно поделятся решением в камментах. Буду признателен.
It should be noted that multiple INSERT, UPDATE operations in one script are performed only in Script Executive (calling it by pressing Ctrl + F12), SQL Editor does not provide such an opportunity.The charm of this decision can be felt already starting with 10 registrations of new users per day. And if registrations are in batches of 7-8 dozens every three days, then the decision cannot be overestimated with all the will.
In addition, when running scripts in Script Executive, do not forget to check the Use current connection box, otherwise you will receive a connection error with the database.
Conclusion
I would like to think that this brief howto on circumventing some of the unpleasant features of Kodos will not close the topic, but rather open it. I hope that in the comments there will be additions to other ACS. But I also won’t be surprised if the topic fades.
I would be grateful for substantive comments, for inaccuracies found, and for a description of personal experience.