README.Rmd

---
output: rmarkdown::github_document
editor_options: 
  chunk_output_type: console
---

# 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)

- <strike>finish DBI driver</strike>
- smart(er) type conversion
- tests
- vignette(s)

## Installation

```{r eval=FALSE}
devtools::install_git("git://gitlab.com/hrbrmstr/osqueryr")
devtools::install_git("git://github.com/hrbrmstr/osqueryr")
```

```{r message=FALSE, warning=FALSE, error=FALSE, include=FALSE}
options(width=120)
```

## Usage

```{r message=FALSE, warning=FALSE, error=FALSE}
library(osqueryr)
library(tidyverse)
library(knitr)

# current verison
packageVersion("osqueryr")

```

### osquery info

```{r}
osqdb <- src_dbi(osqueryr::dbConnect(Osquery()))

glimpse(tbl(osqdb, "osquery_info"))
```

This can work with remote hosts, too:

```{r cache=TRUE}
con <- osqueryr::dbConnect(Osquery())
con

local_db <- src_dbi(con)
local_db

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

osq1_db <- src_dbi(osq1_con)
osq1_db

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

osq2_db <- src_dbi(osq2_con)
osq2_db
```

### available tables

```{r}
osqdb
```

### sample table

```{r}
tbl(osqdb, "dns_resolvers")
```

### check out processes

```{r}
procs <- tbl(osqdb, "processes")

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

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

see if any processes have no corresponding disk image

```{r}
filter(procs, on_disk == 0) %>% 
  select(name, path, pid)
```

(gosh I hope ^^ was empty)

top 10 largest processes by resident memory size

```{r}
arrange(procs, desc(resident_size)) %>% 
  select(pid, name, uid, resident_size)
```

process count for the top 10 most active processes

```{r}
count(procs, name, sort=TRUE)
```

### get all processes listening on a port (join example)

```{r}
listen <- tbl(osqdb, "listening_ports")

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

### get file info

```{r}
files <- tbl(osqdb, "file")

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

### users

```{r}
tbl(osqdb, "users")

tbl(osqdb, "logged_in_users")
```

### groups

```{r}
tbl(osqdb, "groups")
```

### homebrew!

```{r}
tbl(osqdb, "homebrew_packages")
```

## Code of Conduct

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