⁇ 'osquery' 'DBI' and 'dbplyr' Interface for R
‘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 of dbplyr tibblesosq_load_tables: Return all (or selected) local or remote osquery tables as a named list of dbplyr tibbles
devtools::install_git("git://gitlab.com/hrbrmstr/osqueryr")devtools::install_git("git://github.com/hrbrmstr/osqueryr")
library(osqueryr)library(tidyverse)library(knitr)# current verisonpackageVersion("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.