Loading Linux command line logs in to Windows SQL Server

We recently decided, for auditing reasons, that we would like to keep a record of all of the commands that were run on our linux machines and because of separation of duties, we would load those logs in to a SQL Server instance on Windows. Funny how I feel the need to avoid confusion and make sure I say were the SQL Server instance is.

1. First, to be able to get the files I needed to be able to ssh to the linux servers. The easiest way to do this was using Putty

I have separated out the two scripts. First one being the script to download the logs to Windows server and the second script is to load logs to SQL Server

2. Download logs to Windows Server

Since I am connecting to numerous linux servers, I am using a file to store the user names, passwords and server names and will iterate through each line in the file to connect and get the logs.

The files manager.txt and others.txt only contain the password used by that user.

Example server name file

 3. Script to get server names, user names and passwords

 

4. Create SQL Server table with the following columns – filename, command, workingDirectory, server, username and date

5. Load logs in to SQL Server

Example log file from Linux – As you can see, all of the commands came in as comma separated, but they were all on one line.

 

Using the Get-Content command changed the file to a usable format, we used the replace command to remove the space and the command number and we used Set-Content command to save this as a txt file

 

6. SQL Server table

 

 

 

Determine what Linux Version

4 different commands that you can run to see information about your Linux distro.

[oracle@server ~]$ cat /etc/issue
Oracle Linux Server release 6.8

[oracle@server ~]$ cat /etc/*release
LSB_VERSION=base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
Oracle Linux Server release 6.8
NAME=”Oracle Linux Server”
VERSION=”6.8″
ID=”ol”
VERSION_ID=”6.8″
PRETTY_NAME=”Oracle Linux Server 6.8″
ANSI_COLOR=”0;31″
CPE_NAME=”cpe:/o:oracle:linux:6:8:server”
HOME_URL=”https://linux.oracle.com/”
BUG_REPORT_URL=”https://bugzilla.oracle.com/”

ORACLE_BUGZILLA_PRODUCT=”Oracle Linux 6″
ORACLE_BUGZILLA_PRODUCT_VERSION=6.8
ORACLE_SUPPORT_PRODUCT=”Oracle Linux”
ORACLE_SUPPORT_PRODUCT_VERSION=6.8
Red Hat Enterprise Linux Server release 6.8 (Santiago)
Oracle Linux Server release 6.8

Kernel Version commands
[oracle@server ~]$ uname -r
4.1.12-103.9.6.el6uek.x86_64

**Whether you are using 32 bit or 64 bit version… the x86_64 means a 64 bit kernel

[oracle@server ~]$ uname -a
Linux server 4.1.12-103.9.6.el6uek.x86_64 #2 SMP Wed Nov 15 18:03:27 PST 2017 x86_64 x86_64 x86_64 GNU/Linux

2018’s list of goals

Many items peak my interest and that can sometimes make it difficult to choose a few that I would like to gain a deeper learning of, but I think my current role will guide me in 2018.

In no particular order:

SQL Server on Linux: This is a no brainer. As long as I can remember, I have preferred to work with command line over gui based tools. I’ve always felt like I have more control over what I am doing. And now that SQL Server is on Linux, I want to dig in and learn whatever I can. I think I also like the idea, that there aren’t a lot of SQL Server DBA’s that are familiar with Linux.

Cloud: I’ve played around in the cloud for years, but with little reason. Between Azure options and the Oracle Cloud, it’s time to start looking at the differences and see where each can help me.

Power BI: With Power BI Premium, I think this is a game changer for Corporate adoption.

Make it to PASS Summit: I’ve been to Oracle Open World 3 times, but do to job responsibilities or other co-workers training needs, I have unfortunately never made it to PASS. I don’t mind Open World, but more and more I can see PASS being more beneficial.