MySql – Group By Dates

GROUP BY DATE

mysql> SELECT host, SUM(tx_diff) as tx_diff, adapter, ip, SUM(rx_diff) as rx_diff, date_format(date,’%b %D’) as dow FROM log where ip=’10.200.1.11′ group by date(date) order by date desc limit 10;
+—————————+————–+———+————-+————–+———-+
| host | tx_diff | adapter | ip | rx_diff | dow |
+—————————+————–+———+————-+————–+———-+
| monk.micro.washington.edu | 565109044386 | eth1 | 10.200.1.11 | 616332195564 | Sep 22nd |
| monk.micro.washington.edu | 215287966 | eth1 | 10.200.1.11 | 27642389383 | Sep 21st |
| monk.micro.washington.edu | 210201744 | eth1 | 10.200.1.11 | 303483939 | Sep 20th |
| monk.micro.washington.edu | 168338833 | eth1 | 10.200.1.11 | 239533210 | Sep 19th |
| monk.micro.washington.edu | 283795426 | eth1 | 10.200.1.11 | 226393293 | Sep 18th |
| monk.micro.washington.edu | 167879344 | eth1 | 10.200.1.11 | 221869928 | Sep 17th |
| monk.micro.washington.edu | 343850040 | eth1 | 10.200.1.11 | 276676860 | Sep 16th |
| monk.micro.washington.edu | 208617927 | eth1 | 10.200.1.11 | 173938951 | Sep 15th |
| monk.micro.washington.edu | 317222382 | eth1 | 10.200.1.11 | 162031338 | Sep 14th |
| monk.micro.washington.edu | 209365672 | eth1 | 10.200.1.11 | 228773179 | Sep 13th |
+—————————+————–+———+————-+————–+———-+
10 rows in set (1.12 sec)

GROUP BY MONTH

mysql> SELECT host, SUM(tx_diff) as tx_diff, adapter, ip, SUM(rx_diff) as rx_diff, date_format(date,’%M’) as month_year, date(date) as date FROM log where ip=’10.200.1.11′ group by date_format(date,’%Y-%c’) order by date desc limit 10;
+—————————+————–+———+————-+————–+————+————+
| host | tx_diff | adapter | ip | rx_diff | month_year | date |
+—————————+————–+———+————-+————–+————+————+
| monk.micro.washington.edu | 565582903163 | eth1 | 10.200.1.11 | 644158532495 | September | 2011-09-01 |
+—————————+————–+———+————-+————–+————+————+
1 row in set (1.15 sec)

GROUP BY WEEK

mysql> SELECT host, SUM(tx_diff) as tx_diff, adapter, ip, SUM(rx_diff) as rx_diff, date_format(date,’%W’) as dow FROM log where ip=’10.200.1.11′ group by date_format(date,’%W’) order by date desc limit 10;
+—————————+————–+———+————-+————–+———–+
| host | tx_diff | adapter | ip | rx_diff | dow |
+—————————+————–+———+————-+————–+———–+
| monk.micro.washington.edu | 1001045977 | eth1 | 10.200.1.11 | 28207975939 | Wednesday |
| monk.micro.washington.edu | 692902253 | eth1 | 10.200.1.11 | 707541136 | Tuesday |
| monk.micro.washington.edu | 521261648 | eth1 | 10.200.1.11 | 708563349 | Monday |
| monk.micro.washington.edu | 926105476 | eth1 | 10.200.1.11 | 700926063 | Sunday |
| monk.micro.washington.edu | 537660737 | eth1 | 10.200.1.11 | 677285321 | Saturday |
| monk.micro.washington.edu | 655542750 | eth1 | 10.200.1.11 | 705438965 | Friday |
| monk.micro.washington.edu | 572138807078 | eth1 | 10.200.1.11 | 623308403828 | Thursday |
+—————————+————–+———+————-+————–+———–+
7 rows in set (1.38 sec)

RECORDS FOR PAST 24 HOURS

