Help to identify different strings

[Guest]

I have a log file (Table1) from an alarm system which produces different
alarm outputs consisting of textstrings with embedded parameters, one record
per alarm. I need to identify each record type (there about 200 of these) by
identifying some significant text element and then, when the type is
recognized, dig out the interesting parameters within the message by use of
different string commands. The alarm record types are in separate table
(Table2) with columns for "significant string", character for identifier
start/stop etc.
Please help me to generate a query which for each record in Table1 generates
additional columns identifying type of alarm string and start/stop position
for identifier etc by comparison with all record types in Table 2.
All help is appreciated.

 

[John Nurick]

Hi Gunnar,

Without knowing anything about the format of these strings or the
structure of your tables it's not possible to give a firm answer except
that languages such as Perl (or Python or Ruby) offer much better
facilities than Access for parsing complicated log files.

Would you like to give some more information about the data, including
some sample records?

 

[Marshall Barton]

I have a log file (Table1) from an alarm system which produces different
alarm outputs consisting of textstrings with embedded parameters, one record
per alarm. I need to identify each record type (there about 200 of these) by
identifying some significant text element and then, when the type is
recognized, dig out the interesting parameters within the message by use of
different string commands. The alarm record types are in separate table
(Table2) with columns for "significant string", character for identifier
start/stop etc.
Please help me to generate a query which for each record in Table1 generates
additional columns identifying type of alarm string and start/stop position
for identifier etc by comparison with all record types in Table 2.

You might be able to use a non-equi join to match up the
type info:

SELECT table1.*, table2.*
FROM table1 INNER JOIN table2
ON table1.textstring
LIKE table2 "*" & table2.significantstring & "*"

More details about what fields you have in the two tables
would probably help reduce our confusion.

 

[Guest]

Hi John and Marshall,

Below some more details about the tables

Table1 (alarm log)
1 * '* is the identifier for a new alarm message
2 Boiler temp warning. Actual value 240 degC
3 Increase rate 12.7 deg/min
4 Valve 1234 closed
5 *
6 Generator stopped
7 Valve 2345 and 678 opened
and so on

Table 2 (holds typical string identifiers in order to break down Table 1):
1 col1: "Boiler temp warning"; col2: 63 ('pos in string where temp figures
start
2 col1:"Increase rate" , col 2: 34 ('pos in string where rate starts
etc

What I need to achieve is a single line presentation of each complete alarm
message splitted up in different columns which will make it easier to analyze
from a statistical point of view.

As an example present the first alarm above on one line with different
parameters etc in different columns.
col 1: Boiler temp warning (main alarm message)
col 2: Temperature at alarm
col 3: 240 deg C ('parameter 1)
col 4: Temp increase rate
col 5: 12.7 deg/min ('parameter 2)
col 6: Valve closed
col 7: 1234 ('parameter 3)

I have solved how to arrange the alarms on one single line. The problem I
have is to have the query to analyze each row in Table 1 and present message
type and how to extract the parameter value in the text string. Table 2 has
to hold start position of parameter valute (pos in string normally) and end
of parameter value (normally some string delimiter).

I hope this explains and look forward to receive your comments.

Regards
/Gunnar
All alarms do have timestamps. These have been omitted here.
"John Nurick" skrev:

 

[John Nurick]

Hi Gunnar,

It sounds as if the number of lines in each log entry, the contents of
each line vary with the type of alarm, and therefore also the names,
number and order of the "parameters" you want.

In that situation, trying to fit everything on one line is not only
unnecessary, it actually makes both the parsing and the subsequent
statistical analysis much more difficult. I think you'd be much better
off using a normalised structure like this table, in which each entry
from the log is translated into several simple, identically structured
records:

tblLogParameters
ID* (autonumber, unique index, needed to ensure
that the items can be retrieved in the original order)
Timestamp (the timestamp of the log entry of which this
record is part)
EventType (e.g. "Boiler temp warning", "Generator stopped")
ParameterType (e.g. "Temperature at alarm", "Valve closed")
ParameterValue Double (e.g. 240, 1234)
ParameterUnits (e.g. "deg C", "deg/min", or nothing)

So these entries

1 * '* is the identifier for a new alarm message
2 Boiler temp warning. Actual value 240 degC
3 Increase rate 12.7 deg/min
4 Valve 1234 closed
5 *
6 Generator stopped
7 Valve 2345 and 678 opened

might produce these records:

ID, Timestamp, EventType, ParameterType, ParameterValue, ParameterUnits
1, 2006/04/15 18:06:55, "Boiler temp warning", "Temperature at alarm",
240.0, "deg C"
2, 2006/04/15 18:06:55, "Boiler temp warning", "Increase rate", 12.7,
"deg/min"
3, 2006/04/15 18:06:55, "Boiler temp warning", "Valve closed", 1234,
NULL
4, 2006/04/15 20:12:00, "Generator stopped", "Valve opened", 2345, NULL
5, 2006/04/15 20:12:00, "Generator stopped", "Valve opened", 678, NULL

To populate the table, I'd write code that reads the log file line by
line, parses the lines and appends the corresponding records to the
table. Your idea of using a second table with information on the lines
that will need to be parsed is sound - but if you parse individual lines
in the log file rather than the much more complicated ones produced by
stuffing a whole log entry into one line, you'll probably find that each
line fits one of a small number of patterns, such as

<Event type>
e.g. "Generator stopped"

<Event type> <Parameter type> <Parameter value> <Parameter units>
e.g. "Boiler temp warning. Actual value 240 degC"

<Parameter type> <Parameter value> <Parameter units>
e.g. "Increase rate 12.7 deg/min"

<Noun> <Identifier> <Verb>
e.g. "Valve 1234 closed"

Hope this helps,

John

 

[Guest]

Hi John,

I agree - it is easy to parse each individual line in Table1 by applying
different queries checking whether a significant string is available or not.
The problem is that the number of combinations are about 300 pcs, so it must
be maintained in a table (Table2).

Table2 holds info about identifying string, character position where
parameter value starts and ends (alternatively a string which limits the
parameter value).

What I need is to read Table1 record-by-record and for each record start
checking the Table2 records, selecting the identifying string for each
individual alarm type, then check whether this string is available in the
Table1 line or not. If the string is found I then pick the other parameters
from Table2 which makes it possible to fully break down the Table1 line. If
the string is not found, I have to proceed to the next record in Table2 to
see if this suits. So it must be some sort of Do Until Loop running through
Table2 providing the each individual identifying string for comparison with
the Table2 record and, when the right record is found, also provide the other
parameters used to break-down the information in the Table 1 line.

Hope this clarifies.

Regards,
Gunnar

"John Nurick" skrev:

 

[John Nurick]

Hi Gunnar,

I advise against importing the log file into a "Table1" with one record
for each multi-line log entry. Instead, read the log file line by line
from disk and parse each line as you read it.

You don't need an explicit loop to search Table2 for the record that
matches what you've just read from the text file. It's simpler and
probably quicker to use a select query to open a recordset containing
the record you need:


Dim lngFN As Long
Dim strLine As String
Dim strFN As String
Dim rsR As DAO.Recordset
Dim dbD As DAO.Database


strFN = "D:\Folder\Logfile.txt"

lngFN = FreeFile()
Open strFN For Input As #lnfFN

Set dbD = CurrentDB()

Do Until EOF(lngFN) 'loop through input file
Line Input #lngFN, strLine 'read line

Set rsR = dbD.OpenRecordset( _
"SELECT * FROM Table2 WHERE '" & strLine _
& "' LIKE Table2.XXX & '*';"

'XXX is the name of the field in Table2 that contains
'the text for each event.

'rsR.Fields now contains all the values from the relevant
'record in Table2. Use these to parse strLine and assemble
'data ready to append to the table where you want to store it.

Loop

 

[Guest]

Thanks John,

I have got it working by using a defined function now. I don't know if
performance will be an issue here. If so - I will check your method.

Many thanks for your efforts.

Regards,
Gunnar

"John Nurick" skrev: