test-connections – ping a list of addresses from a csv file and report result.

#test_connections - read a csv list of hosts and ping each one, record success or failure. Does a dns lookup of name
# CSV file format
#  ip_address - ipv4 address for ping and traceroute 
#  Name  - dns lookup will be done on this
#  Description
#

$target=import-csv -path "test_connections_target.csv"

Write-Host -ForegroundColor green "Testing from $($env:computername)."	
$results=@()
foreach ( $t in $target ) {
    $t1=$t.name
    $t2=Resolve-DnsName $t1
    $item= New-Object PSobject 
    
    $item | Add-Member -MemberType NoteProperty -Name 'source' -Value ($env:computername)
    
    $item | Add-Member -MemberType NoteProperty -Name 'IPv4Address' -Value ($t.ip_address)
    $item | Add-Member -MemberType NoteProperty -Name 'Name' -Value ($($t2[0].name))
    $item | Add-Member -MemberType NoteProperty -Name 'Description' -Value ($t.Description)
    $item | Add-Member -MemberType NoteProperty -Name 'GateWay' -Value ("")
    $item | Add-Member -MemberType NoteProperty -Name 'ResponseTime' -Value ("")
            
    $message="$($t.IP_Address), $($t2[0].name), $($t.Description), "
    try { $out1=Test-Connection -ComputerName $t1 -Count 1 -ErrorAction stop
            $message+=" response time, $($out1.responsetime)"
            $item.ResponseTime="$($out1.responsetime)"
            #now do a traceroute
            $out2=Test-NetConnection -computername $($t.IP_Address) -TraceRoute
            if ($out2.TraceRoute.Count -gt 1 ) { 
                $message+=" Gateway $($out2.traceroute[0])"
                $item.GateWay=$out2.TraceRoute[0]
                }
            else
                {
                $message+=" local address"
                $item.GateWay="local"
                }
            Write-Host -ForegroundColor green $message
            $item | Add-Member -MemberType NoteProperty -Name 'Result' -Value ("Success")


        }
    catch {
        $item | Add-Member -MemberType NoteProperty -Name 'Result' -Value ("Fail")
        $message+="**Error** no connection or timeout"
        Write-Host -ForegroundColor Red -BackgroundColor Yellow $message
        #Write-Error $message
          
        }
    $results += $item

}

$dt=Get-Date -Format "yyyyMMdd-hhmmss"
$csv_file="$($env:temp)/$dt-$($env:computername).csv"
write-host "Output for $($results.count) targets written to $csv_file"
$results | Export-Csv -NoTypeInformation -Path $csv_file

#Read-Host "how was that?"
Posted in Powershell, VBS, VBA and other scripting. | Tagged , | Comments Off on test-connections – ping a list of addresses from a csv file and report result.

powershell script to create and export a certificate

When you have an internal Microsoft Certificate Authority you can create certificates for all your devices which will be trusted by all the domain member computer.

Domain members will automatically have a certificate generated for them. Devices such as switches, firewalls etc… generally will need the certificate to be created for each of them.

I find it convenient to generate a new certificate and private key and then import those into the device. This script will generate a new certificate from the internal CA. Export that certificate to a PFX file including the private key (password protected).
It then uses openssl to convert the certificate and key to a text format of the data. This might still need to be “cleaned up” for some devices but has the critical encoded data.

you will need to be running the code with an account that has the necessary privileges. The name of the certificate template you are using will need to be edited in the code Basically anything ACME will need to be replace with the relevant details for your setup.

create_and_export_certificate.ps1

#
# create_and_export_certificate.ps1 
# 
function My_Create_Certificate { 
    param ( 
        [string]$hostname, 
        [string]$domain, 
        [string]$subject, 
        [string]$domain2,
        [string]$domain3

        )


    # create a new certificate - 5 year expiration ACME-CA template
    #$hostname="*"
    $name="$hostname.$domain"
    $dnsname=@()
    $dnsname+=$name
    if ( $domain2 -ne "" )  { $dnsname+="$hostname.$domain2" } 
    if ( $domain3 -ne "" )  { $dnsname+="$hostname.$domain3" }
    $dnsname+="$hostname"

    $newcert=Get-Certificate -template "myinternalwebservertemplate5years" -subjectname $subject -dnsname $dnsname  -url ldap: -CertStoreLocation cert:localmachine\my

    $newcert

    
} 

# generate a random name for testing
$datetime=get-date -Format "yyyy-MM-dd-hh-mm-ss"
$hostname="my-new-device-$datetime"

$domain="acme.com.au" 
$domain2="acme.local" 
$domain3="" 
$subject='C=AU;O="Acme Australia Pty Ltd";OU=HQ;CN='+"$hostname.$domain"