mysql> SELECT host, SUM(tx_diff) as tx_diff, adapter, SUM(rx_diff) as rx_diff, date_format(date,’%b %D %h %p’) as dow FROM log where ip=’10.200.1.11′ and date between date_sub(now(),interval 24 hour) and now() group by date_format(date,’%H’) order by date desc; +—————————+————–+———+————–+—————-+
| host | tx_diff | adapter | rx_diff | dow |
+—————————+————–+———+————–+—————-+
| monk.micro.washington.edu | 130414938191 | eth1 | 129831708238 | Sep 22nd 03 PM |
| monk.micro.washington.edu | 130057896533 | eth1 | 129215613916 | Sep 22nd 02 PM |
| monk.micro.washington.edu | 129629346428 | eth1 | 128502661829 | Sep 22nd 01 PM |
| monk.micro.washington.edu | 129221533429 | eth1 | 127824340545 | Sep 22nd 12 PM |
| monk.micro.washington.edu | 10885315048 | eth1 | 16021185857 | Sep 22nd 11 AM |
| monk.micro.washington.edu | 7358250 | eth1 | 5630652540 | Sep 22nd 10 AM |
| monk.micro.washington.edu | 7218214 | eth1 | 5387548561 | Sep 22nd 09 AM |
| monk.micro.washington.edu | 5275165 | eth1 | 5158565423 | Sep 22nd 08 AM |
| monk.micro.washington.edu | 5325261 | eth1 | 4938127636 | Sep 22nd 07 AM |
| monk.micro.washington.edu | 5290874 | eth1 | 4730943343 | Sep 22nd 06 AM |
| monk.micro.washington.edu | 5285302 | eth1 | 4527051636 | Sep 22nd 05 AM |
| monk.micro.washington.edu | 6807211 | eth1 | 4326804360 | Sep 22nd 04 AM |
| monk.micro.washington.edu | 5281350 | eth1 | 4197910141 | Sep 22nd 03 AM |
| monk.micro.washington.edu | 5290722 | eth1 | 3983501232 | Sep 22nd 02 AM |
| monk.micro.washington.edu | 5285438 | eth1 | 3771119213 | Sep 22nd 01 AM |
| monk.micro.washington.edu | 5284276 | eth1 | 3562302274 | Sep 22nd 12 AM |
| monk.micro.washington.edu | 11227607 | eth1 | 3392253923 | Sep 21st 11 PM |
| monk.micro.washington.edu | 2600173 | eth1 | 3429676699 | Sep 21st 10 PM |
| monk.micro.washington.edu | 2556612 | eth1 | 3196016672 | Sep 21st 09 PM |
| monk.micro.washington.edu | 2559485 | eth1 | 2967940563 | Sep 21st 08 PM |
| monk.micro.washington.edu | 2674418 | eth1 | 2630574039 | Sep 21st 07 PM |
| monk.micro.washington.edu | 2556207 | eth1 | 2238077434 | Sep 21st 06 PM |
| monk.micro.washington.edu | 2552245 | eth1 | 2002786733 | Sep 21st 05 PM |
| monk.micro.washington.edu | 45729464710 | eth1 | 46747892032 | Sep 21st 04 PM |
+—————————+————–+———+————–+—————-+
24 rows in set (0.02 sec)

Linux Power Saving for File Servers

root@donkey:~# apt-get install cpufrequtils

root@donkey:~# cpufreq-info
cpufrequtils 006: cpufreq-info (C) Dominik Brodowski 2004-2009
Report errors and bugs to cpufreq@vger.kernel.org, please.
analyzing CPU 0:
driver: powernow-k8
CPUs which run at the same hardware frequency: 0
CPUs which need to have their frequency coordinated by software: 0
maximum transition latency: 8.0 us.
hardware limits: 800 MHz – 2.70 GHz
available frequency steps: 2.70 GHz, 1.90 GHz, 1.50 GHz, 800 MHz
available cpufreq governors: conservative, ondemand, userspace, powersave, performance
current policy: frequency should be within 800 MHz and 2.70 GHz.
The governor “ondemand” may decide which speed to use
within this range.
current CPU frequency is 800 MHz (asserted by call to hardware).
cpufreq stats: 2.70 GHz:0.09%, 1.90 GHz:0.00%, 1.50 GHz:0.00%, 800 MHz:99.91% (19590)

