> For the complete documentation index, see [llms.txt](https://sarpers-organization.gitbook.io/ctftricks/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://sarpers-organization.gitbook.io/ctftricks/_chapter-intro-5/endpoint-analysis/forensics-endpoint-analysis-osquery.md).

# Osquery

## Osquery Query Windows Auto-Start Programs

Query the `autoexec` table in Osquery to list programs configured to run automatically on Windows startup. This table aggregates information from various auto-start locations like registry Run keys and Startup folders, helping identify persistence mechanisms.

The `autoexec` table provides detailed information about each entry, including:

* `name`: The name associated with the autoexec entry.
* `path`: The full path to the executable or script.
* `source`: The specific location where the autoexec entry was found (e.g., a particular registry key path or a Startup folder path).
* `type`: Indicates whether the source is a registry key or a startup folder.
* `target`: Specifies whether the entry applies to a specific user (identified by SID) or the machine globally.
* `command`: The complete command line used to execute the entry, including arguments.
* `version`: Relevant for registry entries, indicating if it's a standard `Run` or `RunOnce` type.
* `attributes`: Additional attributes associated with the entry.

A basic query to retrieve all entries is:

```sql
SELECT * FROM autoexec;
```

To focus on specific types of entries, you can filter the results. For example, to see only entries from standard 'Run' keys:

```sql
SELECT * FROM autoexec WHERE version = 'Run';
```

You can also select only the most relevant columns for a clearer overview:

```sql
SELECT name, path, source, type FROM autoexec;
```

Examine the output for suspicious entries, looking at the listed paths, file types, sources (like unusual registry keys or user Startup folders), and commands to uncover potential persistence methods. For instance, looking for executables running from temporary directories could be a sign of malicious activity:

```sql
SELECT * FROM autoexec WHERE path LIKE 'C:\\Users\\%\\AppData\\Local\\Temp\\%';
```

Analyzing the command line can also reveal hidden parameters or actions performed by the auto-starting program.

***

## Osquery Query Windows Process Execution Evidence

On Windows, use Osquery to query the `userassist` table. This table provides access to the UserAssist registry key, which logs graphical program executions. Querying this table is a direct way to uncover evidence of executed GUI applications, including their paths, execution counts, and last run times.

```sql
SELECT * FROM userassist;
```

Examine the output for suspicious or relevant program names.

The `userassist` table provides the following columns:

* `path`: The path to the executable.
* `count`: The number of times the program has been executed.
* `last_run_time`: The last time the program was executed.
* `sid`: The SID of the user who executed the program.

More specific queries can be used to filter or order results. For example, to find programs executed more than 10 times:

```sql
SELECT path, count, last_run_time FROM userassist WHERE count > 10;
```

To see programs ordered by their last execution time, showing the most recently run first:

```sql
SELECT path, last_run_time FROM userassist ORDER BY last_run_time DESC;
```

These queries help narrow down the results and focus on potentially interesting activity based on frequency or recency.