Tuesday, March 27, 2012

SNMP Introduction: Communicating with your Router

"Simple Network Management Protocol (SNMP) is an Internet-standard protocol for managing devices on IP networks. Devices that typically support SNMP include routers, switches, servers, workstations, printers, modem racks, and more. It is used mostly in network management systems to monitor network-attached devices for conditions that warrant administrative attention." - Wikipedia

Below is a Java code snippet which uses the 3rd party library snmp4j to communicate with a router. In this example we obtain the name of the router and the current data usage. Before executing the code confirm that your router supports SNMP and that it is enabled. Also verify that the SNMP read and write community is set to "public". These are configurable values that can be updated from within your router's administrative application.

Note that the OID variables contain long and cryptic numeric values. These values can be obtained using a free SNMP browser such as iReasoning's MIB Browser. When using an SNMP browser you must supply your router's IP address and SNMP port (usually 161) to connect to your router. Once connected you can browse through the different properties as a tree structure and obtain any of the numeric values for the OIDs.

import java.io.*;
import org.snmp4j.*;
import org.snmp4j.mp.*;
import org.snmp4j.smi.*;
import org.snmp4j.transport.*;

public class SnmpClient
{
 public static final OID sysNameOID = new OID(".1.3.6.1.2.1.1.5.0");
 public static final OID ifNumberOID = new OID(".1.3.6.1.2.1.2.1.0");
 public static final OID ifOutOctetsOID = new OID(".1.3.6.1.2.1.2.2.1.16.1");
 public static final OID ifInOctetsOID = new OID(".1.3.6.1.2.1.2.2.1.10.1");
 
 private String address;
 private Snmp snmp;
 
 public static void main(String[] arg)
 {
  try
  {
   //Create a client with the router's IP Address and SNMP port (161 is default)
   SnmpClient client = new SnmpClient("udp:192.168.0.1/161");
   
   //Get the router's name
   String name = client.getAsString(sysNameOID);
   
   //Display the router's name
   System.out.println("ROUTER'S NAME: " + name);
   
   //Update the router's name (sysName is one of the few properties that are writable)
   client.setAsString(sysNameOID, "TEST");
   
   //I had to read this property before reading the other data usage properties
   client.getAsInt(ifNumberOID);
   
   //Get total number of bytes sent since last router reboot
   int bytesSent = client.getAsInt(ifOutOctetsOID);
   
   //Get total number of bytes received since last router reboot
   int bytesReceived = client.getAsInt(ifInOctetsOID);
   
   //Calculate total number of bytes sent and received
   int bytesTotal = bytesSent + bytesReceived;
   
   //Display combined total data usage in megabytes
   System.out.println("DATA USAGE: " + (bytesTotal / 1024 / 1024) + " MB");
  }
  catch (Exception e)
  {
   e.printStackTrace();
  }
 }
 
 public SnmpClient(String address) throws IOException
 {
  this.address = address;
  start();
 }

 private void start() throws IOException
 {
  DefaultUdpTransportMapping transport = new DefaultUdpTransportMapping();
  snmp = new Snmp(transport);
  transport.listen();
 }
 
 public void stop() throws IOException
 {
  snmp.close();
 }
 
 private Target createTarget()
 {
  Address targetAddress = GenericAddress.parse(address);
  CommunityTarget target = new CommunityTarget();
  target.setCommunity(new OctetString("public"));
  target.setAddress(targetAddress);
  target.setRetries(3);
  target.setTimeout(1500);
  target.setVersion(SnmpConstants.version1);
  return target;
 }
 
 private PDU createGetPDU(OID oid)
 {
  PDU pdu = new PDU();
  pdu.setType(PDU.GET);
  pdu.add(new VariableBinding(oid));   
  return pdu;
 }
 
 public Variable get(OID oid) throws IOException
 {
  PDU response = snmp.send(createGetPDU(oid), createTarget()).getResponse();
  if (response != null && response.size() > 0)
  {
   return response.get(0).getVariable();
  }
  throw new IOException("Unable to obtain response from server");
 }
 
 public String getAsString(OID oid) throws IOException
 {
  return get(oid).toString();
 }
 
 public int getAsInt(OID oid) throws IOException
 {
  return get(oid).toInt();
 }
 
 private PDU createSetPDU(OID oid, Variable value)
 {
  PDU pdu = new PDU();
  pdu.setType(PDU.SET);
  pdu.add(new VariableBinding(oid, value));
  return pdu;
 } 
 
 public void set(OID oid, Variable value) throws IOException
 {
  snmp.send(createSetPDU(oid, value), createTarget());
 }
 
 public void setAsString(OID oid, String value) throws IOException
 {
  set(oid, new OctetString(value));
 }
 
 public void setAsInt(OID oid, int value) throws IOException
 {
  set(oid, new Integer32(value));
 }
}

Monday, March 19, 2012

Search SQL Server Column Names

Below is a short T-SQL code snippet that demonstrates how to search for some text in all the column names in all the tables in a SQL Server database.

declare @SEARCHTEXT varchar(256)
set @SEARCHTEXT = 'TEXT YOU WANT TO SEARCH FOR'
select t.table_name,
c.column_name,
c.data_type,
c.character_maximum_length
from information_schema.tables t
inner join information_schema.columns c
on c.table_name = t.table_name
where c.column_name like '%' + @SEARCHTEXT + '%'
order by t.table_name, c.column_name

Search SQL Server Stored Procedures and Functions

Below is a short T-SQL code snippet that demonstrates how to search for some text in all the stored procedures and functions in a SQL Server database.

DECLARE @SEARCHTEXT VARCHAR(256)
SET @SEARCHTEXT = 'TEXT YOU WANT TO SEARCH FOR'
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECT_DEFINITION(OBJECT_ID(ROUTINE_NAME)) LIKE '%' + @SEARCHTEXT + '%'
ORDER BY ROUTINE_NAME