Get ShoreTel Agents Using Powershell

The Powershell script below can be used to quickly get the number of Shoretel Workgroup agents currently logged-in to take calls. Save the code to .ps1 file.

Add-Type -Path "C:\Program Files (x86)\MySQL\MySQL Connector Net 6.10.8\Assemblies\v4.5.2\MySql.Data.dll"
$Connection = [MySql.Data.MySqlClient.MySqlConnection]@{ConnectionString='server=172.18.1.10;port=4308;uid=st_configread;pwd=passwordconfigread;database=shoreware'}
$Connection.Open()
$MYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand
$MYSQLDataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter
$MYSQLDataSet = New-Object System.Data.DataSet
$MYSQLCommand.Connection=$Connection
$MYSQLCommand.CommandText='SELECT COUNT(UserDN) AS ''ActiveAgents'' FROM shoreware.workgroupagents WHERE AgentStateID not in (0);'
$MYSQLDataAdapter.SelectCommand=$MYSQLCommand
$NumberOfDataSets=$MYSQLDataAdapter.Fill($MYSQLDataSet, 'data')
foreach($DataSet in $MYSQLDataSet.tables[0])
{
 $ActiveAgents = $DataSet.ActiveAgents
}
$Connection.Close()
echo "`n`nTOTAL LOGGED IN AGENTS: $ActiveAgents`n`n"

When executed the script will return the number of agents logged-in similar to this:

Note: the MySQL .NET connector for the version of MySQL server running on your ShoreTel server must be installed on the workstation running the script.

Resources:
http://www.systemadept.com/2017/07/12/querying-mysql-databases-from-powershell/?i=1
https://www.quadrotech-it.com/blog/querying-mysql-from-powershell

Command-line Compression Options

A comparison of command-line options to compress files using 7-Zip and native commands in Windows and Linux.

For testing I created a Word document of Lorem Ipsum and saved it in Word and PDF formats.

Windows Command Prompt

There is no built-in Windows program able to run in a CMD prompt that does what is wanted.* However, the open-source utility 7za.exe can be copied to any Windows folder without installation and be executed from a batch file.

Command: 7z.exe a -t7z target.7z C:\source\folder\

C:\_Tools\7zip\7za.exe a -t7z C:\Temp\Zip\test.7z C:\Temp\Zip\Files\

Changing the archive type to “zip” causes 7Zip to create a slightly larger archive.

Powershell Core on Windows and Linux

Powershell Core can be installed on both Windows and Linux. The following commands will work with Windows Powershell (deprecated) and Powershell Core (open-source).

# Command syntax:
# Compress-Archive -Path C:\source\folder -DestinationPath C:\target\target.zip
  
Compress-Archive -Path C:\Temp\Zip\files -DestinationPath C:\Temp\Zip\powershell.zip

# Command syntax:
# Compress-Archive -Path C:\source\folder -DestinationPath C:\target\target.zip
  
Compress-Archive -Path /home/joey/temp/zip/files -DestinationPath /home/joey/temp/zip/powershell2.zip

Tar command on Linux and Windows 10 BASH

Linux includes the venerable “tar” command, once used to create tape-archives. Installing the Ubuntu Linux subsystem on Windows 10 brngs this feature to Windows as well.

# Command syntax:
# tar -czvf name-of-archive.tar.gz /path/to/directory-or-file
tar -czvf files.tar.gz /mnt/c/temp/zip/files/

Same command run on Linux Mint.

# Command syntax:
# tar -czvf name-of-archive.tar.gz /path/to/directory-or-file
tar -czvf files2.tar.gz /home/joey/temp/zip/files/

Bonus: Windows Compressed Folders

Just to compare how efficient Windows built-in feature is to the command-line options above.

 

Resources:

https://superuser.com/questions/1105516/comparing-7z-exe-and-7za-exe

* There are compression tools built-in to Windows such as compact and makecab that are not covered here.

Generate passwords using Powershell

The Powershell script below can be used to generate 20 passwords using random words and numbers. Save the code to .ps1 file along with a “words” file in .csv format.

# Generates passphrases similar to password generator on intranet
# inspired by:
# https://www.hanselman.com/blog/DictionaryPasswordGeneratorInPowershell.aspx

# requires CSV file in same directory as script
#--------------------------------------------------------------------------------------------------

$rand = new-object System.Random

