Parse and process data from a PBX

Problem scenario:

I can't seem to be able to write the log of our local PBX system into my SQL 2000 database. I have installed the ODBC plugin and have tested the ODBC connection through the ODBC administrator, and everything seems to be working. Now, I wish to parse and write a log of the data to a file in my database.

Fig.1. PBX system captured data by the serial data logger software

Requirements:

• Advanced Serial Data Logger Professional, Enterprise, or a trial version;
• ASCII Data Query and Parser

It is assumed that:

You have configured all communication parameters (baud rate, the number of data bits, flow control, etc.) in the data logger, and you can receive all data without communication errors.

Solution:

The image (Fig.1) above shows a simple data flow. Each record has a fixed size and a fixed position for each item. We should inspect our data stream for non-printable characters with a code of less than 20 Hex. The indicated radio buttons are set, as shown below.

Fig.2. PBX data logger. Serial data view setup

If your data flow is fast, then ASDL will not split your data into separate lines, and you will get one long line on the screen. For this reason, it is necessary to tick the "Wrap words" tick box. It effectively adjusts the line length to the window's width (Fig.3, pos #1).

Fig.3. PBX data logger. Serial data word wrap

Click the "OK" button and attempt to receive data from a serial port. You should get something like the image below.

Fig.4. PBX data logger. Data received.

It is the other view of the data received. Notice that all non-printable characters were replaced with their code i.e., #0D. It is clear from the data screen above that the data block (within the green rectangle) ends with #0A#0D#0A (underlined by red). In this example, our data block contains two parts, namely, 1 and 2. Part #2 was adjusted to the width of the window. Both of these parts should be interpreted as a single whole.

We are now ready to configure the modules. First, select the "ASCII data query and parser" plugin (Fig.5, pos. #1) from a drop-down list. Then, enable a parsing option for data received (Fig.5, pos. #2) and select the necessary data export plugins. The DDE server (Fig.6, pos. #3) will help check the accuracy of the parser's configuration. The "ODBC database" plugin will write the data to the Microsoft SQL Server 2000 (Fig.6, pos. #4).

Fig.5. PBX data logger. The data parser plugin setup.

Fig.6. PBX data logger. Data export plugins setup.

Next, open the ASCII parser and query configuration window (click the "Setup" button near the drop-down box as indicated on pos. 1 on Fig.5). A dialog window will appear on the screen (Fig.7) about the data packet.

Fig.7. PBX data logger. Parser configuration window.

The configuration process should be simple if you have examined your data flow in the data logger window (Fig.4). You should type in the same as in the data logger window in fields 1 and 2. Field #1 marks the beginning of the data block, and field #2 marks the end. In the example, our data block does not contain a start marker. Therefore, this field is left empty. The values to be typed in here are as underlined in red in Fig.4 above.

The next tab is a crucial part of the parser configuration. The data parser uses this information for data extraction from the data block. In the example, the data block contains 4 data items (see Fig.1), namely: date and time, calling party number, called party number, and call length in seconds, which should be separated out to different variables. Later, these variables will be used in the data export and will be placed in different columns of our SQL2000 database.

Fig.8. PBX data logger. Parser items.

Any new items may be added by clicking the "Add item" button (Fig.8, pos. 7). Before adding an item, the program will ask you about an item description. You can type any characters here, which will help you to remember a variable's content. For this example, all four variables with their corresponding descriptions have been added.

Each parser item has some of the properties:

1. Item name. This name will be bound to a column in the data export plugin. It is a limited text description and cannot contain spaces and a few other characters;
2. Parser's type. It is a method which the program will use for data extraction. Our parser has a few methods from simple to most powerful. In this example, where data is being placed in a fixed place, we can use the simple method - fixed position. This method allows the extraction of any number of characters from any fixed position of the data block;
3. Data type. It is the data type of the characters extracted. For example, you can specify FLOAT or STRING data types. The plugin converts the string values to that data type. The plugin uses the options from the "Data formats" tab for converting. In the example, the "Call date and time" field has the DateTime data type, and the "Call len" field has the Integer data type. All other fields have the String data type;
4. Default value. The parser uses the default value if it cannot extract value from the specified position, or the plugin cannot connect it to the specified data type;
5. Fixed position. It is the position of the first variable's character in our data block. The minimum value is 1;
6. Fixed length. It is the number of characters, which will be extracted to a variable. The minimum value is 1.

All other items have the same parameters except the position, length, and data type.

In the next tab, you can specify basic format options as per (Fig.9). If you had specified the data type "String" in the item's parameters, then the first two options allow you to remove blank spaces from a value. Our called and caller number contain blank spaces at the start of a value. The second option allows you to convert the date-time string to a field with the DateTime data type. We specified MMDDYY HHNN here, according to the specification above. For a detailed description of formatting characters, please, see the help file.

Fig.9. PBX data logger. Format of items.

Other options are unnecessary in our case because all our items do not have the date or time data type.

Click the "OK" button and close the parser configuration window. Then click the "OK" button in the options window.

Now that our parser is ready, it is time for testing it. Connect your device and power it on if necessary. Check to see if you can receive a data block from the specified serial port. If the parser had been correctly set up, then you should see all parser items and their values (Fig.10) in the DDE server window below.

Fig.10. PBX data logger. DDE server window.

All parser items are now ready for export to the SQL2000 database. You can read about it in the second part.

Related articles: Parse and process data from a PBX

• Receive and parse data from the Garmin Geko301 GPS receiver
• Parse data from the filling machine
• Log and parse data from an intellectual pressure measuring device