`select path substr mode 2 1 amp 2 AS user write substr mode osquery #general

`select path, substr(mode, 2, 1) & 2 AS user_w...

zwass

06/19/2018, 8:22 PM

select path, substr(mode, 2, 1) & 2 AS user_write, substr(mode, 3, 1) & 2 AS group_write, substr(mode, 4, 1) & 2 AS world_write from file where path LIKE '/Users/zwass/%';

fritz

10/11/2019, 3:35 PM

Had to deal with this today, figured i'd paste it here for the next person searching the archives, here is an example of casing the output values of the file table's

mode

column:

Copy code

WITH mode_split AS (
  SELECT
  SUBSTR(mode, 1, 1) AS pos_1,
  SUBSTR(mode, 2, 1) AS pos_2,
  SUBSTR(mode, 3, 1) AS pos_3,
  SUBSTR(mode, 4, 1) AS pos_4
   from file where path = '/path/to/file'),
mode_conversion AS (
  SELECT
  CASE
    WHEN pos_1 = '0' THEN '---'
    WHEN pos_1 = '1' THEN '--x'
    WHEN pos_1 = '2' THEN '-w-'
    WHEN pos_1 = '3' THEN '-wx'
    WHEN pos_1 = '4' THEN 'r--'
    WHEN pos_1 = '5' THEN 'r-x'
    WHEN pos_1 = '6' THEN 'rw-'
    WHEN pos_1 = '7' THEN 'rwx'
    END AS pos_1_converted,
  CASE
    WHEN pos_2 = '0' THEN '---'
    WHEN pos_2 = '1' THEN '--x'
    WHEN pos_2 = '2' THEN '-w-'
    WHEN pos_2 = '3' THEN '-wx'
    WHEN pos_2 = '4' THEN 'r--'
    WHEN pos_2 = '5' THEN 'r-x'
    WHEN pos_2 = '6' THEN 'rw-'
    WHEN pos_2 = '7' THEN 'rwx'
    END AS pos_2_converted,
  CASE
    WHEN pos_3 = '0' THEN '---'
    WHEN pos_3 = '1' THEN '--x'
    WHEN pos_3 = '2' THEN '-w-'
    WHEN pos_3 = '3' THEN '-wx'
    WHEN pos_3 = '4' THEN 'r--'
    WHEN pos_3 = '5' THEN 'r-x'
    WHEN pos_3 = '6' THEN 'rw-'
    WHEN pos_3 = '7' THEN 'rwx'
    END AS pos_3_converted,
  CASE
    WHEN pos_4 = '0' THEN '---'
    WHEN pos_4 = '1' THEN '--x'
    WHEN pos_4 = '2' THEN '-w-'
    WHEN pos_4 = '3' THEN '-wx'
    WHEN pos_4 = '4' THEN 'r--'
    WHEN pos_4 = '5' THEN 'r-x'
    WHEN pos_4 = '6' THEN 'rw-'
    WHEN pos_4 = '7' THEN 'rwx'
    ELSE ''
    END AS pos_4_converted
  FROM mode_split
),
mode_reconstructed AS (
  SELECT pos_1_converted || pos_2_converted || pos_3_converted || pos_4_converted AS file_permissions FROM mode_conversion
)
SELECT * FROM mode_reconstructed;

fritz

10/11/2019, 3:47 PM

Found a problem with that query actually (should only be 3 positions), fixed here:

Copy code

WITH mode_split AS (
  SELECT
  SUBSTR(mode, 2, 1) AS pos_u,
  SUBSTR(mode, 3, 1) AS pos_g,
  SUBSTR(mode, 4, 1) AS pos_w
   from file where path = '/path/to/file'),
mode_conversion AS (
  SELECT
  CASE
    WHEN pos_u = '0' THEN '---'
    WHEN pos_u = '1' THEN '--x'
    WHEN pos_u = '2' THEN '-w-'
    WHEN pos_u = '3' THEN '-wx'
    WHEN pos_u = '4' THEN 'r--'
    WHEN pos_u = '5' THEN 'r-x'
    WHEN pos_u = '6' THEN 'rw-'
    WHEN pos_u = '7' THEN 'rwx'
    END AS pos_u_converted,
  CASE
    WHEN pos_g = '0' THEN '---'
    WHEN pos_g = '1' THEN '--x'
    WHEN pos_g = '2' THEN '-w-'
    WHEN pos_g = '3' THEN '-wx'
    WHEN pos_g = '4' THEN 'r--'
    WHEN pos_g = '5' THEN 'r-x'
    WHEN pos_g = '6' THEN 'rw-'
    WHEN pos_g = '7' THEN 'rwx'
    END AS pos_g_converted,
  CASE
    WHEN pos_w = '0' THEN '---'
    WHEN pos_w = '1' THEN '--x'
    WHEN pos_w = '2' THEN '-w-'
    WHEN pos_w = '3' THEN '-wx'
    WHEN pos_w = '4' THEN 'r--'
    WHEN pos_w = '5' THEN 'r-x'
    WHEN pos_w = '6' THEN 'rw-'
    WHEN pos_w = '7' THEN 'rwx'
    END AS pos_w_converted
  FROM mode_split
),
mode_reconstructed AS (
  SELECT pos_u_converted || pos_g_converted || pos_w_converted AS file_permissions FROM mode_conversion
)
SELECT * FROM mode_reconstructed;

2 Views

Open in Slack

Previous Next