asked on

VBScript - How to convert massive If/Then script to lookup array?

Experts,

I've created a monster and pasted in the code block below (Scrubbed for security).

For posting on EE, I truncated the If/Then line statements down to only a couple entries. The real code has probably 100 or more If/Then commands.

I need to make it actually managable, and convert the way the script is now - into some sort of lookup array. Basically, I'm hoping to define an excel style table within the VBScript for looking up the data, or something similar.

Any ideas on how I could do something like that would be appreciated.

'User Defined Variables

    'Script logging
        LogFileLocation = "C:\Windows\System32\LogFiles"
        LogFileName = "Variables.log"
        LogFullFileName = LogFileLocation & "\" & LogFileName

    'Site Specific Servers
        StandardSiteAppServer = "-" & "APP01"
        StandardSiteDHCPServer = "-" & "DHCP01"
        StandardSiteDNSServer = "-" & "DNS01"
        StandardSiteDSServer = "-" & "DS02"
        StandardSiteEPServer = "-" & "EPS01"
        StandardSiteFastServer = "-" & "FAST01"
        StandardSiteFileServer = "-" & "FILE01"
        StandardSiteMgmtServer = "-" & "MGMT02"
        StandardSiteMonServer = "-" & "MON01"
        StandardSiteNSServer = "-" & "NS01"
        StandardSiteSQLServer = "-" & "SQL01"
        StandardSiteVCServer = "-" & "VC01"
        StandardSiteWSUSServer = "-" & "WSUS01"
        StandardSiteExchangeServer = "-" & "EX02"
        StandardSiteNASServer = "-" & "NAS01"
        StandardSiteIAServer = "-" & "IAR01"

    'Misc information
        ScriptTitle = " "

' ------------------------------------------------------

'Declare Basic Functions

    'Basic logging functions

        Function LogSubStart
            objLogFile.WriteLine "----------------------------------------------------"
            objLogFile.WriteLine "Initializing Sub-Routine at " & Now() & "."
            LogLineBreak
          End Function

        Function LogSubEnd
            LogLineBreak
            objLogFile.WriteLine "Process Completed at " & Now() & "."
          End Function

        Function LogLineBreak
	    objLogFile.WriteLine " "
          End Function

' ------------------------------------------------------

'Remove Previous and Create New Log File Session

    Set objFSO = CreateObject("Scripting.FileSystemObject")

    If objFSO.FileExists(LogFullFileName) Then
	    objFSO.DeleteFile (LogFullFileName), DeleteReadOnly
    End If

    Set objLogFile = objFSO.OpenTextFile(LogFullFileName,8,True)

        objLogFile.WriteLine "****************************************************"
        objLogFile.WriteLine "*** " & ScriptTitle & " ***"
        objLogFile.WriteLine "****************************************************"
        LogLineBreak
        objLogFile.WriteLine "Log created on " & Now()
        LogLineBreak

' ------------------------------------------------------

'Standard Environment Variables

  LogSubStart

        objLogFile.WriteLine "Setting standard Environment Variables..."

    Computer = "."

    CONST HKEY_LOCAL_MACHINE = &H80000002

    Set objWMIService = GetObject("winmgmts:\\" & Computer & "\root\CIMV2")

    Set objVariable = objWMIService.Get("Win32_Environment").SpawnInstance_

        objLogFile.WriteLine "Standard Environment Variables set."

  LogSubEnd

' ------------------------------------------------------

