Powershell Logon hours comparison - Updated

Not so long ago, I posted my functioning logon hours comparison script . . a few years and more powershell know-how has made my current self a-bit shocked at how my old script attempts look to my eyes now.

And so , I had a feeling to do a newer re-write: 

- same premise as the old script

- only edits required are the "username" values

- the CSV to XLSX conversion needs Excel installed on the machine running the script. 

**************************Begin Script***********************************

Function CSVtoXLSX

{

    [cmdletbinding()]

    Param

    (

        [Parameter(Mandatory=$true, Position=0)]

        [string]$arg1,

       

        [Parameter(Mandatory=$true, Position=1)]

        [string]$arg2

    )

    $XL = new-object -comobject Excel.application

    $XL.visible=$false

    $XL.displayalerts=$false

    $XL.workbooks.open("$arg1").SaveAs("$arg2",51)

    $XL.quit()

    #

    $XL2=new-object -comobject Excel.application

    $XL2.visible=$false

    $XL2.displayalerts=$false

    $WB=$XL2.workbooks.open("$arg2")

    $ws1=$wb.worksheets.item(1)

    $ws1.activate()

    #

    # Find the column count, to define our working-range.

    #

    $findrange=$ws1.usedrange.cells

    $colcount=$findrange.columns.count

    $workrange=$ws1.range($ws1.cells.item(1, 1), $ws1.cells.item(1, $colcount))

    #

    # Adjust the first row into a Header type Row

    #

    $workrange.font.bold=$true

    $workrange.interior.colorindex=15

    #

    # Set autofilter for all columns, and then autofit all columns.

    #

    $xl2.selection.autofilter() | out-null

    [void]$ws1.cells.entirecolumn.autofit()

    #

    # Save changes and close out.

    #

    $wb.saveas("$arg2")

    $wb.close()

    $XL2.quit()

}

import-module activedirectory

$report=@{}

$Default=get-aduser username -properties * # Adjust the username value 

$Defaultcheck=$default.logonhours

$Allhours=get-aduser username -properties * # Adjust the username value

$Allhourscheck=$allhours.logonhours

$users=get-aduser -filter 'enabled -eq $true' -properties *

foreach ($user in $users)

