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 error 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:
Advertisements

[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