$newcert = My_Create_Certificate -hostname $hostname -domain $domain -subject $subject -domain2  $domain2 -domain3 $domain3 
$password="e43$H()cvgs4344c434x612" 

# export the certificate and its private key to a file
cd $env:TEMP
cd pfx

$mycerts=Get-ChildItem -Path "Cert:\localmachine\My\$($newcert.certificate.thumbprint)"
#$mycerts |  Where-Object { $_.hasPrivateKey } | Foreach-Object {&certutil.exe @('-exportpfx', '-f', '-p','$password',$_.Thumbprint, "expo_$($_.Subject).pfx") }
foreach ( $cert in $mycerts ) {
    if ( $cert.hasprivatekey ) {
        $filename= $cert.subject -replace " ","-" 
        $filename=  $filename -replace "\*","X"
        $filename=  $filename -replace ",",""
        $filename=  $filename -replace "CN=",""
        $filename += ".pfx"
        $retval= &certutil.exe @('-exportpfx', '-f', '-p',"$password",$cert.Thumbprint, "$filename") 
        dir $filename

        #
        # now use openssl to split into a key file and a cert file - as wanted by Cisco ISE 
        #
        $fname = $filename -replace ".pfx", ""
        start-process -filepath 'C:\Program Files\OpenSSL-Win64\bin\openssl.exe' -argumentlist "pkcs12 -in $filename -out $fname-certs.txt -nodes -nokeys -password pass:$password" 
        start-process -filepath 'C:\Program Files\OpenSSL-Win64\bin\openssl.exe' -argumentlist "pkcs12 -in $filename -out $fname-key.txt -nodes -nocerts -password pass:$password" 

       
        dir $fname*.txt

        }
    }




Posted in Powershell, VBS, VBA and other scripting. | Comments Off on powershell script to create and export a certificate

IP4 Network Scanning using powershell.

sometimes you want to scan a subnet to see what devices are on it. If you need to scan from inside the more secure environment you can’t install any extra software on it.

Machines with the minimum attack surface.


This powershell script “ipscan.ps1” will work through ipv4 netowrk addresses and output a .csv file containing the address and dns name for all hosts that responded to pings.

#
# create_and_export_certificate.ps1 
# 
function My_Create_Certificate { 
    param ( 
        [string]$hostname, 
        [string]$domain, 
        [string]$subject, 
        [string]$domain2,
        [string]$domain3

        )


    # create a new certificate - 5 year expiration ACME-CA template
    #$hostname="*"
    $name="$hostname.$domain"
    $dnsname=@()
    $dnsname+=$name
    if ( $domain2 -ne "" )  { $dnsname+="$hostname.$domain2" } 
    if ( $domain3 -ne "" )  { $dnsname+="$hostname.$domain3" }
    $dnsname+="$hostname"

    $newcert=Get-Certificate -template "myinternalwebservertemplate5years" -subjectname $subject -dnsname $dnsname  -url ldap: -CertStoreLocation cert:localmachine\my

    $newcert

    
} 

# generate a random name for testing
$datetime=get-date -Format "yyyy-MM-dd-hh-mm-ss"
$hostname="my-new-device-$datetime"

$domain="acme.com.au" 
$domain2="acme.local" 
$domain3="" 
$subject='C=AU;O="Acme Australia Pty Ltd";OU=HQ;CN='+"$hostname.$domain"

$newcert = My_Create_Certificate -hostname $hostname -domain $domain -subject $subject -domain2  $domain2 -domain3 $domain3 
$password="e43$H()cvgs4344c434x612" 

# export the certificate and its private key to a file
cd $env:TEMP
cd pfx

$mycerts=Get-ChildItem -Path "Cert:\localmachine\My\$($newcert.certificate.thumbprint)"
#$mycerts |  Where-Object { $_.hasPrivateKey } | Foreach-Object {&certutil.exe @('-exportpfx', '-f', '-p','$password',$_.Thumbprint, "expo_$($_.Subject).pfx") }
foreach ( $cert in $mycerts ) {
    if ( $cert.hasprivatekey ) {
        $filename= $cert.subject -replace " ","-" 
        $filename=  $filename -replace "\*","X"
        $filename=  $filename -replace ",",""
        $filename=  $filename -replace "CN=",""
        $filename += ".pfx"
        $retval= &certutil.exe @('-exportpfx', '-f', '-p',"$password",$cert.Thumbprint, "$filename") 
        dir $filename

        #
        # now use openssl to split into a key file and a cert file - as wanted by Cisco ISE 
        #
        $fname = $filename -replace ".pfx", ""
        start-process -filepath 'C:\Program Files\OpenSSL-Win64\bin\openssl.exe' -argumentlist "pkcs12 -in $filename -out $fname-certs.txt -nodes -nokeys -password pass:$password" 
        start-process -filepath 'C:\Program Files\OpenSSL-Win64\bin\openssl.exe' -argumentlist "pkcs12 -in $filename -out $fname-key.txt -nodes -nocerts -password pass:$password" 

       
        dir $fname*.txt

        }
    }




