LogViewPlus Support » LogViewPlus Support » Help & Support » How to use SQL to filter out empty column values

How to use SQL to filter out empty column values

sirlogalot — Fri, 18 Apr 2025 05:03:20 GMT

Hi,
I have a log I'm parsing that's JSON. Only some of the rows contain a certain key/value pair (in this case "Duration"). I've gotten those to parse out just fine. Now I want to use a SQL filter to limit the display to rows where Duration is > 2. The problem is I keep getting the error "No data is found for field 'Duration'". I haven't had any luck getting around this issue.

RE: How to use SQL to filter out empty column values

LogViewPlus Support — Fri, 18 Apr 2025 05:03:20 GMT

Thanks for the detailed bug report. I have recreated the issue on my end and can confirm that this is a bug.

I think it is worth noting that the issue exists even the simplifed query:
[code language="sql"]SELECT * FROM CV[/code]
This statement fails with the same error.

I am not sure what the problem could be, but I suspsect it has something to do with how the JSON data is stored. I will need to investigate further and get back to you.

Thanks again,

Toby

RE: How to use SQL to filter out empty column values

sirlogalot — Thu, 17 Apr 2025 23:01:28 GMT

Here's a sanitized version from my logs. I was able to recreate the issue using only 2 lines:

[code]{"message":"Entry without a duration","context":{"call_stack":[" /var/www/example.com/public/wp/wp-cron.php(191): do_action_ref_array()"," /var/www/example.com/public/wp/wp-includes/plugin.php(565): WP_Hook->do_action()"," /var/www/example.com/public/wp/wp-includes/class-wp-hook.php(348): WP_Hook->apply_filters()"," /var/www/example.com/public/wp/wp-includes/class-wp-hook.php(324): Site\\Salesforce->cron_salesforce_sync()"," /var/www/example.com/public/content/plugins/site/classes/salesforce.php(594): Monolog\\Logger->info()"," /var/www/example.com/composer/monolog/monolog/src/Monolog/Logger.php(606): Monolog\\Logger->addRecord()"," /var/www/example.com/composer/monolog/monolog/src/Monolog/Logger.php(377): Site\\CallStackContextProcessor->__invoke()"]},"level":200,"level_name":"INFO","channel":"Salesforce","datetime":"2025-04-15T20:40:04.197364+00:00","extra":{"hook":{"name":"cron_salesforce_sync","priority":10},"request_id":"45a570f7-4a81-4ec4-bb4b-6ba5b307ab85","url":"/wp/wp-cron.php?doing_wp_cron","ip":"0.0.0.0","http_method":"GET","server":"example.com","referrer":null,"file":"/var/www/example.com/public/content/plugins/site/classes/salesforce.php","line":594,"class":"Site\\Salesforce","callType":"->","function":"cron_salesforce_sync"}}
{"message":"Entry with duration","context":{"duration":1.93,"call_stack":[" /var/www/example.com/public/wp/wp-cron.php(191): do_action_ref_array()"," /var/www/example.com/public/wp/wp-includes/plugin.php(565): WP_Hook->do_action()"," /var/www/example.com/public/wp/wp-includes/class-wp-hook.php(348): WP_Hook->apply_filters()"," /var/www/example.com/public/wp/wp-includes/class-wp-hook.php(324): Site\\Salesforce->cron_salesforce_sync()"," /var/www/example.com/public/content/plugins/site/classes/salesforce.php(594): Monolog\\Logger->info()"," /var/www/example.com/composer/monolog/monolog/src/Monolog/Logger.php(606): Monolog\\Logger->addRecord()"," /var/www/example.com/composer/monolog/monolog/src/Monolog/Logger.php(377): Site\\CallStackContextProcessor->__invoke()"]},"level":200,"level_name":"INFO","channel":"Salesforce","datetime":"2025-04-15T20:40:04.197364+00:00","extra":{"hook":{"name":"cron_salesforce_sync","priority":10},"request_id":"45a570f7-4a81-4ec4-bb4b-6ba5b307ab85","url":"/wp/wp-cron.php?doing_wp_cron","ip":"0.0.0.0","http_method":"GET","server":"example.com","referrer":null,"file":"/var/www/example.com/public/content/plugins/site/classes/salesforce.php","line":594,"class":"Site\\Salesforce","callType":"->","function":"cron_salesforce_sync"}}
[/code]

This is the JsonParser config I'm using:

[code]{
"message": "%message",
"context": {
   "duration": "%S{Duration}",
},
"ndc": "%x",
"level_name": "%p",
"channel": "%S{Channel}",
"datetime": "%d{yyyy-MM-ddT%H:mm:ss.ffffff%zzzz}",
"extra": {
   "hook": {
    "name": "%S{Hook Name}",
    "priority": "%S{Hook Priority}"
   },
   "url": "%S{URL}",
   "ip": "%S{IP}",
   "http_method": "%S{Method}",
   "server": "%S{Server}",
   "referred": "%S{Referrer}",
   "file": "%S{File}",
   "line": "%line",
   "class": "%class",
   "function": "%method",
   "request_id": "%t"
}
}
[/code]

RE: How to use SQL to filter out empty column values

LogViewPlus Support — Wed, 16 Apr 2025 08:44:11 GMT

I am surprised you are getting the same error when the type is correctly identified as numeric.

It may have something to do with how the file is being parsed. You mentioned Duration is an optional field in a JSON log message. How are you parsing this field out?

If you are able to provide sample log entries, I can try to recreate the issue locally. Please feel free to contact me directly if confidentiality is needed:
https://www.logviewplus.com/contact.aspx

RE: How to use SQL to filter out empty column values

sirlogalot — Tue, 15 Apr 2025 20:28:37 GMT

I'm using v3.1.18. I just updated to v3.1.21 and tried both SQL commands again, same error. I did notice that the program is recognizing Duration as Numeric instead of Text this time.

RE: How to use SQL to filter out empty column values

LogViewPlus Support — Tue, 15 Apr 2025 19:08:47 GMT

Thanks for the update.

What version of LogViewPlus are you using? Have you tried with the latest BETA release - v3.1.21?

RE: How to use SQL to filter out empty column values

sirlogalot — Tue, 15 Apr 2025 17:52:21 GMT

I tried both null checks, but they both give me the "No data is found for field 'Duration'" error. I know the column is available too. I tried having chatgpt help with the T-SQL and it gave the same suggestions you did.

RE: How to use SQL to filter out empty column values

LogViewPlus Support — Tue, 15 Apr 2025 04:06:10 GMT

Hi,

The problem here is that sometimes the Duration field is NULL. To work around this, you need to write a SQL statement which tests for NULL.

You can try something like:
[code language="sql"]SELECT * FROM CV
WHERE [Duration] IS NOT NULL
AND [Duration] > 2;[/code]
Or...
[code]SELECT * FROM CV
WHERE ISNULL([Duration], 0) > 2;[/code]
Hope that helps,

Toby