Тема: VBS: Экспорт данных LDAP и конвертация Static IP Address
Добрый день. Не могу конвертировать Static IP Address полученный при помощи скрипта. Скрипт в excel выгружает всё отлично, кроме Static IP Address, он выгружается в десятичной системе исчисления. Для конвертации Static IP Address нашел функцию. Теперь не знаю как прикрутить её к скрипту, что бы в excel попадали правильные Static IP Address. Скрипт и функция ниже.
Скрипт взят с Вашего форума.
Dim objRoot, strDomain
Dim objConnection, objCommand, objRSet
Dim objFS, objExcel, objWB, objWSheet, strBook, intRow, intColumn, strTemp
Const ADS_SCOPE_SUBTREE = 2
strBook = "Users.xls"
intRow = 2
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWB = objExcel.Workbooks.Add
Set objWSheet = objWB.Worksheets(1)
Set objFS = CreateObject("Scripting.FileSystemObject")
strBook = objFS.BuildPath(objFS.GetParentFolderName(WScript.ScriptFullName), strBook)
Set objRoot = GetObject("LDAP://RootDSE")
strDomain = objRoot.Get("DefaultNamingContext")
Set objRoot = Nothing
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand = CreateObject("ADODB.Command")
Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Timeout") = 30
objCommand.Properties("Sort On") = "description"
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
strCommandText = _
"SELECT userPrincipalName,msRADIUSFramedIPAddress FROM 'LDAP://" _
& strDomain & "' WHERE objectCategory='Person' AND objectClass='User'"
objCommand.CommandText = strCommandText
Set objRSet = objCommand.Execute
objRSet.MoveFirst
Do
If intRow = 2 Then
For intColumn = 1 To objRSet.Fields.Count
objWSheet.Cells(intRow - 1, intColumn).Value = UCase(objRSet.Fields(intColumn - 1).Name)
Next
End If
For intColumn = 1 To objRSet.Fields.Count
strTemp = objRSet.Fields(intColumn - 1).Value
If Not IsNull(strTemp) Then
objWSheet.Cells(intRow, intColumn).NumberFormat = "@"
If IsArray(strTemp) Then
objWSheet.Cells(intRow, intColumn).Value = Join(strTemp, vbLf)
Else
objWSheet.Cells(intRow, intColumn).Value = strTemp
End If
End If
Next
intRow = intRow + 1
objWSheet.Cells(intRow, 1).Activate 'своего рода индикатор процесса
objRSet.MoveNext
Loop While Not objRSet.EOF
Set objRSet = Nothing
Set objCommand = Nothing
objConnection.Close
Set objConnection = Nothing
Set objFS = Nothing
objExcel.DisplayAlerts = False 'позволяет отключить запрос на выбор действия в случае,
'когда файл уже существует
objWSheet.Columns.AutoFit
objWB.SaveAs strBook
'objWB.Close
'objExcel.Quit
Set objWSheet = Nothing
Set objWB = Nothing
Set objExcel = Nothing
'WScript.Echo "Готово."
WScript.Quit 0Функция.
OPTION EXPLICIT
Const E_ADS_PROPERTY_NOT_FOUND = &h8000500D
DIM objUser,msRADIUSFramedIPAddress
'<<<< Bind to the user object using the distinguished name >>>>
set objUser = GetObject("LDAP://cn=,ou=,ou=,ou=,dc=,dc=")
ON ERROR RESUME NEXT
msRADIUSFramedIPAddress= objUser.get("msRADIUSFramedIPAddress")
IF Err.Number = E_ADS_PROPERTY_NOT_FOUND then
wscript.echo "Static IP Address Not Assigned"
err.clear
else
wscript.echo IntegerToIPAddress(msRADIUSFramedIPAddress)
End If
' Function to convert Integer value to IP Address.
Function IntegerToIPAddress(intIP)
Const FourthOctet = 1
Const ThirdOctet = 256
Const SecondOctet = 65536
Const FirstOctet = 16777216
dim strIP,intFirstRemainder,intSecondRemainder,intThirdRemainder
If sgn(intIP) = -1 Then
strIP = (256 + (int(intIP/FirstOctet))) & "."
intFirstRemainder = intIP mod FirstOctet
strIP = strIP & (256 + (int(intFirstRemainder/SecondOctet))) & "."
intSecondRemainder = intFirstRemainder mod SecondOctet
strIP = strIP & (256 + (int(intSecondRemainder/ThirdOctet))) & "."
intThirdRemainder = intSecondRemainder mod ThirdOctet
strIP = strIP & (256 + (int(intThirdRemainder/FourthOctet)))
Else
strIP = int(intIP/FirstOctet) & "."
intFirstRemainder = intIP mod FirstOctet
strIP = strIP & int(intFirstRemainder/SecondOctet) & "."
intSecondRemainder = intFirstRemainder mod SecondOctet
strIP = strIP & int(intSecondRemainder/ThirdOctet) & "."
intThirdRemainder = intSecondRemainder mod ThirdOctet
strIP = strIP & int(intThirdRemainder/FourthOctet)
End If
IntegerToIPAddress = strIP
end function
