SQL

[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

 

[Shoretel] Get Workgroup and user extension with login name

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

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

[Lansweeper]Workstations less than 2.0 GB free on System Drive (with username and display name)

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tsysOS.OSname,
tblAssets.Description,
tblDiskdrives.Caption,
Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) As
free,
Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) As
[total size],
tblDiskdrives.Lastchanged As [last changed],
tsysOS.Image As icon,
tblAssets.Username,
tblADusers.Displayname
From tblAssets
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblADusers On tblAssets.Username = tblADusers.Username
Where tblDiskdrives.Caption = ‘C:’ And Cast(Cast(tblDiskdrives.Freespace As
bigint) / 1024 / 1024 As numeric) < 2048 And Cast(Cast(tblDiskdrives.Size As
bigint) / 1024 / 1024 As numeric) <> 0 And tblComputersystem.Domainrole < 2
And tblDiskdrives.DriveType = 3 And tblAssetCustom.State = 1
Order By tblAssets.AssetUnique

[Lansweeper] Report of unclean Reboot (Event ID 41) of last 14 days

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblADusers.Displayname,
tblAssets.OScode,
tblNtlog.Eventcode,
Max(tblNtlog.TimeGenerated) As LastOccurrence,
tblNtlogSource.Sourcename,
tblNtlogMessage.Message
From tblAssets
Inner Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID
Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
tblNtlog.SourcenameID
Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Where tblNtlogSource.Sourcename Like ‘%kernel%’ And tblNtlog.Eventcode = 41
Group By tblAssets.AssetID,
tblAssets.AssetName,
tblADusers.Displayname,
tblAssets.OScode,
tblNtlog.Eventcode,
tblNtlogSource.Sourcename,
tblNtlogMessage.Message
Having Max(tblNtlog.TimeGenerated) > GetDate() – 14
Order By LastOccurrence Desc,
tblAssets.AssetName

[Lansweeper] Report of hard disk with bad sector

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblNtlog.Eventcode,
Max(tblNtlog.TimeGenerated) As LastOccurrence,
tblADusers.Displayname,
tblNtlogMessage.Message
From tblAssets
Inner Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID
Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
tblNtlog.SourcenameID
Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Where tblNtlogMessage.Message Like ‘%Harddisk0%’ And tblNtlog.Eventcode = 7
Group By tblAssets.AssetID,
tblAssets.AssetName,
tblNtlog.Eventcode,
tblADusers.Displayname,
tblNtlogMessage.Message,
tblAssets.OScode
Order By tblAssets.AssetName

[Lansweeper] Report of all active printers

Select Top 1000 tblAssets.AssetName,
tblAssets.AssetID,
tblAssets.IPAddress,
web40repprinterstatus.Status,
web40repprinterstatus.[Device  model],
tblAssets.Mac,
tblAssets.LastIPScan,
tblAssets.Lastseen,
tblAssets.Firstseen,
tblAssets.Lasttried
From tblAssets
Inner Join web40repprinterstatus On tblAssets.AssetID = web40repprinterstatus.AssetID
And tblAssets.Assettype = 16

[Lansweeper] Report of hard disk error

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblNtlog.Eventcode,
tblNtlogSource.Sourcename,
Max(tblNtlog.TimeGenerated) As LastOccurrence,
tblADusers.Displayname,
tblAssets.OScode,
tblNtlogMessage.Message
From tblAssets
Inner Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID
Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
tblNtlog.SourcenameID
Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Where tblNtlog.Eventcode In (57, 55, 51, 26, 16, 7) And
tblNtlogSource.Sourcename In (‘Disk’, ‘volsnap’, ‘Application Popup’, ‘Ntfs’)
Group By tblAssets.AssetID,
tblAssets.AssetName,
tblNtlog.Eventcode,
tblNtlogSource.Sourcename,
tblADusers.Displayname,
tblAssets.OScode,
tblNtlogMessage.Message
Order By tblAssets.AssetName

[Lansweeper] Report of computers without monitors

Select Top 10000 m.AssetID,
m.AssetName,
m.Username,
m.LastActiveScan,
m.IPAddress,
m.Description
From tblAssets As m,
upgrade_web40ActiveComputers As a
Where a.Computername = m.AssetID And — Filter out non-active computers
Not Exists(Select r.AssetID From tblMonitor As r Where r.AssetID = m.AssetID) And — Filter out computers with monitors
Not Exists(Select e.AssetID From web50repallscanningerrors As e Where e.AssetID = m.AssetID) — Filter out scan error PC
Order By m.LastActiveScan Desc

— a for Active Computer
— m for main assets
— r for relationship
— e for errorscan