Powershell

Tab-complete Therapy

There is a special place in my heart for tab-completion. Just invoking it brings peace of mind.

There are a lot of people that feel like they are retired school spelling-bee champs. I tend to like the command line validation. It sounds crazy but even when I spell it out all the way it feel like magic to watch camel case completely reshape the letters and when it doesn’t I feel this shame of losing my mojo!

I think having this as a fail-safe or a second way to verifying your typing prowess. It looks awesome when your boss is visiting your desk and you’re showing him the latest update to your project in a quick query or a possession to test connectivity to a server and it’s services and processes. It is massive when you install, update a role or feature.

Take some time and consider if you are using all the tools PowerShell can do and you can do this faster with tab-complete as it works with your own custom built functions and that adds to your highly stacked brownie points lol.

Thanks for reading and let the shell enlightened you.

Powershell

Get-History

It’s strange how often we talk about history and how many of us actually get it.

Today 42 years ago my brother was born on this day as mother’s first child, she celebrated her Mother’s Day two days in a row.

Although he passed away before his time. I will never forget how he affected my history. Without him or my mother I’m not sure we would be sharing or relating on this technical blog. I am thankful. So saying that I know you have to be thankful for the ups and downs and even mistakes that sort out where we end up or the solutions we find in life.

Get-history something that you have to go back and explore and see your mistakes. Your typos and your solutions that may be a reusable philosophy to include in your next project or script. I think using a PowerShell command box is just as important as using an ISE wether it is the OS built in one, visual studio code, notepad++, or even Sapian power Script.

As a challenge I want you to get-history and share your third from last command in the comments. Don’t include sensitive data. Just the command. Sounds silly but it could be fun.

Mine is Restart-Computer -Force

Looking back I think my computer wasn’t performing well after a long zoom meeting. I think it makes me want to see if there is anything in the system log. Had I not challenged myself to do this first. I may not have looked back at an opportunity to learn.

Thanks for reading.

Powershell

Excel Charts from PowerShell data

Looking to make Reports more sharing friendly?

First start with an additional module known as “importExcel” (the link to the psgallery details is there)

Truth be told you can also Export to excel as well even though the name doesn’t give that away.

