osqueryr

‘osquery’ ‘DBI’ and ‘dbplyr’ Interface for R

WIP WIP WIP

But, so far it seems to work pretty well.

NOTE: You need to install osquery for this to work.

Read https://osquery.readthedocs.io/en/stable/ before proceeding.

HEY!

One of the super cool things abt osquery is that it works on every major platform so you can use this package to normalize OS-level queries for anything that you may have wanted to do before but didn’t feel like doing b/c you had to handle so many OS foibles.

Description

‘osquery’ https://osquery.readthedocs.io/en/stable/ is an operating system instrumentation framework for ‘Windows’, ‘OS X (macOS)’, ‘Linux’, and ‘FreeBSD’. The tools make low-level operating system analytics and monitoring both performant and intuitive. A full ‘dbplyr’-compliant ‘DBI’-driver interface is provided facilitating intuitive and tidy analytic idioms.

What’s Inside The Tin

Pretty much what you’d expect for DBI and dbplyr plus:

The following functions are implemented:

• osq_fs_logs: List all the logs on our local system
• osq_expose_tables: Return all (or selected) local or remote osquery tables as a named list of dbplyr tibbles
• osq_load_tables: Return all (or selected) local or remote osquery tables as a named list of dbplyr tibbles

TODO (y’all are encouraged to contribute)

• finish DBI driver
• smart(er) type conversion
• tests
• vignette(s)

Installation

devtools::install_github("hrbrmstr/osqueryr")

Usage

library(osqueryr)
library(tidyverse)
library(knitr)

# current verison
packageVersion("osqueryr")

## [1] '0.1.0'

osquery info

osqdb <- src_dbi(osqueryr::dbConnect(Osquery()))

glimpse(tbl(osqdb, "osquery_info"))

## Observations: ??
## Variables: 11
## $ build_distro   <chr> "10.12"
## $ build_platform <chr> "darwin"
## $ config_hash    <chr> "3bf3bbf3e71f4062ea687875764bb44eb6dc4c31"
## $ config_valid   <chr> "1"
## $ extensions     <chr> "inactive"
## $ instance_id    <chr> "d2961e7f-bbcc-4cb7-b27b-736873256d7a"
## $ pid            <chr> "95956"
## $ start_time     <chr> "1527702872"
## $ uuid           <chr> "3A087DAC-6414-5FA9-9E10-42EE9CED7C25"
## $ version        <chr> "3.2.4"
## $ watcher        <chr> "-1"

This can work with remote hosts, too:

con <- osqueryr::dbConnect(Osquery())
con

## <OsqueryConnection>

local_db <- src_dbi(con)
local_db

## src:  OsqueryConnection
## tbls: account_policy_data, acpi_tables, ad_config, alf, alf_exceptions, alf_explicit_auths, alf_services, app_schemes,
##   apps, apt_sources, arp_cache, asl, augeas, authorization_mechanisms, authorizations, authorized_keys, block_devices,
##   browser_plugins, carbon_black_info, carves, certificates, chrome_extensions, cpu_time, cpuid, crashes, crontab, curl,
##   curl_certificate, device_file, device_firmware, device_hash, device_partitions, disk_encryption, disk_events,
##   dns_resolvers, docker_container_labels, docker_container_mounts, docker_container_networks, docker_container_ports,
##   docker_container_processes, docker_container_stats, docker_containers, docker_image_labels, docker_images,
##   docker_info, docker_network_labels, docker_networks, docker_version, docker_volume_labels, docker_volumes, etc_hosts,
##   etc_protocols, etc_services, event_taps, extended_attributes, fan_speed_sensors, file, file_events, firefox_addons,
##   gatekeeper, gatekeeper_approved_apps, groups, hardware_events, hash, homebrew_packages, interface_addresses,
##   interface_details, iokit_devicetree, iokit_registry, kernel_extensions, kernel_info, kernel_panics, keychain_acls,
##   keychain_items, known_hosts, last, launchd, launchd_overrides, listening_ports, lldp_neighbors, load_average,
##   logged_in_users, magic, managed_policies, mounts, nfs_shares, nvram, opera_extensions, os_version, osquery_events,
##   osquery_extensions, osquery_flags, osquery_info, osquery_packs, osquery_registry, osquery_schedule, package_bom,
##   package_install_history, package_receipts, pci_devices, platform_info, plist, power_sensors, preferences,
##   process_envs, process_events, process_memory_map, process_open_files, process_open_sockets, processes,
##   prometheus_metrics, python_packages, quicklook_cache, routes, safari_extensions, sandboxes, shared_folders,
##   sharing_preferences, shell_history, signature, sip_config, smbios_tables, smc_keys, startup_items, sudoers, suid_bin,
##   system_controls, system_info, temperature_sensors, time, time_machine_backups, time_machine_destinations, uptime,
##   usb_devices, user_events, user_groups, user_interaction_events, user_ssh_keys, users, virtual_memory_info,
##   wifi_networks, wifi_status, wifi_survey, xprotect_entries, xprotect_meta, xprotect_reports, yara, yara_events,
##   yum_sources

