<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/the-power-of-standards-and-consistency/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://rud.is/b/2018/05/26/the-power-of-standards-and-consistency/"> R – rud.is</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>I’m going to (eventually) write a full post on the package I’m mentioning in this one : <a rel="nofollow" href="https://github.com/hrbrmstr/osqueryr" target="_blank"><code>osqueryr</code><img alt="🔗" src="https://i2.wp.com/s.w.org/images/core/emoji/2.4/72x72/1f517.png?w=456&ssl=1" class="wp-smiley" data-recalc-dims="1"></a>. The TLDR on <code>osqueryr</code> is that it is an R DBI wrapper (that has just enough glue to also be plugged into <code>dbplyr</code>) for <a rel="nofollow" href="https://code.facebook.com/posts/844436395567983/introducing-osquery/" target="_blank"><code>osquery</code><img alt="🔗" src="https://i2.wp.com/s.w.org/images/core/emoji/2.4/72x72/1f517.png?w=456&ssl=1" class="wp-smiley" data-recalc-dims="1"></a>. The TLDR on <code>osquery</code> is that it <em>“exposes an operating system as a high-performance relational database. This design allows you to write SQL-based queries efficiently and easily to explore operating systems.”</em></p>
<p>In short, <code>osquery</code> turns the metadata and state information of your local system (or remote system(s)) into a SQL-compliant database. It also works on Windows, Linux, BSD and macOS. This means you can query a fleet of systems with a (mostly) normalized set of tables and get aggregated results. Operations and information security staff use this to manage systems and perform incident response tasks, but you can use it to get just about anything and there are even more powerful modes of operation for <code>osquery</code>. But, more on all the features of <code>osquery[r]</code> in another post.</p>
<p>If you are skeptical, here’s some proof (which I need to show regardless of your skepticism state). First, a local “connection”:</p>
<pre><code>
library(DBI)
library(osqueryr)

con &lt;- DBI::dbConnect(Osquery())

head(dbListTables(con), 10)
##  [1] &quot;account_policy_data&quot; &quot;acpi_tables&quot;         &quot;ad_config&quot;          
##  [4] &quot;alf&quot;                 &quot;alf_exceptions&quot;      &quot;alf_explicit_auths&quot; 
##  [7] &quot;alf_services&quot;        &quot;app_schemes&quot;         &quot;apps&quot;               
## [10] &quot;apt_sources&quot;

dbListFields(con, &quot;processes&quot;)
##  [1] &quot;cmdline&quot;            &quot;cwd&quot;                &quot;disk_bytes_read&quot;   
##  [4] &quot;disk_bytes_written&quot; &quot;egid&quot;               &quot;euid&quot;              
##  [7] &quot;gid&quot;                &quot;name&quot;               &quot;nice&quot;              
## [10] &quot;on_disk&quot;            &quot;parent&quot;             &quot;path&quot;              
## [13] &quot;pgroup&quot;             &quot;pid&quot;                &quot;resident_size&quot;     
## [16] &quot;root&quot;               &quot;sgid&quot;               &quot;start_time&quot;        
## [19] &quot;state&quot;              &quot;suid&quot;               &quot;system_time&quot;       
## [22] &quot;threads&quot;            &quot;total_size&quot;         &quot;uid&quot;               
## [25] &quot;user_time&quot;          &quot;wired_size&quot;

dbGetQuery(con, &quot;SELECT name, system_time FROM processes WHERE name LIKE '%fire%'&quot;)
## # A tibble: 2 x 2
##   name     system_time
## 1 Firewall 3          
## 2 firefox  517846
</code></pre>
<p>then, a remote &quot;connection&quot;:</p>
<pre><code>
con2 &lt;- osqueryr::dbConnect(Osquery(), host = &quot;hrbrmstr@osq1&quot;)

head(dbListTables(con2), 10)
##  [1] &quot;account_policy_data&quot; &quot;acpi_tables&quot;         &quot;ad_config&quot;          
##  [4] &quot;alf&quot;                 &quot;alf_exceptions&quot;      &quot;alf_explicit_auths&quot; 
##  [7] &quot;alf_services&quot;        &quot;app_schemes&quot;         &quot;apps&quot;               
## [10] &quot;apt_sources&quot;

dbListFields(con2, &quot;processes&quot;)
##  [1] &quot;cmdline&quot;            &quot;cwd&quot;                &quot;disk_bytes_read&quot;   
##  [4] &quot;disk_bytes_written&quot; &quot;egid&quot;               &quot;euid&quot;              
##  [7] &quot;gid&quot;                &quot;name&quot;               &quot;nice&quot;              
## [10] &quot;on_disk&quot;            &quot;parent&quot;             &quot;path&quot;              
## [13] &quot;pgroup&quot;             &quot;pid&quot;                &quot;resident_size&quot;     
## [16] &quot;root&quot;               &quot;sgid&quot;               &quot;start_time&quot;        
## [19] &quot;state&quot;              &quot;suid&quot;               &quot;system_time&quot;       
## [22] &quot;threads&quot;            &quot;total_size&quot;         &quot;uid&quot;               
## [25] &quot;user_time&quot;          &quot;wired_size&quot;

