VB AD Problem

Boner

Disclaimer: I don't know anything about programming, I've cobbled together the below from stuff I found on Google. It works and has been in use for a while, I just need to add something extra.

'Var
intDays=3

'Provides access to a computer's file system
Set objFile = createobject("scripting.filesystemobject")

'Creates a specified file name and returns a TextStream object that can be used to read from or write to the file.
Set FileTemp = objFile.createtextFile("c:\2\Accounts.txt")

'Specifies that when a run-time error occurs, control goes to the statement immediately following the statement where the error occurred 
On Error Resume Next

Set strDomain = getobject("WinNT://contoso.net")
strDomain.Filter = Array("User")

For Each User in strDomain

    If User.AccountDisabled = False Then
        lngPasswordLastChanged = User.Get("PasswordAge")        'Get age of password (seconds)
        lngPasswordLastChanged = lngPasswordLastChanged * -1        'Multiply by -1 to enable subtraction on next row
        dtmPasswordSet = DateAdd("s",lngPasswordLastChanged,Now)    'Subtract age of password from current date/time to get password set date/time
        dtmPasswordExpiration = DateAdd("d", 90, dtmPasswordSet)    'Add 90 days to result from line above to get password expiry date/time
        If err.number = 0 Then
            intDateDiff = DateDiff("d",Now,dtmPasswordExpiration)    'Compare password expiry date/time to current date/time
            If intDateDiff >= 0 And intDateDiff < intDays Then    'If difference is between 0 and intDays write to txt file
                FileTemp.WriteLine User.Name & " - Expires: " & dtmPasswordExpiration & " (~" & intDateDiff & " Days) <br>"
                End If
        Else
        Err.clear
        End If
    End If

'Next item in For loop
Next

'close temp file
FileTemp.close
Set objFile = Nothing

I run this vbs from a .bat, it emails the support mailbox with a list of AD accounts that will expire in the next 3 days. What I'd like to do is email the user instead, and to do this I need to change the line

FileTemp.WriteLine User.Name & " - Expires: " & dtmPasswordExpiration & " (~" & intDateDiff & " Days) <br>"

to write the email address property of the AD account rather than "User.Name". The problem being I don't know what I'm doing...

All help appreciated.

No_1

See if using 'User.Mail' does what you want. If not then you'll probably want to start looking at the proxyAddresses attribute, which will require a bit more work as it returns an array of strings rather than just a single value.

http://www.kouti.com/tables/userattributes.htm

Boner

Thanks @No_1 , no joy with User.Mail unfortunately.

I did a Wscript.Echo on all the Attr LDAP Names in the link you provided, the only ones that returned any values are description, homeDirectory, sAMAccountName, primaryGroupID and two more that return the values "-1" and "?".

After checking the account attributes in AD, "mail" does contain the info I need but I've no idea why it's not being pulled but the properties above are.

I was hoping this would be a 1 liner but if it's going to take a lot more effort it's not worth it.

No_1

It looks like I might have been wrong about .mail returning a single value; try this (untested) code to see if the attribute is returning an array:

on error resume next    ' Prevent failure if an attribute is undefined

mailAddrs = user.GetEx("mail")    ' GetEx always returns an array, even for single values

if err.number = 0 then
    for each addr in mailAddrs
        wscript.echo addr
    next
else
    wscript.echo ".mail attribute not found"
end if

on error goto 0

You could also substitute "proxyAddresses" for "mail" in the second line.

Boner

No luck with that.

One of the few properties that does work is User.FullName. I can dump this in the output txt file and use the .bat to transform this into the full email address. This shouldn't be too much of a ballache and I don't think I'm going to get this mail field working for whatever reason.

Thanks for your help.

Eldelshell

@Boner said:

The problem being I don't know what I'm doing

Welcome to the world of software development.

No_1

Doh!

I've just realised that you're using the WinNT: provider (that's what you get for skim-reading), which doesn't expose .mail, .proxyAddresses, and a whole bunch of other stuff that would be available if you were to use the LDAP: provider instead. A quick check here confirmed my suspicions.

I'll see if I can put together some sample code showing an alternative LDAP version.

No_1

Here's some more (untested) example code:

ldapDomain = "DC=contoso,DC=net"
ldapContainer = "CN=Users"
bind = "LDAP://" & ldapContainer & "," ldapDomain

set container = GetObject(bind)

on error resume next    ' Don't fail on undefined attributes

for each item in container
    if item.class = "user" then
        if item.accountDisabled = false then
            wscript.echo item.name
            wscript.echo item.mail
            wscript.echo item.distinguishedName
            wscript.echo
        end if
    end if
next

on error goto 0

Note that "CN=Users" is the default container for new user accounts; I would expect that they'll have been moved to an organizational unit which will have a path description alongs the lines of "OU=Parent,OU=Child,OU=...".

Boner

This is giving me the info I need now it's switched to LDAP, but only for the OU I specify rather than for each user. IIRC I think this is why I went for WinNT originally. I found some recurse code for LDAP on Expert Sexchange but It looks complicated, I think I'll find it easier to hard code the OU's I need and run the code above as many times as I need.

Thanks again.

dkf

@Boner said:

I think I'll find it easier to hard code the OU's I need

That's what everyone else does when querying against LDAP, except occasionally when they just query against the O level instead (e.g., for logins to a main corporate intranet website). Picking the right thing to do is sometimes non-trivial, but it's also totally specific to each deployment so giving advice is difficult.

It gets more complicated if you choose to manage authorization via LDAP as well, because there's no standard attributes for that. (Or at least there aren't for our systems.)

Boner

@dkf said:

That's what everyone else does

That's a relief!

No_1

@dkf said:

That's what everyone else does

Not necessarily, see below.

@Boner said:

I found some recurse code for LDAP ... but it looks complicated

It looks complicated because it's messy and a good example of how to use comments to make code harder to read.

So it's recursion you want is it? Try the following code (again untested, might contain a typo or two, YMMV, caveat emptor, etc.) which will look for all users beneath the root OU that you specify:

ldapDomain = "DC=belgium-group,DC=net"
ldapContainer = "CN=Users"

path = ldapContainer & "," ldapDomain

Recurse path

exit

sub Recurse(path)
    bind = "LDAP://" & path
    set container = GetObject(bind)
    
    on error resume next    ' Don't fail on undefined attributes
    
    for each item in container
        
        if item.class = "user" then
            if item.accountDisabled = false then
                wscript.echo item.name
                wscript.echo item.mail
                wscript.echo item.distinguishedName
                wscript.echo
            end if
        end if
        
        if item.class = "organizationalUnit" then
            Recurse item.distinguishedName
        end if
        
    next
    
    on error goto 0
end sub

Boner

Just cracked it. Posted here on the proviso you don't put me on the front page:

'Var
intDays=4

'Provides access to a computer's file system
Set objFile = createobject("scripting.filesystemobject")

'Creates a specified file name and returns a TextStream object that can be used to read from or write to the file.
Set FileTemp = objFile.createtextFile("c:\2\Accounts.txt")

'Specifies that when a run-time error occurs, control goes to the statement immediately following the statement where the error occurred 
On Error Resume Next

Set strDomain = getobject("WinNT://contoso.net")
strDomain.Filter = Array("User")

For Each User in strDomain

    If User.AccountDisabled = False Then
        lngPasswordLastChanged = User.Get("PasswordAge")        'Get age of password (seconds)
        lngPasswordLastChanged = lngPasswordLastChanged * -1        'Multiply by -1 to enable subtraction on next row
        dtmPasswordSet = DateAdd("s",lngPasswordLastChanged,Now)    'Subtract age of password from current date/time to get password set date/time
        dtmPasswordExpiration = DateAdd("d", 90, dtmPasswordSet)    'Add 90 days to result from line above to get password expiry date/time
        If err.number = 0 Then
            intDateDiff = DateDiff("d",Now,dtmPasswordExpiration)    'Compare password expiry date/time to current date/time
            If intDateDiff >= 0 And intDateDiff < intDays Then    'If difference is between 0 and intDays write to txt file
                
            On Error Resume Next

            Const ADS_SCOPE_SUBTREE = 8

                Set objConnection = CreateObject("ADODB.Connection")
                Set objCommand =   CreateObject("ADODB.Command")
                objConnection.Provider = "ADsDSOObject"
                objConnection.Open "Active Directory Provider"
                Set objCommand.ActiveConnection = objConnection

                objCommand.Properties("Page Size") = 1000
                objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 

                objCommand.CommandText = _
                "SELECT distinguishedName FROM 'LDAP://dc=contoso,dc=net' WHERE objectCategory='user' " & _
                    "AND sAMAccountName='" & user.name & "'"
                Set objRecordSet = objCommand.Execute

                objRecordSet.MoveFirst
                Do Until objRecordSet.EOF
                    fullpath = objRecordSet.Fields("distinguishedName").Value
                    pos = (InStr(fullpath,",")) +1
                    oupath = (Mid(fullpath,pos,99))
                    objRecordSet.MoveNext
                Loop


                bind = "LDAP://" & oupath

                set container = GetObject(bind)

                on error resume next    ' Don't fail on undefined attributes

                for each item in container
                    if item.class = "user" then
                        if item.accountDisabled = false then
                            if item.sAMAccountName = User.Name then
                
                            FileTemp.WriteLine item.givenName & " " & item.sn &  " - Expires: " & dtmPasswordExpiration & " --- " & intDateDiff & " Days " & " " & item.mail & " <br>"

                            end if
                        end if
                    end if
                next

                on error goto 0


            End If
        Else
        Err.clear
        End If
    End If



'Next item in For loop
Next

'close temp file
FileTemp.close
Set objFile = Nothing

No_1

@Boner said:

Just cracked it

I could have offered you an ADO query version as well, but I thought you were looking for a simpler option :)

swayde

@Boner said:

("WinNT://belgium-group.net")

ahem

Luhmann

Clean-up crew required

Boner

Move along please.

PJH

Aisle 9 now clear of debris and vomit....

Boner

I was. Simple = c + p from the internet :)

Thanks for your help.

Luhmann

@PJH said:

vomit

Did you just clear up some clown vomit?

Boner

So my boss was at a conference last week, talking to a lot of other PHBs, and apparently Passwords Are Hard*. I have to tidy up this train wreck of .bat .vbs and .txt so he can send it out as an an app to his new chums.

Ain't nobody got time fo dat.

There were a few people there from the banking sector, who as we know really have their heads screwed on when it comes to security. They're having difficulty because they've set their password complexity/expiry rules to be so strict that noone can remember their password from one day to the next. It's not like banks have plenty of money to be throwing round buying proper applications**emphasized text that do this.

Anyway, what I really need is to stick a few lines in there that'll increase my balance by a few zeroes.

/minirant

No_1

Glancing back at this after the latest post; has anyone else mis-read the thread title on first glimpse as 'VD Problem'?

Or even 'V. BAD Problem'?

HardwareGeek

@Boner said:

my boss was at a conference last week, talking to a lot of other PHBs

DANGER! DANGER!

Boner

But he brought me back a free mug and mechanical pencil!

boomzilla

@No_1 said:

Glancing back at this after the latest post; has anyone else mis-read the thread title on first glimpse as 'VD Problem'?

I misread it as "VB ED" problem, and think...how does VB affect that?

Filed Under: Call your doctor if your dialog lasts more than 4 hours