Export Citrix ADC (NetScaler) Syslog and TCP connection tables into Microsoft Excel

E

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.

About the author

Johannes Norz

Johannes Norz is a Citrix Certified Citrix Technology Advocate (CTA), Citrix Certified Instructor (CCI) and Citrix Certified Expert on Application Delivery and Security.

He frequently works for Citrix international Consulting Services and several education centres all around the globe.

Johannes lives in Austria. He had been borne in Innsbruch (https://www.youtube.com/watch?v=UvdF145Lf2I)

Add comment

By Johannes Norz

Recent Posts

Recent Comments