Microsoft

Excel

Conditional highlighting with functions

This is a collection of frequently used functions for conditional highlighting in Excel.

Highlight cells that are formatted dates and the dates are older than X days. Applies to =$A:$A if all dates you want highlighted are in the A column.

=IF(LEFT(CELL("format",A1),1)="D",IF(A1<TODAY()-37,TRUE,FALSE),FALSE)

:end

Installing Certificates on Windows

Using PowerShell to install into the Local Computer store

This can only be done with elevated privileges.

Import-Certificate -CertStoreLocation Cert:\LocalMachine\Root -FilePath cert.crt

Microsoft Remote Desktop Certificates

Manually replacing RDP certificate

Install the new certificate in the Local Computer Personal store:

If no password is needed:

Import-PfxCertificate -CertStoreLocation Cert:\LocalMachine\My -FilePath cert.pfx

Or if a password is needed:

$mypwd = Get-Credential -UserName 'Enter password below' -Message 'Enter password below'
Import-PfxCertificate -CertStoreLocation Cert:\LocalMachine\My -Password $mypwd.Password -FilePath cert.pfx

After installing the new certificate in the Local Computer Personal store, run the following commands:

Set-Location Cert:\LocalMachine\my
Get-ChildItem

Pick the Thumbprint of the certificate you wish to use then run the following command using the proper thumbprint:

#Replace Certificate for RDS
wmic /namespace:\\root\cimv2\TerminalServices PATH Win32_TSGeneralSetting Set SSLCertificateSHA1Hash="[new_cert_thumbprint]"

This effectively updates the registry key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Terminal Server\WinStations\RDP-Tcp\SSLCertificateSHA1Hash

Use the following command to verify that the proper certificate is being used:

Get-WmiObject "Win32_TSGeneralSetting" -Namespace root\cimv2\terminalservices -Filter "TerminalName='RDP-tcp'"

Source

Windows Server 2022 method

Couldn't the WMI method to work on Windows Server 2022. This did.

$serverName = "MYTS01"
$certHash = "xxxxx"
$path = (Get-WmiObject "Win32_TSGeneralSetting" -ComputerName $serverName -Namespace root\cimv2\terminalservices -Filter "TerminalName='RDP-tcp'")
Set-WmiInstance -Path $path -Arguments @{SSLCertificateSHA1Hash=$certHash}

Signing RDP files

Use the rdpsign app to

rdpsign /sha256 xxxxxxxx "Remote Desktop File.rdp"

 

Network Policy Server / NPS

 

Enable NTLMv2 support for MSCHAPv2 RADIUS requests

Enables proxied radius requests when using things like radsecproxy, duoauthproxy, etc. Avoids the MS-CHAP-Error responses.

$registryPath = "HKLM:\System\CurrentControlSet\Services\RemoteAccess\Policy"
$propertyName = "Enable NTLMv2 Compatibility"
$propertyValue = "1"

New-ItemProperty -Path $registryPath -Name $propertyName -Value $propertyValue -PropertyType DWORD -Force

Stop-Service IAS
Sleep 2
Start-Service IAS

Source 1 / Source 2

Other Useful PowerShell Commands

Placeholder

Format processes by start date

This command will show a lot of errors if you're not running PowerShell as Administrator.

Get-Process | Sort-Object StartTime | Format-Table -View StartTime

Placeholder

Sources:
PowerShell Format-Table

Querying Event Logs

I noticed that there is a huge speed difference between using an XML Query and PowerShell Get-EventLog piped through Where-Object to filter event logs. Thanks to this article, I learned how to use the XML Query via PowerShell, so you get the best of both worlds.

Finding account lockouts.

XML Query

Use this query in the Windows Event Viewer

<QueryList>
  <Query Id="0" Path="Security">
    <Select Path="Security">
      *[
        System[(EventID='4740')]
      ] 
    </Select>
  </Query>
</QueryList>

PowerShell Script - Slow method

Get-EventLog -LogName Security | Where-Object {$_.EventID -eq 4740} |
   Select-Object -Property TimeGenerated, Source, EventID, InstanceId, Message

PowerShell Script - Fast method

$query = @"
<QueryList>
  <Query Id="0" Path="Security">
    <Select Path="Security">
      *[
        System[(EventID='4740')]
      ] 
    </Select>
  </Query>
</QueryList>
"@

Get-WinEvent -FilterXml $query | Format-List

Finding account lockouts for a particular user.

XML Query

<QueryList>
  <Query Id="0" Path="Security">
    <Select Path="Security">
      *[
        EventData[Data[@Name='TargetUserName']='administrator']
        and
        System[(EventID='4740')]
      ] 
    </Select>
  </Query>
</QueryList>

PowerShell Script - Fast method

$query = @"
<QueryList>
  <Query Id="0" Path="Security">
    <Select Path="Security">
      *[
        EventData[Data[@Name='TargetUserName']='administrator']
        and
        System[(EventID='4740')]
      ] 
    </Select>
  </Query>
</QueryList>
"@

Get-WinEvent -FilterXml $query | Format-List

NPS + Azure MFA Logs

XML Query

<QueryList>
  <Query Id="0" Path="System">
    <Select Path="System">*[System[Provider[@Name='NPS']]]</Select>
    <Select Path="Security">*[System[Provider[@Name='Microsoft-Windows-Security-Auditing'] and Task = 12552]]</Select>
    <Select Path="AuthNOptCh">*</Select>
    <Select Path="AuthZAdminCh">*</Select>
    <Select Path="AuthZOptCh">*</Select>
  </Query>
</QueryList>

 

Sources: 1