Thursday, September 24, 2009

Get You Sever Event Log - Emailed To You

There is a very useful script that I came across that sends an email to you every time a new event is added to your (Windows) sever event log. It's a great way to keep track of what's happening on your server while you away.

To implement the script you need to save it as a VBS file and call using your scheduler. Here's what the script looks like.

 strComputer = "." 
Set objWMIService = GetObject("winmgmts:{(Security)}\\" & _
strComputer & "\root\cimv2")
Set colMonitoredEvents = objWMIService.ExecNotificationQuery _
("Select * from __InstanceCreationEvent Where " _
& "TargetInstance ISA 'Win32_NTLogEvent'" _
& "and TargetInstance.EventCode <> '17401'" _
& "and TargetInstance.EventCode <> '17896'" _
& "and TargetInstance.EventCode <> '3198'" _
& "and TargetInstance.EventCode <> '3408'" _
& "and TargetInstance.EventCode <> '576'" _
& "and TargetInstance.EventCode <> '680'" _
& "and TargetInstance.EventCode <> '540'" _
& "and TargetInstance.EventCode <> '12517'" _
& "and TargetInstance.EventCode <> '12503'" _
& "and TargetInstance.EventCode <> '7036'" _
& "and TargetInstance.EventCode <> '17137'" _
& "and TargetInstance.EventCode <> '8128'" _
& "and TargetInstance.EventCode <> '2803'" _
& "and TargetInstance.EventCode <> '7040'" _
& "and TargetInstance.EventCode <> '552'" _
& "and TargetInstance.EventCode <> '538'" _
& "and TargetInstance.EventCode <> '1111'" _
& "and TargetInstance.EventCode <> '9'" _
& "and TargetInstance.EventCode <> '3197'" _
& "and TargetInstance.EventCode <> '528'" _
& "and TargetInstance.EventCode <> '552'" _
& "and TargetInstance.EventCode <> '2'" _
& "and TargetInstance.EventCode <> '7035'")

Do
Set objLatestEvent = colMonitoredEvents.NextEvent
Set objEmail = CreateObject("CDO.Message")

objEmail.From = ""
objEmail.To = ""

objEmail.Subject = ""
objEmail.Textbody = objLatestEvent.TargetInstance.Message & " (" & objLatestEvent.TargetInstance.TimeWritten & ", " & objLatestEvent.TargetInstance.User & ", " & objLatestEvent.TargetInstance.EventCode & ")"

objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = ""

objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusername") = ""

objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendpassword") = ""

objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

objEmail.Configuration.Fields.Update

objEmail.Send

Loop


There are a few things that you need to configure here:

1) The From Email Address
2) The To Email Address
3) The Subject Of The Email
3) Your SMTP server - The script is setup to send email via an smtp server. Authentication is also setup to use a username/password. You can change authentication by changing the value for smtpauthenticate (eg 0 - Anon, 1 - basic, 2 - NTML)

Finally if you would like to exclude events from being emailed to you, you can do so by including an condition in the select clause. I've excluded a few events as they more informational message.

A VBS script can be called by using the CSCRIPT.exe application and then specifying the path to the VBS file.

Saturday, September 19, 2009

Hope You Have A Great EID (2009)

For those of you that are celebrating....hope you have a wonderfull EID and remember me in your prayers.

Wednesday, September 16, 2009

Creating A Script To Backup MS SQL Server - By Command Line

I've been looking for a script for some time now, that would allow me to backup my MS SQL databases on the server. I bumped into this. The script calls the MS SQL command line application and then creates a backup of all databases to a specified folder. After that you could simply zip or rar the backups and then save them to an ftp location or a network drive.

For this to work you need to firstly create an SQL script file as follows:

 Use Master
Declare @ToExecute VarChar(8000)
Select @ToExecute = Coalesce(@ToExecute + 'Backup Database [' + [Name] + '] To Disk = ''C:\ServerBackups\' + [Name] + '.bak'' With Format;' + char(13),'')
From
Master..Sysdatabases
Where
[Name] Not In ('tempdb')
and databasepropertyex ([Name],'Status') = 'online'
Execute(@ToExecute)


Notice in the script file I've hard coded the backup location. Finally you need to call the MS SQL command line utility to execute the script. This can be done as follows:

osql -U -P < c:\scripts\dbbackup.sql

The above can be put into a batch file and schedule to run using the windows task scheduler. Like I said before if you want you can include zip or rar calls to compress the backups and move them to other locations.

The above script has been tested on both MS SQL 2005 and 2008

Note: If you intend FTPing the data, I would suggest you use an application like MoveITFreely. MoveITFreely is a free application that allows you to ftp files via command line. Unfortunately, the Windows versions of the FTP clients do not support binary mode. So you would need to get a third party FTP client to transfeer binary files.

Tuesday, September 15, 2009

Adding Paging Together With Prev/Next Buttons To The Gridview

I've been trying to add custom paging to a Datagrid view and came accross an interesting article. However the article did not show how to include previous and next buttons. So I worked with their existing code and just added some customisation.

There are six steps you need to follow, I've broken them down below:

1) Add a custom pager template to the grid. The code is as follows:
            <PagerTemplate>  
