Powershell — Create Excel File with Formatting and a Top Row Filter

#### Powershell — Create Excel File with formatting and Filter ####
$excel = New-Object -ComObject excel.application
$excel.visible = $false
#$excel.visible = $true
$workbook = $excel.Workbooks.Add()
$Report= $workbook.Worksheets.Item(1)
$Report.Name = ‘Excel Report’
$Report.Cells.Item(1,1) = ‘ServerName’ # Cell 1A on Excel
$Report.Cells.Item(1,2) = ‘Domain’ #Cell 1B on Excel
$Report.Cells.Item(1,3) = ‘Percentage Example’ ## Cell 1C on Excel
$Report.Columns.item(‘c’).NumberFormat = ‘0%’ ## Sets the Percentage Example column “c” to be a percentage with no decimal places.

### Lets make the top row stand out and have a filter
$report.Rows.EntireRow.AutoFilter() # Enable Autofilter on top row
$Report.Rows.item(1).Font.Bold=$True # Make the first Row Bold
$Report.Rows.item(1).Font.Color = 8210719 # Make the first row Blue
$usedRange = $report.UsedRange
$usedRange.EntireColumn.AutoFit() | Out-Null  ### Set the filter to autofit

##Move the row to the next line
$row = 2

Advertisements

Find Video and Image Files on a Server and Email the Locations

monitor-binaryBlue

BOSS:  “Hey reflecto head, someone hacked my laptop and certain…errh images and videos where stolen and placed randomly around the companies network servers.
Can you search for .avi, .mkv, .flac, .jpg and .mp4 files on this list of servers?”

BaldyAdmin: “I can, I can even mail them all to senior management once done”

BOSS: “Eh, no need for that, just mail me directly and don’t open any of them”

BaldyAdmin: “you got it Boss”

######################################

# Variables
$filedate = Get-Date -uformat "%d%m%Y%H%M"
$StartTime = get-date

#Script Version
$sScriptVersion = '1.0'
$hostname = hostname
$scriptname = $MyInvocation.InvocationName
#$extensions = '*.xls*', '*.doc*', '*.txt', '*.pdf', '*.jpg', '*.pub'
$extensions = '*.avi','*.mkv','*.mp4','*.mp3','*.flac','*.m4a'
$resultsLog = 'c:\somwwhere\VideoMusicSearch_'+$filedate+'.txt'

#Email Information
$fromAddress = "donotreply@somwwhere.com"
$toAddress = "TheBoss@somwwhere.com"
$sendingServer = "smtprelay.somwwherenet.com"
$attachment = $resultsLog

$servers = Get-Content C:\lists\ListofServers.txt

foreach($server in $servers){
$searchpath = "\\$server\users"

try{
Get-Childitem -Path $searchpath -Include $extensions -Recurse | out-file $resultsLog -Append
}
Catch{
$ErrorMessage = $_.Exception.Message
$FailedItem = $_.Exception.ItemName
Write-Host "$errorMessage -- $failedItem "
} ##END Catch

} #END foreachServer

$EndTime = get-date

try{
Send-MailMessage -From $fromAddress -To $toAddress -Subject "$scriptname on $hostname Completed" -SmtpServer $sendingServer -attachments $attachment -Body "$scriptname on $hostname Completed"
Write-Host "Mail Sent to $toAddress"
}
catch {
$ErrorMessage = $_.Exception.Message
Write-Host "Sending Mail failed with $ErrorMessage"
Write-Host "Script Ended at $EndTime"
}
########################################

BaldyAdmin: “Hey BOSS, you just got a mail with that Information”

BOSS: “You saved my life..again, Thanks”

Liked this Post? Leave a comment

Azure Powershell function to connect to 0365 / Exchange online

exchange

BOSS: “Hey you, the one with the folic issues, get over here”

BaldyAdmin: “yes oh hairy headed one, how may I serve you?”

BOSS: Bob want to connect to our o365 account and do useful stuff but he needs to do it faster.

BaldyAdmin: “I’m on it”

##Lets make it easy for Bob to connect with a reusable powershell function:

######################

Function Connect-O365{

import-module MSOnline

$UserCredential = Get-Credential youraccounthere@something.onmicrosoft.com

$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection

Import-PSSession $Session
}
Connect-O365

========================

BaldyAdmin: “Hey Boss, Bob has a cool new function that saves him at least 10 mouse clicks.”

BOSS: “Your a genius, perhaps brains work better without insulation?”