osq1_con <- osqueryr::dbConnect(Osquery(), host = "hrbrmstr@osq1")

## New server fingerprint: 37:92:1c:e3:a5:4a:e8:6f:dc:e7:86:00:16:3a:46:5b:b4:9f:df:f1

osq1_con

## <OsqueryConnection>
## <ssh session>
## connected: hrbrmstr@osq1:22
## server: 37:92:1c:e3:a5:4a:e8:6f:dc:e7:86:00:16:3a:46:5b:b4:9f:df:f1

osq1_db <- src_dbi(osq1_con)
osq1_db

## <ssh session>
## connected: hrbrmstr@osq1:22
## server: 37:92:1c:e3:a5:4a:e8:6f:dc:e7:86:00:16:3a:46:5b:b4:9f:df:f1
## src:  OsqueryConnection
## tbls: account_policy_data, acpi_tables, ad_config, alf, alf_exceptions, alf_explicit_auths, alf_services, app_schemes,
##   apps, apt_sources, arp_cache, asl, augeas, authorization_mechanisms, authorizations, authorized_keys, block_devices,
##   browser_plugins, carbon_black_info, carves, certificates, chrome_extensions, cpu_time, cpuid, crashes, crontab, curl,
##   curl_certificate, device_file, device_firmware, device_hash, device_partitions, disk_encryption, disk_events,
##   dns_resolvers, docker_container_labels, docker_container_mounts, docker_container_networks, docker_container_ports,
##   docker_container_processes, docker_container_stats, docker_containers, docker_image_labels, docker_images,
##   docker_info, docker_network_labels, docker_networks, docker_version, docker_volume_labels, docker_volumes, etc_hosts,
##   etc_protocols, etc_services, event_taps, extended_attributes, fan_speed_sensors, file, file_events, firefox_addons,
##   gatekeeper, gatekeeper_approved_apps, groups, hardware_events, hash, homebrew_packages, interface_addresses,
##   interface_details, iokit_devicetree, iokit_registry, kernel_extensions, kernel_info, kernel_panics, keychain_acls,
##   keychain_items, known_hosts, last, launchd, launchd_overrides, listening_ports, lldp_neighbors, load_average,
##   logged_in_users, magic, managed_policies, mounts, nfs_shares, nvram, opera_extensions, os_version, osquery_events,
##   osquery_extensions, osquery_flags, osquery_info, osquery_packs, osquery_registry, osquery_schedule, package_bom,
##   package_install_history, package_receipts, pci_devices, platform_info, plist, power_sensors, preferences,
##   process_envs, process_events, process_memory_map, process_open_files, process_open_sockets, processes,
##   prometheus_metrics, python_packages, quicklook_cache, routes, safari_extensions, sandboxes, shared_folders,
##   sharing_preferences, shell_history, signature, sip_config, smbios_tables, smc_keys, startup_items, sudoers, suid_bin,
##   system_controls, system_info, temperature_sensors, time, time_machine_backups, time_machine_destinations, uptime,
##   usb_devices, user_events, user_groups, user_interaction_events, user_ssh_keys, users, virtual_memory_info,
##   wifi_networks, wifi_status, wifi_survey, xprotect_entries, xprotect_meta, xprotect_reports, yara, yara_events,
##   yum_sources

