Commands in Use

We introduce awk, join, sed, tail, and tr to prepare data for analysis.

Mike Jones
John Smith
Kathy Jones
Jane Kennedy
Tim Scott

$ awk '$2 == "Jones" {print $0}' awkusers.txt

Mike Jones
Kathy Jones

1,jdoe
2,puser
3,jsmith

0745,file1.txt,1
0830,file4.txt,2
0830,file5.txt,3

$ join -1 3 -2 1 -t, accesstime.txt usernames.txt

1,0745,file1.txt,jdoe
2,0830,file4.txt,puser
3,0830,file5.txt,jsmith

ip,OS
10.0.4.2,Windows 8
10.0.4.35,Ubuntu 16
10.0.4.107,macOS
10.0.4.145,macOS

$ sed 's/10\.0\.4\.35/10.0.4.27/g' ips.txt

ip,OS
10.0.4.2,Windows 8
10.0.4.27,Ubuntu 16
10.0.4.107,macOS
10.0.4.145,macOS

s/<regular expression>/<replace with>/<flags/

$ tail -n 1 somefile.txt

12/30/2017 192.168.10.185 login.html

tr '\\:'  '/|' < infile.txt  > outfile.txt

drive:path\name
c:\Users\Default\file.txt

drive|path/name
c|/Users/Default/file.txt

tr -d '\r' < fileWind.txt  > fileFixed.txt

$ sed -i 's/$/\r/' fileLinux.txt

Processing Delimited Files

