SAR graphing/plotting into Excel
Posted 10-02-2017 at 07:39 AM by loadedmind
First, I'll cite my sources that helped make this easier for me:
https://pzolee.blogs.balabit.com/201...om-sar-output/
http://sebastien.godard.pagesperso-orange.fr/faq.html
https://www.thomas-krenn.com/en/wiki...ation_with_sar
http://sebastien.godard.pagesperso-o.../tutorial.html
http://unixdiary101.blogspot.com/201...-which-is.html
http://www.thegeekstuff.com/2011/03/sar-examples/
https://www.youtube.com/watch?v=Xn7Sd5Uu42A&t=209s - how to make a line graph in Excel
Notes:
I've told sysstat to log the max history which is 30 days. Then, I use the following command to provide output of said disk I/O from the first day:
Code:
sar -b -f /var/log/sa/sa01
From here, I use sadf to output to Excel-friendly format:
Code:
sadf -d /var/log/sa/sa01 -- -b | sed 's/;/,/g' > storage01_diskio_1-3-17.csv
For the curious, here's what the output looks like, raw:
Code:
host1,599,2017-01-01 06:10:01 UTC,1.39,0.00,1.39,0.00,26.92
In Excel, I import the data to graph by copying/pasting the output from the previous command. From here, I click the Data ribbon and choose "Text to Columns".
From the new window that appears, I choose the "Delimited" radio button and click Next
I choose the "Comma" checkbox and click Next
"Column Data Format" should be set to General so I click Finish
I now insert a new line above it all for the following headers:
Hostname | Interval | Time | TPS | Reads/sec | Writes/sec | bread | bwrit
While still in Excel, I click the Charts ribbon, highlight all data and choose Scatter->Smooth Marked Scatter.
Lastly, I choose Layout 1 from "Chart Quick Layouts"
https://pzolee.blogs.balabit.com/201...om-sar-output/
http://sebastien.godard.pagesperso-orange.fr/faq.html
https://www.thomas-krenn.com/en/wiki...ation_with_sar
http://sebastien.godard.pagesperso-o.../tutorial.html
http://unixdiary101.blogspot.com/201...-which-is.html
http://www.thegeekstuff.com/2011/03/sar-examples/
https://www.youtube.com/watch?v=Xn7Sd5Uu42A&t=209s - how to make a line graph in Excel
Notes:
I've told sysstat to log the max history which is 30 days. Then, I use the following command to provide output of said disk I/O from the first day:
Code:
sar -b -f /var/log/sa/sa01
From here, I use sadf to output to Excel-friendly format:
Code:
sadf -d /var/log/sa/sa01 -- -b | sed 's/;/,/g' > storage01_diskio_1-3-17.csv
For the curious, here's what the output looks like, raw:
Code:
host1,599,2017-01-01 06:10:01 UTC,1.39,0.00,1.39,0.00,26.92
In Excel, I import the data to graph by copying/pasting the output from the previous command. From here, I click the Data ribbon and choose "Text to Columns".
From the new window that appears, I choose the "Delimited" radio button and click Next
I choose the "Comma" checkbox and click Next
"Column Data Format" should be set to General so I click Finish
I now insert a new line above it all for the following headers:
Hostname | Interval | Time | TPS | Reads/sec | Writes/sec | bread | bwrit
While still in Excel, I click the Charts ribbon, highlight all data and choose Scatter->Smooth Marked Scatter.
Lastly, I choose Layout 1 from "Chart Quick Layouts"
Total Comments 0