Pivot Tables

Today I think we have everything ready to start pulling all the pieces together to start showing how to use the audit normalizer to create data that can be analyzed more easily.

The first thing we need to do is make sure we have the libcurl-devel rpm package installed. There are several libraries that R has that depends on the curl libary to retrieve web pages and things. Building packages will fail if it can't find the system header file. Go ahead and install it if its not on your system.

Once that is done, start up RStudio.

R Libraries
R has a master library repository called CRAN (Comprehensive R Archive Network). We need to install a few libraries so that we can use them for our programs. The libraries can be easily installed from the R console. In RStudio the R console is the one that has text similar to the following:

R version 3.3.2 (2016-10-31) -- "Sincere Pumpkin Patch"
Copyright (C) 2016 The R Foundation for Statistical Computing
Platform: x86_64-redhat-linux-gnu (64-bit)

In this window, type:

install.packages("rpivotTable")

It will then download and compile the library and all its dependencies. It will install the resulting libraries to ~/R/x86_64-redhat-linux-gnu-library/3.3/ under their own subdirectory. If you have trouble building libraries, the first thing to check is your mount options. If you are security conscience, then you probably have /tmp mounted as noexec. Not all packages compile in /tmp so you may install a couple packages and then hit this problem.

Let's go ahead and install a few more. We won't use them all today, but we will be using them in future blogs.

install.packages("plyr")
install.packages("dplyr")
install.packages("ggplot2")
install.packages("data.table")
install.packages("data.tree")
install.packages("networkD3")
install.packages("maps")
install.packages("plotly")

Gathering audit data
Ok. Now that we have some libraries installed, we need the data to analyze. Open a shell and do the following:

$ cd ~/R/audit-data
$ ausearch --start today --format csv > audit.csv

If for some reason ausearch fails because you don't have permissions to read the logs, you have two options. You can su/sudo to root and do this. But that means you also have to copy the file to your unprivileged home dir and change permissions on it. Or you can use set a value for log_group in auditd.conf as suggested in a previous post. You have to stop/start auditd to have it fix directory permissions.

Pivot Table Program
I want to start with a very simple and powerful program to whet your appetite for the kinds of things R can do. In RStudio, Click on File and New Project menu item. Click Empty Project.

Then Click New Directory.

For Directory name choose "audit".

Then click Create Project. Once this completes, let's create our script. Click on New File menu item, then select R script.

For our first program, type in the following (or copy and paste):

library(rpivotTable)
audit <- read.csv("~/R/audit-data/audit.csv", header=TRUE)
rpivotTable(audit)

That's all of it. Three lines. You can single step the program by clicking on the Run icon at the top of the program file. You can also run the whole program by highlighting it and clicking the Run icon. Either way you should wind up with a pivot table loaded with your audit data in the viewer window on the lower right side of RStudio.

Click on the Zoom icon and you will get a detatched window that is bigger.

The way that the pivot table works is you can drag and drop the buttons in gray to the areas in blue. This creates a matrix of information that you can view. If you click on the buttons, you can make them selective about their information.

Let's check for failed logins. Grab "action" and drop it in the blue column, grab "subj_prime" and drag it underneath "action".

Then grab "result" and drag it to the blue row above the matrix.

Click on "action" so that it opens the filter dialog. Click on the "Select None" button to clear it.

and then scroll down and click on the check box for "logged-in".

Then open the "result" filter dialog and select only "failed". My screen shot below shows success because I don't have any failed logins. But I'm sure you get the idea.

If you don't like to do all the dragging and clicking, you can do this programmatically. The following code will show failed file access:

rpivotTable(audit,
            cols = c("RESULT"),
            rows = c("ACTION", "OBJ_PRIME"),
            inclusions = list(ACTION = list("opened-file"),
            RESULT = list("failed")),
)

This should give you some things to ponder. R really does make programming easy to do complex things. Play around with the pivot table. You can't really hurt anything. The next blog will go into detail about what the fields in the CSV file are so that things make more sense as we start doing other visualizations.