# read-in very large file creating smaller list of random words
$words = Get-Content "words.csv" | Sort-Object {Get-Random} -unique | select -first 100

Function Create-Password
{ 
  # query the smaller list of words for single entry (2 times)
  $word1 = $words | Sort-Object {Get-Random} -unique | select -first 1  
  $word2 = $words | Sort-Object {Get-Random} -unique | select -first 1  

  # create random digits
  $number1 = Get-Random -Minimum 1000 -Maximum 9999
  
  # concatenante and return new random password
  return (Get-Culture).TextInfo.ToTitleCase($word1) + (Get-Culture).TextInfo.ToTitleCase($word2) + $number1

}

# generate 20 passwords
for($i=1; $i -le 20; $i++){
  Create-Password
}

When executed the script will return 20 random passwords similar to this:

Note: you will need a “words” file from which random words will be pulled. I already had a “words” table in a MySQL database and used it to query for words between 5 and 7 characters in length. This gave me a file with about 15,000 words.

More information about obtaining a words list can be found on the following sites:
https://github.com/dwyl/english-words
https://stackoverflow.com/questions/2213607/how-to-get-english-language-word-database

Random strings in Powershell

Powershell code to generate 20 strings of 24 random letters and numbers.

for($i=1; $i -le 20; $i++){([char[]]([char]'a'..[char]'z' + [char]'A'..[char]'Z') + 0..9 | sort {get-random})[0..23] -join ''}

This will generate strings similar to this:

Fix broken trust using Powershell

When trying to log in to PC using a domain credential you get the following error:

“The trust relationship between this workstation and the primary domain failed” error when you log in to Windows 7

At this point I would usually re-join to the domain or run the Network Wizard, reboot, and continue on. There has to be a better way. And in-fact, there is via the Powershell command:

Reset-ComputerMachinePassword –server -credential

However, when I ran the command to reset the password I got an error stating the account could not be found on the domain controller:

PS C:\A3336> Reset-ComputerMachinePassword -server DELLR710 -credential AP\client_admin
Reset-ComputerMachinePassword : Cannot find the computer account for the local computer from the domain controller DELLR710.
At line:1 char:1
+ Reset-ComputerMachinePassword -server DELLR710 -credential AP\client_admin ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (A3336:String) [Reset-ComputerMachinePassword], InvalidOperationException
    + FullyQualifiedErrorId : CannotFindMachineAccount,Microsoft.PowerShell.Commands.ResetComputerMachinePasswordCommand

PS C:\A3336>

The fix was to create the account on the domain controller which I was able to do with Powershell on another PC that had Remote Server Administration Tools (RSAT) installed.

    PS C:\AP01-1255-915> New-ADcomputer –name "A3336" –SamAccountName "A3336" -Enabled $true
    PS C:\AP01-1255-915>

Now when the command is run on the client PC I am prompted to enter a username and password with permission to join computers to the domain and the command completes successfully.

    PS C:\A3336> Reset-ComputerMachinePassword -server DELLR710 -credential AP\client_admin
    PS C:\A3336>

http://implbits.com/active-directory/2012/04/13/dont-rejoin-to-fix.html
https://ss64.com/ps/reset-computermachinepassword.html
https://support.microsoft.com/en-us/help/2771040/the-trust-relationship-between-this-workstation-and-the-primary-domain

Start remote PC using WOL and Powershell

How to power-on remote PC using wake-on-lan (WOL) and Powershell. Run from a Windows 7 Pro with Powershell running with domain admin credentials.

$Mac = "f0:92:1c:e3:8f:60"
$MacByteArray = $Mac -split "[:-]" | ForEach-Object { [Byte] "0x$_"}
[Byte[]] $MagicPacket = (,0xFF * 6) + ($MacByteArray  * 16)
$UdpClient = New-Object System.Net.Sockets.UdpClient
$UdpClient.Connect(([System.Net.IPAddress]::Broadcast),7)
$UdpClient.Send($MagicPacket,$MagicPacket.Length)
$UdpClient.Close()

PS C:\Install> ping -4 -t AP01-1221-314

Pinging AP01-1221-314.AP.local [10.10.1.130] with 32 bytes of data
Reply from 10.10.1.179: Destination host unreachable.
Reply from 10.10.1.179: Destination host unreachable.
Reply from 10.10.1.179: Destination host unreachable.
Reply from 10.10.1.179: Destination host unreachable.
Reply from 10.10.1.179: Destination host unreachable.
Reply from 10.10.1.179: Destination host unreachable.
Request timed out.
Request timed out.
Request timed out.
Request timed out.
Request timed out.
Request timed out.
Request timed out.
Reply from 10.10.1.130: bytes=32 time=2ms TTL=128
Reply from 10.10.1.130: bytes=32 time=1ms TTL=128
Reply from 10.10.1.130: bytes=32 time=2ms TTL=128
Reply from 10.10.1.130: bytes=32 time=2ms TTL=128
Reply from 10.10.1.130: bytes=32 time=2ms TTL=128
Reply from 10.10.1.130: bytes=32 time=2ms TTL=128

Ping statistics for 10.10.1.130:
  Packets: Sent = 19, Received = 12, Lost = 7 (36% loss),
Approximate round trip times in milli-seconds:
  Minimum = 1ms, Maximum = 2ms, Average = 1ms
Control-C
PS C:\Install>

Just change MAC address and copy and paste. Of course, WOL must be enabled on remote PC for this to work.

Note: you’ll need Remote Server Administration Tools (RSAT) installed which you can download from Microsoft:
https://support.microsoft.com/en-us/help/2693643/remote-server-administration-tools-rsat-for-windows-operating-systems

Set Out-of-Office in Exchange using Powershell

The Powershell script below can be used to set the out-of-office auto-reply message for a specific mailbox from a workstation.

# start remote session from workstation
$s = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri http://MyExchangeServer/PowerShell/ -Authentication Kerberos
Import-PSSession $s

# enable Out of Office reply
$mbox = 'JCDough'
$internal = 'I am currently out of office and unable to return phone calls and emails. Please contact your TAS Specialist and or someone else on the TAS team with any immediate concerns. Thanks!'
$external = 'I am currently out of office and unable to return phone calls and emails. Please contact your account manager with any immediate concerns and we will make sure your taken care of. Thanks!'
Set-MailboxAutoReplyConfiguration $mbox -AutoReplyState enabled -ExternalAudience all -InternalMessage $internal -ExternalMessage $external

# Disable Out of Office reply
$mbox = 'JCDough'
Set-MailboxAutoReplyConfiguration $mbox -AutoReplyState disabled

References:
https://blogs.technet.microsoft.com/samdrey/2013/07/15/exchange-2010-enabling-an-autoreply-message-out-of-office-using-the-exchange-management-shell-powershell/

Import GAL photo using Powershell

Powershell commands to import contact photo into Exchange Global Address list.

# Import GAL Contact photo - put photo on NAS share
$s = New-PSSession -Name T01 -ConfigurationName Microsoft.Exchange -ConnectionUri http://dellr710/PowerShell/ -Authentication Kerberos
Import-PSSession $s

$alias = "mickey"
$photo = "\\files1\IT\Miscellaneous\Employees\GAL Photos\mickey.jpg" 
Import-RecipientDataProperty -Identity $alias -Picture -FileData ([Byte[]]$(Get-Content -Path $photo -Encoding Byte -ReadCount 0))

$alias = "minnie"
$photo = "\\files1\IT\Miscellaneous\Employees\GAL Photos\minnie.jpg" 
Import-RecipientDataProperty -Identity $alias -Picture -FileData ([Byte[]]$(Get-Content -Path $photo -Encoding Byte -ReadCount 0))

$alias = "donald"
$photo = "\\files1\IT\Miscellaneous\Employees\GAL Photos\donald.jpg" 
Import-RecipientDataProperty -Identity $alias -Picture -FileData ([Byte[]]$(Get-Content -Path $photo -Encoding Byte -ReadCount 0))
    
Update-OfflineAddressBook "Default Offline Address Book"

Exchange PowerShell can import pictures up to 10KB while the AD attribute supports a max size of 100 KB. Recommended thumbnail photo size is 96×96 pixels

Resources:

https://blogs.technet.microsoft.com/exchange/2010/03/10/gal-photos-in-exchange-2010-and-outlook-2010/ — READ THIS ONE
https://blogs.technet.microsoft.com/ilvancri/2009/11/17/upload-picture-in-outlook-2010-using-the-exchange-management-shell-exchange-2010/
https://tahoeninjas.wordpress.com/2015/04/10/uploading-high-resolution-user-profile-pictures-in-office-365/