'Retrieve Unit's IP Address

  LogSubStart

        objLogFile.WriteLine "Retreiving IP Address of unit..."

    Set colItems = objWMIService.ExecQuery( _
        "SELECT * FROM Win32_NetworkAdapterConfiguration",,48)

    For Each objItem in colItems 
        If isNull(objItem.IPAddress) Then
        Else
            IPAddFull = Join(objItem.IPAddress, ",")

        objLogFile.WriteLine "  Variable returned:  " & IPAddFull        

        sIPAdd = Split(IPAddFull,".")

            IPAdd_Octet1 = sIPAdd(0)
            IPAdd_Octet2 = sIPAdd(1)
            IPAdd_Octet3 = sIPAdd(2)
            IPAdd_Octet4 = sIPAdd(3)

                If IPAdd_Octet1 = 10 Then
                    objLogFile.WriteLine "    Initial IP Octet acceptable - processing..."
                        Select Case IPAdd_Octet2
                            Case "232"
                                Complex = "1"
                                    If IPAdd_Octet3 >= 224 And IPAdd_Octet3 <= 255 Then
                                        Site = "A"
                                    Else
                                        SIte = "B"
                                    End If
                            Case "244"
                                Complex = "2"
                                    If IPAdd_Octet3 >= 224 And IPAdd_Octet3 <= 255 Then
                                        Site = "C"
                                    Else
                                        If IPAdd_Octet3 >= 96 And IPAdd_Octet3 <= 127 Then
                                            Site = "D"
                                        Else
                                            If IPAdd_Octet3 >= 224 And IPAdd_Octet3 <= 255 Then
                                                Site = "E"
                                            Else
                                                Site = "UNKNOWN"
                                            End If
                                        End If
                                    End If
                            Case "245"
                                Complex = "3"
                                    If IPAdd_Octet3 >= 224 And IPAdd_Octet3 <= 255 Then
                                        Site = "F"
                                    Else
                                        If IPAdd_Octet3 >= 32 And IPAdd_Octet3 <= 63 Then
                                            Site = "G"
                                        Else
                                            If IPAdd_Octet3 >= 160 And IPAdd_Octet3 <= 191 Then
                                                Site = "H"
                                            Else
                                                Site = "UNKNOWN"
                                            End If
                                        End If
                                    End If
                Else
                    objLogFile.WriteLine "    Initial IP Octet out of range - Terminating."
                    WScript.Quit
                End If

        If Site <> "UNKNOWN" Then
            SiteName = Site
            SiteAppServer = Site & StandardSiteAppServer
            SiteDHCPServer = Site & StandardSiteDHCPServer
            SiteDNSServer = Site & StandardSiteDNSServer
            SiteDSServer = Site & StandardSiteDSServer
            SiteEPServer = Site & StandardSiteEPServer
            SiteFastServer = Site & StandardSiteFastServer
            SiteFileServer = Site & StandardSiteFileServer
            SiteMgmtServer = Site & StandardSiteMgmtServer
            SiteMonServer = Site & StandardSiteMonServer
            SiteNSServer = Site & StandardSiteNSServer
            SiteSQLServer = Site & StandardSiteSQLServer
            SiteVCServer = Site & StandardSiteVCServer
            SiteWSUSServer = Site & StandardSiteWSUSServer
            SiteExchangeServer = "PAC" & "-" & Site & StandardSiteExchangeServer
            SiteNASServer = Site & StandardSiteNASServer
            SiteIAServer = Site & StandardSiteIAServer
        Else
            objLogFile.WriteLine "      Unable to determine site.  Terminating."
            WScript.Quit
        End If

        objLogFile.WriteLine "      Complex identified:  " & Complex
        objLogFile.WriteLine "      Site identified:  " & Site

    Select Case Site
        Case "5"
            SiteSQLServer = Site & "-" & "SQL12"
        Case "9"
            SiteSQLServer = Site & "2"
        Case "15"
            SiteSQLServer = Site & "2"
        Case "16"
            SiteSQLServer = Site & "2"
        Case "17"
            SiteSQLServer = Site & "2"
        Case "18"
            SiteSQLServer = Site & "2"
    End Select

        objLogFile.WriteLine "        Site Name:  " & SiteName
        objLogFile.WriteLine "        Application Server:  " & SiteAppServer
        objLogFile.WriteLine "        DHCP Server:  " & SiteDHCPServer
        objLogFile.WriteLine "        DNS Server:  " & SiteDNSServer
        objLogFile.WriteLine "        Deployment Server:  " & SiteDSServer
        objLogFile.WriteLine "        EP Server:  " & SiteEPServer
        objLogFile.WriteLine "        Fast Server:  " & SiteFastServer
        objLogFile.WriteLine "        FileServer:  " & SiteFileServer
        objLogFile.WriteLine "        Management Server:  " & SiteMgmtServer
        objLogFile.WriteLine "        Monitoring Server:  " & SiteMonServer
        objLogFile.WriteLine "        Notification Server:  " & SiteNSServer
        objLogFile.WriteLine "        SQL Server:  " & SiteSQLServer
        objLogFile.WriteLine "        VC Server:  " & SiteVCServer
        objLogFile.WriteLine "        WSUS Server:  " & SiteWSUSServer
        objLogFile.WriteLine "        Exchange Server:  " & SiteExchangeServer
        objLogFile.WriteLine "        NAS Server:  " & SiteNASServer
        objLogFile.WriteLine "        IA Server:  " & SiteIAServer

        objLogFile.WriteLine "            Setting environmental variables..."

            objVariable.Name = "SiteName"
            objVariable.UserName = "<System>"
            objVariable.VariableValue = SiteName
            objVariable.Put_

            objVariable.Name = "SiteAppServer"
            objVariable.UserName = "<System>"
            objVariable.VariableValue = SiteAppServer
            objVariable.Put_

            objVariable.Name = "SiteDHCPServer"
            objVariable.UserName = "<System>"
            objVariable.VariableValue = SiteDHCPServer
            objVariable.Put_

            objVariable.Name = "SiteDNSServer"
            objVariable.UserName = "<System>"
            objVariable.VariableValue = SiteDNSServer
            objVariable.Put_

            objVariable.Name = "SiteDSServer"
            objVariable.UserName = "<System>"
            objVariable.VariableValue = SiteDSServer
            objVariable.Put_

            objVariable.Name = "SiteEPServer"
            objVariable.UserName = "<System>"
            objVariable.VariableValue = SiteEPServer
            objVariable.Put_

            objVariable.Name = "SiteFastServer"
            objVariable.UserName = "<System>"
            objVariable.VariableValue = SiteFastServer
            objVariable.Put_

            objVariable.Name = "SiteFileServer"
            objVariable.UserName = "<System>"
            objVariable.VariableValue = SiteFileServer
            objVariable.Put_

            objVariable.Name = "SiteMgmtServer"
            objVariable.UserName = "<System>"
            objVariable.VariableValue = SiteMgmtServer
            objVariable.Put_

            objVariable.Name = "SiteMonServer"
            objVariable.UserName = "<System>"
            objVariable.VariableValue = SiteMonServer
            objVariable.Put_

            objVariable.Name = "SiteMonServer"
            objVariable.UserName = "<System>"
            objVariable.VariableValue = SiteMonServer
            objVariable.Put_

            objVariable.Name = "SiteNSServer"
            objVariable.UserName = "<System>"
            objVariable.VariableValue = SiteNSServer
            objVariable.Put_

            objVariable.Name = "SiteSQLServer"
            objVariable.UserName = "<System>"
            objVariable.VariableValue = SiteSQLServer
            objVariable.Put_

            objVariable.Name = "SiteVCServer"
            objVariable.UserName = "<System>"
            objVariable.VariableValue = SiteVCServer
            objVariable.Put_

            objVariable.Name = "SiteWSUSServer"
            objVariable.UserName = "<System>"
            objVariable.VariableValue = SiteWSUSServer
            objVariable.Put_

            objVariable.Name = "SiteExchangeServer"
            objVariable.UserName = "<System>"
            objVariable.VariableValue = SiteExchangeServer
            objVariable.Put_

            objVariable.Name = "SiteNASServer"
            objVariable.UserName = "<System>"
            objVariable.VariableValue = SiteNASServer
            objVariable.Put_

            objVariable.Name = "SiteIAServer"
            objVariable.UserName = "<System>"
            objVariable.VariableValue = SiteIAServer
            objVariable.Put_

        objLogFile.WriteLine "            Environmental variables set."

    End If
