Changing DN to just show the CN using function

[chrisuk11]

I have written a function that will take a DN and only output the CN.
This works fine, however when I add this to my main script (It pulls back usernames of users who have an expiry date) I get error -

Invalid Use of Null

Please be aware I am new to scripting so the script my not follow the "scripting" rules

Code -

'Option Explicit

Dim adoConnection, adoCommand
Dim objRootDSE, strDNSDomain, strFilter, strQuery, adoRecordset
Dim strDN, objShell, lngBiasKey, lngBias
Dim lngDate, objDate, dtmAcctExp, k
Dim strExcelPath, strExpiry


' Obtain local time zone bias from machine registry.
Set objShell = CreateObject("Wscript.Shell")
lngBiasKey = objShell.RegRead("HKLM\System\CurrentControlSet\Control\" _
& "TimeZoneInformation\ActiveTimeBias")
If (UCase(TypeName(lngBiasKey)) = "LONG") Then
lngBias = lngBiasKey
ElseIf (UCase(TypeName(lngBiasKey)) = "VARIANT()") Then
lngBias = 0
For k = 0 To UBound(lngBiasKey)
lngBias = lngBias + (lngBiasKey(k) * 256^k)
Next
End If


' Spreadsheet file to be created.
strExcelPath = "C:\ExpiryDates.xls"

' Bind to Excel object.
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add

' Bind to worksheet.
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
objSheet.Name = "Expiry Dates User"
objSheet.Cells(1, 1).Value = "Name"
objSheet.Cells(1, 2).value = "Username"
objSheet.cells(1, 3).value = "Expiry"
objSheet.cells(1, 4).value = "Department"
objSheet.cells(1, 5).value = "Manager"
objSheet.cells(1, 6).value = "Organizational Unit"


' Use ADO to search the domain.
Set adoConnection = CreateObject("ADODB.Connection")
Set adoCommand = CreateObject("ADODB.Command")
adoConnection.Provider = "ADsDSOOBject"
adoConnection.Open "Active Directory Provider"
Set adoCommand.ActiveConnection = adoConnection

' Determine the DNS domain from the RootDSE object.
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("DefaultNamingContext")

' Filter to retrieve all user objects with accounts
' that expire.
strFilter = "(&(objectCategory=person)(objectClass=user)" _
& "(!accountExpires=0)(!accountExpires=9223372036854775807))"

strQuery = "<LDAP://" & strDNSDomain & ">;" & strFilter _
& ";distinguishedName,accountExpires,sAMAccountName,GivenName,SN,Department,Manager;subtree"

' Run the query.
adoCommand.CommandText = strQuery
adoCommand.Properties("Page Size") = 1000
adoCommand.Properties("Timeout") = 60
adoCommand.Properties("Cache Results") = False

k=2
'x=2
Set adoRecordset = adoCommand.Execute

' Enumerate the recordset.
Do Until adoRecordset.EOF
' Retrieve attribute values.
strDN = adoRecordset.Fields("distinguishedName").Value
lngDate = adoRecordset.Fields("accountExpires")
strsAMAccountName = adoRecordset.Fields("sAMAccountName")
strFirstName = adoRecordset.Fields("GivenName")
strSurname = adoRecordset.Fields("SN")
strDepartment = adoRecordset.Fields("Department")
strManager = adoRecordset.Fields("Manager")

strManager = GetCN(strManager)


' Convert accountExpires to date in current time zone.
Set objDate = lngDate
dtmAcctExp = Integer8Date(objDate, lngBias)
strExpiry = dtmAcctExp
' Output to Excel
'Wscript.Echo strDN & ";" & dtmAcctExp
objsheet.cells(k,1).value = strFirstName & " " & strSurname
objsheet.cells(k,2).value = strsAMAccountName
objSheet.cells(k,3).value = strExpiry
objSheet.cells(k,4).value = strDepartment
objSheet.cells(k,5).value = strManager
objSheet.cells(k,6).value = strDN
k = k + 1

adoRecordset.MoveNext
Loop
adoRecordset.Close


' Format the spreadsheet.
objSheet.Range("A1:H1").Font.Bold = True
objSheet.Select
objExcel.Columns("A:J").AutoFit




