How to use SQL to filter out empty column values

LogViewPlus Support

How to use SQL to filter out empty column values

https://www.logviewplus.com/forum/Topic2411.aspx

By sirlogalot - 14 Apr 2025

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.

By LogViewPlus Support - 15 Apr 2025

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:

SELECT * FROM CV
WHERE [Duration] IS NOT NULL
  AND [Duration] > 2;

Or...

SELECT * FROM CV
WHERE ISNULL([Duration], 0) > 2;

Hope that helps,

Toby

By sirlogalot - 15 Apr 2025

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.

By LogViewPlus Support - 15 Apr 2025

Thanks for the update.

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

By sirlogalot - 15 Apr 2025

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.

By LogViewPlus Support - 16 Apr 2025

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

By sirlogalot - 17 Apr 2025

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

{"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"}}

This is the JsonParser config I'm using:

{
  "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"
  }
}

By LogViewPlus Support - 18 Apr 2025

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:

SELECT * FROM CV

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