root@donkey:~# cpufreq-set -g powersave
root@donkey:~# cpufreq-info
cpufrequtils 006: cpufreq-info (C) Dominik Brodowski 2004-2009
Report errors and bugs to cpufreq@vger.kernel.org, please.
analyzing CPU 0:
driver: powernow-k8
CPUs which run at the same hardware frequency: 0
CPUs which need to have their frequency coordinated by software: 0
maximum transition latency: 8.0 us.
hardware limits: 800 MHz – 2.70 GHz
available frequency steps: 2.70 GHz, 1.90 GHz, 1.50 GHz, 800 MHz
available cpufreq governors: conservative, ondemand, userspace, powersave, performance
current policy: frequency should be within 800 MHz and 2.70 GHz.
The governor “powersave” may decide which speed to use
within this range.
current CPU frequency is 800 MHz (asserted by call to hardware).
cpufreq stats: 2.70 GHz:0.09%, 1.90 GHz:0.00%, 1.50 GHz:0.00%, 800 MHz:99.91% (19592)

for dual cpu, set per processor.

root@backupsrv:~# cpufreq-set -g powersave -c 0
root@backupsrv:~# cpufreq-set -g powersave -c 1

cpufrequtils 006: cpufreq-info (C) Dominik Brodowski 2004-2009
Report errors and bugs to cpufreq@vger.kernel.org, please.
analyzing CPU 0:
driver: powernow-k8
CPUs which run at the same hardware frequency: 0
CPUs which need to have their frequency coordinated by software: 0
maximum transition latency: 8.0 us.
hardware limits: 800 MHz – 2.90 GHz
available frequency steps: 2.90 GHz, 2.20 GHz, 1.70 GHz, 800 MHz
available cpufreq governors: conservative, ondemand, userspace, powersave, performance
current policy: frequency should be within 800 MHz and 2.90 GHz.
The governor “powersave” may decide which speed to use
within this range.
current CPU frequency is 800 MHz (asserted by call to hardware).
cpufreq stats: 2.90 GHz:0.10%, 2.20 GHz:0.00%, 1.70 GHz:0.11%, 800 MHz:99.78% (110341)
analyzing CPU 1:
driver: powernow-k8
CPUs which run at the same hardware frequency: 1
CPUs which need to have their frequency coordinated by software: 1
maximum transition latency: 8.0 us.
hardware limits: 800 MHz – 2.90 GHz
available frequency steps: 2.90 GHz, 2.20 GHz, 1.70 GHz, 800 MHz
available cpufreq governors: conservative, ondemand, userspace, powersave, performance
current policy: frequency should be within 800 MHz and 2.90 GHz.
The governor “powersave” may decide which speed to use
within this range.
current CPU frequency is 800 MHz (asserted by call to hardware).
cpufreq stats: 2.90 GHz:0.01%, 2.20 GHz:0.00%, 1.70 GHz:0.00%, 800 MHz:99.98% (6750)

root@backupsrv:~# apt-get install powertop

Disable Windows Manager

apt-get install rcconf
-disable unneeded services

*disabling x11 saves me 100 watts!

STDERR, STDOUT Script Redirection & Crontab

REDIRECT STDOUT TO NEW FILE

python /etc/cron.d/check_perms.py 1> logfile #create new file or overwrite existing
python /etc/cron.d/check_perms.py 1>> logfile #append to existing

REDIRECT STDOUT TO NEW FILE

python /etc/cron.d/check_perms.py 2> logfile #create new file or overwrite existing
python /etc/cron.d/check_perms.py 2>> logfile #append to existing

CRONTAB REDIRECTION

* * * * * root python somefile.py 1> /dev/null, discards stdout, email stderr if set.
* * * * * root python somefile.py 2> /dev/null, discards stderr, email stdout if set.
* * * * * root python somefile.py 2>&1 , emails both stderr and stdout and combines into one stream.
* * * * * root python somefile.py > /dev/null #sends all output to trash

EMAIL SCRIPT OUPUT FROM CMD LINE

All output…

python ./check_perms.py 2>&1 | mail -s ‘files perms’ jjest@uw.edu

To get output from stderr or stdout you must redirect to file first then to mail address…
*This is the onloy way i can get thish to work!!!

SENDS STDOUT TO EMAIL

$ python stderr.py 2>&1 /var/log/test 2> /dev/null | mail -s ‘yo’ jjest@uw.edu

SENDS STDERR TO EMAIL

$ python stderr.py 2>&1 /var/log/test 1> /dev/null | mail -s ‘yo’ jjest@uw.edu