Posted in Powershell, VBS, VBA and other scripting. | Comments Off on IP4 Network Scanning using powershell.

One drive Camera Roll – folder for each month

If you use one drive to backup your photos and videos from your phone you might have noticed it puts all of the files in one folder per year.

I find it much easier to put one folder for month, this powershell will move all files into subfolders based on the month in the file name.

ie: a photo take on December 25th 2020 will be named, 20201225??????????????.

So the month is assumed to be the 4th and 5th characters of the filename, any files that dont have numbers in that position are left where they are. and yes I should test for months greater than 12 but didnt bother.

$filespath = "$env:onedrive\pictures\camera roll\2020"
$ff=get-childitem -Path $filespath -File 
foreach ( $f in $ff ) { 
    #$f
    if ( $f.name.substring(4,2) -match '[0-9][0-9]' ) { 
        $newpath=$f.Directoryname + "\" + $f.name.Substring(4,2)
        write-host "Moving file $f.name to $newpath" -ForegroundColor DarkYellow
        if (!(test-path -path $newpath)) {new-item -path $newpath -itemtype directory  }
        Move-Item $f.fullname $newpath 
    }
    else {
        "No match $f.name"
    }
}
Posted in Hints and Tips, Powershell, VBS, VBA and other scripting. | Comments Off on One drive Camera Roll – folder for each month

Cleaning up old DNS NS records in an Active Directory forest

Cleaning up old DNS NS records in an Active Directory forest,

needed some powershell to help remove all the old DNS server names from various domains that have been removed from the forest over time or demoted / decommissioned.

$dnszones = Get-DnsServerZone
foreach ( $dnz in $dnszones ) { 
    $dnz 
    $z1= $dnz  | Get-DnsServerResourceRecord  -name "@" -rrtype "NS"
    $bad1=@() ; 
    foreach ( $z in $z1 ) {
        # $z.recorddata
        try { 
            $i= Resolve-DnsName $z.RecordData.NameServer -erroraction stop 
            } 
        catch {
            $bad1 += $z.recorddata.nameserver
            "$($z.RecordData.NameServer) error add to bad list " | write-host -ForegroundColor DarkYellow
            }
        }
        foreach ( $b in $bad1 ) {
            "removing $b from $dnz.zonename" | Write-host -ForegroundColor Yellow
            Remove-DNSServerResourceRecord -zonename  $dnz.zonename –Name “@” –RRType NS –RecordData $b  -force
        }
}

Posted in Powershell, VBS, VBA and other scripting., System Administration | Comments Off on Cleaning up old DNS NS records in an Active Directory forest

Dump all Distribution Groups -Exchange Server Powershell

Getting the members of each distribution group into a CSV file on an exchange server is easy using powershell.

This will create one CSV file per group.

$dg=get-distributiongroup

foreach ( $g in $dg ) {
    Get-DistributionGroupMember $g.Name
    $fn="C:\scripts\distgroup_$($g.name).csv"
    $fn
    Get-DistributionGroupMember $g.Name | export-csv -Path $fn 

    }
    

Posted in Powershell, VBS, VBA and other scripting., System Administration | Comments Off on Dump all Distribution Groups -Exchange Server Powershell

Exchange server message tracking logs – examine them using powershell and excel

The Exchange server interface for examining message tracking logs forces you to put in one mailbox to search before you can do anything else.

If you need to find emails sent from one outside person to groups of users or all users on site then this interface doesnt really help.

Using powershell we can read the messagetracking logs and then expand and process them in any way we want.

By expanding the arrays and hashes lists in powershell we can have one flat CSV to analyse any way we want (excel data filters).

#
# read the message tracking log  (search by subject)
$mylog=get-messagetrackinglog -MessageSubject "Message Subject I am looking for." -Server myexcgangeserver  -ResultSize unlimited
# can save that to a file for later processing.
$mylog | Export-Clixml "MyLogsSaved01.xml"
#

Now read that back and process it.

#
$MyLogs=Import-Clixml "MyLogsSaved01.xml"
#
# convert recipientemail which is an array of text elements to one text element
#
$m1= $MyLogs | Select-Object  timestamp, eventid, sender, source, recipientcount, @{Name=“recipientemail”;Expression={$_.recipients }} , messagesubject , directionality,  transporttraffictype , @{name="eventdata1";expression={$_.eventdata | foreach-object { "$($_.key)=$($_.value)"}}} , eventdata
#
# convert eventdata convert hash to one text element
#
$m2= $m1 |Select-Object  timestamp, eventid, sender, source, recipientcount, recipientemail , @{Name=“EventDataNew”;Expression={$_.eventdata1 -join " - "}} , messagesubject , directionality,  transporttraffictype
# save as CSV for import to excel
#
$m2 | export-csv "MyLogsSaved01.csv"
#

