VB AD Problem



  • 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.



  • 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.



  • 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.



  • 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.



  • 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.



  • @Boner said:

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

    Welcome to the world of software development.



  • 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.



  • 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=...".



  • 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.


  • Discourse touched me in a no-no place

    @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.)



  • @dkf said:

    That's what everyone else does

    That's a relief!



  • @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


  • 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


  • @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 :)



  • @Boner said:

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

    ahem



  • :facepalm:

    Clean-up crew required



  • Move along please.



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



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

    Thanks for your help.



  • @PJH said:

    vomit

    Did you just clear up some clown vomit?



  • 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 applicationsemphasized 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



  • 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'?



  • @Boner said:

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

    DANGER! DANGER!



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



  • @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


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.