osq2_con <- osqueryr::dbConnect(Osquery(), host = "bob@osq2", osquery_remote_path = "/usr/bin")

## New server fingerprint: f4:b6:c1:28:28:7d:d0:03:92:a3:a5:fe:c3:40:e3:7d:8c:db:71:1a

osq2_con

## <OsqueryConnection>
## <ssh session>
## connected: bob@osq2:22
## server: f4:b6:c1:28:28:7d:d0:03:92:a3:a5:fe:c3:40:e3:7d:8c:db:71:1a

osq2_db <- src_dbi(osq2_con)
osq2_db

## <ssh session>
## connected: bob@osq2:22
## server: f4:b6:c1:28:28:7d:d0:03:92:a3:a5:fe:c3:40:e3:7d:8c:db:71:1a
## src:  OsqueryConnection
## tbls: acpi_tables, apt_sources, arp_cache, augeas, authorized_keys, block_devices, carbon_black_info, carves,
##   chrome_extensions, cpu_time, cpuid, crontab, curl, curl_certificate, deb_packages, device_file, device_hash,
##   device_partitions, disk_encryption, dns_resolvers, docker_container_labels, docker_container_mounts,
##   docker_container_networks, docker_container_ports, docker_container_processes, docker_container_stats,
##   docker_containers, docker_image_labels, docker_images, docker_info, docker_network_labels, docker_networks,
##   docker_version, docker_volume_labels, docker_volumes, ec2_instance_metadata, ec2_instance_tags, etc_hosts,
##   etc_protocols, etc_services, file, file_events, firefox_addons, groups, hardware_events, hash, intel_me_info,
##   interface_addresses, interface_details, iptables, kernel_info, kernel_integrity, kernel_modules, known_hosts, last,
##   listening_ports, lldp_neighbors, load_average, logged_in_users, magic, md_devices, md_drives, md_personalities,
##   memory_devices, memory_info, memory_map, mounts, msr, npm_packages, opera_extensions, os_version, osquery_events,
##   osquery_extensions, osquery_flags, osquery_info, osquery_packs, osquery_registry, osquery_schedule, pci_devices,
##   platform_info, portage_keywords, portage_packages, portage_use, process_envs, process_events, process_file_events,
##   process_memory_map, process_open_files, process_open_sockets, processes, prometheus_metrics, python_packages, routes,
##   rpm_package_files, rpm_packages, shadow, shared_memory, shell_history, smbios_tables, socket_events, sudoers,
##   suid_bin, syslog_events, system_controls, system_info, time, uptime, usb_devices, user_events, user_groups,
##   user_ssh_keys, users, yara, yara_events, yum_sources

available tables

osqdb

## src:  OsqueryConnection
## tbls: account_policy_data, acpi_tables, ad_config, alf, alf_exceptions, alf_explicit_auths, alf_services, app_schemes,
##   apps, apt_sources, arp_cache, asl, augeas, authorization_mechanisms, authorizations, authorized_keys, block_devices,
##   browser_plugins, carbon_black_info, carves, certificates, chrome_extensions, cpu_time, cpuid, crashes, crontab, curl,
##   curl_certificate, device_file, device_firmware, device_hash, device_partitions, disk_encryption, disk_events,
##   dns_resolvers, docker_container_labels, docker_container_mounts, docker_container_networks, docker_container_ports,
##   docker_container_processes, docker_container_stats, docker_containers, docker_image_labels, docker_images,
##   docker_info, docker_network_labels, docker_networks, docker_version, docker_volume_labels, docker_volumes, etc_hosts,
##   etc_protocols, etc_services, event_taps, extended_attributes, fan_speed_sensors, file, file_events, firefox_addons,
##   gatekeeper, gatekeeper_approved_apps, groups, hardware_events, hash, homebrew_packages, interface_addresses,
##   interface_details, iokit_devicetree, iokit_registry, kernel_extensions, kernel_info, kernel_panics, keychain_acls,
##   keychain_items, known_hosts, last, launchd, launchd_overrides, listening_ports, lldp_neighbors, load_average,
##   logged_in_users, magic, managed_policies, mounts, nfs_shares, nvram, opera_extensions, os_version, osquery_events,
##   osquery_extensions, osquery_flags, osquery_info, osquery_packs, osquery_registry, osquery_schedule, package_bom,
##   package_install_history, package_receipts, pci_devices, platform_info, plist, power_sensors, preferences,
##   process_envs, process_events, process_memory_map, process_open_files, process_open_sockets, processes,
##   prometheus_metrics, python_packages, quicklook_cache, routes, safari_extensions, sandboxes, shared_folders,
##   sharing_preferences, shell_history, signature, sip_config, smbios_tables, smc_keys, startup_items, sudoers, suid_bin,
##   system_controls, system_info, temperature_sensors, time, time_machine_backups, time_machine_destinations, uptime,
##   usb_devices, user_events, user_groups, user_interaction_events, user_ssh_keys, users, virtual_memory_info,
##   wifi_networks, wifi_status, wifi_survey, xprotect_entries, xprotect_meta, xprotect_reports, yara, yara_events,
##   yum_sources

sample table

tbl(osqdb, "dns_resolvers")

## # Source:   table<dns_resolvers> [?? x 5]
## # Database: OsqueryConnection
##   address id    netmask options type      
##   <chr>   <chr> <chr>   <chr>   <chr>     
## 1 9.9.9.9 0     32      1729    nameserver
## 2 1.1.1.1 1     32      1729    nameserver
## 3 rud.is  0     ""      1729    search

check out processes

procs <- tbl(osqdb, "processes")

filter(procs, cmdline != "") %>% 
  select(cmdline, total_size)

## # Source:   lazy query [?? x 2]
## # Database: OsqueryConnection
##    cmdline                                                                                                   total_size
##    <chr>                                                                                                     <chr>     
##  1 /System/Library/CoreServices/loginwindow.app/Contents/MacOS/loginwindow console                           79962112  
##  2 /System/Library/Frameworks/CoreServices.framework/Frameworks/Metadata.framework/Versions/A/Support/mdwor… 26521600  
##  3 /usr/sbin/cfprefsd agent                                                                                  8904704   
##  4 /usr/libexec/lsd                                                                                          27041792  
##  5 /usr/libexec/trustd --agent                                                                               39198720  
##  6 /usr/sbin/distnoted agent                                                                                 15196160  
##  7 /usr/libexec/secd                                                                                         26959872  
##  8 /usr/libexec/UserEventAgent (Aqua)                                                                        9367552   
##  9 /System/Library/Frameworks/CoreTelephony.framework/Support/CommCenter -L                                  9097216   
## 10 /usr/libexec/languageassetd --firstLogin                                                                  8486912   
## # ... with more rows

filter(procs, name %like% '%fire%') %>% 
  glimpse()

## Observations: ??
## Variables: 26
## $ cmdline            <chr> "/Applications/FirefoxDeveloperEdition.app/Contents/MacOS/firefox -foreground"
## $ cwd                <chr> "/"
## $ disk_bytes_read    <chr> "54669312"
## $ disk_bytes_written <chr> "535171072"
## $ egid               <chr> "20"
## $ euid               <chr> "502"
## $ gid                <chr> "20"
## $ name               <chr> "firefox"
## $ nice               <chr> "0"
## $ on_disk            <chr> "1"
## $ parent             <chr> "1"
## $ path               <chr> "/Applications/FirefoxDeveloperEdition.app/Contents/MacOS/firefox"
## $ pgroup             <chr> "94889"
## $ pid                <chr> "94889"
## $ resident_size      <chr> "1031360512"
## $ root               <chr> ""
## $ sgid               <chr> "20"
## $ start_time         <chr> "1278843"
## $ state              <chr> "R"
## $ suid               <chr> "502"
## $ system_time        <chr> "64813"
## $ threads            <chr> "77"
## $ total_size         <chr> "762355712"
## $ uid                <chr> "502"
## $ user_time          <chr> "139677"
## $ wired_size         <chr> "0"

