Monday, December 12, 2016

GoogleSheets: Find the number of occurrences in the column

I love 'uniq -c' Linux command because it returns all the unique values along with their occurrences count.
I wanted do the same from Google Sheets. This is how I do it.

Let's say I have a list of fruits in Column A. There are repetitions and I would like to find the number of their occurrences.

Step1: Go to cell C1 and enter the formula '=UNIQUE(A:A)' . It returns all the unique items on column A.


Step2: Go to Cell D1 and enter the header 'Count'. Go to Cell D2 and enter a formula '=COUNTIF(A:A,C2)' .  It counts the number of occurrences of the unique items listed in Column C.


Isn't that easy? 

Tuesday, September 3, 2013

Kill long running MySQL queries automatically using PERL script

Note: Make sure that you have perl-DBD-mysql, perl-DBI and perl installed. If those package are missing, you can use YaST, YUM, ZYPPER, APT-GET to install those missing packages based on your distro.

Step1: Create a perl script that connects to the database and runs a query to show full processlist. Then, it goes through each row of the output from show full processlist and checks if a process is Query and it is running over 300 seconds(5 minutes). If so, kill that query.

/usr/local/bin # less killLongRunningSql.pl
use strict;
use DBI;
use DBD::mysql;

#MySQL connection
my $db_name = "YourDatabase";
my $db_connection = DBI->connect("DBI:mysql:$db_name","username","password") or die "Connection Error: $DBI::errstr\n";

#Execute query that shows the processlist
my $run_query = $db_connection->prepare("SHOW FULL PROCESSLIST");
$run_query->execute or die "SQL Error: $DBI::errstr\n";

#Declare @row array to store each row of above query being executed
my @row;

while (@row=$run_query->fetchrow_array()){
        if ( @row[5] > 300 && @row[4] =~ /Query/ ){
                my $killQuery = "KILL QUERY @row[0]";
                print "Query to be Executed: $killQuery\n";
                print "Process Info: @row[0] @row[1] @row[2]  @row[4] @row[5]\n";

                my $killQueryExecute = $dbh -> prepare($killQuery);
                $killQueryExecute-> execute;
        }
}

Step2: Add above script to cronjobs to run every minute

 # crontab -l
*/1 * * * * /usr/local/bin/killLongRunningSql.pl > /usr/local/bin/killLongRunningSql.log 2>&1


That's it!  Cheers!!!

Monday, August 12, 2013

Routing Protocols Basics : Must know

I like to classify routing protocols as following:

Class Algorithm Examples
Distance Vector(DV) Bellman-Ford Algorithm RIP, BGP
Link State Protocol(LSP) Dijkstra's Algoirthm OSPF, IS-IS
Advanced DV Bellman Ford + DUAL(Diffusing Update Algorithm) EIGRP

You can use multiple routing protocols in the same environment. Administrative Distance value is used for a selection of the best route when multiple routing protocols are in place. The lowest Administrative Distance value wins. For example: Static Routing is more trust worthy than EIGRP. EIGRP is more trust worthy than OSPF.

Main goal of Routing Protocol is a calculation of the optimum path. Fundamentally there are two base algorithms for the shortest path calculation:
  • Distance Vector (Bellman-Ford Algorithm)
    • Router sends a copy of routing table to it's neighbors
    • Periodic update
    • Slow convergence
    • Counting to infinity problem
      • Solutions to prevents count to infinity problem:
        • Define MAX count limit (e.g max 16 hop count in RIP; 15 hop count is the maximum diameter of RIP; Disadvantage: convergence is very slow i.e 15*30=450 seconds before routers know that network is not reachable)
        • Split Horizon: Don't advertise the route to the router from where it learned the route
        • Route Poisoning and Poisoning Reverse: If neighbor router goes down, router advertise that route is unreachable
