Read a remote log, out put the log to your screen

Works rather well, but it takes a moment to read in the log and parse it.
This one asks for your domain credentials and goes back 7 days.
$d = Get-Date -displayhint date
$FQDNservername="your server"
$A = Get-WmiObject win32_NTLogEvent –computerName $FQDNservername -credential $Credentials -filter "logfile = ‘application’ and eventcode = 1"
Write-Host "Application event log acquired for Server $FQDNservername"  -backgroundcolor green -foregroundcolor black
$results = $A | select TimeGenerated,Message

Read the log, out put the log

This one will work on a local machine to find an event ID and displays it on your screen
It looks in the application log for events for the last two days and ID = 9
$date = Get-Date -displayhint date
$d = ($date.AddDays(-2))
Get-EventLog -logname application | where {$_.timegenerated -gt $d -and $_.eventid -eq "9"}

Record an Error in Excel

If I get an error, Access denied or RPC service is unavailable, how
would I Print the error message out to  $sh.Cells.Item($row, 2)

And the answer is:

$cred = Get-Credential ‘YourAdminName’ #adname or name@domain

$row = 2

$xl = New-Object -c excel.application

$xl.visible = $true

$wb = $xl.workbooks.add()

$sh = $wb.sheets.item(1)

$strComputer = "dogbert"

$Var3 = gwmi -Class "Win32_OperatingSystem" -namespace "rootCIMV2" -computername $strComputer -cred $cred -ea continue

# the above is all one line

if ($Var3) {$sh.Cells.Item($row, 3) = $Var3.Caption }
{ $sh.Cells.Item($row, 2) = $error[0].tostring() }


Adding borders in Excel the Power Shell way

This will open an Excel Workbook and add borders to a range of cells.
<————- Start PS Script——————–>
$xlAutomatic = -4105
$xlBottom = -4107
$xlCenter = -4108
$xlContext = -5002
$xlContinuous = 1
$xlDiagonalDown = 5
$xlDiagonalUp = 6
$xlEdgeBottom = 9
$xlEdgeLeft = 7
$xlEdgeRight = 10
$xlEdgeTop = 8
$xlInsideHorizontal = 12
$xlInsideVertical = 11
$xlNone = -4142
$xlThin = 2
$xl = new-object -com excel.application
$wb = $"C:ScriptsBook1.xlsx")
$ws = $wb.worksheets | where {$ -eq "sheet1"}
$selection = $ws.range("A1:F29")
$selection.HorizontalAlignment = $xlCenter
$selection.VerticalAlignment = $xlBottom
$selection.WrapText = $false
$selection.Orientation = 0
$selection.AddIndent = $false
$selection.IndentLevel = 0
$selection.ShrinkToFit = $false
$selection.ReadingOrder = $xlContext
$selection.MergeCells = $false
$selection.Borders.Item($xlInsideHorizontal).Weight = $xlThin
$selection.Borders.Item($xlInsideHorizontal).LineStyle = $xlContinuous
$selection.Borders.Item($xlInsideHorizontal).ColorIndex = $xlAutomatic
<——————-end Script—————————————————–>

Find All Scheduled Tasks, VBScript

Yes I do use VBScript, a lot. I was recently asked to pull a record of all the scheduled tasks on a Windows 2000 server.

The folks wanted to know as much as possible about their tasks so that they could recreate them when they migrated to Server 2003 or 08.
I found that this was no easy task. Fortunatly, others had gone before me and done the hard work. It is true, We stand on the shoulders of giants.
Here is my final script.

‘ VBScript Source File — Created with SAPIEN Technologies PrimalScript 2007

‘ NAME: SchAgent.vbs

‘ AUTHOR: Mike Felkins , IT
‘ DATE  : 9/3/2008

‘ COMMENT: Collect Scheduled tasks from a Windows 2000 server and records them in an Excel file
‘ Target Environment
‘ The DLL and the scripts apply to Winxp and earlier. Both Vista and Server08 have their own DLL, version 2.

‘ You need to register TaskScheduler.dll
‘ regsvr32 TaskScheduler.dll

‘ Available at
Dim objXL
Dim strComputer
Dim objWMIService
Dim Title, Text1, Text2
Dim Message, result
‘ Define Msg Box Var
Message = "Please Enter Server Name"
Title = "Automated Scheduled Task Inventory"
Text1 = "User Input Cancelled"
Text2 = "You Entered: " & vbTab
result = InputBox(Message, Title, "Type Server Name Here", 100, 100)
‘ Evaluate the user input.
If result = "" Then
 WScript.Echo Text1
 WScript.Echo Text2 & result & "  —-> Please wait"
