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

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *