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
- Download Putty: Windows Installer version https://www.chiark.greenend.org.uk/~sgtatham/putty/latest.html
- Run the Installer
- This will install the file pscp.exe which will be used to connect to Linux
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
