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

Resolve Cloudflare warning about exposed IP

When setting-up DNS entries for a website on Cloudflare, the website displays the following warning when adding an MX record:

The fix is to not use Cloudflare’s server for the A record that the MX record references:

Warning message presented by Cloudflare:

This record is exposing your origin server’s IP address, potentially exposing it to denial of service.

List Mailboxes in Exchange 2010 using Powershell

This will list user mailboxes – mailboxes that are not resource mailboxes (rooms, calendars, etc.).

Does not work cannot match on actual word “False” (or “True”):

get-mailbox -resultsize unlimited | where { $_.IsResource -eq ‘False’ } | select Alias, Name, IsResource, IsMailboxEnabled | ft -auto

Instead you must match on the results of the evaluation:

get-mailbox -resultsize unlimited | where { $_.IsResource -eq $False } | select Alias, Name, IsResource, IsMailboxEnabled, OrganizationalUnit | ft -auto

Get number of user mailboxes:

$c = get-mailbox -resultsize unlimited | where { $_.IsResource -eq $False } | select Identity
$c.count

Things IT People Say

Usually the funny stuff is saved for my personal blog but, technically this is IT related and my non-IT friends wouldn’t get it anyway. This isn’t “cat herder” funny but, still pretty funny.

And yeah I tell my users this stuff!

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/

How to re-enable Recovery Media creation on Lenovo T530

I had to restore a Lenovo T530 laptop running Windows 8 back to Windows 7. I tried to use the recovery media that I put on an SD card but, the laptop couldn’t boot from the SD card. So, I decided I would just recreate the recovery media using DVDs.

I went to another T530 laptop running Windows 7 to create the recovery media but, was presented with the following message:

You can have only one copy of the Microsoft Windows operating system.
The Product Recovery Media Creator will exit now.

Apparently, I had already created recovery media from this machine. Setting aside the stupidity of this tactic or it’s implied meaning (creating recovery media = piracy), I needed to trick this machine into letting me do this.

A quick Google search shows just how common this problem is and fortunately a fix. Unfortunately, many of the results are for older Thinkpads that reference a file that no longer exists. Fortunately, the latest fix was found in this forum post on superuser.com.

To fix, just run this command from a CMD prompt:

echo 0 > Q:\FactoryRecovery\RECOVERY.INI:Done

After running go back into ThinkVantage Tools and retry creating recovery media – it should work.

Create custom wallpaper for your servers

I like to set the Windows Desktop to a specific color to easily identify it from other desktops I may have open. This way when I have multiple Remote Desktops running I can quickly see which one I need to click on. At a minimum I usually set the background color of the Desktop.

Recently though, I started making custom images that I use as wallpaper that incorporate color idea and adds information to positively identify the server I am working on. This information usually includes the host name, IP address, a brief description, and the type of OS running.

I try to make the color the same as the color associated with the primary software installed on  the server. For example, our Prophet21 server is purple since that was their primary color in their logo, orange is used for our Shoretel server, etc. Some servers just have a color that was picked randomly years ago and stuck. Our database server uses olive while our webserver uses teal.

I use Gimp to make the images and put them on the root of the C: drive of the server. Since these are servers themes are usually disabled. To make the image appear as wallpaper I have to open them in Paint and from the File menu select “Set as Wallpaper”. The following are examples of wallpaper for a few of my servers.

This is a screenshot of my Taskbar showing how helpful these wallpapers are:

Cannot login to Windows machine on vmware snapshot

In setting up a new SQL Server 2008 server in VMWare I created a baseline snapshot of the machine as it was before installing SQL Server. This way I could practice installing SQL Server refining the process with each installation.

Today, I reverted back to my baseline snapshot and suddenly could not log in to the server using Remote Desktop. The Widows 7 Remote Desktop client was less than helpful about what could be the cause.

Checking the Event Log on the server only added to the mystery. The server recorded event ID 4625 for a logon failure. According to the log the username and password was not correct but, I know that was not true.

The clue that revealed the actual cause of the problem came from Windows 8! When trying to connect to the server using Remote Desktop on Windows 8 the error was much more specific:

True enough, when I looked down at the time the date and time was way off. The server running in the virtual machine appeared to be using the date of the snapshot’s creation. Being a member of an Active Directory domain I expected the date to be set to the date and time of the domain controller.

