| Downloads |
|---|
| 96568.zip |
Executive Summary:
| Because SNMP runs over the Internet, it can pose a security risk if it's improperly configured on Microsoft Windows servers. ServerAlerts.vbs uses Microsoft Windows Management Instrumentation (WMI) to check Windows servers for SNMP security vulnerabilities. For each server it checks, ServerAlerts.vbs reports its findings in a Microsoft Excel worksheet whose tab is colorized to denote the server's status. An additional Microsoft Excel worksheet includes hyperlinks to the server worksheets; the hyperlinks are color coded and grouped so you can easily spot problematic servers. |
Because SNMP runs over the Internet, it can pose a security risk if it's improperly configured. I wrote ServerAlerts.vbs to check servers for SNMP security vulnerabilities. Specifically, it checks the SNMP community string. Community strings define what can be done on a server through SNMP. By default, out-of-the-box SNMP implementations typically use the "public" community string, which is configured to let a remote device retrieve information from a server (i.e., Read-Only privileges) and the "private" community string, which is configured to let a remote device read and modify settings on that server (Read-Write privileges). To secure servers, you should customize the community strings and typically use only No Access or Read-Only privileges.
After retrieving a list of target servers from an input file, ServerAlerts.vbs queries each server to see whether the SNMP service is running and checks the SNMP community string. The script writes this server's information to a Microsoft Excel worksheet and changes the color of the worksheet's tab to coincide with its status. I picked colors that I readily associate with varying degrees of importance:
- Red (color index number 3) indicates a serious problem. The server's SNMP service is running, and the community string is "public" or "private". From a security standpoint, this server needs immediate attention.
- Yellow (color index number 6) specifies there’s a potentially serious problem. The server's SNMP service isn't running, and the community string is "public" or "private".
- Magenta (color index number 7) specifies the server is unreachable or nonexistent. It might mean there’s a connectivity issue, the server is no longer in use, or there's a typo in the input file.
- Cyan (color index number 8) indicates a nonsecurity-related problem with the SNMP service. The server's SNMP service isn't running, but the SNMP community string is secure (i.e., not "public" or "private").
- Black (color index number 1) indicates that SNMP isn’t installed on the server.
- Green (color index number 4) indicates there are no problems. The server's SNMP service is running, and the SNMP community string is secure (i.e., not "public" or "private").
You can easily change these colors if desired. I've included alternative color index numbers in the script.
What I consider the major highlight of ServerAlerts.vbs didn't originally exist in my first version but came shortly afterward out of necessity. Initially, the Excel worksheets weren't organized in any manner. Instead, they were in the same order as the computer names in the input file. This meant that, although the worksheets were colored-coded and contained all the pertinent information, I had to scroll through all of them until I found the worksheet I wanted to review.
To make accessing the worksheets easier, I added code to create an extra worksheet that contains a hyperlink to each server worksheet. These hyperlinks are particularly helpful when you have to find a specific server's worksheet among hundreds of server worksheets. I also colored each hyperlink so that it matches the color of the worksheet's tab and grouped the hyperlinks and worksheets by color. With the hyperlinks grouped by color, you can quickly see the problematic servers.
Before I discuss ServerAlerts.vbs further , I need to state that my main intent is to demonstrate how you can use color and hyperlinks to organize a script's output in Excel. I use searching for SNMP community strings as an example and won't be discussing SNMP security itself. Instead, I will discuss how ServerAlerts.vbs prepares for its main loop, what the main loop does, and how the script creates the hyperlink worksheet.
The Preparations
ServerAlerts.vbs begins by declaring and setting the variables and constants it will use to retrieve registry information, sort the worksheets, and define color settings. One variable it sets is SKP, which is short for subkey path. This variable holds the registry path SYSTEM\CurrentControlSet\Services\SNMP\Parameters\ValidCommunities, which the script uses to find the SNMP community strings. Later in the script, the subtree HKEY LOCAL MACHINE is added to form a complete path.
Next, ServerAlerts.vbs creates three objects:
- A Dictionary object. The Dictionary object, which is set to the ServerAlerts variable, holds all the server names and their respective color index numbers in key-item pairs. The key is the server's name, and the item is the color index number for that server.
- A FileSystemObject object. This object is used to open the input file and read its contents into the SrvList variable. Using the Split function, the script creates an array of server names from SrvList's contents and assigns that array to the aray variable.
- An Excel Application object. This object is used to create a separate worksheet for every server in the input list and the hyperlink worksheet.
The Main Loop
With the preparations done, ServerAlerts.vbs begins its main loop, which Listing 1 shows. This For Each…Next statement processes the servers in the aray variable. For each server, the script retrieves the SNMP community string, determines whether the SNMP service is running, assigns an appropriate alert color, and creates a key-item pair.
Retrieving the SNMP community string. The main loop begins by clearing any runtime errors and setting the PBL variable to False. If the script finds that a server has an SNMP community string of "public" or "private" in the registry, it sets the PBL variable to True, thereby flagging the server.
In callout A in Listing 1, the script uses VBScript's Len function to see whether the array element currently being processed is empty. If the array element is empty, the script simply loops and gets the next array element. If the array element contains a value, the script creates a new worksheet, naming it after the value stored in the array element (i.e., the server's name). Then, as callout B shows, the script creates a Windows Management Instrumentation (WMI) StdRegProv class object to connect to that server's registry.
If there’s a problem creating the object—that is, if the server is unreachable—the script writes the error number and description to the worksheet. It also sets the worksheet tab color to UNREACHABLE, which the script defined earlier as magenta. Finally, it creates a ServerAlerts key-item pair and loops back to get the next server name.
If the registry object is successfully created—that is, if the server is reachable—the script uses the StdRegProv class's EnumValues method to check whether the SNMP community string is "public" or "private", as callout C shows. EnumValues has two optional input parameters: the subtree (in this case, HKEY_LOCAL_MACHINE) and the subkey path (in this case, the path in SKP). This method also has two optional output parameters: one returns an array of the subkey's value names and the other returns an array of the subkey's data types. The script sets the value names and value types to the ValueNames and ValueTypes variables, respectively. The script then checks ValueNames to see whether it contains the value "public" or "private" . If either value is found, the script sets the PBL variable to True.
Next, the script retrieves and writes the value to the worksheet. Because I adapt this script for different tasks that require checking a variety of registry keys, the Select Case statement in callout D is needed. This statement compares each data type in ValueTypes against the possible registry data types. When a match is found, the script applies the appropriate StdRegProv class method to retrieve the value, then writes that value to the worksheet.
Determining whether the SNMP service is running. To check whether the SNMP service is running, the script calls the checkSNMP subroutine, as callout E in Listing 1 shows. However, before doing so, it sets the strService variable to SNMP.
Listing 2 shows checkSNMP. This subroutine uses WMI's Win32_Service class to query the target server for its services. The query specifically looks for services named SNMP (which the strService variable holds) and assigns the results to the colItems variable. The subroutine then sets the SNMPInstalled variable to False. If checkSNMP finds that the SNMP service is running, it sets SNMPInstalled to True, thereby flagging the server.
As callout A in Listing 2 shows, checkSNMP uses a For Each…Next loop to iterate through each item in the colItems variable . Because the WMI query specifically sought the SNMP service, the loop executes only when the SNMP service is installed on the server. When the service exists, checkSNMP sets the SNMPInstalled flag to True, then gathers and writes following information to the worksheet:
- Whether the service has been started. In the worksheet, the value will be either True or False.
- The service's StartMode. The most common modes are Automatic, Manual, and Disabled.
- The service's state. The most common states are Running or Stopped.
Assigning an appropriate alert color. A server's alert color is mainly determined by whether the SNMP service is running and whether the SNMP community string is "public" or "private" (i.e., the PBL variable's value is True). So, the code at callout B in Listing 2 sets the worksheet tab color by following these rules:
- If the service is running and PBL is True, set the color to red.
- If the service isn't running and PBL is True, set the color to yellow.
- If the service isn't running and PBL is False, set the color to cyan.
- If the service isn't installed (i.e., SNMPInstalled is False), set the color to black.
- If the service is running and PBL is False, set the color to green.
In callout B, the lines that set the worksheet tab color look like
XL.ActiveWorkbook.Sheets(computer).Tab.ColorIndex = CRITICAL
In this code, computer is a variable that contains the name of the computer from the array of computer names. Because the worksheets are named after the computers, computer can be used to refer to the worksheet name as well. CRITICAL is also a variable. It contains a constant value of 3, which represents red in the color index.
Creating a key-item pair. The last task that the script performs in the main loop is to create a key-item pair for the target computer and add it to ServerAlerts. As callout F in Listing 1 shows, the Dictionary's object's Add method is used to add the server's name as the key and the servers' color index number as the item.
After the For Each…Next loop processes the last server in the aray variable, you have a spreadsheet full of worksheets and a Dictionary object (ServerAlerts) full of server names and worksheet tab colors. The script uses this Dictionary object to create the hyperlinks to the server worksheets.
Creating the Hyperlink Worksheet
Now it’s time to create the worksheet named Hyperlinks. This worksheet has a hyperlink for every server in the input file, and each hyperlink is the color of the server's worksheet tab. With the colored hyperlinks, you can easily identify the problematic servers and quickly access those servers' worksheets.
Listing 3 shows the code that creates and populates the Hyperlinks worksheet. This code begins by adding the Hyperlinks worksheet to the spreadsheet, after which it sets the Row variable to 1. This variable is used to keep track of the row currently being used in the worksheet.
The script then imports the ServerAlerts variable's contents into two arrays. The arrKeys array holds the server names. The arrItems array holds the tab colors. This was the easiest way I found to access both elements in the key-item pairs.
Next, a For…Next statement populates the Hyperlinks worksheet. This code might look a bit tricky, but it's fairly straightforward if you take it line by line. The code
For i = 0 To ServerAlerts.Count - 1
sets the starting and ending counter values for the loop. Because arrays are zero based, the counter (i.e., the i variable) starts at 0 and ends at the number of elements in the ServerAlerts variable minus 1.
The code
XL.Cells(row,1).Value = arrKeys(i)
writes the server name in arrKeys(i) to the Hyperlinks worksheet. The script then assigns that server's worksheet tab color to the idxcolor variable:
idxcolor = arrItems(i)
Next, the script uses the code
HLink = Chr(39) & arrKeys(i) & Chr(39) _ & "!a1"
to create the hyperlink to the server worksheet and assign it to the HLink variable. Note the concatenation of the Chr(39) function before and after the server name in arrKeys(i) . Chr(39) returns a single quote (') during runtime. Enclosing the server name in single quotes is necessary when there's a hyphen in it. If you don’t enclose a hyphenated server name in single quotes, the hyperlink will not work. Enclosing a nonhyphenated server name in single quotes doesn't affect the hyperlink at all (i.e., it still works), so the script automatically encloses all server names in single quotes. (If you find a hyperlink not working because of some character in it, try enclosing the hyperlink in single quotes. It might just do the trick.) The "!a1" code at the end specifies the cell to link to. So, when you click a hyperlink, you end up in the worksheet that has the same name as the hyperlink and the cursor will be sitting in cell A1.
With the hyperlink in HLink, it's time to add it to the appropriate cell in the worksheet. All the hyperlinks are in column A, so that value is hard-coded into the script. The Row variable specifies the row. In the first iteration through the loop, the Row variable's value is 1, which was set earlier. At the end of each pass through the For…Next loop, that value is incremented by 1. So, the code
XL.Range("A" & Row).Select
selects the cell to add the hyperlink to. The script then adds the hyperlink to the selected cell with the code
XL.ActiveSheet.Hyperlinks.Add _ XL.Selection,"",HLink, _ "Link to " & arrKeys(i)
The last part of this code specifies the tooltip that appears if you hover over the hyperlink with your mouse. For example, if the server name is DomainController1, the tooltip would read Link to DomainController1.
Next, the script makes the hyperlink the same color as the server worksheet tab. Because the idxcolor variable contains that color, the script simply sets hyperlink cell's interior color to the color specified in idxcolor and changes the hyperlink text's font color to boldfaced white. I found setting the cell's interior color is better than setting the font color because yellow and cyan hyperlink text doesn’t show up well. Note that the script also populates the cell to the right of the hyperlink with the value of idxcolor. This cell is used to sort the hyperlinks by color. Later, the script ends up hiding this column because it’s distracting.
On rare occasions, the script doesn't set a worksheet tab color, in which case idxcolor contains a value of -4142. The code in callout A in Listing 3 tests for this value. If found, the script changes the hyperlink text to a boldfaced blue font and leaves the cell's interior at the default color of white.
After the For…Next loop populates the Hyperlinks worksheet with colorized hyperlinks, the script sorts the hyperlinks, sorts the worksheets, and adds a legend. The code at callout B in Listing 3 sorts the hyperlinks by color. It does this by sorting in ascending order the idxcolor value that was assigned to the cell to the right of the hyperlink.
To sort the worksheets, the script calls the SheetSorter subroutine, which Listing 4 shows. SheetSorter arranges the server worksheets in the same order in which the hyperlinks appear in the Hyperlinks worksheet.
The SheetSorter subroutine begins by using the SpecialCells method with the value of 11 (which represents the xlCellTypeLastCell constant) to select the last cell in the specified range. As callout A in Listing 4 shows, the subroutine selects the last hyperlink cell in the column containing the hyperlinks. Because the hyperlink text is the same as the worksheet's name, SheetSorter uses the hyperlink cell's value to move the corresponding worksheet into the second worksheet position. (The Hyperlinks worksheet is always the first worksheet in the spreadsheet.) The subroutine then moves to the second last hyperlink cell to get that worksheet's name and moves that worksheet into the second position. The worksheet that was previously moved shifts one place to the right (i.e., the third position). This process repeats until SheetSorter steps backward through all the hyperlink cells.
To add the legend, the script calls the ShowLegend subroutine, which Listing 5 shows. This subroutine displays a legend that shows what each color represents. I found that the legend adds a nice finishing touch and helps bring the server report together.
Another helpful finishing touch you might want to consider is adding a Back button to your Excel toolbar. A Back button makes it much easier to get back to the Hyperlinks worksheet after you’ve entered another worksheet via a hyperlink. In Excel 2003, you can add the Back button by selecting Customize under the Tools menu, then clicking the Commands tab. Under the Categories section, select Web, then find the Back icon in the Commands section. Drag the icon onto your toolbar. If you don't want to add a Back button to Excel, you can use the scroll bar at the bottom left corner of the worksheet to get back to the beginning of your worksheets, then click the Hyperlinks worksheet tab.
A Versatile Reporting Tool
I wrote ServerAlerts.vbs to check servers running Windows Server 2003 and Windows 2000. The machine on which I run this script has Windows XP and Excel 2003 installed. It might not work with different versions of Excel because of differences in functionality.
You can adapt ServerAlerts.vbs to check a variety of services on your servers. For example, I use variations of the script to determine whether our antivirus service is running, to check on date and version information of antivirus signature files, and to make sure certain critical application processes and services are running. No matter what service or process you're checking, the Excel report that the script produces makes it easy for you to spot problematic servers.