Next

  LogSubEnd

' ------------------------------------------------------

        objLogFile.WriteLine "****************************************************"
        LogLineBreak
        objLogFile.WriteLine "Script Execution Complete."
        LogLineBreak

        objLogFile.WriteLine "Closing Log on " & Date() & " " & Time()
        objLogFile.Close

Open in new window

Bill Prew

I don't understand what IF you are trying to simplify?

~bp

usslindstrom

ASKER

These...

If IPAdd_Octet3 >= 224 And IPAdd_Octet3 <= 255 Then
Site = "C"
Else
If IPAdd_Octet3 >= 96 And IPAdd_Octet3 <= 127 Then
Site = "D"
Else
If IPAdd_Octet3 >= 224 And IPAdd_Octet3 <= 255 Then
Site = "E"
Else
Site = "UNKNOWN"
End If
End If
End If

There are about 100 or more in the real script.

Bill Prew

So, if we took an approach like this, would that make sense (before I write the actual code of the functions).

' First, load data values into array
AddSite("C", 224, 255)
AddSite("D", 96, 127)
AddSite("E", 244, 255) ' WHY IS THIS THE SAME RANGE AS "C" ???
. . .

Then, down below we use a funtion to lookup the site based on the octet, like this:

Site = GetSite(IPAdd_Octet3)

