compare mysql datetime timestamp days ago

Posted on by
Reply

To get all entries in a table with a “datetime” field that is newer than 3 days ago (i.e. maybe a user last logged in in the last 72 hours):

mysql> select id,timestamp_field from sometable where timestamp_field > ( CURDATE() - INTERVAL 3 DAY );

Note that you could just use “where like” if you were looking for just today, or just yesterday, or just some month. In this case “newer than 3 days” means “since right now minus 72 hours”

There’s a multitude of ways to compare timestamps from a point in time (i.e. now()).

Here’s older than 3 months:

 timestamp_field < ( CURDATE() - INTERVAL 3 MONTH )

count new connections per minute to a tcp port

Posted on by
Reply

I was running a custom FTP service out of inetd, when it intermittently stopped responding to requests (Connection refused.) In the logs inetd was logging:

Mar 23 06:54:36 fordodone inetd[1510]: ftp/tcp server failing (looping), service terminated for 10 min

After some searching I discovered this error happens when there are too many connections to an inetd service per minute. How many is too many? From the man page for inetd.conf we can see that the default is 256. So the aggregate number of opening connections was over 256 per minute and inetd stops responding for 10 minutes to protect itself and the system from running out of resources. I increased the default to 512 (debian system) and restarted inetd for now.

# echo 'OPTIONS="-R"' >> /etc/default/openbsd-inetd  && service openbsd-inetd restart

How close am I to the 256 default? How often would it happen? Is there a pattern? Could this be legit traffic or a DoS attack? I wrote this one liner to see new or opening connections to the ftp control port per minute. You could change it a little for other services.

# tcpdump -lni eth1 "tcp[13] & 2 != 0" and dst port 21 2>/dev/null | while read i ; do j=`echo $i | cut -d : -f -2`; if [ "$k" == "$j" ]; then l=$(($l+1)); else echo "$k -- $l"; k=$j; l=1; fi; done;

Start with tcpdump on the interface you want to listen(-i eth1), no need to resolve hostnames(-n), or buffer output(-l), and look at the TCP flags byte (tcp[13]) (13th byte) for the SYN bit (2) to see if it is set, and only if the destination port is 21. Pipe it to a while loop and read in the lines as they come. Note the hour:minute, and count packets for that minute. If the minute changes, output the last minutes count, and reset the counter.

You have to ignore the first 2 lines. The first one means nothing, and the second one is missing the portion of the minute that was before you started it. The real results start to roll in on iteration 3.

 --
17:26 -- 6
17:27 -- 21
17:28 -- 20
17:29 -- 34
17:30 -- 38
17:31 -- 27
17:32 -- 37
17:33 -- 22
17:34 -- 23
17:35 -- 33
17:36 -- 29
17:37 -- 23
17:38 -- 28
17:39 -- 26
17:40 -- 73
17:41 -- 99
17:42 -- 132
17:43 -- 110
17:44 -- 130
17:45 -- 112
17:46 -- 109
17:47 -- 104
17:48 -- 182
17:49 -- 155
17:50 -- 145
17:51 -- 110
17:52 -- 154
17:53 -- 147
17:54 -- 86
17:55 -- 39
17:56 -- 39
17:57 -- 30
17:58 -- 30
17:59 -- 38

monitor NetApp SnapMirror transfer speed

Posted on by
Reply

You may want to monitor the speed of a current snapmirror to get an idea of how fast the transfer is going. The speed might change throughout the day due to load, or disk bottleneck, etc. I started with this one-liner:

i=0;j=0; while true; do j=$i; i=`ssh toaster01 "snapmirror status -l volname" | grep Progress | awk '{print $2}'| cut -d \( -f2`; if [ $j -eq 0 ]; then sleep 1m; continue; fi; echo -n "$i-$j = ";echo "scale=2;($i-$j)/(1024*1024)" | bc | tr '\n' ' '; echo "GB/min"; sleep 1m; done;

Which lead to this short script:

#!/bin/bash
# FILE: netapp_sm_monitor.sh
# AUTHOR: For Do Done <fordodone@fordodone.com>
# DATE: 2014/03/26
# NOTES: 
# 

if [ $# -lt 2 ]
then
  echo ""
  echo "usage: netapp_sm_monitor.sh <filer> <srcvol> [-v]"
  echo ""
  exit
fi

i=0;
j=0; 

while true; 
do 
  j=$i; 
  i=`ssh $1 "snapmirror status -l $2" | grep Progress | awk '{print $2}'| cut -d \( -f2`; 
  if [ $j -eq 0 ]; 
    then 
    sleep 1m; 
    continue; 
  fi; 
  if [ "$3" == "-v" ]
  then
    echo -n "$i-$j = ";
  fi
  echo "scale=2;($i-$j)/(1024*1024)" | bc | tr '\n' ' '; echo "GB/min"; 
  sleep 1m; 
done;

recursively copy directory with smbclient

Posted on by
Reply

When using smbclient to copy a directory, make sure to use the recurse and prompt commands. This makes it possible to non-interactively copy a directory and all of its contents:

smbclient -Udomainname/fordodone //10.234.92.21/sharename
Password:
Domain=[DOMAINNAME] OS=[Windows 5.0] Server=[Windows 2000 LAN Manager]
smb: \> cd testdir
smb: \testdir\> get C
NT_STATUS_FILE_IS_A_DIRECTORY opening remote file \testdir\C
smb: \testdir\> prompt
smb: \testdir\> recurse
smb: \testdir\> mget C
getting file ...