{

$Name=$user.name

$Manager=$user.manager

$Userhours=$user.logonhours

$Temp=[ordered]@{}

$Temp.Name=$Name

#

If ($userhours -eq $null)

{

$Temp.LogonHours="Not Set"

If ($manager -eq $null)

{

$Temp.Manager="Not Set"

$Report +=new-object -typename psobject -property $Temp

}

ELSE

{

$Manquery=get-aduser $Manager

$Manname=$manquery.name

$Temp.Manager=$Manname

$Report +=new-object -typename psobject -property $Temp

}

}

ELSE

{

$Compare1="$Userhours" -match "$Defaultcheck"

If ($Compare1 -eq $True)

{

$Temp.LogonHours="Default"

If ($manager -eq $null)

{

$Temp.Manager="Not Set"

$Report +=new-object -typename psobject -property $Temp

}

ELSE

{

$Manquery=get-aduser $Manager

$Manname=$manquery.name

$Temp.Manager=$Manname

$Report +=new-object -typename psobject -property $Temp

}

}

$Compare2="$Userhours" -match "$Allhourscheck"

If ($Compare2 -eq $True)

{

$Temp.Logonhours="All hours access"

If ($manager -eq $null)

{

$Temp.Manager="Not Set"

$Report +=new-object -typename psobject -property $Temp

}

ELSE

{

$Manquery=get-aduser $Manager

$Manname=$manquery.name

$Temp.Manager=$Manname

$Report +=new-object -typename psobject -property $Temp

}

}

}

$csv="C:\temp\logonhoursCompareReport.csv"

$xlsx="C:\temp\logonhoursCompareReport.xlsx"

$Report | export-csv "$csv" -notypeinformation

CSVtoXLSX $csv $xlsx

RI $CSV 

***********************end Script********************************************

Powershell: Change static DNS values on the fly

Problem = you have a metric crap ton of statically IP assigned windows machines, and you want to change their DNS settings. 

 I've seen much of this info posted elsewhere, but it feels like no one really puts it all together very well, so here's my own take on how to pull this particular trick off. 

First off, the commands needed to "set" new DNS values, want to know which "adapter" on a machine you're going to change, this is returned as a number value with a name of "interfaceindex". That number VARIES per machine. 

So the following can be run per machine, which will quickly find the proper adapter to adjust and adjust it:

******************Begin script********************

$adapter=get-netadapter | select -expandproperty interfaceindex

$newdns1="1.1.1.1"

$newdns2="2.2.2.2"

set-dnsclientserveraddress -interfaceindex $adapter -serveraddresses ($newdns1,$newdns2)

****************End script***********************

run interactively, these command would require elevation to execute, so plan for that in your deployment. 

This change is immediate, with no event log errors generated, or service outages caused. 

 

Powershell: Function: Convert CSV into auto-filtered XLSX files

The purpose of this powershell function is to be used as a means of within script generating report like files.

This function has been generalized for use under a variety of situations, but expect that so long as your data is broken out within the input CSV the result XLSX file should have:

- Top row - to last filled column is assumed to be a header row.
  - this row gets bolded, a filled in color, and the filters are set at this layer.

System executing a script that contains this function needs to have excel installed and working.

**********************script begin***************************************

Function CSVtoXLSX
{
    [cmdletbinding()]
    Param
    (
        [Parameter(Mandatory=$true, Position=0)]
        [string]$arg1,
       
        [Parameter(Mandatory=$true, Position=1)]
        [string]$arg2
    )
    $XL = new-object -comobject Excel.application
    $XL.visible=$false
    $XL.displayalerts=$false
    $XL.workbooks.open("$arg1").SaveAs("$arg2",51)
    $XL.quit()
    #
    $XL2=new-object -comobject Excel.application
    $XL2.visible=$false
    $XL2.displayalerts=$false
    $WB=$XL2.workbooks.open("$arg2")
    $ws1=$wb.worksheets.item(1)
    $ws1.activate()
    #
    # Find the column count, to define our working-range.
    #
    $findrange=$ws1.usedrange.cells
    $colcount=$findrange.columns.count
    $workrange=$ws1.range($ws1.cells.item(1, 1), $ws1.cells.item(1, $colcount))
    #
    # Adjust the first row into a Header type Row
    #
    $workrange.font.bold=$true
    $workrange.interior.colorindex=15
    #
    # Set autofilter for all columns, and then autofit all columns.
    #
    $xl2.selection.autofilter() | out-null
    [void]$ws1.cells.entirecolumn.autofit()
    #
    # Save changes and close out.
    #
    $wb.saveas("$arg2")
    $wb.close()
    $XL2.quit()
}

*********************************end script********************

Use case:

$input="c:\testfolder\testfile.csv"
$output="c:\testfolder\convertedreport.csv"

CSVtoXLSX $input $output

Note:

The default sheet name given to the spreadsheet created will be the name of the original CSV file.

Random Notes:

I have for many years now, leveraged a VBS script to perform the same tasks I wrote this powershell function for. It still works wonderfully, I just wanted to see if I could do this conversion within powershell itself vs calling a seperate script.

There may be additional edits to this one adding features going forward, but the base code as it is solid and meant to be slapped into any existing powershell script. 

Powershell: reporting on simple ldap DC connections

Every windows admin should be aware of what's coming March 2020:

https://support.microsoft.com/en-us/help/4520412/2020-ldap-channel-binding-and-ldap-signing-requirement-for-windows

The general recommendation at this point is to make this registry key adjustment to all your domain controllers:

# Enable Simple LDAP Bind Logging

Reg Add HKLM\SYSTEM\CurrentControlSet\Services\NTDS\Diagnostics /v "16 LDAP Interface Events" /t REG_DWORD /d 2

Once this has been done, you can monitor the event log on your DCs for event ID 2889 under the directory service log . . or you can run my script to check all your servers, and create a single report of all connections over the last 24 hours.

My script is based off the nice work by "Russell Tomkins" from Microsoft, his version available here:

https://github.com/russelltomkins/active-directory

The differences between our versions, his checks a single dc, mine checks the domain gets a list of DC's to check, then creates a report of all connections across them all.

The only parts to edit, are the lines for where to find the OU for he domain controllers, enter your domain name. And the path for the output CSV needs to exist as well.

hope it helps

**********start script***************

import-module activedirectory
cls
echo " "
echo " "
#
# Create shell arrays for holding the 2 needed data sets.
#
$Comps=@()
$Data=@()
#
# Gather list of Domain controllers
#
$Comps=get-adcomputer -filter * -searchbase "OU=Domain Controllers,DC=YOURDOMAINNAMEGOESHERE!!!,DC=com" | Sort Name
$compstocheck=$comps.count
#
# Gather data from each server's event logs, pull into single array.
#
echo " "
write-host "I found $compstocheck domain controllers, and will start checking their event data one by one" -foregroundcolor green
echo " "
ForEach ($DC in $Comps)
    {
        $dcname=$DC.name
        echo " "
        write-host "Pulling events from $dcname" -foregroundcolor Yellow
        echo " "
        $hours=24
        $Events=get-winevent -computername $dcname -filterhashtable @{Logname='Directory Service';Id=2889; StartTime=(get-date).AddHours("-$hours")} -ea silentlycontinue
        write-host "Processing events from $dcname" -foregroundcolor Cyan
        echo " "
        ForEach ($Event in $Events)
            {
$Etime=$Event.Timecreated
                $eventXML = [xml]$Event.ToXml()
                $Client = ($eventXML.event.EventData.Data[0])
                $IPAddress = $Client.SubString(0,$Client.LastIndexOf(":"))
                $Port = $Client.SubString($Client.LastIndexOf(":")+1)
                $User = $eventXML.event.EventData.Data[1]
                Switch ($eventXML.event.EventData.Data[2])
{
                        0 {$BindType = "Unsigned"}
                        1 {$BindType = "Simple"}
                    }
                $Row="" | select DCname,IPAddress,Port,User,BindType,TimeCreated
                $Row.DCname=$dcname
                $Row.IPAddress=$IPAddress
                $Row.Port=$Port
                $Row.User=$User
                $Row.BindType=$BindType
                $Row.TimeCreated=$ETime
                #
# Add the found event data to the master array
                #
                $Data +=$Row
            }
        write-host "Completed processing all related events for $DCname, moving on" -foregroundcolor Green
        echo " "
        }
#
#
write-host "Generating report" -foregroundcolor Green
echo " "
$Reportcsv="C:\SimpLdap\SimpleLdapReport.csv"
#
$Data | export-csv "$Reportcsv" -notypeinformation
#
*************End Script***********

Powershell - Arrays and how they handle multi-lined input data

For years with various methods of scripting I've often fallen to using temporary files as a means to store and retrieve the information my scripts were working on. I've always felt it was a 'poor mans' solution that any good scripter would look to avoid as much as possible.

So as I've spent more time with powershell recently, I've been making an effort to move away from temporary files, and trying to use powershell's hash table and array functions to store and retrieve the scripted info I need.

I had a case, where I needed to create a report and the data I was gathering was seemingly going into my array the same way that it was being displayed in my console...but everytime I went to extract the data I had put into my arrays, the returned info in code was junk and my reports never built.

After much trial and error I found 2 ways around my problem, the first was modifying export-csv command within my script to update my data as it was extracted so it would display properly .. . the second was "cleaning" my data before i imported it into the array . .and that's what I want to discuss with this post.

But first, the problem that powershell attempts to help you with.

You have a text file, with the following data within it:

the quick brown fox
jumped over the slow
brown cow who ate grass
with ducks by the pond

This is multi-lined data, here's how powershell displays this info once it's been pulled into an array:

Where did those comma's come from?

Did the text data, have comma separators?  Hmm..Looks like no...

This would be an example of powershell "adapting" your content, because it realized as it was ingesting data that was 'multi-line' so it had to adjust it so it could work with it within the array framework.

Line separations are defined within the array as "commas" and the entire set of data is bracketed "{}".

And so the question becomes, once you know this limitation of powershell arrays exist, how do you plan around it?

For me, the "best" way around this is going back to what I know well . . LOL . . using text files as temporary staging areas to "clean" the data the I'm importing into my arrays so that it can be exported cleanly with export-csv and no special tricks.

"Data cleaning of the txt file" in this case, means removing empty lines, adding semi-colons to the end of each line, and merging all lines into a single line . . . the commands that help with those are:

Remove empty lines:

(GC $txtfile) | foreach {$_.trimend()} | where {$_ -ne ""} | sc $txtfile

Adding semi-colons:

(gc $txtfile) | foreach {$_ + ";"} | sc $txtfile

Turning all lines into one line for array import (2 liner):

$txtarrayimport=gc $txtfile
$DataToImport=$txtarrayimport -join ""

This may seem like an extreme amount of effort, but it's been extremely important lesson for me in terms of knowing that powershell can and will adjust your data on it's own. Taking the time to import data into powershell arrays in a format it can handle better can mean a big difference in exporting that data later.

Powershell - lessons learned when trying to document ADSI permissions...

I had a need to document permissions assigned from within ADSI to a configuration container with hundreds / thousands of objects beneath.

Powershell surprisingly treats active directory much like the file system with regards to it's object references when you're looking for access rights information.

What I couldn't find was a good reference for how to recursively report on permissions under Active Directory's configuration area (think MS Exchange).

Below is my first attempt at code to dump all CN / OU permissions. Note that AD pathing is important, and you'll want to edit the $config line to the path you want to report on.

****************************Begin Script******************************************

Import-module activedirectory
$Temp="c:\temp\test.txt"
$config=get-childitem -recurse -path "AD:CN=Microsoft Exchange,CN=Services,CN=Configuration,DC=YourDomain,DC=com" | select -expandproperty DistinguishedName
Foreach ($object in $config)
{
$Path="AD:" + $object
$object >>$Temp
(get-acl $path).Access | Where {$_.IsInherited -eq $FALSE}|select InheritanceType,AccessControlType,IdentityReference,IsInherited >>$Temp
echo " ">>$Temp
}

****************************End Script ********************************************

This code, got the job done, it documented my permissions, but it created a fairly large Txt file which was a bit of a chore to sift through. It can be done better. . .

However, lets talk about what is going on with what we have first. . . If you're following along in the script, the data returned from the $config query is the full path to the AD object we want to query. But we can't just give that path to the "get-acl" command, it won't take it. Instead, we have to pre-pend the "AD:" to the query for get-acl, so a separate variable is used to turn our $config path into a value that get-acl can actually query and report on.

Also note, the data set is trimmed to only return directly assigned permissions. Any "inherited" permissions won't be shown as that makes for a massively sized report.

So I had results, but I wasn't happy with how it was presented, so I thought a bit about how I was getting at the data I wanted, and how it was being expressed . . and I realized something that I feel is very important to understand with powershell . . .

I am using a command in "get-acl" which returns multiple values. I point it at a folder / file / adobject, and it gives me a response that I can further filter to get only the data I want . . but I have to tinker with powershell a bit to do it...in my first code attempt no filtering was being done of the get-acl data, I was just displaying it as powershell would present it . . to a text file.

Here's another attempt at the same task, but filtering the get-acl query into specific values that a report is then built from:

**********************Begin Script****************************************

Import-module activedirectory
$Temp="c:\temp\test.txt"
$DataSet=get-childitem -recurse -path "AD:CN=Microsoft Exchange,CN=Services,CN=Configuration,DC=YourDomain,DC=com" | select -expandproperty DistinguishedName
echo "CN Path;Group;Allow-Deny;Permissions;Inherited">$Temp
Write-host "Gathering permissions data..." -foregroundcolor Green
Foreach ($object in $DataSet)
{
$Path="AD:" + $object
$PermCheck=(get-acl $path).Access | Where {$_.IsInherited -eq $FALSE} | select ActiveDirectoryRights,AccessControlType,IdentityReference,IsInherited
Foreach ($Perm in $PermCheck)
{
$ADR=$perm.activedirectoryrights
$ACT=$perm.accesscontroltype
$IR=$perm.IdentityReference
$II=$perm.isinherited
echo "$object;$IR;$ACT;$ADR;$II">>$Temp
}
}
echo " "
Write-host "Data dump completed, finalizing report..." -foregroundcolor Green
$csv="c:\temp\MSAD-Perms-Config-Exchange.csv"
import-csv $Temp -delimiter ";" | export-csv $csv -NoTypeInformation
$ReportXLSX="c:\temp\MSAD-Perms-Config-Exchange.xlsx"
$vbscript="\\server\networkshare\CSV-Convert\csv_to_excel.vbs"
& $vbscript $csv $reportxlsx
timeout /t 5 /nobreak
echo " "
write-host "Report created" -foregroundcolor Green
echo " "

********************End Script*******************************************

Following along in the code here, I've changed the "get-acl" into a variable, then added a foreach section to make new variables out of each of the specific values I want to see queried from each permission. Then, only those values are "echo'd" to my waiting temp text file. The data sent to the txt file is separated with a ';' for importing with import-csv later in the script. I also referencing some code I've leveraged in the past on my blog in other postings, to convert a CSV file into a pre-formated XLSX file.

The result is a clear break-out of any and all permission assignments for an AD structure. The more I think about this, it feels like it wouldn't take much to convert this same code to reporting on file system permissions. I may test that out in another blog posting . . . ;)

Hope this helps others-

Powershell - quickly stopping all skype services

For some reason, there's very little out there on a quick way to find and stop all skype services (this is now needed when running skype updates.)

Skype has it's own powershell commandlets for doing a variety of administration for skype systems, in this case we're going to leverage a single line command to get and stop all services:

*****script start*****

get-cswindowsservice | stop-cswindowsservice

*****script end*****

I have seen cases where this will simply hang out because the services themselves are locked up. In those cases, you either need to wait out the service stopping, or force a reboot and try again after your server is back online.