[DV Analogy: When you are driving down to some place (say from Houston to Dallas), you look at the miles. If miles keep on going down, you know that you are heading in the right direction. ]
  • Links State Protocol (Dijkstra's Algorithm)
    • Router shares neighbors info with all the routers
    • No periodic update
    • Convergence is very fast (nearly 6 seconds)
    • Three different tables:
      • Adjacency Table
      • Topology Table
      • Forwarding Table
    • CPU and Memory intensive as changes in the network requires all the routers to update link state database, run the SPF algorithm, build the SPF tree and then rebuild the routing table.

Understanding Bellman-Ford Algorithm:

Understanding Dijkstra's Algorithm:
Really nice YouTube video:
http://www.youtube.com/watch?v=8Ls1RqHCOPw

Important notes to keep in mind:
  • Routing takes place at Layer 3(Network Layer). Packet Forwarding takes place at Layer 2(Data Layer).
  • MPLS (Multiprotocol Label Switching) is a mechanism that allows packet forwarding using labels, hence making it an independent of Protocol Type. It offers L3 VPN solution. MPLS is connection-oriented and packets are forwarded across pre-configured LSPs(Label Switched Paths).
[MPLS Analogy: Postal Service network uses ZIP code/ Postal Code as a label to forward your mail (could be anything like documents, TV, gifts, etc) to the recipient. After the mail reaches to the destination ZIP code/Postal code area, then actual address of the mail recipient is used to forward the mail. This process makes mail delivery much easier and efficient. MPLS works the same way.]
  • IGPs(RIP/OSPF/EIGRP) are used for routing within AS while EGP(BGP) is used for routing between different ASs. IGP is used to route within your own network and BGP is used when you are connecting to a network you don't control. 
[BGP/IGP Analogy: Considering US Interstate System: Interstates are BGP backbone and Interstates Exits are handoffs to IGPs ]
  • BGP relies on IGP for the routing table. If route is not in the table, BGP won't advertise it.
  • BGP (Border Gateway Protocol) is used to make core routing decisions on the Internet and decisions are made based on Path and Network Policies. BGP allows multihoming (connect to multiple ISPs) for better redundancy.
  • With MPLS in place inside providers network, BGP only need to be setup on PE(Provider Edge) routers.
  • VRF (Virtual Routing and Fowarding) allows multiple instances of a routing table to exist in a router and work simultaneously. VRF allows network path segmentation, thus increases network security. Thus, VRF is also referred as VPN routing and forwarding.
[ VRF Analogy: Running multiple VRFs on a router is just like running multiple Virtual Machines on a single hypervisor. Virtual Machines run independent of each other, likewise Routing Table of each VRFs are independent of each other. ].


>>>>>>Time to get your hand  dirty>>>>>>>

BGP Lab:


Scenario:
Customer edge router is connected to two different ISPs for redundancy.
IP Address and ASN info is shown in above topology.
Customer uses EIGRP for routing within it's own network. 
Customer uses BGP to connect to two different ISPs.

Step1: Configure IP addresses on the routers as show above

CPE-RTR-CORE#
interface Serial1/0
 ip address 169.153.1.2 255.255.255.0
 clockrate 64000
 no shut

CPE-RTR-EDGE#
interface Serial1/0
 ip address 169.153.1.1 255.255.255.0
 clockrate 64000
 no shut
interface FastEthernet0/0
 ip address 172.20.1.1 255.255.255.0
 no shut
interface FastEthernet0/1
 ip address 172.20.2.1 255.255.255.0
 no shut

ISP-1#
interface Loopback0
 ip address 2.2.2.2 255.255.255.0
 no shut
interface FastEthernet0/0
 ip address 172.20.1.2 255.255.255.0
 no shut

ISP-2#
interface Loopback0
 ip address 3.3.3.3 255.255.255.0
 no shut
interface FastEthernet0/0
 ip address 172.20.2.2 255.255.255.0
 no shut

Step2: Configure BGP

ISP-1#
router bgp 200
 network 2.2.2.0 mask 255.255.255.0
 neighbor 172.20.1.1 remote-as 100

ISP-2#
router bgp 300
 network 3.3.3.0 mask 255.255.255.0
 neighbor 172.20.2.1 remote-as 100

CPE-RTR-EDGE#
ip as-path access-list 10 permit ^$    
[Note: This allow only local routes being advertised to ISP. In other words, this filters internet routes from one ISP to go back to another ISP.]  

route-map localonly permit 10
 match as-path 10

router bgp 100
 network 169.153.1.0 mask 255.255.255.0
 neighbor 172.20.1.2 remote-as 200
 neighbor 172.20.1.2 route-map localonly out
 neighbor 172.20.2.2 remote-as 300
 neighbor 172.20.2.2 route-map localonly out

Detailed explaination on access-list and regex

Step3: Configure EIGRP 

CPE-RTR-CORE#
router eigrp 100
 network 169.153.1.0 0.0.0.255
 network 0.0.0.0 255.255.255.255  [This advertises all the routes known to this router]

CPE-RTR-EDGE#
router eigrp 100
 network 169.153.1.0 0.0.0.255
 network 0.0.0.0 255.255.255.255 [This advertises all the routes known to this router]


Some useful commands for troubleshooting:
#sh ip bgp
#sh ip bgp 
#sh ip bgp regexp ^$   [Display only local routes]
#sh ip bgp regexp ^100$  [Display routes learned from ASN 100 ]
#sh ip bgp regexp ^100_  [ Display routes with ASN 100 at front ]
#sh ip route
#clear bgp *   [Clear all BGP peers]
#debug bgp updates in
#debug bgp updates out



VRF Lab:





Scenario:
Say an ISP has two customers: Plano ISD (PISD) and Dallas ISD (DISD)
PISD and DISD both uses same subnet for IP addressing (172.20.0.0/24 & 172.20.1.0/24) and they can't change their addressing scheme.
As an ISP, you want to do business with both the customers. Your goal to isolate PISD network and DISD network ensuring network security. VRF is your solution.


Step1: Configure IP addresses on the routers as shown above

PISD1#
interface Loopback0
 ip address 1.1.1.1 255.255.255.0
interface FastEthernet0/0
 ip address 172.20.0.1 255.255.255.0

PISD2#
interface Loopback0
 ip address 2.2.2.2 255.255.255.0
interface FastEthernet0/0
 ip address 172.20.1.1 255.255.255.0

DISD1#
interface Loopback0
 ip address 1.1.1.1 255.255.255.0
interface Serial1/0
 ip address 172.20.0.1 255.255.255.0
 clockrate 64000

DISD2#
interface Loopback0
 ip address 2.2.2.2 255.255.255.0
interface Serial1/0
 ip address 172.20.1.1 255.255.255.0
 clockrate 64000

Note: You can't configure IP address on ISP router as it will throw an error that you are trying to configure duplicate IP address. In out network topology ISP s1/0 and f0/0 ports are going to have same IP address (i.e 172.20.0.2/24). ISP s1/1 and f0/1 are going to have same IP address (i.e 172.20.1.2/24).

Step2: Create VRF on ISP router and add interfaces to desired VRF so that you can configure IP address on ISP router to connect to CPE routers.

ISP#
ip vrf PISD
 rd 1:1
ip vrf DISD
 rd 2:2

Step3: Configure IP addresses on ISP router
ISP#
interface FastEthernet0/0
 ip vrf forwarding PISD
 ip address 172.20.0.2 255.255.255.0


interface FastEthernet0/1
 ip vrf forwarding PISD
 ip address 172.20.1.2 255.255.255.0

interface Serial1/0
 ip vrf forwarding DISD
 ip address 172.20.0.2 255.255.255.0
 clockrate 64000

interface Serial1/1
 ip vrf forwarding DISD
 ip address 172.20.1.2 255.255.255.0
 clockrate 64000

Step4: Configure OSPF on all the routers

CPE-ROUTERS(PISD1, PISD2, DISD1, DISD2)#
router ospf  1
 network 0.0.0.0 255.255.255.255 area 0 [This advertises all the routes known to this router]

ISP#
router ospf  1 vrf  PISD
 network 0.0.0.0 255.255.255.255 area 0 [This advertises all the routes known to this router]

router ospf  2 vrf  DISD
 network 0.0.0.0 255.255.255.255 area 0  [This advertises all the routes known to this router]

Some useful commands for troubleshooting:
#sh ip route
#sh ip route vrf  PISD
#sh ip router vrf DISD


Wednesday, June 12, 2013

Reset Windows / Windows Server / Domain Controller Administrator Password

Step1: Boot from Windows Bootable disk and select "Repair your Computer" Option


Step2: Follow  instructions until you get to following "Command Prompt" Option

Step3: Find which drive has Windows
Check if  C: drive has 'Windows' folder. If not, try D drive
>c:
         c:\>dir
        
        c:\> d:
        d:\> dir

Step4:  Replace Utilman.exe with cmd.exe. Note: Utilman.exe and cmd.exe are located under  Windows/System32
>cd Windows
Windows>cd System32
Windows\system32>move Utilman.exe  Utilman.exe.old
Windows\system32>copy  cmd.exe Utilman.exe
Windows\system32>exit

Step5:  Restart the server

Step6:  Once the booting is done, you are back to the logon screen. Click on the 'Ease of Access' icon

Hurray!!!.... There is your command prompt :)

