Shoretel

[AutoIT] Creating a Welcome document for new joiners.

The WelcomePack is an application written in AutoIT to generate a Word document with information of the current login user. The generated document will be saved under a folder with the current user’s display name.

WelcomePack.exe will search for $Date, $Username, $tel, $ext, $Email, $ComputerName, $NetworkDrives, $Printers and replace them with the information gathered.

The information includes with the source listed:

  • logon name – Windows session
  • direct line – AD
  • extension number – Shoretel
  • email address – AD
  • distribution list the user is a member of – AD
  • computer name – Windows session
  • mapped drive – Windows session
  • connected printers – Windows session

 

System requirements:

  • Word 2010 or later
  • The template file named “!Default Template.doc”
  • MySQL ODBC 5.3 Unicode Driver

 

*Shoretel DB credential  and server info removed

Minimal handling has been implement into the code.

 

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Compression=4
#AutoIt3Wrapper_UseX64=Y
#AutoIt3Wrapper_Res_Comment=Welcome Pack Executable
#AutoIt3Wrapper_Res_Description=WF Welcome Pack
#AutoIt3Wrapper_Res_Fileversion=1.0
#AutoIt3Wrapper_Res_LegalCopyright=Max Lee (argentolee@hotmail.com)
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
#include <Word.au3>
#include <Date.au3>
#include <MsgBoxConstants.au3>
;*****************************************
;WelcomePack.au3 by Max Lee
;Created with ISN AutoIt Studio v. 0.95 BETA
;*****************************************
; Create application object
Global $oWord = _Word_Create()
If @error <> 0 Then Exit MsgBox($MB_SYSTEMMODAL, "Word UDF: _Word_DocFindReplace Example", _
        "Error creating a new Word application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
; Open template document, read-only
Global $oDoc = _Word_DocOpen($oWord, @ScriptDir & "\!Default Template.doc", Default, Default, True)
If @error <> 0 Then Exit MsgBox($MB_SYSTEMMODAL, "Word UDF: _Word_DocFindReplace Example", _
        "Error opening '!Default Template.doc'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
; *****************************************************************************
; Replacing variables in the Word document
; *****************************************************************************
_Word_DocFindReplace($oDoc, "$Date", _NowDate())
_Word_DocFindReplace($oDoc, "$Username", @UserName)
_Word_DocFindReplace($oDoc, "$ComputerName", @ComputerName)
_Word_DocFindReplace($oDoc, "$Email", GetMail())
_Word_DocFindReplace($oDoc, "$NetworkDrives", GetMappedDrives())
_Word_DocFindReplace($oDoc, "$Printers", GetMappedPrinters())
_Word_DocFindReplace($oDoc, "$tel", GetTel())
_Word_DocFindReplace($oDoc, "$DL", _GetDL())
_Word_DocFindReplace($oDoc, "$ext", _GetExt())
;_Word_DocFindReplace($oDoc, "$othertel", GetotherTelephone())
If @error <> 0 Then Exit MsgBox($MB_SYSTEMMODAL, "Word UDF: _Word_DocFindReplace Example", _
        "Error replacing text in the document." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
;MsgBox($MB_SYSTEMMODAL, "Word UDF: _Word_DocFindReplace Example", "Text successfully replaced.")
DirCreate(@ScriptDir & "\" & GetDisplayName())
_Word_DocSaveAs($oDoc, @ScriptDir & "\" & GetDisplayName() & "\" & GetDisplayName() & ".doc")
Func GetDisplayName()
    Local $objRootDSE, $strDomain, $objConnection, $objCommand, $objRecordSet, $strDN
    ;Const ADS_SCOPE_SUBTREE = 2
    
    $objRootDSE = ObjGet("LDAP://RootDSE")
    $strDomain = $objRootDSE.Get("DefaultNamingContext")
    ;Set ADO connection
    $objConnection = ObjCreate("ADODB.Connection")
    $objConnection.Provider = "ADsDSOObject"
    $objConnection.Open("Active Directory Provider")
    ;Set ADO command
    $objCommand = ObjCreate("ADODB.Command")
    $objCommand.ActiveConnection = $objConnection
    $objCommand.Properties("Searchscope") = 2
    $objCommand.CommandText = "SELECT distinguishedName FROM 'LDAP://" & $strDomain & "' WHERE objectCategory='user' AND samAccountName = '" & @UserName & "'"
    ;Set recordset to hold the query result
    $objRecordSet = $objCommand.Execute
    ;If a user was found - Retrieve the distinguishedName
    If Not $objRecordSet.EOF Then
        $strDN = $objRecordSet.Fields("distinguishedName").Value
        Dim $objUser = ObjGet("LDAP://" & $strDN)
        Return $objUser.displayName
    Else
        ;"Not found"
        Return
    EndIf
EndFunc   ;==>GetDisplayName
Func GetMail()
    Local $objRootDSE, $strDomain, $objConnection, $objCommand, $objRecordSet, $strDN
    ;Const ADS_SCOPE_SUBTREE = 2
    
    $objRootDSE = ObjGet("LDAP://RootDSE")
    $strDomain = $objRootDSE.Get("DefaultNamingContext")
    ;Set ADO connection
    $objConnection = ObjCreate("ADODB.Connection")
    $objConnection.Provider = "ADsDSOObject"
    $objConnection.Open("Active Directory Provider")
    ;Set ADO command
    $objCommand = ObjCreate("ADODB.Command")
    $objCommand.ActiveConnection = $objConnection
    $objCommand.Properties("Searchscope") = 2
    $objCommand.CommandText = "SELECT distinguishedName FROM 'LDAP://" & $strDomain & "' WHERE objectCategory='user' AND samAccountName = '" & @UserName & "'"
    ;Set recordset to hold the query result
    $objRecordSet = $objCommand.Execute
    ;If a user was found - Retrieve the distinguishedName
    If Not $objRecordSet.EOF Then
        $strDN = $objRecordSet.Fields("distinguishedName").Value
        Dim $objUser = ObjGet("LDAP://" & $strDN)
        Return $objUser.mail
    Else
        ;Msgbox(4096,"Title","Not found")
        Return
    EndIf
EndFunc   ;==>GetMail
Func GetMappedDrives()
    Local $strNetworkDrives
    Local $aArray = DriveGetDrive("NETWORK")
    If @error Then
        ; An error occurred when retrieving the drives.
        MsgBox($MB_SYSTEMMODAL, "", "It appears an error occurred.")
    Else
        For $i = 1 To $aArray[0]
            ; Show all the drives found and convert the drive letter to uppercase.
            ;MsgBox($MB_SYSTEMMODAL, "", "Drive " & $i & "/" & $aArray[0] & ":" & @CRLF & StringUpper($aArray[$i]) & "  " & DriveMapGet($aArray[$i]) &@CRLF )
            $strNetworkDrives = $strNetworkDrives & (@TAB & @TAB & @TAB & StringUpper($aArray[$i]) & "  " & DriveMapGet($aArray[$i]) & @CR)
        Next
        Return $strNetworkDrives
    EndIf
EndFunc   ;==>GetMappedDrives
Func GetMappedPrinters()
    $objWMIService = ObjGet("winmgmts:\\.\root\CIMV2")
    $colItems = $objWMIService.ExecQuery("SELECT * FROM Win32_Printer")
    Local $strNetworkPrinters
    For $objItem In $colItems
        If StringLen($objItem.ShareName) > 3 Then
            $strNetworkPrinters = $strNetworkPrinters & @TAB & @TAB & @TAB & $objItem.ShareName & @CR
        Else
        EndIf
    Next
    Return $strNetworkPrinters
EndFunc   ;==>GetMappedPrinters
Func GetTel()
    Local $objRootDSE, $strDomain, $objConnection, $objCommand, $objRecordSet, $strDN
    ;Const ADS_SCOPE_SUBTREE = 2
    
    $objRootDSE = ObjGet("LDAP://RootDSE")
    $strDomain = $objRootDSE.Get("DefaultNamingContext")
    ;Set ADO connection
    $objConnection = ObjCreate("ADODB.Connection")
    $objConnection.Provider = "ADsDSOObject"
    $objConnection.Open("Active Directory Provider")
    ;Set ADO command
    $objCommand = ObjCreate("ADODB.Command")
    $objCommand.ActiveConnection = $objConnection
    $objCommand.Properties("Searchscope") = 2
    $objCommand.CommandText = "SELECT distinguishedName FROM 'LDAP://" & $strDomain & "' WHERE objectCategory='user' AND samAccountName = '" & @UserName & "'"
    ;Set recordset to hold the query result
    $objRecordSet = $objCommand.Execute
    ;If a user was found - Retrieve the telephoneNumber
    If Not $objRecordSet.EOF Then
        $strDN = $objRecordSet.Fields("distinguishedName").Value
        Dim $objUser = ObjGet("LDAP://" & $strDN)
        Return $objUser.telephoneNumber
    Else
        ;Not found
        Return
    EndIf
EndFunc   ;==>GetTel
Func _GetExt()
    Local $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")
    Local $sUsername = 'username'
    Local $sPassword = 'password'
    Local $sDatabase = 'shoreware'
    Local $sServer = 'server'
    Local $sDriver = "{MySQL ODBC 5.3 Unicode Driver}"
    Local $iPort = 4308!Default Template
    Local $result
    $ObjConn = ObjCreate("ADODB.Connection")
    $ObjConn.ConnectionString = "DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";User=" & $sUsername & ";Password=" & $sPassword & ";PORT=" & $iPort
    If IsObj($ObjConn) Then
        $ObjConn.open()
    Else
        Exit
    EndIf
    $rsResult = $ObjConn.Execute("SELECT UserDN FROM users WHERE GuiLoginName='" & @UserName & "'")
    With $rsResult
        While Not .EOF
            
            ;MsgBox(0,'',.Fields("UserDN").Value)
            $result = .Fields("UserDN").Value
            .MoveNext
        WEnd
        .Close
    EndWith
    $ObjConn.Close
    ;MsgBox(0,'',$result)
    Return $result
    
EndFunc   ;==>_GetExt
Func _ErrFunc($oError)
    ; Do anything here.
    ConsoleWrite(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _
            @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _
            @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _
            @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _
            @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF)
EndFunc   ;==>_ErrFunc
Func _GetDL()
    
    Local $objUser, $objRootDSE, $objConnection, $objRecordSet, $objCommand
    Local $GroupCollection, $ObjGroup, $result
    Local $StrUserName, $StrDomName, $StrSQL
    $objRootDSE = ObjGet("LDAP://RootDSE")
    $StrDomName = $objRootDSE.Get("DefaultNamingContext")
    $StrUserName = @UserName
    $StrSQL = "Select ADsPath From 'LDAP://" & $StrDomName & "' Where ObjectCategory = 'User' AND SAMAccountName = '" & $StrUserName & "'"
    ;Set ADO connection
    $objConnection = ObjCreate("ADODB.Connection")
    $objConnection.Provider = "ADsDSOObject"
    $objConnection.Open("Active Directory Provider")
    ;Set ADO command
    $objCommand = ObjCreate("ADODB.Command")
    $objCommand.ActiveConnection = $objConnection
    $objCommand.Properties("Searchscope") = 2
    $objCommand.CommandText = $StrSQL
    ;Set recordset to hold the query result
    $objRecordSet = $objCommand.Execute
    If Not $objRecordSet.EOF Then
        $objRecordSet.MoveLast
        $objRecordSet.MoveFirst
        $objUser = ObjGet($objRecordSet.Fields("ADsPath").Value)
        $GroupCollection = $objUser.Groups
        For $ObjGroup In $GroupCollection
            If ($ObjGroup.GroupType = 2 Or $ObjGroup.GroupType = 4 Or $ObjGroup.GroupType = 8) Then
                $result = $result & @TAB & @TAB & @TAB & $ObjGroup.CN & @CR
            Else
                ;Do nothing
            EndIf
            
        Next
        $ObjGroup = Null ;
        $GroupCollection = Null ;
        $objUser = Null ;
        Return $result ;
    Else
    EndIf
    
EndFunc   ;==>_GetDL
Func GetotherTelephone()
    Local $objRootDSE, $strDomain, $objConnection, $objCommand, $objRecordSet, $strDN
    ;Const ADS_SCOPE_SUBTREE = 2
    
    $objRootDSE = ObjGet("LDAP://RootDSE")
    $strDomain = $objRootDSE.Get("DefaultNamingContext")
    ;Set ADO connection
    $objConnection = ObjCreate("ADODB.Connection")
    $objConnection.Provider = "ADsDSOObject"
    $objConnection.Open("Active Directory Provider")
    ;Set ADO command
    $objCommand = ObjCreate("ADODB.Command")
    $objCommand.ActiveConnection = $objConnection
    $objCommand.Properties("Searchscope") = 2
    $objCommand.CommandText = "SELECT distinguishedName FROM 'LDAP://" & $strDomain & "' WHERE objectCategory='user' AND samAccountName = '" & @UserName & "'"
    ;Set recordset to hold the query result
    $objRecordSet = $objCommand.Execute
    ;If a user was found - Retrieve the otherTelephone
    If Not $objRecordSet.EOF Then
        $strDN = $objRecordSet.Fields("distinguishedName").Value
        Dim $objUser = ObjGet("LDAP://" & $strDN)
        Return $objUser.otherTelephone
    Else
        ;"Not found"
        Return
    EndIf
EndFunc   ;==>GetotherTelephone
Default template attached:

[SHORETEL] Get workgroup, user extension, user login and workgroup description.

To get a list of workgroup, user extension, user login and workgroup description, connect to the Shoretel Shoreware database and run the following SQL:

SELECT workgroupmembers.WorkgroupDN,workgroupmembers.UserDN,users.GuiLoginName, dn.Description FROM workgroupmembers 
INNER JOIN DN ON workgroupmembers.WorkgroupDN=dn.DN
INNER JOIN users ON workgroupmembers.UserDN=users.UserDN