URL Certificate expiration check using powershell

Using powershell to check for certificate expiration for a url.

Create a folder called C:\URLCertexpiry
add a text file in it named URLsToCheckforCertExpiry.txt. Add the url's you want to check for certificate expiration in this text file.


$ErrorActionPreference = "Stop"

Out-file -FilePath C:\URLCertExpiry\URLCERTExpiryLog.txt -InputObject "Starting Script at $(get-date)" -Append
try {Remove-Item -path C:\URLCertExpiry\URLCERTExpiryReport.txt -Force -ErrorAction Continue} catch {$_.exception}
$UrlList = @()
$Data = @()
$ExpiringCollection = @()
$ExpiredCollection = @()

$username = "username"
$password = "Password" | ConvertTo-SecureString -asPlainText -Force
$cred = New-Object System.Management.Automation.PSCredential($username,$password)
$UrlList = GC "C:\URLCertExpiry\URLsToCheckforCertExpiry.txt"
$TLS12urls = ("https://www.google.com",
"https://www.bing.com"
)
foreach ($url in $UrlList )
{
try{
$message = "Working on $url"
Write-host $message -ForegroundColor Yellow
Out-file -FilePath C:\URLCertExpiry\URLCERTExpiryLog.txt -InputObject $message -Append
$minimumCertAgeDays = 30
 $timeoutMilliseconds = 20000
#disabling the cert validation check. This is what makes this whole thing work with invalid certs...
 [Net.ServicePointManager]::ServerCertificateValidationCallback = {$true}
if($TLS12urls -contains $url)
{

 [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
}

else
{
 [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls
}

 Write-Host Checking $url -f Green
 $req = [Net.WebRequest]::Create($url)
 $req.Credentials = $cred
 $req.Timeout = $timeoutMilliseconds
try {
 $response = $req.GetResponse()
 $response.Close()
[datetime]$expiration = $req.ServicePoint.Certificate.GetExpirationDateString()
 [int]$certExpiresIn = ($expiration - $(get-date)).Days

 if($certExpiresIn -gt $minimumCertAgeDays)
 {
   $expirationState = "NotExpiring"
 }

 elseif($certExpiresIn -lt $minimumCertAgeDays -and $certExpiresIn -gt 0)
 {
   $expirationState = "Expiring"
$ExpiringCerts= $url + "        " +  $expirationState + "      Validity   "+ $certExpiresIn +" " + "days"
$ExpiringCollection+=$ExpiringCerts
 }

elseif($certExpiresIn -lt $minimumCertAgeDays -and $certExpiresIn -lt 0)
 {
   $expirationState = "Expired"
$ExpiredCerts= $url + "        " +  $expirationState + "      Validity   "+ $certExpiresIn +" " + "days"
$ExpiredCollection+=$ExpiredCerts
 }

 $Data1 = $url + "        " +  $expirationState + "      Validity   "+ $certExpiresIn +" " + "days"
  Write-Host $data1 -ForegroundColor Yellow
 $Data+=$Data1


}

catch {
$message =  "Exception while checking URL $url`: $_ "
Out-file -FilePath C:\URLCertExpiry\URLCERTExpiryLog.txt -InputObject $message -Append
continue
}
}

catch {
$Message = $_.exception.Message
Out-file -FilePath C:\URLCertExpiry\URLCERTExpiryLog.txt -InputObject $(get-date) -Append
Out-file -FilePath C:\URLCertExpiry\URLCERTExpiryLog.txt -InputObject $message -Append
Continue
}


}

Write-Host $data -ForegroundColor Yellow
Out-file -FilePath C:\URLCertExpiry\URLCERTExpiryReport.txt -InputObject $Data -Append
$Data
Out-file -FilePath C:\URLCertExpiry\URLCERTExpiryReport.txt -InputObject "Email Data" -Append
Out-file -FilePath C:\URLCertExpiry\URLCERTExpiryReport.txt -InputObject $ExpiringCollection -Append
$ExpiringCollection
Out-file -FilePath C:\URLCertExpiry\URLCERTExpiryReport.txt -InputObject $ExpiredCollection -Append
$ExpiredCollection

$ExpiringCollectionArray=$null
$ExpiredCollectionArray=$null
$ExpiringCollection | %{$ExpiringCollectionArray+=$_}
$ExpiredCollection | %{$ExpiredCollectionArray+=$_}

$ExpiringCollection | Select @{label='Expiring Certificates:';expression={$_}} | ConvertTo-HTML -Fragment -Property 'Expiring Certificates:' -As List | % { $_ -replace '<td>Expiring Certificates::</td>', ''} | % { $_ -replace '<tr><td><hr></td></tr>', '' } | Out-File C:\URLCertExpiry\report.html -append
$ExpiredCollection | Select @{label='Expired Certificates:';expression={$_}} | ConvertTo-HTML -Fragment -Property 'Expired Certificates:' -As List| % { $_ -replace '<td>Expired Certificates::</td>', '' } | % { $_ -replace '<tr><td><hr></td></tr>', '' }   | Out-File C:\URLCertExpiry\report.html -append


Out-file -FilePath C:\URLCertExpiry\URLCERTExpiryReport.txt -InputObject "Collection Values" -Append
Out-file -FilePath C:\URLCertExpiry\URLCERTExpiryReport.txt -InputObject $ExpiredCollectionArray -Append
Out-file -FilePath C:\URLCertExpiry\URLCERTExpiryReport.txt -InputObject $ExpiringCollectionArray -Append

Out-file -FilePath C:\URLCertExpiry\URLCERTExpiryLog.txt -InputObject "Ending Script at $(get-date)" -Append

$Data
$ExpiringCollectionArray
$ExpiredCollectionArray

Openssl convert pfx to key and cert file

Link to full list of commands is here.

https://stackoverflow.com/questions/13732826/convert-pem-to-crt-and-key

Convert pfx to cert without keys

openssl pkcs12 -in ServerName.pfx -clcerts -nokeys -out ServerName.crt

Convert pfx to pem. add -nokeys to only export cert. add -nocerts to only export keys

openssl pkcs12 -in ServerName.pfx -out ServerName.pem -nodes

Convert pem to cert

openssl x509 -outform der -in ServerName.pem -out ServerName-Cert.crt

RDP issues

Turn of NLA:

https://fixingitpro.com/2011/07/06/disabling-rdp-network-level-authentication-nla-remotely-via-the-registry/

Give network service account read permissions to the Keys folder.

https://serverfault.com/questions/541364/how-to-fix-rdp-on-windows-server-2012

https://support.microsoft.com/en-us/help/2001849/how-to-force-remote-desktop-services-on-windows-7-to-use-a-custom-serv

Make sure Remote Desktop Services(TermService) service is running under network service.

SQL spn and linked server double hop and kerberos

Good link to setup linked server and spn's

https://sqljana.wordpress.com/2017/06/16/sql-server-curse-of-linked-server-security-and-the-fix-pass-through-authentication/


https://www.alexandreviot.net/2014/09/30/sql-server-could-not-register-the-service-principal-name-spn/


Query to see if Kerberos is being used.

Collection of usefull excel formulas

This formula will give a day of the week with name from a cell

=CHOOSE(WEEKDAY(C2),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

Use a matching value in a cell to get another cells value

If you use match formula. It gives you the cell number of the matching value found.










 

Adding a disk to Azure vm using powershell

Powershell Script to add a disk to Azure RM virtual machine.

$Credentials = Get-Credential
param([string] $VirtualMachineName,$DiskSize)

Login-AzureRmAccount -EnvironmentName AzureUSGovernment -Credential $Credentials

$Managed_Prod_Sub = Get-AzureRMSubscription -SubscriptionId "Your subscription id"
Select-AzureRMSubscription -SubscriptionId $Managed_Prod_Sub.SubscriptionId ##
$Managed_ProdVMS = Get-AzureRMVM


if($VM.name -eq $VirtualMachineName)
  {
  Write-host "Working on $($VM.name)" -ForegroundColor Yellow
     $rgName = $VM.ResourceGroupName
    $vmName = $VM.Name
    $location = $VM.location
    $storageType = 'Premium_LRS'
    $dataDiskName = $vmName + '_datadisk1'

   
    $diskConfig = New-AzureRmDiskConfig -AccountType PremiumLRS -Location $location -CreateOption Empty -DiskSizeGB $DiskSize -OsType Windows

    $dataDisk1 = New-AzureRmDisk -DiskName $dataDiskName -Disk $diskConfig -ResourceGroupName $rgName
   
    $vm = Get-AzureRmVM -Name $vmName -ResourceGroupName $rgName
    $vm = Add-AzureRmVMDataDisk -VM $vm -Name $dataDiskName -CreateOption Attach -ManagedDiskId $dataDisk1.Id -Lun 1

    Update-AzureRmVM -VM $vm -ResourceGroupName $rgName


   }

Good trick for using Try Catch in Powershell

    try{

 Something......

    }

    Catch{
$formatstring = "{0} : {1}`n{2}`n" +
                "    + CategoryInfo          : {3}`n" +
                "    + FullyQualifiedErrorId : {4}`n"
$fields = $_.InvocationInfo.MyCommand.Name,
          $_.ErrorDetails.Message,
          $_.InvocationInfo.PositionMessage,
          $_.CategoryInfo.ToString(),
          $_.FullyQualifiedErrorId

Out-File -FilePath 'C:\Temp\error.txt' -inputobject ($formatstring -f $fields) -Append
 
  }
   

Get Azure VM status and ip address

Powershell script to get the list of virtual machines in Azure Gov with ip address.

$username = "parag.waghmare@contoso.com"

$Password = "Password"

$Credentials = New-Object System.Management.Automation.PSCredential ($username, $password)

Login-AzureRmAccount -EnvironmentName AzureUSGovernment -Credential $Credentials 

$subs = Get-AzureRmSubscription 

foreach ($Sub in $Subs) { 

   

    $SelectSub = Select-AzureRmSubscription -SubscriptionName $Sub.Name 

    $nics = get-azurermnetworkinterface | where VirtualMachine -NE $null #skip Nics with no VM

    $VMs = Get-AzureRMVM -Status

    foreach($nic in $nics)

{

    $vm = $vms | where-object -Property Id -EQ $nic.VirtualMachine.id

    $prv =  $nic.IpConfigurations | select-object -ExpandProperty PrivateIpAddress

    $alloc =  $nic.IpConfigurations | select-object -ExpandProperty PrivateIpAllocationMethod

    $data = $Sub.Name + "`t "+ $vm.Name + "`t "+ $prv + "`t "+ $alloc  + "`t " + $Vm.powerstate 

    Write-Host $data 

}

} 

Match vmware virtual machine hard disk to computer hard disks

One of the common problems that vcenter admins have faced is mapping the hard disks seen in vmware to the hard disks seen on computers in disk management.

This powershell script helps in matching disks seen in vcenter to those seen on the computer.
Install the vmware powercli before this. Recent powercli link is here
https://code.vmware.com/web/dp/tool/vmware-powercli/6.5.4

Even if you have an updated one the script should work fine. Unless the cmdlets are changed.

You also need to have at least read permissions in vmware and administrator on the server.

If the disk serial number is missing in the OS this script will not work.

Check  if the disk serial number is present by running this in powershell.

Get-WmiObject -Class Win32_DiskDrive | select serialnumber

If there is no serial number then add/modify the following parameter in the vm's advanced configuration.

“disk.EnableUUID = “TRUE”

KB link is here
https://kb.vmware.com/s/article/52815


 ## Show corresponding disks in vmware and computer ##

param(
   [Parameter(Mandatory=$true)]
[string] $Computername
)


Function OutData($data) {Write-host $data -ForegroundColor Green}
Function Get-LocalDisk($Computer,[System.Management.Automation.CredentialAttribute()]$Credentials)
{
$PDiskDrives = Get-WmiObject -Class Win32_DiskDrive -ComputerName $Computer -Credential $Credentials

return $PDiskDrives
}

## Load Vmware assemblies ##
Import-Module VMWare.VimAutomation.sdk
Import-Module VMWare.VimAutomation.Core

## Connect Vmware
$Credentials = Get-Credential  -Message "Enter your Vcenter credentials"
$LocalDriveObjects =  @()
Connect-VIServer Vcenter -Credential $Credentials
$LocalDisks = Get-LocalDisk $Computername -Credentials $Credentials
$vmHardDisks = Get-VM -Name $ComputerName | Get-HardDisk
$vmDatacenterView = Get-VM -Name $ComputerName | Get-Datacenter | Get-View
$virtualDiskManager = Get-View -Id VirtualDiskManager-virtualDiskManager
Write-Host "Number of disks : $($LocalDisks.count)"
foreach($DObject in $LocalDisks)
 {

     # Write-host "Working on $DObject"
 
      foreach($vmHardDisk in $vmHardDisks)
              {
              $vmHardDiskUuid = $virtualDiskManager.queryvirtualdiskuuid($vmHardDisk.Filename, $vmDatacenterView.MoRef) | foreach {$_.replace(' ','').replace('-','')}
            
              if($DObject.SerialNumber -eq $vmHardDiskUuid)
                {
                 $Output = "$($vmHardDisk.Name) $($vmHardDisk.capacityGB)" + "GB" + " Computer Disk: " + $("Disk " + $Dobject.Index) + " " + $([math]::round($DObject.Size/1GB, 3)) + "GB" + " on $Computername"
                 $Vdisk = "Vcenter: $($vmHardDisk.Name) $($vmHardDisk.capacityGB)" + "GB "
                 $ComputerDisk = "Computer: " + $("Disk " + $Dobject.Index) + " " + $([math]::round($DObject.Size/1GB, 2)) + "GB"
                 Write-Host $Vdisk -ForegroundColor Yellow -NoNewline
                 Write-Host $ComputerDisk -ForegroundColor White
             
               
                }
              }
   
 }






 ## Author: Parag Waghmare

Service Now: Invoke-WebRequest : The remote server returned an error: (403) Forbidden.

If you get 403 for an api call in service now. One of the reasons could be that the user does not have permissions on the table. Which in my case was the "sc_requests"
Here is the error:
Invoke-WebRequest : The remote server returned an error: (403) Forbidden.
Open the service now portal and elevate permissions if not already done

Click System Definition -- Tables and search for your table



 The click Access controls.
Either create a new one or find one which has been already created with name sc_request.* . If you open this ACL you may see that it has the catalog and itil roles added to it.
Click on catalog and edit users tab to add the user account which is being provided in your web request.



You can also directly search for the catalog user role in User Administration - User Roles and add permissions from there.

Service Now: Script to access servicenow Api using powershell.

Script to access servicenow Api using powershell.

# Eg. User name="admin", Password="admin" for this code sample.
$user = "admin"
$pass = "admin"

# Build auth header
$base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f $user, $pass)))

# Set proper headers
$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$headers.Add('Authorization',('Basic {0}' -f $base64AuthInfo))
$headers.Add('Accept','application/json')

# Specify endpoint uri
$uri = "https://myservicenow.service-now.com/api/now/table/sc_request?sysparm_limit=10"

# Specify HTTP method
$method = "get"

# Send HTTP request
try{$response1 = Invoke-WebRequest -Headers $headers -Method $method -Uri $uri}
catch{$_.Exception.Response.StatusCode.Value__}

# Print response
$response1.RawContent