see if any processes have no corresponding disk image

filter(procs, on_disk == 0) %>% 
  select(name, path, pid)

## # Source:   lazy query [?? x 3]
## # Database: OsqueryConnection

(gosh I hope ^^ was empty)

top 10 largest processes by resident memory size

arrange(procs, desc(resident_size)) %>% 
  select(pid, name, uid, resident_size)

## # Source:     lazy query [?? x 4]
## # Database:   OsqueryConnection
## # Ordered by: desc(resident_size)
##    name                  pid   resident_size uid  
##    <chr>                 <chr> <chr>         <chr>
##  1 java                  54764 4010483712    502  
##  2 com.docker.hyperkit   28543 2868359168    502  
##  3 rsession              21656 1592000512    502  
##  4 firefox               94889 1031344128    502  
##  5 QtWebEngineProcess    16108 943153152     502  
##  6 Adobe Desktop Service 1242  796278784     502  
##  7 kbfs                  13741 606306304     502  
##  8 Finder                1073  577482752     502  
##  9 recentsd              656   505774080     502  
## 10 plugin-container      94890 490287104     502  
## # ... with more rows

process count for the top 10 most active processes

count(procs, name, sort=TRUE)

## # Source:     lazy query [?? x 2]
## # Database:   OsqueryConnection
## # Ordered by: desc(n)
##    n     name                
##    <chr> <chr>               
##  1 17    MTLCompilerService  
##  2 14    mdworker            
##  3 9     bash                
##  4 8     iTerm2              
##  5 7     login               
##  6 6     Dropbox             
##  7 6     Google Chrome Helper
##  8 5     distnoted           
##  9 5     plugin-container    
## 10 5     ssh                 
## # ... with more rows

get all processes listening on a port (join example)

listen <- tbl(osqdb, "listening_ports")

left_join(procs, listen, by="pid") %>%
  filter(port != "") %>% 
  distinct(name, port, address, pid)

## # Source:   lazy query [?? x 4]
## # Database: OsqueryConnection
##    address   name                                         pid   port 
##    <chr>     <chr>                                        <chr> <chr>
##  1 127.0.0.1 2BUA8C4S2C.com.agilebits.onepassword4-helper 67094 6258 
##  2 ::1       2BUA8C4S2C.com.agilebits.onepassword4-helper 67094 6258 
##  3 127.0.0.1 2BUA8C4S2C.com.agilebits.onepassword4-helper 67094 6263 
##  4 ::1       2BUA8C4S2C.com.agilebits.onepassword4-helper 67094 6263 
##  5 ""        ARDAgent                                     494   0    
##  6 0.0.0.0   ARDAgent                                     494   3283 
##  7 ""        Adobe CEF Helper                             1234  0    
##  8 ""        Adobe CEF Helper                             1294  0    
##  9 ""        Adobe Desktop Service                        1242  0    
## 10 127.0.0.1 Adobe Desktop Service                        1242  15292
## # ... with more rows

get file info

files <- tbl(osqdb, "file")

filter(files, path == "/etc/hosts") %>% 
  select(filename, size)

## # Source:   lazy query [?? x 2]
## # Database: OsqueryConnection
##   filename size   
##   <chr>    <chr>  
## 1 hosts    1425159

users

tbl(osqdb, "users")