In Excel for my purposes I just needed to filter for DELIVER in the eventid and then copy the recipients emails to see who had received the particular message. some emails could have multiple recipients but in this case they didnt.

Posted in Powershell, VBS, VBA and other scripting., System Administration | Comments Off on Exchange server message tracking logs – examine them using powershell and excel

Moving (copying) a VM between standalone ESX hosts. – using ovftool

Download ovftool from VMware (https://my.vmware.com/group/vmware/get-download?downloadGroup=OVFTOOL430UPDATE3)

For example copy a machine called jetfun from host – esxhost1 to esxhost2 – replace the new with the new hosts “vm network”
overwrite any machine with the same name there
place the new machine on datastore1
provision as a thin disk

.\ovftool  -dm=thin  -nw="VM Network" --overwrite  -ds=datastore1 vi://root@esxhost1/jetfun vi://root@esxhost2

You will be prompted for the passwords for the hosts, source and then target.

Output

Enter login information for source vi://192.168.4.200/
Username: root
Password: ****************
Opening VI source: vi://root@192.168.4.200:443/jetfun
Opening VI source: vi://root@192.168.4.200:443/jetfun
Enter login information for target vi://192.168.4.201/
Username: root
Password: ********
Opening VI target: vi://root@192.168.4.201:443/
Deleting VM: jetfun
Deploying to VI: vi://root@192.168.4.201:443/
Disk Progress: 1%

If the hardware versions dont match you get an error like

unsupported hardware family 'vmx-14'

that means you are trying to copy from a newer version of esx to and older one. 

easy fix is to add the switch

--maxVirtualHardwareVersion=10

with the appropriate version number and the VM will be converted to the older version compatibility. You might need to Upgrade it once it is in the new server.

also worth noting that the source or destination can be local disk folder and the seperare files ovf, vmdk, iso, nvram and mf will be created or read in the subfolder.


Posted in Powershell, VBS, VBA and other scripting., System Administration | Tagged | Comments Off on Moving (copying) a VM between standalone ESX hosts. – using ovftool

Saving passwords for use in powershell scripts.

the get-credential command gets a username/password pair interactively.

these can be created and saved to a file, making this file a secure document means that only the user who created it can use it.

this means if you want to use the password in a script which will berun by a service account you need to create the password using that account. Do a run/as for a powershell prompt then doing the get-credential and save will work.

to create a credential using a plain text password (Don’t do this 😉 )

##
###
$username="bgates@microsoft.com"
$password = "XXXXXXXXXXXX"
$secureStringPwd = $password | ConvertTo-SecureString -AsPlainText -Force 
$UserCredential = New-Object System.Management.Automation.PSCredential -ArgumentList $username, $secureStringPwd
###

To get a credential and save the password as a secure file.

#
$credential = Get-Credential
$credential.Password | ConvertFrom-SecureString | Set-Content c:scriptsencrypted_password4.txt
#

to read the credential password back and use it

#
$username="bgates@microsoft.com"
$securestringpwd = get-Content c:scriptsencrypted_password4.txt | ConvertTo-SecureString
$UserCredential = New-Object System.Management.Automation.PSCredential -ArgumentList $username, $secureStringPwd

Once you have created the credential object you can use it anywhere that accepts a credential. MSonline, Azuread sharepoint.

# eg use it against a sharepoint site
$siteurl="https://microsoft.sharepoint.com/"
Connect-PnPOnline -Url $siteUrl -Credentials $USERcredential
Posted in Powershell, VBS, VBA and other scripting., System Administration | Tagged , | Comments Off on Saving passwords for use in powershell scripts.

Mail Tips – show when recipients are external.

Office 365 / Exchange has the option to warn you if you are sending to external people. To stop that “oops – that wasnt meant to go outside” feeling after you have sent it.

https://social.technet.microsoft.com/Forums/en-US/800c1b34-52d8-44d5-a5e1-186305caf91f/how-to-set-specific-external-domainrecipient-warning-before-sending-by-mailtips?forum=exchangesvradminlegacy

In summary this is how to set that on.

#turn on the mail tip for external emails
Set-OrganizationConfig -MailTipsExternalRecipientsTipsEnabled $true
# to turn it off put $false
Posted in Powershell, VBS, VBA and other scripting. | Comments Off on Mail Tips – show when recipients are external.