DNS monitoring using MRTG

Thanks to howe81, I am now able to monitor DNS server using MRTG. The script file required to monitor is located here.

The MRTG config file should look like the following:
Target[mydomain_DNS]: `/etc/mrtg/dnsstats.pl`
Options[mydomain_DNS]: gauge,growright,nopercent,integer,unknaszero
Title[mydomain_DNS]: DNS Server
RouterUptime[mydomain_DNS]: public@localhost
MaxBytes[mydomain_DNS]: 32000
AbsMax[mydomain_DNS]: 64000
WithPeak[mydomain_DNS]: wmy
Colours[mydomain_DNS]: YELLOW #F9C000,RED #F90000,LIGHT YELLOW #FFFFBB,LIGTH RED #FF8080
ShortLegend[mydomain_DNS]:queries/m
YLegend[mydomain_DNS]: Qs per Minute
Legend1[mydomain_DNS]: Queries received over 1 minute
Legend2[mydomain_DNS]: Failed Queries received over 1 minute
Legend3[mydomain_DNS]: Maximal Queries over 5 minutes
Legend4[mydomain_DNS]: Maximal Failed Queries over 5 minutes
LegendI[mydomain_DNS]:  Queries: 
LegendO[mydomain_DNS]:  Failures: 
PageTop[mydomain_DNS]: <H1>DNS Info</H1>
<TABLE>
<TR><TD>System:</TD> <TD>mydomain</TD></TR>
<TR><TD>Maintainer:</TD> <TD>Ram Prasad (ram@mydomain)</TD></TR>
<TR><TD>Description:</TD><TD>DNS Monitor</TD></TR>
<TR><TD>Details:</TD> <TD>Query Monitor</TD>
</TABLE>

Making a PHP Site on Linux Work with a Microsoft SQL Server Database

Making a PHP Site on Linux Work with a Microsoft SQL Server Database
by David Perrin

A recent project at my current employer called for creating a new web frontend to an existing MSSQL database. My boss, having created a sophisticated MySQL and PHP-driven black diamond web site in PHP was enthused about the prospects of further web development with PHP. He suggested trying to get PHP on a Linux box to connect to a MSSQL database server.

An attempt at this task made months before ended in frustration. This time, after nibbling away at the task for a couple of days on a standard Red Hat system, success was had. Here’s how.

Similar to Windows, one method of connecting to a MSSQL database is through an ODBC DSN (open database connection, data source name). The ODBC DSN specifies a MSSQL driver to make the connection to the database. Recent versions of Red Hat include the utility for creating the ODBC DSN, but not the driver.

The driver chosen and discussed in this article is a FreeTDS driver. TDS (tabular datastream) is a protocol used by Sybase and MSSQL. This driver enables the Linux machine to connect to the MSSQL server.

Once the driver is installed, you can configure an ODBC connection on your Linux machine to use the driver, which then allows a connection to MSSQL. Start by downloading and saving the FreeTDS driver.

[root@localhost]# lynx
http://ibiblio.org/pub/Linux/ALPHA/freetds/freetds-0.60.tgz

Next, uncompress, configure and make the FreeTDS driver.

[root@localhost]# tar xvfz freetds-0.60.tgz
[root@localhost]# cd freetds-0.60
[root@localhost]# ./configure –with-tdsver=7.0 –with-unixodbc

su to root if you are not already root.

[root@localhost]# make
[root@localhost]# make install
[root@localhost]# make clean

Now test the ability of FreeTDS to connect to your MSSQL server:

[root@localhost]# /usr/local/bin/tsql -S -U

Password:

With luck, you’ll see the following prompt

1>

Then, use Ctrl-C to exit.

If the tsql command doesn’t return the 1> prompt, verify that you can get to your MSSQL server with

[root@localhost]# telnet 1433

If you’re able to telnet to port 1433, try opening the Microsoft Query Analyzer. Use the login combination you tried above to verify that a user name and password combination exists for your SQL server.

Once you can get the 1> prompt from tsql, we can configure the TDS driver and make the ODBC connection.

[root@localhost]# cd /usr/local/etc

From /usr/local/etc/, edit freetds.conf. At the end of this file, add an entry something like this:

[TDS]
host = mssql.serverhost.com
port = 1433
tds version = 7.0

Set Up the ODBC Data Source
Red Hat comes with a graphic interface tool called ODBCConfig. We’ll use it to set up our DSN.

From KDE, select K -> System -> ODBCConfig