dbGetQuery(con2, &quot;SELECT name, system_time FROM processes WHERE name LIKE '%fire%'&quot;)
## # A tibble: 1 x 2
##   name    system_time
## 1 firefox 1071992
</code></pre>
<h3>&quot;You're talking an <em>awful</em> lot about the package when you said this was a post on 'standards' and 'consistency'.&quot;</h3>
<p>True, but we needed that bit above for context. To explain what this post has to do with &quot;standards&quot; and &quot;consistency&quot; I also need to tell you a bit more about <em>how</em> both <code>osquery</code> and the <code>osqueryr</code> package are implemented.</p>
<p>You can read about <code>osquery</code> in-depth starting at the link at the top of this post, but the authors of the tool <em>really</em> wanted a consistent idiom for accessing system metadata with usable, normalized output. They chose (to use a word they didn't but one that works for an R audience) a &quot;data frame&quot; as the output format and picked the universal language of &quot;data frames&quot; -- SQL -- as the inquiry interface. So, <em>right there</em> are examples of both standards and consistency: using SQL vs coming up with yet-another-query-language and avoiding the chaos of the myriad of outputs from various system commands by making all results conform to a rectangular data structure.</p>
<p>Let's take this one-step further with a specific example. All modern operating systems have the concept of a &quot;process&quot; and said processes have (mostly) similar attributes. However, the commands used to get a detailed listing of those processes differ (sometimes wildly) from OS to OS. The authors of <code>osquery</code> came up with a set of <a rel="nofollow" href="https://osquery.io/schema/3.2.4" target="_blank">schemas</a> to ensure a common, rectangular output and naming conventions (note that some schemas are unique to a particular OS since some elements of operating systems have no useful counterparts on other operating systems).</p>
<p>The <code>osquery</code> authors also took consistency and standards to yet-another-level by taking advantage of a feature of SQLite called <a rel="nofollow" href="https://sqlite.org/vtab.html" target="_blank">virtual tables</a>. That enables them to have C/C++/Objective-C &quot;glue&quot; that gets called when a query is made so they can dispatch the intent to the proper functions or shell commands and then send all the results back -- <strong>or</strong> -- use the SQLite engine capabilities to do joining, filtering, UDF-calling, etc to produce rich, targeted rectangular output back.</p>
<p>By not reinventing the wheel and relying on well-accepted features like data frames, SQL and SQLite the authors could direct all their focus on solving the problem they posited.</p>
<h3>&quot;Um, you're talking alot about everything but R now.&quot;</h3>
<p>We're getting to the good (i.e. &quot;R&quot;) part now.</p>
<p>Because the authors didn't try to become SQL parser writer experts and relied on the standard SQL offerings of SQLite, the queries made are &quot;real&quot; SQL (if you've worked with more than one database engine, you know how they all implement different flavours of SQL).</p>
<p>Because these queries are &quot;real&quot; SQL, we can write an R <a rel="nofollow" href="https://github.com/r-dbi/DBI" target="_blank">DBI</a> driver for it. The <code>DBI</code> package aims <em>&quot;[to define] a common interface between R and database management systems (DBMS). The interface defines a small set of classes and methods similar in spirit to Perl's DBI, Java's JDBC, Python's DB-API, and Microsoft's ODBC. It defines a set of classes and methods defines what operations are possible and how they are performed.&quot;</em></p>
<p>If you look at the <code>osqueryr</code> package source, you'll see a bunch of DBI boilerplate code (which is in the <code>r-dbi</code> <a rel="nofollow" href="https://github.com/r-dbi" target="_blank">organization</a> example code) and only a handful of &quot;touch points&quot; for the actual calls to <code>osqueryi</code> (the command that processes SQL). No handling of anything but passing on SQL to the <code>osqueryi</code> engine and getting rectangular results back. By abstracting the system call details, R users can work with a familiar, consistent, standard interface and have full access to the power of <code>osquery</code> without firing up a terminal.</p>
<p><em>But it gets even better.</em></p>
<p>As noted above, one design aspect of <code>osquery</code> was to enable remote usage. Rather than come up with yet-another-daemon-and-custom-protocol, the <code>osquery</code> authors suggest <a rel="nofollow" href="https://www.openssh.com/" target="_blank"><code>ssh</code><img alt="🔗" src="https://i2.wp.com/s.w.org/images/core/emoji/2.4/72x72/1f517.png?w=456&ssl=1" class="wp-smiley" data-recalc-dims="1"></a> as one way of invoking the command on remote systems and getting the rectangular results back.</p>
<p>Because the <code>osqueryr</code> package used the <a rel="nofollow" href="https://github.com/jeroen/sys" target="_blank"><code>sys</code><img alt="🔗" src="https://i2.wp.com/s.w.org/images/core/emoji/2.4/72x72/1f517.png?w=456&ssl=1" class="wp-smiley" data-recalc-dims="1"></a> package for making local system calls, there was only a tiny bit of extra effort required to switch from <code>sys::exec_internal()</code> to a sibling call in the <a rel="nofollow" href="https://github.com/ropensci/ssh" target="_blank"><code>ssh</code><img alt="🔗" src="https://i2.wp.com/s.w.org/images/core/emoji/2.4/72x72/1f517.png?w=456&ssl=1" class="wp-smiley" data-recalc-dims="1"></a> package -- <code>ssh::ssh_exec_internal()</code> when remote connections were specified. (Said effort could have been zero if I chose a slightly different function in <code>sys</code>, too.)</p>
<p>Relying on well-accepted standards made both <code>osqueryi</code> and the R DBI-driver work seamlessly without much code at all and definitely without a rats nest of nested <code>if</code>/<code>else</code> statements and custom <code>httr</code> functions.</p>
<h3>But it gets <em>even</em> more better-er</h3>
<p>Some folks like &amp; grok SQL, others don't. (Humans have preferences, <em>go figure</em>.)</p>
<p>A few years ago, Hadley (<em>do I even need to use his last name at this point in time?</em>) came up with the idea to have a more expressive and consistent way to work with data frames. We now know this as the <code>tidyverse</code> but one core element of the tidyverse is <code>dplyr</code>, which can really level-up your data frame game (no comments about <code>data.table</code>, or the beauty of base R, please). Not too long after the birth of <code>dplyr</code> came the ability to work with remote, rectangular, SQL-based data sources with (mostly) the same idioms.</p>
<p>And, not too long after that, the remote <code>dplyr</code> interface (now, <code>dbplyr</code>) got up close and personal with <code>DBI</code>. Which ultimately means that if you make a near-fully-compliant DBI interface to a SQL back-end you can now do something like this:</p>
<pre><code>
library(DBI)
library(dplyr)
library(osqueryr)