## # Source:   table<users> [?? x 10]
## # Database: OsqueryConnection
##    description           directory          gid   gid_signed shell          uid   uid_signed username     uuid         
##    <chr>                 <chr>              <chr> <chr>      <chr>          <chr> <chr>      <chr>        <chr>        
##  1 AMaViS Daemon         /var/virusmails    83    83         /usr/bin/false 83    83         _amavisd     FFFFEEEE-DDD…
##  2 Analytics Daemon      /var/db/analyticsd 263   263        /usr/bin/false 263   263        _analyticsd  FFFFEEEE-DDD…
##  3 AppleEvents Daemon    /var/empty         55    55         /usr/bin/false 55    55         _appleevents FFFFEEEE-DDD…
##  4 applepay Account      /var/db/applepay   260   260        /usr/bin/false 260   260        _applepay    FFFFEEEE-DDD…
##  5 Application Owner     /var/empty         87    87         /usr/bin/false 87    87         _appowner    FFFFEEEE-DDD…
##  6 Application Server    /var/empty         79    79         /usr/bin/false 79    79         _appserver   FFFFEEEE-DDD…
##  7 Mac App Store Service /var/empty         33    33         /usr/bin/false 33    33         _appstore    FFFFEEEE-DDD…
##  8 Apple Remote Desktop  /var/empty         67    67         /usr/bin/false 67    67         _ard         FFFFEEEE-DDD…
##  9 Asset Cache Service   /var/empty         235   235        /usr/bin/false 235   235        _assetcache  FFFFEEEE-DDD…
## 10 Astris Services       /var/db/astris     245   245        /usr/bin/false 245   245        _astris      FFFFEEEE-DDD…
## # ... with more rows

tbl(osqdb, "logged_in_users")

## # Source:   table<logged_in_users> [?? x 6]
## # Database: OsqueryConnection
##    host  pid   time       tty     type  user    
##    <chr> <chr> <chr>      <chr>   <chr> <chr>   
##  1 ""    128   1526421243 console user  hrbrmstr
##  2 ""    16306 1527603827 ttys001 user  hrbrmstr
##  3 ""    18733 1527605466 ttys002 user  hrbrmstr
##  4 ""    18954 1527606102 ttys003 user  hrbrmstr
##  5 ""    21736 1527610627 ttys004 user  hrbrmstr
##  6 ""    23855 1527620471 ttys005 user  hrbrmstr
##  7 ""    23930 1527620785 ttys006 user  hrbrmstr
##  8 ""    72881 1527694548 ttys007 user  hrbrmstr
##  9 ""    29637 1527627647 ttys008 dead  hrbrmstr
## 10 ""    63892 1526725572 ttys009 dead  hrbrmstr
## # ... with more rows

groups

tbl(osqdb, "groups")

## # Source:   table<groups> [?? x 5]
## # Database: OsqueryConnection
##    gid   gid_signed groupname      
##    <chr> <chr>      <chr>          
##  1 701   701        1              
##  2 83    83         _amavisd       
##  3 263   263        _analyticsd    
##  4 250   250        _analyticsusers
##  5 55    55         _appleevents   
##  6 260   260        _applepay      
##  7 87    87         _appowner      
##  8 81    81         _appserveradm  
##  9 79    79         _appserverusr  
## 10 33    33         _appstore      
## # ... with more rows

homebrew!

tbl(osqdb, "homebrew_packages")

## # Source:   table<homebrew_packages> [?? x 3]
## # Database: OsqueryConnection
##    name         path                            version  
##    <chr>        <chr>                           <chr>    
##  1 adns         /usr/local/Cellar/adns/         1.5.1    
##  2 alpine       /usr/local/Cellar/alpine/       2.21     
##  3 ant          /usr/local/Cellar/ant/          1.10.3   
##  4 apache-arrow /usr/local/Cellar/apache-arrow/ 0.9.0    
##  5 apache-arrow /usr/local/Cellar/apache-arrow/ 0.9.0_1  
##  6 aria2        /usr/local/Cellar/aria2/        1.33.1   
##  7 armadillo    /usr/local/Cellar/armadillo/    8.400.0  
##  8 armadillo    /usr/local/Cellar/armadillo/    8.400.1  
##  9 armadillo    /usr/local/Cellar/armadillo/    8.500.0_1
## 10 arpack       /usr/local/Cellar/arpack/       3.5.0_1  
## # ... with more rows

Code of Conduct

Please note that this project is released with a Contributor Code of Conduct. By participating in this project you agree to abide by its terms.