From GNOME, select G -> Programs -> System -> ODBCConfig

Click on the Drivers Tab and click Add. The window should contain the following data:

Name: TDS
Description: v0.60 with Protocol v7.0
Driver: /usr/local/lib/libtdsodbc.so
Setup: /usr/lib/libtdsS.so
FileUsage: 1

The rest can be blank. Click the check-mark in the upper left-hand corner. Select the TDS driver and click OK.

Name: MSSQLServer
Description: TDS MSSQL (description isn’t important)
Servername: mssql.serverhost.com
UID: sa
PWD:
Port: 1433

Then click the System DSN tab and select Add. Test out the unixODBC connection with:

[root@localhost]# isql -v MSSQLServer username password
+—————————————+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+—————————————+
SQL> use Northwind
0 rows affected
SQL> SELECT TOP 1 CategoryName from Categories
+——————————-+
| CategoryName |
+——————————-+
| Beverages |
+——————————-+
1 rows affected
SQL> quit

Now to test it using PHP, put this page in a web-viewable directory and try to view it from the browser.

— begin odbctest.php—
// connect to DSN MSSQL with a user and password
$connect = odbc_connect(“MSSQLServer”, “username”, “password”) or die
(“couldn’t connect”);

odbc_exec($connect, “use Northwind”);

$result = odbc_exec($connect, “SELECT CompanyName, ContactName ” .
“FROM Suppliers”);

while(odbc_fetch_row($result)){
print(odbc_result($result, “CompanyName”) .
‘ ‘ . odbc_result($result, “ContactName”) . “
\n”);
}

odbc_free_result($result);
odbc_close($connect);

— end odbctest.php —

Should a SQL statement contain an error, PHP will return a cryptic, incomprehensible warning message via the driver. In debugging, it has been helpful to echo out the offending queries.

The web application that instigated this setup is now in its third month of production and is performing quite well.

AWStats Log Processor

I have been implementing AWstats log processors for various websites now. I find it really good, easy to configure maintain and use. I will be posting various scripts run/maintain config files.

Awstats can be found here:
http://awstats.sourceforge.net

and here are few features of awstats:
A full log analysis enables AWStats to show you the following information:
* Number of visits, and number of unique visitors,
* Visits duration and last visits,
* Authenticated users, and last authenticated visits,
* Days of week and rush hours (pages, hits, KB for each hour and day of week),
* Domains/countries of hosts visitors (pages, hits, KB, 266 domains/countries detected),
* Hosts list, last visits and unresolved IP addresses list,
* Most viewed, entry and exit pages,
* Files type,
* Web compression statistics (for mod_gzip),
* Browsers used (pages, hits, KB for each browser, each version, 78 browsers: Web, Wap, Media browsers…),
* OS used (pages, hits, KB for each OS, 31 OS detected),
* Visits of robots (307 robots detected),
* Search engines, keyphrases and keywords used to find your site (The 90 most famous search engines are detected like yahoo, google, altavista, etc…),
* HTTP errors (Page Not Found with last referrer, …),
* Other personalized reports based on url, url parameters, referer field for miscellanous/marketing purpose.

AWStats also supports the following features:
* Can analyze a lot of log formats: Apache NCSA combined log files (XLF/ELF) or common (CLF), IIS log files (W3C), WebStar native log files and other web, proxy, wap or streaming servers log files (but also ftp or mail log files). See AWStats F.A.Q. for examples.
* Works from command line and from a browser as a CGI (with dynamic filters capabilities for some charts),
* Update of statistics can be made from a web browser and not only from your scheduler,
* Unlimited log file size, support split log files (load balancing system),
* Support ‘nearly sorted’ log files even for entry and exit pages,
* Reverse DNS lookup before or during analysis, support DNS cache files,
* Country detection from IP location (geoip) or domain name.
* WhoIS links,
* A lot of options/filters and plugins can be used,
* Multi-named web sites supported (virtual servers, great for web-hosting providers),
* Cross Site Scripting Attacks protection,
* Several languages. See AWStats F.A.Q. for full list.
* No need of rare perl libraries. All basic perl interpreters can make AWStats working,
* Graphical and framed reports,
* Look and colors can match your site design,
* Help and tooltips on HTML reported pages,
* Easy to use (Just one configuration file to edit),
* Absolutely free (even for web hosting providers), with sources (GNU General Public License),
* Available on all platforms,
* AWStats has a XML Portable Application Description.