con &lt;- DBI::dbConnect(Osquery())

osqdb &lt;- src_dbi(con)

procs &lt;- tbl(osqdb, &quot;processes&quot;)
listen &lt;- tbl(osqdb, &quot;listening_ports&quot;)

left_join(procs, listen, by=&quot;pid&quot;) %>%
  filter(port != &quot;&quot;, protocol == &quot;17&quot;) %>% # 17 == TCP
  distinct(name, port, address, pid)
## # Source:   lazy query [?? x 4]
## # Database: OsqueryConnection
##    address name              pid   port 
##  1 0.0.0.0 BetterTouchTool   46317 57183
##  2 0.0.0.0 Dropbox           1214  17500
##  3 0.0.0.0 SystemUIServer    429   0    
##  4 0.0.0.0 SystemUIServer    429   62240
##  5 0.0.0.0 UserEventAgent    336   0    
##  6 0.0.0.0 WiFiAgent         493   0    
##  7 0.0.0.0 WiFiProxy         725   0    
##  8 0.0.0.0 com.docker.vpnkit 732   0    
##  9 0.0.0.0 identityservicesd 354   0    
## 10 0.0.0.0 loginwindow       111   0    
## # ... with more rows
</code></pre>
<p>The <code>src_dbi()</code> call wires up everything for us because <code>d[b]plyr</code> can rely on <code>DBI</code> doing it's standard &amp; consistent job and <code>DBI</code> can rely on the SQLite processing crunchy goodness of <code>osqueryi</code> to ultimately get us a list of really dangerous (if not firewalled off) processes that are listening on all network interfaces. (<em>Note to self: find out why the <code>BetterTouchTool</code> and <code>Dropbox</code> authors feel the need to bind to <code>0.0.0.0</code>…</em>)</p>
<p>FIN</p>
<p>What did standards and consistency get us?</p>
<ul>
<li>The <code>osquery</code> authors spent time solving a hard problem vs creating new data formats and protocols</li>
<li>Rectangular data (i.e. &quot;data frame&quot;) provides consistency and structure which ends up causing more freedom </li>
<li>&quot;Standard&quot; SQL enables a consistent means to work with rectangular data</li>
<li><code>ssh</code> normalizes (secure) access across systems with a consistent protocol</li>
<li>A robust, well-defined standard mechanism for working with SQL databases enabled nigh instantaneous wiring up of a whole new back-end to R</li>
<li><code>ssh</code> and <code>sys</code> common idioms made working with the new back-end on remote systems as easy as is on a local system</li>
<li>Another robust, well-defined modern mechanism for working with rectangular data got wired up to this new back-end with (pretty much) one line of code because of the defined standard and expectation of consistency (and works for local and remote)</li>
</ul>
<p>Standards and consistency are <em>pretty darned cool</em>.</p>
	<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://rud.is/b/2018/05/26/the-power-of-standards-and-consistency/"> R – rud.is</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...
<img width="1" alt="" src="http://feeds.feedburner.com/~r/RBloggers/~4/gEIjve83IJk" height="1"></a></div>