Thread Info
  • State Verified Answer
  • Locked Locked
  • Replies 1 reply
  • Subscribers 13 subscribers
  • Views 987 views
  • Users 0 members are here
Options
Suggested
This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Querying a Registry path, parsing it in CASE...not working.

Andrew Short1

Trying to create a Live Query to assess the state of the Windows Firewall via the registry.  I started with the "View registry Section" query and modified it to the following.  The initial case statement ALWAYS produces the results "Error", even though the strings I'm evaluating come from the raw values produced by the path field in the registry table.  I've tried the full string, starting with 'HKEY_LOCAL_MACHINE..." instead of "%", as well.

What am I doing wrong?

SELECT
CASE path
WHEN path like '%DomainProfile\EnableFirewall' THEN 'Domain'
WHEN path like '%PublicProfile\EnableFirewall' THEN 'Public'
WHEN path like '%StandardProfile\EnableFirewall' THEN 'Standard'
ELSE 'error'
END AS 'Firewall Profile',

path,

CASE type
WHEN 'subkey' THEN ''
ELSE name
END AS 'Registry Key',

CASE data
When '1' THEN 'enabled'
WHEN '0' THEN 'disabled'
END AS 'Firewall Status',
STRFTIME('%Y-%m-%dT%H:%M:%SZ', DATETIME(mtime, 'unixepoch')) AS last_time_modified
FROM registry
WHERE
path LIKE 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\StandardProfile\EnableFirewall'
OR
path LIKE 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\PublicProfile\EnableFirewall'
OR
path LIKE 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\DomainProfile\EnableFirewall'
ORDER BY path ASC



This thread was automatically locked due to age.
  • +1

    I figured it out.  Removed the "path LIKE" and used the entire expected string, like this:  

    CASE path

     WHEN 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\StandardProfile\EnableFirewall'  THEN 'Standard'  

    The entire query, with the technical bits commented out to produce more friendly results:  

    SELECT  

    CASE path

         WHEN 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\StandardProfile\EnableFirewall'               THEN 'Standard'

         WHEN 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\PublicProfile\EnableFirewall'       THEN 'Public'

         WHEN 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\DomainProfile\EnableFirewall'       THEN 'Domain'

         ELSE 'Error'  

    END AS 'Firewall Profile',

    -- path,

    --  

    -- CASE type

    -- WHEN 'subkey' THEN ''

    -- ELSE name

    -- END AS 'Registry Key',

    CASE data  

         When '1' THEN 'enabled'

         WHEN '0' THEN 'disabled'  

    END AS 'Firewall Status',  

    STRFTIME('%Y-%m-%dT%H:%M:%SZ', DATETIME(mtime, 'unixepoch')) AS last_time_modified

    FROM registry

    WHERE

    path LIKE 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\StandardProfile\EnableFirewall' 

    OR

    path LIKE 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\PublicProfile\EnableFirewall'

    OR  

    path LIKE 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\DomainProfile\EnableFirewall'

    ORDER BY path ASC

Unfiltered HTML