If you liked this post please leave a comment.

The Baldy Admin

Is the Playstation VR worth the price tag?

playstation-vr-review

 

As someone who took the plunge and splashed out roughly 450Eur for the Starter pack, Move controllers and Aim Controller,  This is the question I get asked most when talking to fellow gamers.

Its a tough one to answer, I’ve had great fun playing some of the games and quite a few “Wow” moments but I’ve have than a few issues with it.

Look, if your the kind of person that has 4 -5 hundred bucks laying around and you love new tech, go for it.

Will it live up to the images and marketing….not a chance.

The TV screen may show a lovely 1080p image but once you slip that headset on your looking through a screen door and your graphics drop to early 90’s game quality.

Edges become jagged, most stuff is a little blurry and it can be difficult to focus.

Add to that the fact that most games are barely games by todays standards.

Batman Arkam VR (10Eur on sale) was great but I competed the entire thing in around 25 minutes.

Superhot (20 Eur) was awesome and I got many hours of play out of it. tracking was a regular issue with my hand often appearing off to one side or way out in front of me.

Robinson the Journey was cool (13Eur on sale), I saw dinosaurs up close and personal.

Pixel Gear (3Eur on sale) — I was ripped off on that one, utter sh$%£.

Doom VFR (13Eur on sale) pretty Meh for me but I’ll hold out as I just got it.

VR Worlds was fun for the dive expierience, would I pay 20Eur for it….NO.

There huge potential there but I think we’ll all be a little older before it becomes a main staple.

Finally I have to talk about build quality….there’s none. Sony really dropped the ball on this one, at the price point I expect high quality but that headset is as fragile as glass.

If your not extremely careful the lens WILL scratch, the case WILL crack and on mine it shipped with a faulty headphone jack. If you buy store it somewhere safe.

In summary: PSVR is at a stage where it’s a bit of expensive fun so if you can afford it then I say why not. Just don’t expect anything like the quality you see on the youtube videos or screenshots and be careful watching those youtubers who are only after sponsorship and views.

Yours virtually

The Baldy Admin

 

Import a CSV and find a row based on a search

BOSS: “Hey Crome Dome, Bob needs info from an exported csv but he needs to find a specific row and only pull that information”

ME: “sounds complicated, might have to pay someone big bucks to figure that out”

BOSS: “Perhaps I could pay them if I fire you?”

ME: “I’m on it”

 

=======================================

##Import the CSV -- in that CSV are headers like "Server Name" and "IP"  -- Great we can search on those.

$csv = Import-Csv C:\serverwiki.csv

##Assign a search item in this case a server name

$servername = 'SERVER01'

##Do the search on the header "Server Name", you need to enclose it in '' as the header has spaces.
$result = $csv | Where-Object {$_.'Server Name' -like $servername}

## $result now contains the row that contains all that lovely information.
#We can now get each piece of that info by using the object e.g. $result.Server #Name or $result.IP

 

ME: “Hi Boss, I got a quote of 2000 for a contractor to do that report so I took a crack at it and Bob has what he needs”

BOSS: “Wow, for a bald guy, your really smart”

 

Like this post, leave a comment and follow me.

Thanks for reading,

The Baldy Admin

Powershell to create and configure Group Policy settings

BOSS: “Hey Slaphead, get over here I’ve a job for you”
ME: “Yes Sir, what can I do you today besides spit in your coffee again.”
BOSS: “I need a new GPO rolled out to 26 Domains by tomorrow and it need specific permissions comments and only apply to computers”
ME: “WHAT!!!! that’s impossible no-one could do it in that timeframe”
BOSS: “Dont care, I want it, I get it, YOU DO IT NOW”
ME: “ok, I’ll see what I can do” — turns and smiles

$gponame = ‘The impossible GPO demand’

$domains = (Get-ADForest).domains

