‘osquery’ ‘DBI’ and ‘dbplyr’ Interface for R
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.
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.
‘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.
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 systemosq_expose_tables: Return all (or selected) local or remote osquery tables as a named list ofdbplyrtibblesosq_load_tables: Return all (or selected) local or remote osquery tables as a named list ofdbplyrtibbles
finish DBI driver- smart(er) type conversion
- tests
- vignette(s)
devtools::install_git("git:https://gitlab.com/hrbrmstr/osqueryr")
devtools::install_git("git:https://github.com/hrbrmstr/osqueryr")library(osqueryr)
library(tidyverse)
library(knitr)
# current verison
packageVersion("osqueryr")## [1] '0.1.0'
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> ""
## $ config_valid <chr> "0"
## $ extensions <chr> "inactive"
## $ instance_id <chr> "0bda930c-43ad-48cc-a324-23ca614aaa00"
## $ pid <chr> "45863"
## $ start_time <chr> "1527364673"
## $ uuid <chr> "709F311C-B34D-5CF4-A111-91C8AA4E079B"
## $ 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## NULL
## 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")
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")
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
osqdb## NULL
## 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
tbl(osqdb, "dns_resolvers")## NULL
## # Source: table<dns_resolvers> [?? x 5]
## # Database: OsqueryConnection
## address id netmask options type
## <chr> <chr> <chr> <chr> <chr>
## 1 "" 0 32 1729 nameserver
## 2 "" 1 32 1729 nameserver
## 3 9.9.9.9 2 32 1729 nameserver
## 4 hsd1.nh.comcast.net 0 "" 1729 search
## 5 hsd1.nh.comcast.net 1 "" 1729 search
procs <- tbl(osqdb, "processes")
filter(procs, cmdline != "") %>%
select(cmdline, total_size)## NULL
## # Source: lazy query [?? x 2]
## # Database: OsqueryConnection
## cmdline total_size
## <chr> <chr>
## 1 /System/Library/CoreServices/loginwindow.app/Contents/MacOS/loginwindow console 45940736
## 2 /usr/sbin/cfprefsd agent 4009984
## 3 /usr/libexec/UserEventAgent (Aqua) 6242304
## 4 /usr/sbin/distnoted agent 16588800
## 5 /System/Library/CoreServices/ControlStrip.app/Contents/MacOS/TouchBarAgent 3379200
## 6 /usr/libexec/lsd 18866176
## 7 /System/Library/Frameworks/CoreTelephony.framework/Support/CommCenter -L 7561216
## 8 /usr/libexec/trustd --agent 13250560
## 9 /usr/libexec/languageassetd --firstLogin 7131136
## 10 /usr/libexec/secinitd 14454784
## # ... with more rows
filter(procs, name %like% '%fire%') %>%
glimpse()## Observations: ??
## Variables: 26
## $ cmdline <chr> "/usr/libexec/ApplicationFirewall/Firewall", "/Applications/FirefoxDeveloperEdition.app/...
## $ cwd <chr> "/", "/"
## $ disk_bytes_read <chr> "8192", "261332992"
## $ disk_bytes_written <chr> "0", "1746137088"
## $ egid <chr> "20", "20"
## $ euid <chr> "501", "501"
## $ gid <chr> "20", "20"
## $ name <chr> "Firewall", "firefox"
## $ nice <chr> "0", "0"
## $ on_disk <chr> "1", "1"
## $ parent <chr> "1", "1"
## $ path <chr> "/usr/libexec/ApplicationFirewall/Firewall", "/Applications/FirefoxDeveloperEdition.app/...
## $ pgroup <chr> "28329", "39010"
## $ pid <chr> "28329", "39010"
## $ resident_size <chr> "32768", "1188757504"
## $ root <chr> "", ""
## $ sgid <chr> "20", "20"
## $ start_time <chr> "1089108", "1216586"
## $ state <chr> "R", "R"
## $ suid <chr> "501", "501"
## $ system_time <chr> "3", "289281"
## $ threads <chr> "2", "65"
## $ total_size <chr> "647168", "1064378368"
## $ uid <chr> "501", "501"
## $ user_time <chr> "6", "768714"
## $ wired_size <chr> "0", "0"
see if any processes have no corresponding disk image
filter(procs, on_disk == 0) %>%
select(name, path, pid)## NULL
## # 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)## NULL
## # Source: lazy query [?? x 4]
## # Database: OsqueryConnection
## # Ordered by: desc(resident_size)
## name pid resident_size uid
## <chr> <chr> <chr> <chr>
## 1 java 35914 1434951680 501
## 2 firefox 39010 1188757504 501
## 3 RStudio 42595 745156608 501
## 4 plugin-container 40130 613330944 501
## 5 plugin-container 39014 499740672 501
## 6 plugin-container 42533 304885760 501
## 7 plugin-container 42666 277454848 501
## 8 plugin-container 45069 268763136 501
## 9 Adobe Desktop Service 642 252526592 501
## 10 Messages 43128 203517952 501
## # ... with more rows
process count for the top 10 most active processes
count(procs, name, sort=TRUE)## NULL
## # Source: lazy query [?? x 2]
## # Database: OsqueryConnection
## # Ordered by: desc(n)
## n name
## <chr> <chr>
## 1 15 MTLCompilerService
## 2 9 mdworker
## 3 6 Dropbox
## 4 6 ssh
## 5 5 plugin-container
## 6 4 com.apple.CommerceKit.TransactionService
## 7 4 distnoted
## 8 4 trustd
## 9 3 ACCFinderSync
## 10 3 AdobeCRDaemon
## # ... with more rows
listen <- tbl(osqdb, "listening_ports")
left_join(procs, listen, by="pid") %>%
filter(port != "") %>%
distinct(name, port, address, pid)## NULL
## # Source: lazy query [?? x 4]
## # Database: OsqueryConnection
## address name pid port
## <chr> <chr> <chr> <chr>
## 1 "" 2BUA8C4S2C.com.agilebits.onepassword4-helper 476 0
## 2 127.0.0.1 2BUA8C4S2C.com.agilebits.onepassword4-helper 476 6258
## 3 ::1 2BUA8C4S2C.com.agilebits.onepassword4-helper 476 6258
## 4 127.0.0.1 2BUA8C4S2C.com.agilebits.onepassword4-helper 476 6263
## 5 ::1 2BUA8C4S2C.com.agilebits.onepassword4-helper 476 6263
## 6 "" Adobe CEF Helper 745 0
## 7 "" Adobe CEF Helper 25701 0
## 8 "" Adobe Desktop Service 642 0
## 9 127.0.0.1 Adobe Desktop Service 642 15292
## 10 "" AdobeIPCBroker 596 0
## # ... with more rows
files <- tbl(osqdb, "file")
filter(files, path == "/etc/hosts") %>%
select(filename, size)## NULL
## # Source: lazy query [?? x 2]
## # Database: OsqueryConnection
## filename size
## <chr> <chr>
## 1 hosts 1037548
tbl(osqdb, "users")## NULL
## # 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 Guest User /Users/Guest 201 201 /bin/bash 201 201 Guest FFFFEEEE-DDDD-C…
## 2 AMaViS Daemon /var/virusmails 83 83 /usr/bin/false 83 83 _amavisd FFFFEEEE-DDDD-C…
## 3 AppleEvents Daemon /var/empty 55 55 /usr/bin/false 55 55 _appleevents FFFFEEEE-DDDD-C…
## 4 applepay Account /var/db/applepay 260 260 /usr/bin/false 260 260 _applepay FFFFEEEE-DDDD-C…
## 5 Application Owner /var/empty 87 87 /usr/bin/false 87 87 _appowner FFFFEEEE-DDDD-C…
## 6 Application Server /var/empty 79 79 /usr/bin/false 79 79 _appserver FFFFEEEE-DDDD-C…
## 7 Apple Remote Desktop /var/empty 67 67 /usr/bin/false 67 67 _ard FFFFEEEE-DDDD-C…
## 8 Asset Cache Service /var/empty 235 235 /usr/bin/false 235 235 _assetcache FFFFEEEE-DDDD-C…
## 9 Astris Services /var/db/astris 245 245 /usr/bin/false 245 245 _astris FFFFEEEE-DDDD-C…
## 10 ATS Server /var/empty 97 97 /usr/bin/false 97 97 _atsserver FFFFEEEE-DDDD-C…
## # ... with more rows
tbl(osqdb, "logged_in_users")## NULL
## # Source: table<logged_in_users> [?? x 6]
## # Database: OsqueryConnection
## host pid time tty type user
## <chr> <chr> <chr> <chr> <chr> <chr>
## 1 "" 111 1526120904 console user bob
## 2 "" 40196 1527301872 ttys001 user bob
## 3 "" 45313 1527364233 ttys002 dead bob
## 4 "" 36182 1527218774 ttys003 dead bob
## 5 "" 45379 1527364355 ttys004 dead bob
## 6 "" 67343 1527010299 ttys005 dead bob
## 7 "" 4386 1527013112 ttys006 dead bob
## 8 "" 56171 1527005765 tty?? dead bob
## 9 "" 16477 1527024957 ttys007 dead bob
## 10 "" 5013 1527013576 ttys008 dead bob
## # ... with more rows
tbl(osqdb, "groups")## NULL
## # Source: table<groups> [?? x 5]
## # Database: OsqueryConnection
## gid gid_signed groupname
## <chr> <chr> <chr>
## 1 83 83 _amavisd
## 2 55 55 _appleevents
## 3 260 260 _applepay
## 4 87 87 _appowner
## 5 81 81 _appserveradm
## 6 79 79 _appserverusr
## 7 33 33 _appstore
## 8 67 67 _ard
## 9 235 235 _assetcache
## 10 245 245 _astris
## # ... with more rows
tbl(osqdb, "homebrew_packages")## NULL
## # Source: table<homebrew_packages> [?? x 3]
## # Database: OsqueryConnection
## name path version
## <chr> <chr> <chr>
## 1 adns /usr/local/Cellar/adns/ 1.5.1
## 2 apache-arrow /usr/local/Cellar/apache-arrow/ 0.9.0
## 3 aria2 /usr/local/Cellar/aria2/ 1.33.1
## 4 asciinema /usr/local/Cellar/asciinema/ 2.0.0_1
## 5 asciinema2gif /usr/local/Cellar/asciinema2gif/ 0.5
## 6 atk /usr/local/Cellar/atk/ 2.28.1_1
## 7 atomicparsley /usr/local/Cellar/atomicparsley/ 0.9.6
## 8 augeas /usr/local/Cellar/augeas/ 1.10.1
## 9 autoconf /usr/local/Cellar/autoconf/ 2.69
## 10 automake /usr/local/Cellar/automake/ 1.16.1
## # ... with more rows
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.