LogViewPlus Support » LogViewPlus Support » Help & Support » Interpret Column as Number

Interpret Column as Number

Marc — Sat, 15 Feb 2025 16:10:07 GMT

Hi!

I have a log file where i'd like the retrieve values from two columns called duration and rows and interpret those as numbers in order to sort them by value and not lexicographically. (I need to find long running SQL Queries)

The Logfile has the following properties (with example below):

Some entries have a message + duration + row count + Additional content
Some entries have a message + duration + row count
Some entries have a message + duration
Some entries have only a message

[code]09.12.2024 18:49:28:211 [Worker-0] com.example.MyLogger call INFORMATION: UPDATE RandomValueStore SET Value = 15 WHERE ID = 519342 (duration=10.1504, #rows=9)
09.12.2024 18:49:28:211 [Worker-0] com.example.MyLogger call INFORMATION: UPDATE RandomValueStore SET Value = 185 WHERE Value = 1 (duration=1000.1504, #rows=2451)
09.12.2024 18:49:28:222 [Worker-0] com.example.MyLogger call INFORMATION: SELECT DISTINCT CURRENT_TIMESTAMP FROM RandomValueStore (duration=2.9189, #rows=50, result=2024-12-09 18:49:28.207)
09.12.2024 18:49:31:150 [Worker-0] com.example.MyLogger execute INFORMATION: Begin transaction (duration=2.5889)
09.12.2024 18:49:31:155 [Worker-0] com.example.MyLogger execute INFORMATION: INSERT INTO RandomValueStore (ID, Value) VALUES (?, ?) (duration=4.0736)
09.12.2024 18:49:31:166 [Worker-0] com.example.MyLogger execute INFORMATION: Commit transaction (duration=10.6521)
09.12.2024 18:49:31:168 [Worker-0] com.example.MyLogger fireDeferredEvents INFORMATION: Start fire 18 deferred events
09.12.2024 18:49:31:170 [Worker-0] com.example.MyLogger fireDeferredEvents INFORMATION: Fire 18 deferred events took totally 2 ms
[/code]
Which lead me to the following parser configuration:
[code]%d{dd.MM.yyyy %H:mm:ss:fff} [%t] %c %method %p: %m (duration=%S{Duration}, #rows=%S{Rows},%S)%n
%d{dd.MM.yyyy %H:mm:ss:fff} [%t] %c %method %p: %m (duration=%S{Duration}, #rows=%S{Rows})%n
%d{dd.MM.yyyy %H:mm:ss:fff} [%t] %c %method %p: %m (duration=%S{Duration})%n
%d{dd.MM.yyyy %H:mm:ss:fff} [%t] %c %method %p: %m%n[/code]

How can i interpret those strings as numbers?

In an other post I've read that, i could use the SQL Filter which could be at least somewhat helpful.
But when I try to apply an SQL Filter I get the following error:

When I first use Test to validate my script everything seems fine (Image below)
until I click on Execution Result. Then an error is displayed (image above)

Furthermore I've found out that when I repeat the steps above but without any WHERE term an error will be displayed.

Am I doing something wrong or how can I solve those Issues?

Kind Regards
Marc

RE: Interpret Column as Number

LogViewPlus Support — Sat, 15 Feb 2025 16:10:07 GMT

Glad that helped Marc.

Good point regarding the documentation - thanks for the suggestion! I will update that now to point to message parsing and explain the difference. You should see the update in the next documentation release.

RE: Interpret Column as Number

Marc — Sat, 15 Feb 2025 15:51:24 GMT

Hi Toby,
Thank you very much for your quick reply and support. Maybe you could this this to the Multi-Patterns documentation as a hint.
This is the regex I'm using now (selects SQL, Duration and Row count):
[code](?<SQL>(.*)(?=\(duration\=))\(duration=(?<Duration>(\d+\.\d+)?)(?=[,\)]), #rows=(?<Rows>(\d+))(?=[,\)])
[/code]
Kine Regards
Marc

RE: Interpret Column as Number

LogViewPlus Support — Sat, 15 Feb 2025 09:22:02 GMT

Hi Marc,

Thanks for the detailed issue report.

I think you might be coming at this from the wrong angle. LogViewPlus really wants all log entries to be in the same format, so it has one schema definition for the log file. We do support a multi-parse scenario, but this should not be used for log message parsing.

Instead, I would suggest you use a simplified parser for the log file:
[code]%d{dd.MM.yyyy %H:mm:ss:fff} [%t] %c %method %p: %m%n[/code]
This will load your log file and ignore differences between log messages. Once loaded, you can parse the log entry messages separately:

I would suggest using a Regex Parser for this task:
[code]\(duration=(?<duration>\d+(\.\d+)?)(?=[,\)])[/code]

This will create a parse message filter which isolates the duration values:

From there, it should be straight-forward to apply your SQL statement:

Regarding the screenshots provided above:

The first is likely failing because with your original parsing strategy, some of your Duration column values are null. Hence - "no data found".
The second statement is simply saying "your SQL statement is valid". LogViewPlus only attempts to execute statements once the "Apply" command is executed.
It looks like the third issue is a variation on the first. You have null values for some of your "Rows" columns. So - "no data found".

Hope that helps,

Toby