I recently had been asked, if it would be possible to export syslog files and the TCP connection table into Microsoft Excel.
Exporting Syslog to Microsoft Excel
Exporting the syslog file is quite simple: It’s just a tiny bash script:
rm /var/log/output.csv
while read -r month day time servity ip date timezone hostname ppe spacer msg;
do printf "%s;" "$month $day $time" "$servity" "$ip" "$date $timezone" "$hostname" "$ppe" "$msg" $'\n' >>/var/log/output.csv
done < /var/log/ns.log;
This script goes through /var/log/ns.log and creates a CSV output file /var/log/output.csv.
Of course, you could improve this script by skipping undesired events, but it does its job.
the first line cleans up data from previous attempts, as we just append data to the output file.
Next, we open /var/log/ns.log and process it, line by line. We split it up (delimiters are spaces) and write data into variables. These variables are written into fields, seperated by ; ("%s;") Any other delimiter would be possible as well. Every group of variables in quotation marks will be a field in the CSW file. $'\n' creates a line-break. >>/var/log/output.csv will redirect the output into a file.
Exporting the TCP connection table to Microsoft Excel
This is way more tricky, as the TCP connection table is not available from BSD, but from NetScaler OS. So we have to do a call to NetScaler OS using the nscli command (I have written about it in detail).
There are two scripts, one executing the NetScaler command, and one processing the output.
Fetching data from NetScaler OS
nscli -U 127.0.0.1:nsroot:Password1 show connectiontable >/var/tmp/2delete.tmp
This script connects to the NetScaler OS on 127.0.0.1.
-U the host:user-name:password of a user with sufficient permissions. The built in command policy read-only is more than enough, so this user can’t do and harm.
>/var/tmp/xxx.rep redirects the output into a file called 2delete.tmp
processing the extracted data
rm /var/log/output.csv
while read -r sourceIP sourcePort destIP destPort protocol service idletime state trafficDomain; do
printf "%s;" "$sourceIP:$sourcePort" "$destIP:$destPort" "$protocol" "$service" "$idletime" "$state" "$trafficDomain" $'\n'>>/var/log/output.csv
done < /var/tmp/2delete.tmp
The first line will clean up remains from previous attempts (we are only appending data to the output file)
The next lines are similar to the export of syslog.