Would that work?

~bp

usslindstrom

ASKER

Ah - I think I can see the process of where you're going... Yes - your solution would be a great implentation of this idea.

For your question on why a couple of the 3rd octets matched... It's because we have multiple sites with standardized IP ranges. In those cases, the 2nd octet is different. In my original script I used case statements for those:

Select Case IPAdd_Octet2
Case "232"

usslindstrom

ASKER

Ah - and I think you caught a typo with the "duplicate" you brought to my attention. :) LOL

I guess it just proves that I really do need to make this more "managable". :)

Bill Prew

Would it make any sense to handle all 3 octets in this single function call, something like:

Site = GetSite(IPAdd_Octet1, IPAdd_Octet2, IPAdd_Octet3)

although it looks like there is another "return" needed for the variable named "Complex" ? Can't return two values with a Function, but we could with a sub if we passed Complex and Site variables by reference.

~bp

Bill Prew

And then, based on your original code, the initialization would go something like this:

AddSite 10, 232, 001, 223, "A", 1
AddSite 10, 232, 224, 255, "B", 1
AddSite 10, 244, 224, 255, "C", 2
AddSite 10, 244, 96, 127, "D", 2
AddSite 10, 244, 224, 255, "E", 2
AddSite 10, 245, 224, 255, "F", 3
AddSite 10, 245, 32, 63, "G", 3
AddSite 10, 245, 160, 191, "H", 3

~bp

usslindstrom

ASKER

Sure. Any solution you're suggesting would be more than perfect.

I don't need the "Complex" variable at all, it was just how my brain processed the original solution to this when I coded it the first time. The only variable I'm actually interested in, is the "A, B, C" SiteCode after the fact.

Having them all as a single function call would be perfect. Much easier to manage in the future too.

So... Apart from my lack of knowledge on the "cool" features of VB, how would I go about handling this?

Bill Prew

I'll work up the supporting functions.

~bp

ASKER CERTIFIED SOLUTION

Bill Prew

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

usslindstrom

ASKER

This code is absolutely amazing!

This will be exactly what I need. To be sure though, I would use the following in my implentation:

GetSite(IPAdd_Octet0, IPAdd_Octet1, IPAdd_Octet2, IPAdd_Octet3)

?

usslindstrom

ASKER

Er.. I meant variables IPAdd_Octet1-4

usslindstrom

ASKER

***My apologies, but I'm having some mass-confusion here.

I've adapted what you've given me, and am about 99% complete with implementing it. I'm having the following issues though:

The code doesn't work using the variables I have assigned. But does when I manually type the numbers in. It's hard to explain, so I'll throw up both outputs of the version of code and what gets logged in my output file...