Script Time!!!! (I actually had to type this because I say this in real life in response to real challenges and tasks.

Here is a script that takes the performance counters from a machine, note this is different depending on the OS and the hardware. you can explore performance counters inside of the performance monitor tool searchable inside the start menu.

<#Script to gather Performance Data From PCs#>

#load machines for Testwrite-host "Gathering Worstation names" -foregroundcolor Yellow$Workstations= Get-Content 'C:\Performance Data\workstations.csv'$computers = Get-CimInstance -ClassName Win32_ComputerSystem -ComputerName $Workstations -ErrorAction SilentlyContinue#gather Memory SamplesWrite-Host "Gathering Memory Data Samples" -foregroundcolor Cyan$memoryData=Get-Counter -Counter "\memory\available mbytes" -SampleInterval 1 -MaxSamples 10 -ComputerName $Workstations -ErrorAction SilentlyContinue -ErrorVariable memerror #gather Processor SamplesWrite-Host "Gathering Processor Data Samples" -foregroundcolor Cyan

$processorData=Get-Counter -Counter "\Processor(_Total)\% Processor Time" -MaxSamples 10 -SampleInterval 1 -ComputerName $Workstations -ErrorAction SilentlyContinue -ErrorVariable ProcErr$processorReport=$null$memoryReport=$null

$date=Get-Date -Format MM.dd.hh.mm.sstt                                              

foreach ($computer in $computers){$processorReport=$null$memoryReport=$null


#write the computernameWrite-Host $computer.Name -ForegroundColor Yellow$TotMEM='{0:0}'-f  ($($computer.TotalPhysicalMemory/1mb))

#Process Data Report$processorReport+=$processorData.countersamples | Where-Object -Property path -Match $computer.Name | Select-Object @{name="Time";e={$_.timestamp| Get-Date -Format hh:mm:sstt}},@{name="Processor %";e={$_.cookedvalue}},@{name="Model";e={$computer.Model}},@{name="Computer";e={$computer.name}}




$memoryReport+=$memoryData.countersamples | Where-Object -Property path -Match $computer.Name | Select-Object @{name="Time";e={$_.timestamp| Get-Date -Format hh:mm:sstt}},@{name="MemoryUsed";e={$TotMEM-$_.cookedvalue}},@{name="Model";e={$computer.Model}},@{name="Computer";e={$computer.name}}

$MemChart = New-ExcelChartDefinition -XRange "Table1[Time]"  -YRange "Table1[MemoryUsed]" -ChartType Line  -Width 800 -Height 350  -Title "Memory Utilization" -SeriesHeader 'Memory(MB)' -XAxisNumberformat 'h:mm:ss AM/PM'$ProcChart=New-ExcelChartDefinition -XRange "Table2[Time]"  -YRange "Table2[Processor %]"   -ChartType LineStacked  -Width 800 -Height 350  -Title "CPU Usage" -SeriesHeader 'Percent Utilized(Ghz)'  -XAxisNumberformat 'h:mm:ss AM/PM'$memoryReport   |   Export-Excel "C:\Performance Data\Reportdata_$($computer.Name).xlsx" -WorksheetName MemoryUsage  -TableStyle Medium9 -AutoSize -Append -ExcelChartDefinition $MemChart  $processorReport| Export-Excel "C:\Performance Data\Reportdata_$($computer.Name).xlsx" -workSheetName ProcessorData  -TableStyle Medium17 -AutoSize -Append -ExcelChartDefinition $ProcChart}
This is a complex one and one of my more favorite script where a lot of PowerShell magic is used.
Mind you the input is a simple csv with a list of names. explore the different elements of get-counter using get-help -full or at least get-help -examples.
Functions, Powershell, server, Server Management Tools

Visualizing Data with Excel and Powershell

Recently I was Reading a write-up on API and transforming data with Python.

I know this is a blog about PowerShell right? However I want to point out that python is a scripting environment and actually more involved to get started so the restraints of that for a beginner is constricting (Pun intended).

PowerShell is on every windows computer since windows XP and PowerShell version 7.0 is now available for install on all Major OS including Mac, Linux and even IoT and its backwards compatible to windows 7.

If you are interested in a python project go here, because it will satisfy your needs and also take hours!

https://www.freecodecamp.org/news/how-to-create-auto-updating-data-visualizations-in-python-with-matplotlib-and-aws/

I will however show you how to do it with Html and Excel.

The First way is with an additional module known as “importExcel” (the link to the psgallery details is there)

Truth be told you can also Export to excel as well even though the name doesn’t give that away.

Let’s install it, we will do it in a way that is allowed if you don’t have local admin rights on your PC.

Install-Module -Name importexcel -Scope CurrentUser

Import-module importexcel

Thus your environment is ready for the next commands. 

 

get-service | where  -property status -eq running |select name,status,starttype |Export-Excel -Path $home\documents\TestExport.xlsx -Show -Title "Running Services" -TableStyle Dark10 -AutoSize 

This is how you can start reporting and looking professional and save the formatting steps.

See the next post to see how we tie performance metric to Excel reporting.

 

Powershell

Setting the Max Memory in SQL Server

See the source image

Here is a quick post about setting the max memory in SQL Server so that you got no worries if you forgot to set this at installation.

I get it we all been there before. This was a step I over looked when starting out with SharePoint many years ago. However I did recently have this come up in a discussion and I remembered and thought I should do this in PowerShell to exercise my scripting technique. It seems to be best used as an expression.

Disclaimer:

Test this in a dev environment first and not production to ensure compatibility with versions and the .net framework:

The Nitty Gritty:


Function Set-SQLServerMemory
{
    [CmdletBinding()]
    Param(
            [parameter(ValueFromPipelineByPropertyName)][string[]]$ServerName,
            [parameter(ValueFromPipelineByPropertyName)][int[]]$MaxMemory,
            [parameter(ValueFromPipelineByPropertyName)][int[]]$MinMemory




         )
    Begin{
    write-verbose "Begining"
     $report=@()
     #$report+=New-Object -TypeName psobject -Property @{Servername="testServer"; TotalMemory=8076;} | select Servername,TotalMemory
     $servernames=$servername

    }

    Process{
    write-verbose "Begining the Process"

    foreach($ServerName in $ServerNames){
    Write-Verbose "In The Loop"
            $Rawmemorytotal = Get-wmiobject -ComputerName $ServerName -class Win32_PhysicalMemory | Measure-Object -Property Capacity -Sum
            $TotalMemory=$Rawmemorytotal.Sum
            $report+=New-Object -TypeName psobject -Property @{ 
                                                                ServerName="$Servername" 
                                                                TotalMemory=($Rawmemorytotal.Sum / 1MB)
                                                              }
    
            $min_os_memory = 2048 
            if( $TotalMemory -le $min_os_memory) {
                Write-Host "Memory too Low, Server could be Serverly impacted." -ForegroundColor Red 
            }
            if ($TotalMemory -ge 8192) {
                $sql_mem = $TotalMemory - $min_os_memory
            } else {
                $sql_mem = $TotalMemory * 0.79 ;
            }

    if ($MinMemory -eq 0) {
            $MinMemory = $MaxMemory
        }
        [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
        $SQLServer = New-Object Microsoft.SQLServer.Management.Smo.Server($SQLInstanceName)
        if ($SQLServer.status) {
              $SQLServer.Configuration.MaxServerMemory.ConfigValue = $MaxMemory
            $SQLServer.Configuration.MinServerMemory.ConfigValue = $MinMemory   
            $SQLServer.Configuration.Alter()
            }#end if

    
        }#end Foreach
        }

        End{
        return $report | select Servername,TotalMemory
        #return [int]$sql_mem ;  

        }
}


 

Powershell

Learning to access data from APIs (Invoke-WebRequest)

PowerShell is known for its wide capabilities to “shell” out changes and reporting data.

Websites are embracing the movement with data standards native to PowerShell and it’s cyphering functionality. Formats like JSON, and XML that are leveraged as responses for sites that load data.

So using invoke-webrequest and piping the results to convertfrom-json can turn this output in to readable in-memory native PowerShell styled .NET objects.

Using foreach-object and leveraging PowerShell 7 newest parallel feature could add some more speed to your larger task sets. I encourage you to explore these parameters.

 Check out the link for syntax

https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/invoke-webrequest?view=powershell-7

 

 

Powershell

Scripting in other languges

Recently I noticed that there are not a lot of scriptwriters stick to PowerShell only.

There is a way to harness PowerShell to do other jobs like:

  • modify/build databases
  • Build/update websites
  • build and update dashboards
  • reconfigure Switches

I will be going over some simple examples that you can explore and build on.

Stay Tuned….

Powershell

Effective practices

I have been scripting for quite sometime and sometimes there are older CMD tools that just work.

Most would say why reinvent the wheel?

Well if the wheel wasn’t constantly being revised and upgraded then all the newer methods and functions wouldn’t be as robust.

With programming in c# there are methods and you can build functions there and in powershell to simply your daily task and pc deployments.

Don’t forget that most great ideas come from someone thinking it could be done better. Making things better would never happen if we don’t take steps to refine our processes we have grown so attached to.

Do you have a function that you created to make your life easier? Please share in the comments below.

Uncategorized

PowerShell to Manage thumbnail Cache databases used by file Explorer

So I have seen some issues with thumbnails in 1809 Enterprise edition.

When I am aggravated with them I clip them. it’s just good hygiene. lol

 

so here is a telltale script that does just that.

<#
Thumbnail Issue
-kill explorer tasks
-Remove Thumbnail databases
-restart explorer

#>


#-kill explorer tasks
write-host "stopping the Explorer Process."
Stop-Process -Name explorer -Force

#-Remove Thumbnail databases

Write-Host "These files need to be removed to ensure that they are not Corrupted:" -ForegroundColor Yellow
Get-ChildItem -Path "$env:LOCALAPPDATA\Microsoft\Windows\Explorer\" thumbcache_* | select -ExpandProperty fullname -OutVariable THUMBS

remove-item $THUMBS -Force  -ErrorAction SilentlyContinue -ErrorVariable ThumbErrors

new-item c:\ -name Logs -ItemType Directory -ErrorAction Stop; $THUMBS| Export-Csv THumbDBErrorLog.csv -NoTypeInformation

That is how you clip those unsightly thumbnails….