' Save the spreadsheet.
objExcel.ActiveWorkbook.SaveAs strExcelPath
objExcel.ActiveWorkbook.Close

' Quit Excel.
objExcel.Application.Quit




' Clean up.
adoConnection.Close

Function Integer8Date(ByVal objDate, ByVal lngBias)
' Function to convert Integer8 (64-bit) value to a date, adjusted for
' local time zone bias.
Dim lngAdjust, lngDate, lngHigh, lngLow
lngAdjust = lngBias
lngHigh = objDate.HighPart
lngLow = objdate.LowPart
' Account for bug in IADslargeInteger property methods.
If (lngLow < 0) Then
lngHigh = lngHigh + 1
End If
If (lngHigh = 0) And (lngLow = 0) Then
lngAdjust = 0
End If
lngDate = #1/1/1601# + (((lngHigh * (2 ^ 32)) _
+ lngLow) / 600000000 - lngAdjust) / 1440
Integer8Date = CDate(lngDate)
End Function


Function GetCN(strManager)
'Function to output only the CN from a distinguished Name

Dim icharacter1, icharacter2
icharacter1 = InStr(1, strManager, "=")
icharacter2 = InStr(1, strManager, ",")
GetCN = Mid(strManager, icharacter1+1, icharacter2-4)
End Function

 

[Andrzejek]

Are you talking about this:

...
strManager = adoRecordset.Fields("Manager")[blue].Value[/blue]

strManager = GetCN(strManager)
...
Function GetCN(strManager)
'Function to output only the CN from a distinguished Name

Dim icharacter1, icharacter2
icharacter1 = InStr(1, strManager, "=")
icharacter2 = InStr(1, strManager, ",")
GetCN = Mid(strManager, icharacter1+1, icharacter2-4)
End Function

And I bet you get the error 'Invalid Use of Null' at the line with the blue font. You cannot assign NULL to your strManager

Try:

strManager = adoRecordset.Fields("Manager").Value[blue] & ""[/blue]

Couple of questions:
1. Why do you have Option Explicit commented out? Bad idea, put it back on.
2. Why all your variables are declared as Variants?

Have fun.

---- Andy

 

[strongm]

>Why all your variables are declared as Variants?

Becasue this looks very much like it is VBScript, not VB

 

[Andrzejek]

strongm, that makes sense.

Sometimes I just assume that since it is a "Visual Basic(Microsoft): Version 5 & 6" forum, all questions are about VB 5 or 6, but we get here VBA, VBScript, VB.NET, VB* questions as well....

Have fun.

---- Andy

 

[chrisuk11]

Hi,

Thanks for the responses. It was vbscript and I managed to figure out the issue. It was because some Users did not have a manager set, hence the value was null and I hadn't determined what the script should do with this.

Though I now have a new problem -

The script now removes all groups from the user, disbales the account. I am now having a problem moving it to another OU.

The code I have is below, I have now removed all code that was trying to move the user.

Would anyone be able to take a look

Thanks again for the responses


Main Code -

STRDN = "CN=Test1 s. smith,OU=Users,DC=hollishome,DC=local"

DelGroups

Sub DelGroups
Const ADS_PROPERTY_DELETE = 4
Const E_ADS_PROPERTY_NOT_FOUND = &h8000500D

Set objUser = GetObject("LDAP://" & strDN)
arrMemberOf = objUser.GetEx("memberOf")
'Disable Account
objUser.AccountDisabled = True
'Hide from Address Book
'objUser.HideFromAddressBook = True
'Retrieve Group Names


'Remove AD Groups

'If Err.Number = E_ADS_PROPERTY_NOT_FOUND Then
' WScript.Echo "This account is not a member of any security groups."
' WScript.Quit
'End If

For Each Group in arrMemberOf
Set objGroup = GetObject("LDAP://" & Group)
objGroup.PutEx ADS_PROPERTY_DELETE, "member", Array(STRDN)
objGroup.SetInfo
Next

objUser.SetInfo
End Sub

 

[chrisuk11]

Have fixed this now.

Sorry for the waste of time

Thread can be closed

 

[dilettante]

forum329 is the place for these kinds of issues.