***VARIABLE VERSION***

        objLogFile.WriteLine "Retreiving IP Address of unit..."

    Set colItems = objWMIService.ExecQuery( _
        "SELECT * FROM Win32_NetworkAdapterConfiguration",,48)

    For Each objItem in colItems 
        If isNull(objItem.IPAddress) Then
        Else
            IPAddFull = Join(objItem.IPAddress, ",")

        objLogFile.WriteLine "  Variable returned:  " & IPAddFull        

        sIPAdd = Split(IPAddFull,".")

            IPAdd_Octet1 = sIPAdd(0)
            IPAdd_Octet2 = sIPAdd(1)
            IPAdd_Octet3 = sIPAdd(2)

            objLogFile.WriteLine "      Processing the following internal function:  GetSite(" & IPAdd_Octet1 & "," & IPAdd_Octet2 & "," & IPAdd_Octet3 & ")"

                 objLogFile.WriteLine "            First Octet:  " & IPAdd_Octet1
                 objLogFile.WriteLine "            Second Octet:  " & IPAdd_Octet2
                 objLogFile.WriteLine "            Third Octet:  " & IPAdd_Octet3

                 Site = GetSite(IPAdd_Octet1, IPAdd_Octet2, IPAdd_Octet3)

---------------

Retreiving IP Address of unit...
  Variable returned:  10.228.108.120
      Processing the following internal function:  GetSite(10,228,108)
            First Octet:  10
            Second Octet:  228
            Third Octet:  108
      Unable to determine site.  Terminating.




***HARD CODED VERSION***

        objLogFile.WriteLine "Retreiving IP Address of unit..."

    Set colItems = objWMIService.ExecQuery( _
        "SELECT * FROM Win32_NetworkAdapterConfiguration",,48)

    For Each objItem in colItems 
        If isNull(objItem.IPAddress) Then
        Else
            IPAddFull = Join(objItem.IPAddress, ",")

        objLogFile.WriteLine "  Variable returned:  " & IPAddFull        

        sIPAdd = Split(IPAddFull,".")

            IPAdd_Octet1 = sIPAdd(0)
            IPAdd_Octet2 = sIPAdd(1)
            IPAdd_Octet3 = sIPAdd(2)

            objLogFile.WriteLine "      Processing the following internal function:  GetSite(" & IPAdd_Octet1 & "," & IPAdd_Octet2 & "," & IPAdd_Octet3 & ")"

                 objLogFile.WriteLine "            First Octet:  " & IPAdd_Octet1
                 objLogFile.WriteLine "            Second Octet:  " & IPAdd_Octet2
                 objLogFile.WriteLine "            Third Octet:  " & IPAdd_Octet3

                 Site =  GetSite(10,228,108)

---------------

Retreiving IP Address of unit...
  Variable returned:  10.228.108.120
      Processing the following internal function:  GetSite(10,228,108)
            First Octet:  10
            Second Octet:  228
            Third Octet:  108
      Site identified:  DPDO
        Site Name:  DPDO
            Deployment Server:  DPDO-DS02
            File Server:  DPDO-FILE01
            NAS Server:  DPDO-NAS01
            SQL Server:  DPDO-SQL12
                Setting environmental variables...
                Environmental variables set.

Open in new window

Bill Prew

To try and debug this a bit, can you change this line:

Site = GetSite(10,228,108)

to:

Site = GetSite("10","228","108")

in your hard coded test.

~bp

usslindstrom

ASKER

I'll run this on Mon and keep you informed.

I was playing around with quotes in some of my tests, but couldn't get very far. Either or, I'll let ya' know once I get back to the Office in the AM on Mon.

usslindstrom

ASKER

When I quote the numbers in that line I get the same output as the variables, where it can't continue.

Retreiving IP Address of unit...
Variable returned: 10.228.108.120
Processing the following internal function: GetSite(10,228,108)
First Octet: 10
Second Octet: 228
Third Octet: 108
Unable to determine site. Terminating.

I'm guessing I need to DIM the variables as integers I'm thinking...

usslindstrom

ASKER

That was absolutely it!

This script fully worked by converting the Strings to Integers with adding these lines after the original variable definitions:

IPAdd_Octet1 = CInt(IPAdd_Octet1)
IPAdd_Octet2 = CInt(IPAdd_Octet2)
IPAdd_Octet3 = CInt(IPAdd_Octet3)

This script works like a champ! Much appreciated!

usslindstrom

ASKER

I can't thank you enough for your assistance here.

It's much appreciated!

Bill Prew

Very welcome, thanks.

~bp