RSS

It sounds easy, graphing http errors over time

18 Mar

I have access to several fancy dancy monitoring tools. They alert and report quite well but I had a unique case where I needed to adjust a website monitor. I was getting alerts almost daily that the website was down but I would immediately check it out and it was operating just fine. The server logs showed no trace of the monitor request or any errors to point me in any particular direction.

I approached this issue with the assumption that is was either the inconsistent interwebz or a monitor false alarm. We have monitors that were successful that run via agents locally on the web server. That proves the server was functional, it just wasn’t receiving the traffic.

What I needed was a a tool that could check the site more frequently and allow me to adjust the timeout value. Sure, there are probably a lot of tools already, but why not give myself maximum flexibility and write one myself.

I realize it’s a little sloppy and needs some thread management to handle some timing but it did the trick. It’s a GUI with a few buttons but here is the code behind the form.

Imports System.Net
Imports System.Data.SqlClient
Public Class Form1


    Private Sub btnStart_Click(sender As System.Object, e As System.EventArgs) Handles btnStart.Click
        Timer1.Interval = Integer.Parse(txtInterval.Text)
        Timer1.Enabled = True
        Timer1.Start()
        System.IO.File.WriteAllText("ispchecklog.txt", "")
        btnStart.Enabled = False
    End Sub

    Private Sub btnStop_Click(sender As System.Object, e As System.EventArgs) Handles btnStop.Click
        Timer1.Stop()
        Timer1.Enabled = False
        btnStart.Enabled = True
    End Sub


    Private Sub Timer1_Tick(sender As System.Object, e As System.EventArgs) Handles Timer1.Tick
        CreateCommand("INSERT INTO [ISPCheck].[dbo].[log] ([stamp] ,[rc], ) VALUES ('" & DateTime.Now.ToString("yyyyMMdd hh:mm:ss") & "','" & WRequest(txtURL.Text) & "','" & System.Environment.MachineName & "' );")
    End Sub


    Function WRequest(ByVal URL As String) As Integer
        Dim responseData As String = ""
        Try
            Dim hwrequest As Net.HttpWebRequest = Net.WebRequest.Create(URL)
            hwrequest.Accept = "*/*"
            hwrequest.AllowAutoRedirect = True
            hwrequest.UserAgent = "http_requester/0.1"
            hwrequest.Timeout = Integer.Parse(txtTimeout.Text)
            hwrequest.Method = "GET"

            Dim proxy As IWebProxy = WebRequest.GetSystemWebProxy()
            proxy.Credentials = CredentialCache.DefaultCredentials
            hwrequest.Proxy = proxy


            Dim hwresponse As Net.HttpWebResponse = hwrequest.GetResponse()
            If hwresponse.StatusCode = Net.HttpStatusCode.OK Then
                responseData = 200
            End If
            hwresponse.Close()
        Catch e As Exception
            System.IO.File.AppendAllText("log.txt", DateTime.Now.ToString("yyyyMMdd hhmmss") & e.ToString & System.Environment.NewLine)
            responseData = 0
            MessageBox.Show(e.ToString)
        End Try

        Return responseData
    End Function

    Public Sub CreateCommand(ByVal queryString As String)
        Try
            Using connection As New SqlConnection(txtConnstr.Text)
                Dim command As New SqlCommand(queryString, connection)
                command.Connection.Open()
                command.ExecuteNonQuery()
            End Using
        Catch ex As Exception
            System.IO.File.AppendAllText("log.txt", DateTime.Now.ToString("yyyyMMdd hhmmss") & ex.ToString & System.Environment.NewLine)
            MessageBox.Show(ex.ToString)
        End Try
    End Sub

End Class

What I got back was a lot of alerts in a table with their time and http return code where 0 was a timeout. I had this test running on several computers so the time values were not consistant. I thought they would line up to show some “outages” but I couldn’t tell from just looking at the table. I wanted to see if they were clustered together so I needed to see them on a graph. No matter how I sliced it I couldn’t get it to look good. Then I realized what I needed was a time dimension, and then to count the errors within certain time slices. This query will group the events into 5 minute intervals. Also important, it will give me the intervals that don’t have any alerts so I can easily produce a graph over time.

SELECT
dateadd(minute,(datediff(minute,0,stamp)/5)*5,0) as interval,
MIN(rc) as min_rc,
COUNT(case when rc = 0 then 1 end) as 'number of occurances'
from ispcheck.dbo.log
group by dateadd(minute,(datediff(minute,0,stamp)/5)*5,0)

This link got me close to the query I needed. I found quite a few that didn’t really do the trick at all before I located this forum post.

http://www.techtalkz.com/microsoft-sql-server/170861-grouping-sql-output-5-minute-intervals.html

Advertisements
 
Leave a comment

Posted by on March 18, 2012 in Network Admin, SQL Dev

 

Tags: ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: