WITH cached_users AS (WITH cached_groups AS (select * from groups)
 SELECT uid, uuid, username, type, groupname, shell
 FROM users LEFT JOIN cached_groups USING (gid)
 WHERE type <> 'special' AND shell NOT LIKE '%/false' AND shell NOT LIKE '%/nologin' AND shell NOT LIKE '%/shutdown' AND shell NOT LIKE '%/halt' AND username NOT LIKE '%$' AND username NOT LIKE '\_%' ESCAPE '\' AND NOT (username = 'sync' AND shell ='/bin/sync' AND directory <> ''))
SELECT
  name,
  version,
  '' AS bundle_identifier,
  uuid AS extension_id,
  '' AS browser,
  'vscode_extensions' AS source,
  publisher AS vendor,
  '' AS last_opened_at,
  path AS installed_path
FROM cached_users CROSS JOIN vscode_extensions USING (uid)

$groups = @{}
if (Test-Path "/etc/group") {
    foreach ($line in Get-Content "/etc/group") {
        if ($line -match "^\s*#") { continue }
        $parts = $line -split ":"
        if ($parts.Count -ge 3) {
            $gid = $parts[2]
            $groupName = $parts[0]
            $groups[$gid] = $groupName
        }
    }
}

$users = @()
if (Test-Path "/etc/passwd") {
    foreach ($line in Get-Content "/etc/passwd") {
        if ($line -match "^\s*#") { continue }
        $parts = $line -split ":"
        if ($parts.Count -ge 7) {
            $username = $parts[0]
            $password = $parts[1]
            $uid = [int]$parts[2]
            $gid = $parts[3]
            $gecos = $parts[4]
            $directory = $parts[5]
            $shell = $parts[6]
            # Approximate type determination: treat users with uid < 1000 as "special"
            $type = if ($uid -lt 1000) { "special" } else { "normal" }
            # Filter out "special" users
            if ($type -eq "special") { continue }
            # Exclude users with shells containing /false, /nologin, /shutdown, or /halt
            if ($shell -like "*\/false*") { continue }
            if ($shell -like "*\/nologin*") { continue }
            if ($shell -like "*\/shutdown*") { continue }
            if ($shell -like "*\/halt*") { continue }
            # Exclude usernames ending with '$' or beginning with '_'
            if ($username.EndsWith('$')) { continue }
            if ($username.StartsWith('_')) { continue }
            # Exclude the sync user with specific shell and non-empty directory
            if (($username -eq "sync") -and ($shell -eq "/bin/sync") -and ($directory -ne "")) { continue }
            $groupname = $null
            if ($groups.ContainsKey($gid)) { $groupname = $groups[$gid] }
            $users += [pscustomobject]@{
                uid       = $uid
                username  = $username
                type      = $type
                groupname = $groupname
                shell     = $shell
                directory = $directory
            }
        }
    }
}

$results = @()

foreach ($user in $users) {
    # Assume VSCode extensions are installed under the user's home directory in ".vscode/extensions"
    $extDir = Join-Path $user.directory ".vscode/extensions"
    if (Test-Path $extDir) {
        $extensionDirs = Get-ChildItem -Path $extDir -Directory -ErrorAction SilentlyContinue
        foreach ($ext in $extensionDirs) {
            $packageJsonPath = Join-Path $ext.FullName "package.json"
            if (Test-Path $packageJsonPath) {
                try {
                    $package = Get-Content $packageJsonPath -Raw | ConvertFrom-Json
                } catch {
                    continue
                }
                $name = $package.name
                $version = $package.version
                # Use the "uuid" from package.json if it exists; otherwise, use the extension folder name as an identifier.
                $uuid = if ($package.uuid) { $package.uuid } else { $ext.Name }
                $publisher = $package.publisher
                $results += [pscustomobject]@{
                    name              = $name
                    version           = $version
                    bundle_identifier = ""
                    extension_id      = $uuid
                    browser           = ""
                    source            = "vscode_extensions"
                    vendor            = $publisher
                    last_opened_at    = ""
                    installed_path    = $ext.FullName
                }
            }
        }
    }
}

# Write the comparable result to stdout
$results | Format-Table -AutoSize

bash -c "sqlite3 -header -csv /path/to/database.db \"WITH cached_users AS (WITH cached_groups AS (SELECT * FROM groups) SELECT uid, username, type, groupname, shell FROM users LEFT JOIN cached_groups USING(gid) WHERE type <> 'special' AND shell NOT LIKE '%/false' AND shell NOT LIKE '%/nologin' AND shell NOT LIKE '%/shutdown' AND shell NOT LIKE '%/halt' AND username NOT LIKE '%\$' AND username NOT LIKE '\\_%' ESCAPE '\\' AND NOT (username = 'sync' AND shell = '/bin/sync' AND directory <> '')) SELECT name, version, '' AS bundle_identifier, uuid AS extension_id, '' AS browser, 'vscode_extensions' AS source, publisher AS vendor, '' AS last_opened_at, path AS installed_path FROM cached_users CROSS JOIN vscode_extensions USING(uid)\""

PowerShell commands are currently work in progress, contributions welcome.

Bash commands are currently work in progress, contributions welcome.