Many of the files you will collect and process are likely to contain text, which makes the ability to manipulate text from the command line a critical skill. Text files are often broken into fields using a delimiter such as a space, tab, or comma. One of the more common formats is known as Comma Separated Values (CSV). As the name indicates, CSV files are delimited using commas, and fields may or may not be surrounded in double quotes ("). The first line of a CSV file is often the field headers. Here is an example:

"name","username","phone","password hash"
"John Smith","jsmith","555-555-1212",5f4dcc3b5aa765d61d8327deb882cf99
"Jane Smith","jnsmith","555-555-1234",e10adc3949ba59abbe56e057f20f883e
"Bill Jones","bjones","555-555-6789",d8578edf8458ce06fbc5bb76a58c5ca4

To extract just the name from the file you can use cut by specifying the field delimiter as a comma and the field number you would like returned.

$ cut -d',' -f1 csvex.txt

"name"
"John Smith"
"Jane Smith"
"Bill Jones"

Note that the field values are still enclosed in double quotations. This may not be desirable for certain applications. To remove the quotations you can simply pipe the output into tr with its -d option.

$ cut -d',' -f1 csvex.txt | tr -d '"'

name
John Smith
Jane Smith
Bill Jones

You can further process the data by removing the field header using the tail command’s -n option.

$ cut -d',' -f1 csvex.txt | tr -d '"' | tail -n +2

John Smith
Jane Smith
Bill Jones

The -n +2 option tells tail to output the contents of the file starting at line number 2, thus removing the field header.

"name","username","phone","password hash"
"John Smith","jsmith","555-555-1212",5f4dcc3b5aa765d61d8327deb882cf99
"Jane Smith","jnsmith","555-555-1234",e10adc3949ba59abbe56e057f20f883e
"Bill Jones","bjones","555-555-6789",d8578edf8458ce06fbc5bb76a58c5ca4

password,md5hash
123456,e10adc3949ba59abbe56e057f20f883e
password,5f4dcc3b5aa765d61d8327deb882cf99
welcome,40be4e59b9a2a2b5dffb918c0e86b3d7
ninja,3899dcbab79f92af727c2190bbd8abc5
abc123,e99a18c428cb38d5f260853678922e03
123456789,25f9e794323b453885f5181f1b624d0b
12345678,25d55ad283aa400af464c76d713c07ad
sunshine,0571749e2ac330a7455809c6b0e7af90
princess,8afa847f50a716e64932d995c8e7435a
qwerty,d8578edf8458ce06fbc5bb76a58c5c

$ awk -F "," '{print $4}' csvex.txt

"password hash"
5f4dcc3b5aa765d61d8327deb882cf99
e10adc3949ba59abbe56e057f20f883e
d8578edf8458ce06fbc5bb76a58c5ca4

$ grep "$(awk -F "," '{print $4}' csvex.txt)" passwords.txt

123456,e10adc3949ba59abbe56e057f20f883e
password,5f4dcc3b5aa765d61d8327deb882cf99
qwerty,d8578edf8458ce06fbc5bb76a58c5ca4

$ cut -d',' -f3 csvex.txt | cut -c2-13 | tail -n +2

555-555-1212
555-555-1234
555-555-6789

Processing XML

Extensible Markup Language (XML) allows you to arbitrarily create tags and elements that describe data. Below is an example XML document.

<book title="Rapid Cybersecurity Ops" edition="1">
  <author>
    <firstName>Paul</firstName>
    <lastName>Troncone</lastName>
  </author>
  <author>
    <firstName>Carl</firstName>
    <lastName>Albing</lastName>
  </author>
</book>

This is a start tag that contains two attributes, also known as name/value pairs. Attribute values must always be quoted.

This is a start tag.

This is an element that has content.

This is an end tag.

For useful processing, you must be able to search through the XML and extract data from within the tags, which can be done using grep. Lets find all of the firstName elements. The -o option is used so only the text that matches the regex pattern will be returned, rather than the entire line.

$ grep -o '<firstName>.*<\/firstName>' book.xml

<firstName>Paul</firstName>
<firstName>Carl</firstName>

Note that the regex pattern above will only find the XML element if the start and end tags are on the same line. To find the pattern across multiple lines you need to make use of two special features. First, add the -z option to grep, which treats newlines like any ordinary character in its searching and adds a null (ASCII 0) at the end of each string it finds. Then add the -P option and (?s) to the regex pattern, which is a Perl-specific pattern match modifier. It modifies the . metacharacter to also match on the newline character.

$ grep -Pzo '(?s)<author>.*?<\/author>' book.xml

<author>
  <firstName>Paul</firstName>
  <lastName>Troncone</lastName>
</author><author>
  <firstName>Carl</firstName>
  <lastName>Albing</lastName>
</author>

To strip the XML start and end tags and extract the content you can pipe your output into sed.

$ grep -Po '<firstName>.*?<\/firstName>' book.xml | sed 's/<[^>]*>//g'

Paul
Carl

The sed expression can be described as s/expr/other/ to replace (or substitute) some expression (expr) with something else (other). The expression can be just literal characters or a more complex regex. If an expression has no “other” portion, such as s/expr// then it replaces anything that matches the regular expression with nothing, essentially removing it. The regex pattern we use in the above example, namely the <[^>]*> expression, is a little confusing, so lets break it down.

< - The pattern begins with a literal less-than character <

[^>]* - Zero or more (indicated by the asterisk) characters from the set of characters inside the brackets; the first character is a ^ which means “not” any of the remaining characters listed. Here that’s just the solitary greater-than character, so [^>] matches any character that is not >

> - The pattern ends with a literal >

This should match a single XML tag, from its opening less-than to its closing greater-than character, but not more than that.

Processing JSON

JavaScript Object Notation (JSON) is another popular file format, particularly for exchanging data through Application Programming Interfaces (APIs). JSON is a simple format that consists of objects, arrays, and name/value pairs. Here is a sample JSON file:

{
  "title": "Rapid Cybersecurity Ops",
  "edition": 1,
  "authors": [
    {
      "firstName": "Paul",
      "lastName": "Troncone"
    },
    {
      "firstName": "Carl",
      "lastName": "Albing"
    }
  ]
}

This is an object. Objects begin with { and end with }.

This is a name/value pair. Values can be a string, number, array, boolean, or null.

This is an array. Arrays begin with [ and end with ].

When processing JSON you are likely going to want to extract key/value pairs. To do that you can use grep. Lets extract the firstName key/value pair from book.json.

$ grep -o '"firstName": ".*"' book.json

"firstName": "Paul"
"firstName": "Carl"

Again, the -o option is used to return only the characters that match the pattern rather than the entire line of the file.

If you want to remove the key and only display the value you can do so by piping the output into cut, extracting the second field, and removing the quotations with tr.

$ grep -o '"firstName": ".*"' book.json | cut -d " " -f2 | tr -d '\"'

Paul
Carl

We will perform more advanced processing of JSON in a later chapter.

Aggregating Data

Data is often collected from a variety of sources, and in a variety of files and formats. Before you can analyze the data you must get it all into the same place and in a format that is conducive to analysis.

Suppose you want to search a treasure trove of data files for any system named ProductionWebServer. Recall that in previous scripts we wrapped our collected data in XML tags with the following format: '<systeminfo host="">. During collection we also named our files using the host name. You can now use either of those attributes to find and aggregate the data into a single location.

find /data -type f -exec grep '{}' -e 'ProductionWebServer' \;
-exec cat '{}' >> ProductionWebServerAgg.txt \;

The command find /data -type f lists all of the files in the /data directory and its subdirectories. For each file found, it runs grep looking for the string ProductionWebServer. If found, the file is appended (>>) to the file ProductionWebServerAgg.txt. Replace the cat command with cp and a directory location if you would rather copy all of the files to a single location rather than to a single file.

You can also use the join command to take data that is spread across two files and aggregate it into one. Take the two files seen in Example 5-10 and Example 5-11.

ip,OS
10.0.4.2,Windows 8
10.0.4.35,Ubuntu 16
10.0.4.107,macOS
10.0.4.145,macOS

user,ip
jdoe,10.0.4.2
jsmith,10.0.4.35
msmith,10.0.4.107
tjones,10.0.4.145

The files share a common column of data, which is the IP addresses. Because of that the files can be merged using join.

$ join -t, -2 2 ips.txt user.txt

ip,OS,user
10.0.4.2,Windows 8,jdoe
10.0.4.35,Ubuntu 16,jsmith
10.0.4.107,macOS,msmith
10.0.4.145,macOS,tjones

The -t, option tells join that the columns are delimited using a comma, by default it uses a space character.

The -2 2 option tells join to use the second column of data in the second file (user.txt) as the key to perform the merge. By default join uses the first field as the key, which is appropriate for the first file (ips.txt). If you needed to join using a different field in ips.txt you would just add the option -1 n where n is replaced by the appropriate column number.

Summary

In this chapter we explored ways to process common data formats including delimited, positional, JSON, and XML. The vast majority of data you collect and process will be in one of those formats.

In the next chapter we will look at how data can be analyzed and transformed into information that will provide insights into system status and drive decision making.

Exercises

1. Given the file tasks.txt below, use the cut command to extract columns 1 (Image Name), 2 (PID), and 5 (Mem Usage).

   Image Name;PID;Session Name;Session#;Mem Usage
   System Idle Process;0;Services;0;4 K
   System;4;Services;0;2,140 K
   smss.exe;340;Services;0;1,060 K
   csrss.exe;528;Services;0;4,756 K

2. Given the file procowner.txt below, use the join command to merge the file with tasks.txt.

   Process Owner;PID
   jdoe;0
   tjones;4
   jsmith;340
   msmith;528

3. Use the tr command to replace all of the semicolon characters in tasks.txt with the tab character and print it to the screen.

4. Write a command that extracts the first and last names of all of the authors in book.json.