Multiple conditions for Select Case using ChassisType
We use MDT in our environment to image computers. We are using a script in order to name computers based on IP location and the script then writes the data to a database. I need to modify the script to include a section of additional conditions to construct the computer name. I am having difficulty with the portion that uses the additional condition of Chassis type in the script below.
The portion I need help with is
Case (InStr(1, strIP, ".111.")>0),(strChassisType = 8,9 Or 10)
strSiteCode="UKL"
Case (InStr(1, strIP, ".112.")>0),(strChassisType = 8,9 Or 10)
strSiteCode="NYL"
Case (InStr(1, strIP, ".113.")>0),(strChassisType = 8,9 Or 10)
strSiteCode="HKL"
I would like to have these three conditions work and set the site code based on the condition of Site and then chassis type, but I am not sure how to use multiple conditions or how to combine the chassis type and would like guidance.
Following is the full script
Function UserExit(sType, sWhen, sDetail, bSkip)
oLogging.CreateEntry "entered UserExit ", LogTypeInfo
UserExit = Success
End Function
Function computerName()
'On Error Resume Next
Dim strSerial,strAsset, strManufacturer, strIP, strSiteCode
Set WshNetwork = WScript.CreateObject("WScript.Network")
Set WSHShell = CreateObject("WScript.Shell")
'----Establish SQL Connection----
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=sqloledb;Data Source=XXXXXXXX;Initial Catalog=NetMetrics;User Id=netmetrics;Password=*********;"
'----Determine local Service & Asset Tags----
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colSMBIOS = objWMIService.ExecQuery _
("Select * from Win32_SystemEnclosure")
For Each objSMBIOS In colSMBIOS
strManufacturer = objSMBIOS.Manufacturer
strSerial = objSMBIOS.SerialNumber
Next
'----Determine Site Code based upon IP address----
Set IPConfigSet = objWMIService.ExecQuery _
("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled=TRUE")
For Each IPConfig In IPConfigSet
If Not IsNull(IPConfig.IPAddress) Then
For i = LBound(IPConfig.IPAddress) To UBound(IPConfig.IPAddress)
strIP = strIP + IPConfig.IPAddress(i)
Next
End If
Next
Set colChassis = objWMIService.ExecQuery _
("Select * from Win32_SystemEnclosure")
For Each objChassis in colChassis
For Each strChassisType in objChassis.ChassisTypes
Select Case strChassisType
Select Case True
Case (InStr(1, strIP, ".111.")>0)
strSiteCode = "UK"
Case (InStr(1, strIP, ".112.")>0)
strSiteCode = "NY"
Case (InStr(1, strIP, ".113.")>0)
strSiteCode = "HK"
Case (InStr(1, strIP, ".111.")>0),(strChassisType = 8,9 Or 10)
strSiteCode = "UKL"
Case (InStr(1, strIP, ".112.")>0),(strChassisType = 8,9 Or 10)
strSiteCode = "NYL"
Case (InStr(1, strIP, ".113.")>0),(strChassisType = 8,9 Or 10)
strSiteCode = "HKL"
End Select
If (inStr(1,strManufacturer,"Dell")) Then
strSQLQuery = "select count(*) from AssetTags where ServiceTag='" & strSerial & "'"
priorEntry = objConnection.Execute(strSQLQuery)
If priorEntry(0) = 0 Then
strSQLQuery = "select right(concat('00',right(max(assettag),3)+1),3) from AssetTags where AssetTag like '" & strSiteCode & "[^S]%'"
Set arrNewTag = objConnection.Execute(strSQLQuery)
strSQLQuery = "INSERT INTO AssetTags Values ('" & strSerial & "','"& strSiteCode & arrNewTag(0) & "', 'New')"
objConnection.Execute(strSQLQuery)
computerName=(strSiteCode & arrNewTag(0))
Else
strSQLQuery = "select assettag from AssetTags where ServiceTag='" & strSerial & "'"
Set arrNewTag = objConnection.Execute(strSQLQuery)
computerName=(arrNewTag(0))
End If
Else
computerName = "Set Computer Name"
End If
objConnection.Close
End Function
Answer
If you want to use multiple conditions in a Case statement you need to connect them with logical operators unless you want to match any of them (the comma means you have a list with several independent conditions).
A statement
Select Case True
Case x=1, y=2
...
End Select
matches if x has the value 1 or y has the value 2.
A statement
Select Case True
Case x=1 And y=2
...
End Select
matches only if x has the value 1 and y has the value 2.
However, personally I consider this an abuse of Select statements since they're normally meant to branch depending on different values of one variable or expression.
Select Case x
Case 1
'do some
Case 2
'do other
Case Else
'didn't expect this value
End Select
I prefer If..ElseIf..Else statements for checking multiple different conditions.
If x=1 And y=3 Then
...
ElseIf x=2 And y<5 Then
...
ElseIf y>2 And z-x=4 Then
...
Else
...
End If
*steps off soapbox*
With that said, in your case you'd never even get to the three additional checks, because one of the first three conditions would've matched first. You'd need to put the combined conditions before the simple conditions to make your statement work. Also, you can't check a variable against multiple values like this:
strChassisType = 8,9 Or 10
What you can do is use comparison operations like this if your values are numeric:
If strChassisType >= 8 And strChassisType <= 10 Then
do a dictionary lookup:
chassisTypes = CreateObject("Scripting.Dictionary")
chassisTypes.Add "8", True
chassisTypes.Add "9", True
chassisTypes.Add "10", True
...
If chassisTypes.Exists(strChassisType) Then
or define a Contains function to check for the presence of a value in an array:
Function Contains(a, v)
Contains = False
For Each e In a
If e = v Then
Contains = True
Exit For
End If
Next
End Function
...
If Contains(Array("8", "9", "10"), strChassisType) Then
Since your second set of conditions would basically just modify the result of the first set of conditions I'd probably do exactly that: modify the result of the first set of conditions. And maybe use Split(strIP, ".") for splitting the relevant subnet from the IP address.
subnet = Split(strIP, ".")(2)
For Each objChassis In colChassis
For Each strChassisType In objChassis.ChassisTypes
Select Case subnet
Case "111": strSiteCode = "UK"
Case "112": strSiteCode = "NY"
Case "113": strSiteCode = "HK"
Case Else
WScript.Echo "Unknown subnet: " & subnet
WScript.Quit 1
End Select
If strChassisType >= 8 And strChassisType <= 10 Then
strSiteCode = strSiteCode & "L"
End If
Next
Next