End If
strComputer = result
Set objXL = WScript.CreateObject("Excel.Application")
objXL.Visible = True
objXL.Columns(1).ColumnWidth = 20
objXL.Columns(2).ColumnWidth = 30
objXL.Columns(3).ColumnWidth = 40
objXL.Columns(4).ColumnWidth = 40
objXL.Columns(5).ColumnWidth = 40
objXL.Columns(6).ColumnWidth = 40
objXL.Cells(1, 1).Value = "Name"
objXL.Cells(1, 2).Value = "ApplicationName"
objXL.Cells(1, 3).Value = "LastRunTime"
objXL.Cells(1, 4).Value = "NextRunTime"
objXL.Cells(1, 5).Value = "Triggers"
objXL.Cells(1, 6).Value = "CommandLine"
objXL.Selection.Font.Bold = True
objXL.Selection.Interior.ColorIndex = 1
objXL.Selection.Interior.Pattern = 1 ‘xlSolid
objXL.Selection.Font.ColorIndex = 2
objXL.Selection.HorizontalAlignment = &hFFFFEFDD ‘ xlLeft
Dim intIndex
intIndex = 2
Call SchAgent
objXL.ActiveWorkbook.SaveAs("C:scripts" & result & "_ScheduledJobs.xls")
If MsgBox("Finished processing. Results saved to: " & "C:scripts" &result & "_ScheduledJobs.xls" &_
            vbTab & vbcrlf & VbCrLf & "Do you want to view the results now?", _
            4 + 32, sTitle) = 6 Then
  ‘ Quit Excel.
‘ Clean Up
 Set objXL = Nothing
End If 
Sub SchAgent
Set objS = CreateObject("Scheduler.SchAgent")
objS.TargetComputer = "\" & strComputer
WScript.Echo "Looking at: " & result & "  —-> Please wait"
 For Each job In objS
  objXL.Cells(intIndex, 1).Value = job.Name
  objXL.Cells(intIndex, 2).Value = job.ApplicationName
  objXL.Cells(intIndex, 3).Value = job.LastRunTime
  objXL.Cells(intIndex, 4).Value = job.NextRunTime
   For Each oTrig In job.Triggers
         objXL.Cells(intIndex, 5).Value = "Trigger #" & i _
         & " (Typ " & oTrig.TriggerType _
         & ") " & oTrig.Text
         i = i + 1
  objXL.Cells(intIndex, 6).Value = job.CommandLine
  intIndex = intIndex + 1
End Sub

Exiting Excel

Alex, over at the Power Shell group in Google talked about Excel not shutting down when told.

Excel is generally the worst offender this way – there have been problems
with it not shutting down when told to that go back a very long time – but
there definitely are other ActiveX applications that exhibit this kind of
behavior. Since ActiveX applications are generally rare and each have their
own quirks, I usually test whether they shut down in different situations
when using them from PowerShell by keeping an eye on the process list, then
release the object if they don’t shut down correctly.
Unfortunately, this isn’t all that useful if you’re keeping an application
around to use during a PowerShell session. In those cases, I’ve noticed that
even some of the better-behaved ActiveX applications don’t work the way we
might want. For example, some of the better-behaved ActiveX applications
will indeed shut down automatically if they don’t have an open, modified
document when you close PowerShell by typing exit, but will hang around if
you close PS with the "X" at the top of the shell window. My general rule of
thumb is to always unhide ActiveX applications and minimize them. This at
least ensures that I can see and close them if I exit PS the wrong way or
lose connection to the app somehow.

One way in Power Shell to kill the process is;

Stop-Process -name EXCEL

Another is to try to release the com object after quiting:


Or this;


spps -n excel

I am sure that there are others.

Fixed the chart example

 There seems to a problem with the chart example. Doug, over at the power shell group sent this fix.

$xrow = 1
$yrow = 8
$xl = New-Object -c excel.application
$xl.visible = $true
$wb = $xl.workbooks.add()
$sh = $wb.sheets.item(1)
1..8 | % { $sh.Cells.Item(1,$_) = $_ }
1..8 | % { $sh.Cells.Item(2,$_) = 9-$_ }
$range = $sh.range("a${xrow}:h$yrow")
# create and assign the chart to a variable
$ch = $xl.charts.add()
$ch.chartType = 58