Help! - Windows Process or File Audit

  REVIEWED by Sophos 

Hi all,

I'm quite new to Live Discover and can't quite figure out how to go about writing a query that simply reports on the preesnce of a specific Windows file process or an application file within a given directory.

Can someone point me in the direction of how to get started with this?


Thank you!

  • This is what I suggest: I have a batch file which has the following 2 lines that I run as admin:

    start "" "C:\Program Files\Sophos\Live Query\SophosOsquery.exe" --allow_unsafe --socket \\.\pipe\sophososquery.sock

    "C:\Program Files\Sophos\Live Query\SophosOsqueryExtension.exe" --verbose --socket \\.\pipe\sophososquery.sock

    This will give you a prompt:


    To get a list of tables you can run:


    You will see a "processes" table, this is for currently running processes rather than a history of processes as recorded by Sophos.

    You can run:

    .schema processes

    This command will show the schema of a given table.  In this case:

    CREATE TABLE processes(`pid` BIGINT, `name` TEXT, `path` TEXT, `cmdline` TEXT, `state` TEXT, `cwd` TEXT, `root` TEXT, `uid` BIGINT, `gid` BIGINT, `euid` BIGINT, `egid` BIGINT, `suid` BIGINT, `sgid` BIGINT, `on_disk` INTEGER, `wired_size` BIGINT, `resident_size` BIGINT, `total_size` BIGINT, `user_time` BIGINT, `system_time` BIGINT, `disk_bytes_read` BIGINT, `disk_bytes_written` BIGINT, `start_time` BIGINT, `parent` BIGINT, `pgroup` BIGINT, `threads` INTEGER, `nice` INTEGER, `is_elevated_token` INTEGER, `elapsed_time` BIGINT, `handle_count` BIGINT, `percent_processor_time` BIGINT, `upid` BIGINT HIDDEN, `uppid` BIGINT HIDDEN, `cpu_type` INTEGER HIDDEN, `cpu_subtype` INTEGER HIDDEN, `phys_footprint` BIGINT HIDDEN, PRIMARY KEY (`pid`)) WITHOUT ROWID;

    So the cmdline and or path columns might be of use to you if you're interested in processes running from a specific location.  I am limiting the results to just 5 results.

    select path, cmdline from processes where cmdline like '%.exe%' limit 5;
    | path                             | cmdline                                                      |
    | C:\Windows\System32\smss.exe     | \SystemRoot\System32\smss.exe                                |
    | C:\Windows\System32\wininit.exe  | wininit.exe                                                  |
    | C:\Windows\System32\services.exe | C:\WINDOWS\system32\services.exe                             |
    | C:\Windows\System32\lsass.exe    | C:\WINDOWS\system32\lsass.exe                                |
    | C:\Windows\System32\svchost.exe  | C:\WINDOWS\system32\svchost.exe -k DcomLaunch -p -s PlugPlay |

    Does this help get you started? You can update the like to include the path.

    One of the queries you can run via Central is the 'Table Schema' report, this is a built-in query which runs:

    SELECT "table",
    CAST( AS TEXT) "column",
    CAST(pti.type AS TEXT) "type", "extension"
    FROM osquery_extensions
    JOIN osquery_registry ON osquery_extensions.uuid = osquery_registry.owner_uuid
    JOIN pragma_table_info( pti
    WHERE osquery_registry.registry = 'table'

    You can then export this to CSV as reference for the future.


  • In reply to jak:

    Jak -- this is awesome!  Thank you!!!