Step7: Change the user password using command prompt.
>net   user   administrator   *
(Note: Windows doesn't allow easy passwords)

That's it. Now you can login as 'administrator' user

Let's say you want to add new user and add that user to the admin group
>net   user hacker   password123   /add
>net localgroup   administrators   hacker   /add

Don't forget to rename Utilman.exe.old to Utilman.exe.
Windows\system32>move Utilman.exe  Utilman.exe.old

I hope this helped.

Monday, May 6, 2013

Setup OSPF Routing Protocol for IPv6 network


GNS3 has been used for this tutorial.
Assumption: You have basic knowledge of  CISCO and OSPF

  • IPv6 is 128 bits. Make life simple. Break it into two 64 bits. First 64 bits for network and second 64 bits for interface.You can break first 64 network bits into Global Unicast Prefix(48 bits) and Subnet(64 minus 48 = 16 bits).
  • OSPF is a link state dynamic routing protocol and it maintains a topology of the configured area. Area 0 acts as backbone area. Area 0 maintains the topology for the entire network. All Areas must have single interface attached to Area 0.
  • It is best practice to create Loopback 0 with IPv4 address that will be used by OSPF as Router-ID. For example: We used 192.168.1.1 for Router#1 and OSPF picks this address as Router-ID
R1(config)#interface loopback 0
R1(config-if)#ip address 192.168.1.1 255.255.255.0
R1(config-if)#no shut

  • Enable IPv6 unicast-routing and create OSPF router process. For example @ Router1
R1(config)#ipv6 unicast-routing
R1(config)#ipv6 router ospf 1
R1(config-rtr)#exit

  • For this tutorial, create Loopback 1 with IPv6 address and assign it to OSPF Area N (1 if it is Router1, 2 if it Router2). For example @ Router1
R1(config)#interface loopback 1
R1(config-if)# ipv6 address 2001:DEAD:BEEF:1B01::1/64
R1(config-if)# ipv6 ospf network point-to-point
R1(config-if)# ipv6 ospf 1 area 1

  • Configure IPv6 addresses on the interfaces interconnecting routers and assign that interface to OSPF Area 0 (Area 0 is the backbone area). For example @ Router1
R1(config)#interface f0/0
R1(config-if)#ipv6 address 2001:DEAD:BEEF:1::1/64
R1(config-if)#ipv6 ospf 1 area 0

  • Finally, check the IPv6 OSPF routing table and perform ping tests.
R1#sh ipv6 ospf neighbor
Neighbor ID     Pri   State           Dead Time   Interface ID    Interface
192.168.2.1       1   FULL/DR         00:00:38    4               FastEthernet0/0

R1#sh ipv6 route ospf
IPv6 Routing Table - 9 entries
Codes: C - Connected, L - Local, S - Static, R - RIP, B - BGP
       U - Per-user Static route
       I1 - ISIS L1, I2 - ISIS L2, IA - ISIS interarea, IS - ISIS summary
       O - OSPF intra, OI - OSPF inter, OE1 - OSPF ext 1, OE2 - OSPF ext 2
       ON1 - OSPF NSSA ext 1, ON2 - OSPF NSSA ext 2
O   2001:DEAD:BEEF:2::/64 [110/2]
     via FE80::C601:23FF:FE9C:0, FastEthernet0/0
OI  2001:DEAD:BEEF:1B02::/64 [110/2]
     via FE80::C601:23FF:FE9C:0, FastEthernet0/0
OI  2001:DEAD:BEEF:1B03::/64 [110/2]
     via FE80::C601:23FF:FE9C:0, FastEthernet0/0




Router configurations:
Router1#
!

ipv6 unicast-routing
!

!
interface Loopback0
 ip address 192.168.1.1 255.255.255.0
!
interface Loopback1
 no ip address
 ipv6 address 2001:DEAD:BEEF:1B01::1/64
 ipv6 ospf network point-to-point
 ipv6 ospf 1 area 1
!
interface FastEthernet0/0
 no ip address
 duplex auto
 speed auto
 ipv6 address 2001:DEAD:BEEF:1::1/64
 ipv6 ospf 1 area 0
!

!
ipv6 router ospf 1
 log-adjacency-changes
!

Router2#
!
ipv6 unicast-routing

!
interface Loopback0
 ip address 192.168.2.1 255.255.255.0
!
interface Loopback1
 no ip address
 ipv6 address 2001:DEAD:BEEF:1B02::1/64
 ipv6 ospf network point-to-point
 ipv6 ospf 1 area 2
!
interface FastEthernet0/0
 no ip address
 duplex auto
 speed auto
 ipv6 address 2001:DEAD:BEEF:1::2/64
 ipv6 ospf 1 area 0
!
interface FastEthernet0/1
 no ip address
 duplex auto
 speed auto
 ipv6 address 2001:DEAD:BEEF:2::2/64
 ipv6 ospf 1 area 0
!

!
ipv6 router ospf 1
 log-adjacency-changes
!
!


Router3#
!
ipv6 unicast-routing
!

!
interface Loopback0
 ip address 192.168.3.1 255.255.255.0
!
interface Loopback1
 no ip address
 ipv6 address 2001:DEAD:BEEF:1B03::1/64
 ipv6 ospf 1 area 3
!
interface FastEthernet0/0
 no ip address
 duplex auto
 speed auto
 ipv6 address 2001:DEAD:BEEF:2::1/64
 ipv6 ospf 1 area 0
!
!
ipv6 router ospf 1
 log-adjacency-changes
!
!




CISCO Bonus tips: 
How to setup SSH login in my Cisco router?
Ans: Following set of commands will create user 'admin' with password 'cisco'. RSA keys will be generated for encryption and authentication. Telnet will be disabled (Telnet is bad as there is no encryption involved) and SSH will be enabled.


conf t
 username admin privilege 15 secret cisco
 crypto key generate rsa general-keys label myrouterkey modulus 2048 

 ip ssh rsa keypair-name myrouterkey

 line vty 0 4
 login local
 transport input ssh



Wednesday, April 3, 2013

Setup secure firewall in Linux : iptables and netfilter

In Linux, components of netfilter and iptables are responsible for the filtering and manipulation of network packets.
The filtering criteria and actions are stored in chains, which must be matched one after another for each  network packets. The chains to match are stored in tables. The iptables command allows to alter these tables and rule sets. 
Check out the switches of iptables command 
#iptables -h 

Most frequently used switches are -t , -j, -A, -F, -p, -s, -d, -i and -o
-t table        table to manipulate (default: `filter')
-j target       target for rule (may load target extension)
-A chain            Append to chain
-F [chain]          Delete all rules in  chain or all chains
-p proto        protocol: by number or name, eg. `tcp'
-i  in-interface 
-o  out-interface

There are three different tables for Linux based firewall, each for a particular function:
  1. FILTER (Packet filtering; This table holds the filter rules that determine whether to ACCEPT or DROP packet)
  2. NAT (Masquerading; This table defines any changes to the source and target address of packets)
  3. MANGLE (The rules in this table allows IP header manipulation)
These tables contain several predefined chains to match packets:
  1. PREROUTING
  2. INPUT
  3. FORWARD
  4. OUTPUT
  5. POSTROUTING
Fig. iptables : Possible paths for a packet (Src: SLES Security book)


Let's start with some examples. Warning!!! Be very careful while executing iptables rules as you may lock yourself out of the server or disrupt network based services running on the server.

Basic Iptables operations: 
Note: Please follow the instructions step-by-step. Skipping steps is not advised.
  • Allow all kind of traffic(tcp/udp) from 192.168.1.0/24 subnet
  • Drop everything else
Rule1# iptables -A INPUT -s 192.168.1.0/255.255.255.0 -i eth0 -p all -j ACCEPT
(We are appending a rule to INPUT chain of FILTER table(default table). This rule checks if source address of the packet is in 192.168.1.0/24 subnet. If so, it will allow the traffic. Else, it will pass on to the next rule)

Rule2# iptables -A INPUT -s 0/0 -j DROP
(We are appending a rule to INPUT chain of FILTER table(default table). This rule drops everything else. Note: 0/0 means ANY )

Check the rules
         # iptables -vL --line-numbers
  • Now we want to INSERT a new rule after Rule#1. We want to allow UDP traffic from 10.0.0.0/8 subnet
NewRule# iptables -I INPUT 2 -s 10.0.0.0/255.0.0.0 -i  eth0 -p udp -j ACCEPT
(We are inserting a new rule as rule#2. This rules allows UDP traffic from 10.0.0.0/8 subnet)

Check the rules
         # iptables -vL --line-numbers

  • Now we want to REPLACE a new rule we just added earlier. We want to allow UDP traffic only from 10.11.0.0/16 subnet but not 10.0.0.0/8 subnet
ReplaceRule# iptables -R INPUT 2 -s 10.11.0.0/255.255.0.0 -i  eth0 -p udp -j ACCEPT
(We are replacing rule#2 with above rule. This rule allows UDP traffic from 10.11.0.0/16 subnet)

Check the rules
         # iptables -vL --line-numbers

  • Now we want to place this set of rules at system startup.
Let's say you validated all the rules and your system is working as desired. Now, you want to place this set of rules at system startup so that you don't have to type above commands manually again. 

#iptables-save > /etc/iptables.up.rules


#cd /etc/sysconfig/network/if-pre-up.d/
#vi iptables-load
#!/bin/sh
iptables-restore < /etc/iptables.up.rules
exit 0

#cd /etc/sysconfig/network/if-post-down.d/
#vi iptables-unload
#!/bin/sh
iptables-save -c > /etc/iptables.up.rules
if [ -f /etc/iptables.down.rules ]; then
   iptables-restore < /etc/iptables.down.rules
fi
exit 0
    #chmod +x iptables-load
    #chmod +x iptables-unload

Restart your server and check if rules are still there and your system is working as desired. 


Application1: Linux as NAT Router

Step1: Enable packet forwarding for IPv4
$ sudo vi /etc/sysctl.conf
# Uncomment the next line to enable packet forwarding for IPv4
net.ipv4.ip_forward=1


$ sudo sysctl -p /etc/sysctl.conf

Step2: MASQUERADE all the traffic leaving external interface (in our case eth1). MASQUERADE operation mask the private IP address of PC1 or PC2 with an external IP address of the Linux Router.
$ sudo /sbin/iptables -t nat -A POSTROUTING -o eth1 -j MASQUERADE

Step3: Forward all packets incoming from an internal interface (eth0) to external interface (eth1)
$ sudo /sbin/iptables -A FORWARD -i eth0 -o eth1 -j ACCEPT

Step4: Forward only RELATED and ESTABLISHED packets incoming from an external interface (eth1) to internal interface (eth0)
$ sudo  /sbin/iptables -A FORWARD -i eth1 -o eth0 -m state --state RELATED,ESTABLISHED -j ACCEPT

Step5: Check iptables
$ iptables -vL
OR
$ iptables -t filter -vL

To check NAT table
$ iptables -t nat -vL

[Note: if you don't specify table name using -t flag, default table 'filter' will be used ]

Step6: Try to get out to internet from PC1 or PC2. Say, browse www.google.com.

Bonus information:  Let's say you want to SSH  to PC2 (192.168.1.3 port 22) from an external network, you have to setup DNAT
Here, I am mapping port 11015 on an external IP address(Public Routable Address) of Linux Router to port 22 on PC2 which is in our internal network.


$   sudo iptables -t nat -A PREROUTING -p tcp --dport 11015 -j DNAT --to-destination 192.168.1.3:22
$  sudo iptables -A FORWARD -p tcp --dport 22 -d 192.168.1.3 -j ACCEPT
$  sudo iptables -t nat -A POSTROUTING -d 192.168.1.3 -p tcp --dport 22 -j MASQUERADE

Now, ssh  external_IP_address_of_LinuxRouter:11015 from an external network , you should get to 192.168.1.3:22.


Application2: Advanced Scenario (Firewall rules to mitigate an impact of DoS attack on Asterisk- VoIP Servers)

  • We want to delete all rules defined earlier and start fresh. We will be using 'hashlimit' match.
#iptables -F
  • Now, we want to define some advanced rules. We want to:
    • allow all packets from 192.168.1.0/24 subnet
    • limit the rate of SIP Invite from a host to mitigate DoS attack impact
    • limit the rate of SIP Registration from a host to mitigate DoS attack impact
    • allow all RTP(udp) traffic incoming from 10.0.0.0/8 subnet to ports 5000:31000(default RTP ports for asterisk)
    • drop any other packets
#iptables -A INPUT -s 192.168.1.0/255.255.255.0 -i eth0 -p all -j ACCEPT

#iptables -A INPUT -p udp -m udp --dport 5060 -m string --string "INVITE sip:" --algo bm -m hashlimit --hashlimit-upto 10/sec --hashlimit-burst 10 --hashlimit-mode srcip --hashlimit-name sip_i_limit -j ACCEPT

Look for the string "INVITE sip:" inside the UDP payload 
--hashlimit-upto   10/sec will allow upto 10 connection per second
--hashlimit-burst 10  will allow additional 10 packets before hit the limit (or how many fast connections you can have)
--hashlimit-htable-expire 10000   will expires hash entries in 10000 miliseconds

#iptables -A INPUT -p udp -m udp --dport 5060 -m string --string "REGISTER sip:" --algo bm -m hashlimit --hashlimit-upto 1/sec --hashlimit-burst 10 --hashlimit-mode srcip --hashlimit-name sip_r_limit -j ACCEPT

#iptables -A INPUT -s 10.0.0.0/8 -i eth2 -p udp -m udp --dport 5000:31000 -j ACCEPT
#iptables -A INPUT -s 0/0 -j DROP


  • We want to delete all rules defined earlier and start fresh. We will be using 'recent' match instead of 'hashlimit' match and achieve similar goal mentioned earlier to mitigate an impact of DoS attack.
#iptables -F

#iptables -A INPUT -s 192.168.1.0/255.255.255.0 -i eth0 -p all -j ACCEPT

#iptables -A INPUT 1 -i eth0 -p udp -m udp --dport 5060 -m string --string "REGISTER sip:" --algo bm --to 65535 -m recent --set --name VOIP --rsource

#iptables -A INPUT 1 -i eth0 -p udp -m udp --dport 5060 -m string --string "REGISTER sip:" --algo bm --to 65535 -m recent --update --seconds 60 --hitcount 12 --rttl --name VOIP --rsource -j DROP
Note: The maximum value for the hitcount parameter is given by the "ip_pkt_list_tot" parameter of the xt_recent kernel module. Exceeding this value on the command line will cause the rule to be rejected.

#iptables -A INPUT 1 -i eth0 -p udp -m udp --dport 5060 -m string --string "INVITE sip:" --algo bm --to 65535 -m recent --set --name VOIPINV --rsource

#iptables -A INPUT 1 -i eth0 -p udp -m udp --dport 5060 -m string --string "INVITE sip:" --algo bm --to 65535 -m recent --update --seconds 60 --hitcount 12 --rttl --name VOIPINV --rsource -j DROP

#iptables -A INPUT 1 -s 10.0.0.0/8 -i eth0 -p udp -m udp --dport 5000:31000 -j ACCEPT

#iptables -A INPUT -s 0/0 -j DROP

Go to iptables manual ( #man iptables ) to understand about hashlimit and recent match in detail.

Tuesday, March 26, 2013

Telnet Automation using Python: Copy CISCO configs to TFTP server


Scenario:
Enterprise is using TELNET for CISCO management. It wants to design an automated script that will copy config (either startup-config or running-config) of the CISCO routers and switches to the TFTP server. TFTP server has separate folder for each routers.
There are the times when a network engineer forgets to copy router config to the TFTP server after he/she makes changes to CISCO config. This little python script can be scheduled to copy configs periodically saving network engineers from possible unforeseen havoc because they don't have backup copy of the router config and router has crashed.

Note: This Python script is based on  Python version 3.3. You can execute this script on both windows and linux/unix environment. You can download python from http://www.python.org/getit/. When you install python make sure that python is added to your system environment variables.


#File name: telnetautomation.py
#!/usr/bin/python
#Script starts here
import getpass
import sys
import telnetlib
import time

pwd1 = "user_exec_mode_password"
pwd2 = "privilege_exec_mode_password"
config = "startup-config"

#Create a list of router IP address, folder location and router hostname  

hostlist= [ ("router1_ip_address","plano","planoRouter1"),
            ("router2_ip_address","dallas","dallasRouter1"),
        ]

#Use for loop to telnet into each routers and execute commands
for host in hostlist:
    
    cmd1 = "en"
    cmd2 = "copy "+config+" tftp://tftp_server/cisco/"+host[1]+"/"+host[2]+".txt"
#copy startup-config  tftp://tftp_server/plano/planoRouter1.txt

    tn = telnetlib.Telnet(host[0])
    tn.set_debuglevel(5)

    time.sleep(2)
    tn.write(pwd1.encode('ascii') + b"\n")
    time.sleep(2)
    tn.write(cmd1.encode('ascii') + b"\n")
    time.sleep(2)
    tn.write(pwd2.encode('ascii') + b"\n")
    time.sleep(2)
    tn.write(cmd2.encode('ascii') + b"\n")
    time.sleep(2)
    tn.write(b"\n")
    time.sleep(2)
    tn.write(b"\n")
    time.sleep(2)
    tn.close()

sys.exit("operation completed")

#script ends here

[Note: TELNET is not my favorite protocol as it is very insecure. Communication is done in plain text. However, there are many enterprises still running this insecure protocol in their environment. Upgrade to SSH and disable TELNET in your environment if possible.]