foreach ($domain in $domains) {

Write-host $Domain -ForegroundColor Green

### Create New GPO
New-GPO -Name $gponame -Domain $domain

### Add delegation privelages to GPO
Set-GPPermissions -Name $gponame -TargetName “domain\Admins without Hair” -TargetType Group -PermissionLevel GpoRead -Replace -DomainName $domain
Set-GPPermissions -Name $gponame -TargetName “domain\Admins with Hair” -TargetType Group -PermissionLevel GpoEditDeleteModifySecurity -Replace -DomainName $Domain
Set-GPPermissions -Name $gponame -TargetName “domain\GPO Admins” -TargetType Group -PermissionLevel GpoEditDeleteModifySecurity -Replace -DomainName $Domain

##Change Settings / GPO Status
(get-gpo -name $gponame -Domain $domain).gpostatus = ‘UserSettingsDisabled’
## The possible enumeration values are “AllSettingsDisabled, UserSettingsDisabled, ComputerSettingsDisabled, AllSettingsEnabled”

##Set A Comment
(get-gpo -name $gponame -Domain $domain).Description = “24/08/2018 – This took 10 minutes but the boss thinks it took me hours”
#Display the Change
Get-Gpo -Name $gponame -Domain $domain

}# END Foreach Domain

…..The Next morning
BOSS: hows that GPO going, how many have you got done
ME: Just finished it, I was here really late last night…can i go home early, I need to wash my head.
BOSS: Wow, your amazing..Take the rest of the day off.
ME: Cheers Boss, here I made you a coffee 😉

technology-792180__340

Like this post, leave a comment

Powershell Function to create a Scheduled Task

I often get requests to run x or y command on any number of servers so after I finish shining my head this is the function I use to make life easier and make my boss think I’m a hero 🙂

  1. You need to create the scheduled task on one server and then export it to an xml
  2. Place that xml in here C:\TaskXMLs\scheduledtask.xml
  3. Run the function and pass the following parameters:

$servername, ##to create task on — This is usually a txt or AD filter and then a loop calling the function.

$TaskName,  ## This is the name that you will see in windows task scheduler

$Action ## this is the action the task will make
$DateTime ## The date and time you want your task to run at– NB the format matter a lot here, 2018-08-24T01:00:00 works in my environment but beware whitespaces.

a mistake in $action or $dateTime will simply cause the task to not create and you wont get any error message…in fact you’ll often get a “SUCCESS” message. It’s always because the XML is not valid, test it by importing it into task scheduler and see if it works

4. Run the script

CreateScheduledTask localhost testSchedule ‘”c:\test.bat”‘ 2018-08-24T01:00:00

Requires ScheduledTask.xml in same folder

Function CreateScheduledTask{
<# Read XML file Edit XML WriteTask ##USAGE: CreateScheduledTask localhost testSchedule ‘”c:\test.bat”‘ 2016-6-26T01:00:00 #>
PARAM($servername, ##to create task on
$TaskName,
$Action,
$DateTime)

$CommandToReplace = $null

$modifiedDate = $datetime
$modifiedTime
#$CommandToReplace = $Action
##Get folder where script is running
#$ScriptRoot = $MyInvocation.PSScriptRoot
#Write-host $ScriptRoot

#$ScheduledTaskXml = $ScriptRoot +’\ScheduledTask.xml’
$ScheduledTaskXml = “C:\TaskXMLs\scheduledtask.xml”

#——–Modify the XML File————#
##These are the setting to inject into the xml

$entryTime = “” + $modifiedDate +””
$command = “” + $action + “”

write-host “New Entry time to write to XML: “$entryTime
Write-host “New Command to write to XML : ” $command

$lines = get-content $ScheduledTaskXml
foreach ($line in $lines)
{
if ($line -like “**”)
{
$TimeToReplace = $line
}
}

foreach ($line in $lines)
{
if ($line -like “**”)
{
$CommandToReplace = $line
}
}

write-host “Writing new values into XML” -ForegroundColor Green

Write-host $TimeToReplace
Write-Host $entryTime
Write-host $CommandToReplace
Write-Host $command

(get-content $ScheduledTaskXml) | foreach-object {$_ -replace $CommandToReplace,$command} | set-content $ScheduledTaskXml
(get-content $ScheduledTaskXml) | foreach-object {$_ -replace $TimeToReplace,$entryTime} | set-content $ScheduledTaskXml

##Create a Schedule Object
$schedule = new-object -com(“Schedule.Service”)
##Connect to the server
$schedule.connect($serverName)
if (!$schedule){
Write-host “ERROR – Could not connect to $server”
}
## Get all Current tasks on the server
$tasks = $schedule.getfolder(“\”).gettasks(0) | select-object -ExpandProperty name
Write-host $tasks

Write-host “Creating Job on $server”
schtasks /Create /S $serverName /TN $TaskName /xml $ScheduledTaskXml

}

###EXAMPLE

CreateScheduledTask localhost testSchedule ‘”c:\test.bat”‘ 2018-08-24T01:00:00

Like this post..Leave a comment