<table width="100%" cellpadding="5" CellSpacing="5">
<tr>
<td style="text-align: center">
<div class="quail"></div>
<asp:PlaceHolder ID="PlaceHolder1" runat="server" />
</td>
</tr>
</table>
</PagerTemplate>


2) The next step is to add the paging code to the web form:

Create A Procedure To Display The Pages Together With The Next and Previous Buttons

   private void SetPaging()
{
GridViewRow row = GridViewCategoryWiseProduct.BottomPagerRow;
if (GridViewCategoryWiseProduct.PageIndex >0)
{
LinkButton prev = new LinkButton();
prev.CommandName = "Page";
prev.CommandArgument = (GridViewCategoryWiseProduct.PageIndex).ToString();
prev.CssClass = "pagination_link";

prev.Text = "Prev";
prev.ToolTip = "Page " + (GridViewCategoryWiseProduct.PageIndex).ToString();
PlaceHolder place = row.FindControl("PlaceHolder1") as PlaceHolder;
place.Controls.Add(prev);

Label lbl = new Label();
lbl.Text = " ";
place.Controls.Add(lbl);

}

for (int i = 1; i <= GridViewCategoryWiseProduct.PageCount; i++)
{

LinkButton btn = new LinkButton();
btn.CommandName = "Page";
btn.CommandArgument = i.ToString();

btn.CssClass = "pagination_link";
PlaceHolder place = row.FindControl("PlaceHolder1") as PlaceHolder;

if (i == GridViewCategoryWiseProduct.PageIndex + 1)
btn.CssClass = "pagvisited";

btn.Text = i.ToString();
btn.ToolTip = "Page " + i.ToString();
place.Controls.Add(btn);

Label lbl = new Label();
lbl.Text = " ";
place.Controls.Add(lbl);

}

if ((GridViewCategoryWiseProduct.PageIndex + 1) < GridViewCategoryWiseProduct.PageCount)
{
LinkButton next = new LinkButton();
next.CommandName = "Page";
next.CommandArgument = (GridViewCategoryWiseProduct.PageIndex + 2).ToString();
next.CssClass = "pagination_link";

next.Text = " Next";
next.ToolTip = "Page " + (GridViewCategoryWiseProduct.PageIndex + 2).ToString();
PlaceHolder place = row.FindControl("PlaceHolder1") as PlaceHolder;

place.Controls.Add(next);
}

}


3) Insert Code Into The Page Load Event To Maintain State Of The Pager

  protected void Page_Load(object sender, EventArgs e)
{
SetPaging();
}


4) Update the pageindex when the page changes

   protected void GridViewCategoryWiseProduct_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridViewCategoryWiseProduct.PageIndex = e.NewPageIndex;
}


5) Finally Display Paging Details When The Grid Is Bound

   protected void GridViewCategoryWiseProduct_DataBound(object sender, EventArgs e)
{
if (Page.IsPostBack)
SetPaging();
}


6) Add Some CSS Formatting To Make The Page Look Resonably Good

 /*Paging Styles - Based on Facebook*/ 
.quail{color:#3B5998;float:left;padding:3px 0px; }
.pagination_link{
font-family:Arial, Helvetica, sans-serif;
font-size:12px;
color:#000;
padding:3px 3px 2px 3px;
}

.pagination_link:hover{
font-family:Arial, Helvetica, sans-serif;
font-size:12px;
color:#fff;
text-decoration:underline;
background-color:#C2BF8E;
}

.pagvisited{
font-family:Arial, Helvetica, sans-serif;
font-size:12px;
color:#964908;
background:transparent;
border-color:#3B5998;
border-bottom:2px solid #964908;
color:#964908;
font-weight:bold;
padding-left:2px;
padding-right:2px;
}


If all goes well you should see paging like this:











If you looking for different styles and CSS formatting for paging, I suggest you look at this link:

Pager styles from around the web css images and html