Fortunately, you can run DOS command to set the time to the correct time provided by the network time server. The following command resets the time and instructs the server to seek out the network time server:

w32tm /resync /rediscover

Surprisingly, the ancient “net time” command still works and gives you instant feedback:

net time /set

The lesson learned here was to make sure to reset the date and time when going back to a previous snapshot in VMWare.

How to start Prophet21 Acclaim

Today I needed to power-up our old Prophet21 Acclaim server to find some old information. It has been 7 years since we upgraded and the terminal connected to the machine did not work. So, I connected a switch and laptop to the server and tried to get in that way.

Unfortunately, when I tried to go into Acclaim I got the “Stopped!” message. I remember I had to enter an ON and a GO somewhere but, couldn’t remember where. The trick it turns out is being the console. The console automatically goes to UTILITY COMMAND. The problem was I wasn’t on the console. It took a call from a colleague to remind me how to seize the console.

1. login as root or su to root
2. if not on console take control of console: #p21 -t tty0
3. at UTILITY COMMAND… type ON
4. at UNIT NR… type 0
5. at FRACTION FULL DISPLAY… <enter>
6. keep hitting <enter> until back at UT prompt
7. at UTILITY COMMAND… type GO <enter>
8. at UTILITY COMMAND… hit <enter>
9. at JOB… prompt hit <enter>
10. Login to P21

Hopefully, I will never have to use this again but, just in case here it is.

Use 7-Zip to install ISO files

Challenge: You download an ISO (like Office 2010 from Microsoft) but, cannot install it because your computer doesn’t have a CD-ROM.

Solution: Use 7-Zip to extract the contents to a folder and install from there.

Note: Windows 8 and Windows Server 2012 can mount ISOs to virtual CD drives natively but, Windows XP, Windows Vista, and Windows 7 cannot. However, Microsoft has created a utility to enable this functionality which can be downloaded from here:

http://www.microsoft.com/en-us/download/details.aspx?id=38780

Importance of monitoring

One day we noticed our Internet just wasn’t what we were used to. Although no-one was complaining we in the IT department were feeling it whenever we downloaded a large file like a Microsoft ISO. Fortunately, we had setup Cacti — a MRTG/RRD monitoring solution. A quick look at Cacti showed we were only getting about 3Mbs of Internet:

Cacti graph of our Internet circuit

Our Internet provider supplies us with a portal in which we can see our usage. Comparing our graph with their graph confirmed a problem existed:

Vendor’s utilization graph matching ours

Now, we just had to determine where the problem lie. First, we checked our Internet using speedtest.net on the inside of the firewall. The result was about 3Mbs – just what we expected. Then we ran the same test on the outside of the firewall. The result was 20Mbs — what we should be getting. This meant the problem was in the firewall.

Turns out a port had recently been mis-configured to use half-duplex causing collisions on the connection. Within just a few minutes of discovery our problem had been resolved. If it had not been for the visual graph provided by Cacti we would have been going through logs to determine what and where the problem was easily using up time better spent elsewhere.

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/

How to edit client permissions in Exchange 2010

Exchange Management Powershell window

Aside from the GUI Exchange 2010 gives you two ways to assign client permissions — the permissions that dictate what end-users or clients can do — to Public Folders. One is a Powershell command that will apply permissions to a single folder. The other is a Powershell script that will apply permissions to a single folder and all subfolders.

Command to add client permission to single folder:

Add-PublicFolderClientPermission -User ‘mydomain\jchaven’ -AccessRights ‘Reviewer’ -Identity ‘\Management\Former Employee Mailboxes\John Doe\Calendar’ -Server ‘servername.mydomain.local’

Powershell script to add client permissions to folder and below:

AddUsersToPFRecursive.ps1 -TopPublicFolder “\Management\Former Employee Mailboxes\John Doe” -User “jchaven” -Permission Reviewer

Note: The Shell doesn’t load scripts automatically. You must precede all scripts with .\ (a period, followed by a backslash). For example, to run the AggregatePFData.ps1 script, type .\AggregatePFData.ps1.

The collection of Shell scripts described in this topic is installed, by default, at <Exchange Installation Path>\v14\Server\Scripts.

See TechNet for more information

NOTE: You may need to change path to location of scripts in Powershell. When I tried adding location of scripts to PATH variable in a command prompt it did not work. Click on image to see what I mean.