Figure 1: Example of displaying servers by Role

2. Must display at least display the following information on that server. See Figure 1

a. ID, Name

b. What application it’s running

c. What it’s role (e.g. DB, Test, Dev, App) for that application is.

d. Support Team, contact information of Primary contact (EMPLID1)

e. Status

f. Impact, and Acceptable Down Hours

3. Provide the ability to update the server status. See Figure 2.

Figure 2: Formview to change server status, and provide a timestamp

4. Provide a timestamp when the server status is changed. See Figure 2.

5. Change the coloring of the server record based on the status (Red for Down, Yellow for Warning, Green for Up). See Figure 1.

Part 1: Create a Server List

1. Create a new Web Form, using your masterpage, called SystemTracker
2. Add a Gridview, with a new SqlDataSource. Call the SqlDataSource ‘Servers’
3. Your GridView will need to display data from multiple tables. To achieve this, you can use the Query Builder inside your DataSource configuration. To get to the Query Builder, select ‘Specify a custom SQL statement or stored procedure’
4. Select the Query Builder button
5. Select the tables that will give you the data necessary to meet requirement #2.
6. Relationships should be built automatically, adjust them if necessary.
7. Select the columns that will give you the data necessary to meet requirement #3. The SELECT and INNER JOIN query will be created automatically.
8. Only create one join between Call_LIst and Employee.
9. Here’s what mine looks like

1. Next, and Finish
2. Auto Format, Enable Paging, Sorting, Selection

1. Save and visit your site on the web, should look something like this

1. In the properties of the GridView, set the DataKeyNames to the primary key of the server table. Mine is called ID. Just type in the column name.

Part 2: Add filtering

1. To meet requirement #1, Add a dropdownlist, databound to your applications table. Make sure to Enable AutoPostBack.
2. After you've completed your dropdownlist, go back and re-Configure your Servers Data Source. Keep this radio button selected

1. Next
2. Add the following to the end of your existing SELECT statement WHERE (Servers.APP_ID = @APP_ID)

1. When you press Next, you’ll be on a screen where you can select Control, and your DropDownList to assign the value to the APP_ID parameter.

1. Next and Finish
2. Save, and visit your page on the web. You should be able to pick an application from your dropdownlist and have the Gridview update, showing only the servers required to run that application.

Part 3: Changing GridView row color depending on the data values

21. Select your Gridview, Change your properties viewer to display Events by clicking on the lightning bolt

22. Double-click in the RowDataBound field. This will take you to the Code-Behind to create a sub to apply logic to trigger your GridView binds to its data source. Paste the following code in the sub.

If e.Row.RowType = DataControlRowType.DataRow Then

Dim ss = e.Row.DataItem

If Trim(ss("STATUS")) = "DOWN" Then

e.Row.BackColor = Drawing.Color.OrangeRed

e.Row.ForeColor = Drawing.Color.Black

ElseIf Trim(ss("STATUS")) = "WARNING" Then

e.Row.BackColor = Drawing.Color.Yellow

e.Row.ForeColor = Drawing.Color.Black

Else

e.Row.BackColor = Drawing.Color.LightGreen

e.Row.ForeColor = Drawing.Color.Black

End If

End If

23. The highlighted text STATUS represents the name of the column that holds the server status.

24. While you’re in the code-behind file go to the very top of the page and add the following line

Imports System.Data.SqlClient

25. Save, and test on the web. Assuming all of your servers were UP when you created them, you might have to change the status on a couple of your servers from your CMDB interface to see if the highlighting is working.

Part 4: Updating Server Status

1. Add a FormView to the lower left of your page. Create a new Data Source to bind it to. Call the Data Source, ChgServerStatus.
2. Select your Servers table, and the columns that will display ID, Name, AppID, STATUS, and Status_Change.

1. Select both Checkboxes.
2. Select the WHERE button to filter on your GridView based on the selected GridView record

1. Once you complete the data source configuration, enter the Edit Templates task of your FormView
2. In the ItemTemplate, delete the links that say New, Update, and Delete. All we want this view to do is to change the server status, so add a button at the bottom of the formview

1. Change the Command Name of the Button to Edit, and change the text to Change Server Status.

1. Enter the EditItemTemplate display
2. Replace the STATUS textbox with a DropDownList databound to your Status table (The one that contains the UP, DOWN, WARNING values). Change the dropdownlist ID to ddlSTATUS
3. Edit the Data Bindings to allow two-way binding to the STATUS field.

1. We don’t want users to be able to change any data except the server status. Change the rest of text boxes in this template to be Read Only. Set the ReadOnly property to True to do this.
2. Should look something like this

1. Save
2. Requirement #5 states we need a timestamp when the Server Status changes. To accomplish this, double-click the Update link on the bottom left of your FormView. Add the following code to the Sub.

Dim conn As SqlConnection

Dim cmd1 As SqlCommand

Dim ddl2 As DropDownList = Me.FormView1.FindControl("ddlStatus")

Dim cmdString1 As String = "Update Servers SET Status_Change = '" & Date.Now & "', STATUS = '" & ddl2.SelectedItem.Value & "' WHERE Servers.ID = " & FormView1.SelectedValue & ""

conn = New SqlConnection("Data Source=yourserverName\INSTANCENAME;Initial Catalog=yourdatabaseName; User ID=sa; Password=yourSApassword")

‘'the previous command is should be on one line. Replace the info in green with your database information.

cmd1 = New SqlCommand(cmdString1, conn)

conn.Open()

cmd1.ExecuteNonQuery()

conn.Close()

1. Back in Design View, End the FormView Template Editing.
2. View the Events (lightning bolt) for your FormView. Double-click on the ItemUpdated field.
3. Add this line to the code-behind to make the Gridview refresh when the server status is updated. GridView1.DataBind()
4. Save. Run on the Web. Try to update a server status. You should see the status change, the timestamp get added, and the Gridview update colors reflecting the new server status.