Some help needed on script to convert from csv to html

I have two scripts one starts as a scheduled task then calls the second script which does a function and process the data.

After script two finishes it creates two output files as .csv I would lie to convert them to HTML and place in the body of the EMail

First script

$LogFolder = "c:\util\logs"

$LogFile = "pendingrebootreportservers.csv"

$LogFile1 = "pendingrebootreportcomputers.csv"

$command = “.\Get-PendingReboot.ps1"

remove-item -path $LogFolder\$LogFile -Force

remove-item -path $LogFolder\$LogFile1 -Force

Invoke-Command -ComputerName TGKW001 -ScriptBlock {get-service "remoteregistry" | start-service}

Invoke-Command -ComputerName TGKW004 -ScriptBlock {get-service "remoteregistry" | start-service}

Invoke-Command -ComputerName TGKW006 -ScriptBlock {get-service "remoteregistry" | start-service}

Invoke-Command -ComputerName TGKW007-10 -ScriptBlock {get-service "remoteregistry" | start-service}

Invoke-Command -ComputerName TGKW008-10 -ScriptBlock {get-service "remoteregistry" | start-service}

Invoke-Command -ComputerName TGKW009 -ScriptBlock {get-service "remoteregistry" | start-service}

Invoke-Command -ComputerName TGKW008-10 -ScriptBlock {get-service "remoteregistry" | start-service}

Invoke-Command -ComputerName TGKW012 -ScriptBlock {get-service "remoteregistry" | start-service}

Invoke-Expression $command

$Server = hostname

$Params = @{

 Subject = "$Server Pending Reboot Report"

 Body = "$bigbody"

 From = "no-reply@tgcsnet.com"

 To = "systems-alert@tgcsnet.com"

 smtpserver = "InternalRelay.TGCSNET.COM"

}

Send-MailMessage @Params

Second script

Function Get-PendingReboot

{

<#

.SYNOPSIS

Gets the pending reboot status on a local or remote computer.

.DESCRIPTION

This function will query the registry on a local or remote computer and determine if the

system is pending a reboot, from either Microsoft Patching or a Software Installation.

For Windows 2008+ the function will query the CBS registry key as another factor in determining

pending reboot state. "PendingFileRenameOperations" and "Auto Update\RebootRequired" are observed

as being consistant across Windows Server 2003 & 2008.

CBServicing = Component Based Servicing (Windows 2008)

WindowsUpdate = Windows Update / Auto Update (Windows 2003 / 2008)

CCMClientSDK = SCCM 2012 Clients only (DetermineIfRebootPending method) otherwise $null value

PendFileRename = PendingFileRenameOperations (Windows 2003 / 2008)

.PARAMETER ComputerName

A single Computer or an array of computer names. The default is localhost ($env:COMPUTERNAME).

.PARAMETER ErrorLog

A single path to send error data to a log file.

.EXAMPLE

PS C:\> Get-PendingReboot -ComputerName (Get-Content C:\ServerList.txt) | Format-Table -AutoSize

Computer CBServicing WindowsUpdate CCMClientSDK PendFileRename PendFileRenVal RebootPending

-------- ----------- ------------- ------------ -------------- -------------- -------------

DC01 False False False False

DC02 False False False False

FS01 False False False False

This example will capture the contents of C:\ServerList.txt and query the pending reboot

information from the systems contained in the file and display the output in a table. The

null values are by design, since these systems do not have the SCCM 2012 client installed,

nor was the PendingFileRenameOperations value populated.

.EXAMPLE

PS C:\> Get-PendingReboot

Computer : WKS01

CBServicing : False

WindowsUpdate : True

CCMClient : False

PendFileRename : False

PendFileRenVal :

RebootPending : True

This example will query the local machine for pending reboot information.

.EXAMPLE

PS C:\> $Servers = Get-Content C:\Servers.txt

PS C:\> Get-PendingReboot -Computer $Servers | Export-Csv C:\PendingRebootReport.csv -NoTypeInformation

This example will create a report that contains pending reboot information.

.LINK

Component-Based Servicing:

http://technet.microsoft.com/en-us/library/cc756291(v=WS.10).aspx

PendingFileRename/Auto Update:

http://support.microsoft.com/kb/2723674

http://technet.microsoft.com/en-us/library/cc960241.aspx

http://blogs.msdn.com/b/hansr/archive/2006/02/17/patchreboot.aspx

SCCM 2012/CCM_ClientSDK:

http://msdn.microsoft.com/en-us/library/jj902723.aspx

.NOTES

Author: Brian Wilhite

Email: bwilhite1@carolina.rr.com

Date: 08/29/2012

PSVer: 2.0/3.0

Updated: 05/30/2013

UpdNote: Added CCMClient property - Used with SCCM 2012 Clients only

Added ValueFromPipelineByPropertyName=$true to the ComputerName Parameter

Removed $Data variable from the PSObject - it is not needed

Bug with the way CCMClientSDK returned null value if it was false

Removed unneeded variables

Added PendFileRenVal - Contents of the PendingFileRenameOperations Reg Entry

#>

[CmdletBinding()]

param(

[Parameter(Position=0,ValueFromPipeline=$true,ValueFromPipelineByPropertyName=$true)]

[Alias("CN","Computer")]

[String[]]$ComputerName="$env:COMPUTERNAME",

[String]$ErrorLog

)

Begin

{

# Adjusting ErrorActionPreference to stop on all errors, since using [Microsoft.Win32.RegistryKey]

# does not have a native ErrorAction Parameter, this may need to be changed if used within another

# function.

$TempErrAct = $ErrorActionPreference

$ErrorActionPreference = "Stop"

 }#End Begin Script Block

Process

{

Foreach ($Computer in $ComputerName)

{

Try

{

# Setting pending values to false to cut down on the number of else statements

$PendFileRename,$Pending,$SCCM = $false,$false,$false

# Setting CBSRebootPend to null since not all versions of Windows has this value

$CBSRebootPend = $null

# Querying WMI for build version

$WMI_OS = Get-WmiObject -Class Win32_OperatingSystem -Property BuildNumber, CSName -ComputerName $Computer

# Making registry connection to the local/remote computer

$RegCon = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey([Microsoft.Win32.RegistryHive]"LocalMachine",$Computer)

# If Vista/2008 & Above query the CBS Reg Key

If ($WMI_OS.BuildNumber -ge 6001)

{

$RegSubKeysCBS = $RegCon.OpenSubKey("SOFTWARE\Microsoft\Windows\CurrentVersion\Component Based Servicing\").GetSubKeyNames()

$CBSRebootPend = $RegSubKeysCBS -contains "RebootPending"

 }#End If ($WMI_OS.BuildNumber -ge 6001)

# Query WUAU from the registry

$RegWUAU = $RegCon.OpenSubKey("SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\")

$RegWUAURebootReq = $RegWUAU.GetSubKeyNames()

$WUAURebootReq = $RegWUAURebootReq -contains "RebootRequired"

# Query PendingFileRenameOperations from the registry

$RegSubKeySM = $RegCon.OpenSubKey("SYSTEM\CurrentControlSet\Control\Session Manager\")

$RegValuePFRO = $RegSubKeySM.GetValue("PendingFileRenameOperations",$null)

# Closing registry connection

$RegCon.Close()

# If PendingFileRenameOperations has a value set $RegValuePFRO variable to $true

If ($RegValuePFRO)

{

$PendFileRename = $true

 }#End If ($RegValuePFRO)

# Determine SCCM 2012 Client Reboot Pending Status

# To avoid nested 'if' statements and unneeded WMI calls to determine if the CCM_ClientUtilities class exist, setting EA = 0

$CCMClientSDK = $null

$CCMSplat = @{

 NameSpace='ROOT\ccm\ClientSDK'

 Class='CCM_ClientUtilities'

 Name='DetermineIfRebootPending'

 ComputerName=$Computer

 ErrorAction='SilentlyContinue'

}

$CCMClientSDK = Invoke-WmiMethod @CCMSplat

If ($CCMClientSDK)

{

If ($CCMClientSDK.ReturnValue -ne 0)

{

Write-Warning "Error: DetermineIfRebootPending returned error code $($CCMClientSDK.ReturnValue)"

 }#End If ($CCMClientSDK -and $CCMClientSDK.ReturnValue -ne 0)

If ($CCMClientSDK.IsHardRebootPending -or $CCMClientSDK.RebootPending)

{

$SCCM = $true

 }#End If ($CCMClientSDK.IsHardRebootPending -or $CCMClientSDK.RebootPending)

 }#End If ($CCMClientSDK)

Else

{

$SCCM = $null

}

# If any of the variables are true, set $Pending variable to $true

If ($CBSRebootPend -or $WUAURebootReq -or $SCCM -or $PendFileRename)

{

$Pending = $true

 }#End If ($CBS -or $WUAU -or $PendFileRename)

# Creating Custom PSObject and Select-Object Splat

$SelectSplat = @{

 Property=('Computer','CBServicing','WindowsUpdate','CCMClientSDK','PendFileRename','PendFileRenVal','RebootPending')

}

New-Object -TypeName PSObject -Property @{

 Computer=$WMI_OS.CSName

 CBServicing=$CBSRebootPend

 WindowsUpdate=$WUAURebootReq

 CCMClientSDK=$SCCM

 PendFileRename=$PendFileRename

 PendFileRenVal=$RegValuePFRO

 RebootPending=$Pending

 } | Select-Object @SelectSplat

 }#End Try

Catch

{

Write-Warning "$Computer`: $_"

# If $ErrorLog, log the file to a user specified location/path

If ($ErrorLog)

{

Out-File -InputObject "$Computer`,$_" -FilePath $ErrorLog -Append

 }#End If ($ErrorLog)

 }#End Catch

 }#End Foreach ($Computer in $ComputerName)

 }#End Process

End

{

# Resetting ErrorActionPref

$ErrorActionPreference = $TempErrAct

 }#End End

}#End Function

$Servers = Get-Content C:\UTIL\Servers.txt

Get-PendingReboot -ComputerName $Servers | Export-CSV C:\Util\Logs\PendingRebootReportServers.csv -NoTypeInformation

$Computers = Get-Content C:\UTIL\Desktops.txt

Get-PendingReboot -ComputerName $Computers | Export-CSV C:\Util\Logs\PendingRebootReportComputers.csv -NoTypeInformation 

The Last four lines in the second script I believe need to be changed. from Export-csv to export-html??

Add $bigbody = to the beginging of get-pendingreboot?

If so how do I pass $bigbody back to first script? is that possible.

 

Sample output

"Computer","CBServicing","WindowsUpdate","CCMClientSDK","PendFileRename","PendFileRenVal","RebootPending"

"TGCS002-2016",,"False",,"True","System.String[]","True"

"TGCS003-2012R2","False","False",,"False",,"False"

"TGCS004","False","False",,"False",,"False"

"TGCS005-N1","False","False",,"False",,"False"

"TGCS005-N2","False","False",,"False",,"False"

"TGCS005-S1","False","False",,"False",,"False"

"TGCS006-2012R2","False","False",,"False",,"False"

"TGCS006-2016",,"False",,"True","System.String[]","True"

"TGCS007-2012R2","False","False",,"False",,"False"

"TGCS008","False","False",,"False",,"False"

"TGCS009-2012R2","False","False",,"False",,"False"

"TGCS011","False","False",,"True","System.String[]","True"

"TGCS012","False","False",,"True","System.String[]","True"

"TGCS013","False","False",,"True","System.String[]","True"

"TGCS014-N1","False","False",,"False",,"False"

"TGCS014-N2","False","False",,"False",,"False"

"TGCS015","False","False",,"False",,"False"

"TGCS016","False","False",,"False",,"False"

"TGCS019-2012R2","False","False",,"False",,"False"

"TGCS020-2016",,"False",,"True","System.String[]","True"

"TGCS021-N1",,"False",,"True","System.String[]","True"

"TGCS021-N2",,"False",,"True","System.String[]","True"

 

 

 

 

Any Ideas ?

Thank you

Tom

Tom,

You’re obviously working on reporting. You’ve opened up multiple threads. First, there is a FREE eBook provided at this site Free Resources > eBooks > Creating HTML Reports in Windows PowerShell.

Basically, to answer your question, CSV’s should be straight forward. This is some basic pseudo code:

$csv1 = Import-CSV -Path C:\MyCSV.csv
$csv2 = Import-CSV -Path C:\MyCSV2.csv

$csv1html = $csv1 | ConvertTo-HTML -Fragment
$csv2html = $csv2 | ConvertTo-HTML -Fragment

#Thread strips html tags
$body = @"
html open tag
body open tag
Something about CSV 1

$csv1html

Something about CSV 2

$csv2html
body close tag
html close tag
"@

...

Rob yes very busy with converting all my scripts have about 30 more to go thru.

I tried your code

$Server = hostname

$csv1 = import-csv -path $LogFolder\$logfile

$csv1 = import-csv -path $LogFolder\$logfile1

$csv1html = $csv1 | ConvertTo-HTML -Fragment

$csv2html = $csv2 | ConvertTo-HTML -Fragment

#Thread strips html tags

$bigbody = @"

html open tag

body open tag

Servers

$csv1html

Workstations

$csv2html

body close tag

html close tag

"@

$Params = @{

 Subject = "$Server Pending Reboot Report"

 Body = "$bigbody"

 From = "no-reply@tgcsnet.com"

 To = "systems-alert@tgcsnet.com"

 smtpserver = "InternalRelay.TGCSNET.COM"

}

Send-MailMessage @Params

 

 

The email went thur but was not formatted correctly

html open tag

body open tag

Servers

<colgroup><col/><col/><col/><col/><col/><col/><col/></colgroup>

Computer CBServicing WindowsUpdate CCMClientSDK PendFileRename PendFileRenVal RebootPending
TGKW001 False True System.String True
TGKW004 False False False
TGKW006 False False True System.String True
TGKW007-10 False True System.String True
TGKW008-10 False False False
TGKW009 False False True System.String True
TGKW012 False False False False

Workstations

body close tag

html close tag

 

What I am missing here?

 

Thank you,

 

 

 

Please note that Get-Service has a -ComputerName parameter and it accepts multiple computers. So its ideally a oneliner.

Get-Service -Name RemoteRegistry -ComputerName s1,s2,s3 | Start-Service -Verbose

You could it via Invoke-Command as well in a more efficient way.

Invoke-command -ComputerName s1,s2,s3 -ScriptBlock { Get-Service -Name RemoteRegistry  | Start-Service -Verbose }

#As background jib will improve performance if you have more servers.
Invoke-command -ComputerName s1,s2,s3 -ScriptBlock { Get-Service -Name RemoteRegistry  | Start-Service -Verbose } -AsJob

Recommend taking a minute to understand the fundamentals. There are two types of emails, text and html. The only difference is a switch BodyAsHtml for Send-MailMessage:

$Params = @{
    To = "systems-alert@tgcsnet.com"
    From = "no-reply@tgcsnet.com"
    Subject = "$Server Pending Reboot Report"
    Body = "$bigbody"
    BodyAsHtml = $true
    SmtpServer= "InternalRelay.TGCSNET.COM"
}

Send-MailMessage @Params

HTML provides the ability to do text size, bold, color, etc. whereas not using the switch is straight text. If you are just getting text out of log, then you can do something simple like:

$body = Get-Content -Path C:\MyLog.txt | Out-String

If you want to use HTML, then it takes more work. Then you are converting PSObjects into HTML tables and basically building a web page to the mail client. Take a look at the eBook.

Thank you for the tip I converted the invoke-command to a single line

Rob,

Getting closer to finished product.

 

$LogFolder = "c:\util\logs"

$LogFile = "pendingrebootreportservers.csv"

$LogFile1 = "pendingrebootreportcomputers.csv"

$command = “.\Get-PendingReboot.ps1"

remove-item -path $LogFolder\$LogFile -Force

remove-item -path $LogFolder\$LogFile1 -Force

Invoke-Command -ComputerName TGKW001,TGKW003,TGKW004,TGKW006,TGKW007-10,TGKW008-10,TGKW009,TGKW012 -ScriptBlock {get-service -Name "remoteregistry" | start-service -verbose} -Asjob

Invoke-Expression $command

$Server = hostname

$csv1 = import-csv -path $LogFolder\$logfile

$csv2 = import-csv -path $LogFolder\$logfile1

$csv1html = $csv1 | ConvertTo-HTML -Fragment

$csv2html = $csv2 | ConvertTo-HTML -Fragment

#Thread strips html tags

$bigbody = @"

html open tag

body open tag

Servers

$csv1html

Workstations

$csv2html

body close tag

html close tag

"@

$Params = @{

 Subject = "$Server Pending Reboot Report"

 Body = "$bigbody"

 BodyAsHtml = $true

 From = "no-reply@tgcsnet.com"

 To = "systems-alert@tgcsnet.com"

 smtpserver = "InternalRelay.TGCSNET.COM"

}

Send-MailMessage @Params 

The email looks ok but it is not in HTML format

 

html open tag body open tag Servers it came in as Text which is ok but the “HTML open tag body open tag” lines should not be there

Computer CBServicing WindowsUpdate CCMClientSDK PendFileRename PendFileRenVal RebootPending
TGCS001-2012R2 False False False False
TGCS002-2016 False True System.String[] True
TGCS003-2012R2 False False False False
TGCS004 False False False False
TGCS005-N1 False False False False
TGCS005-N2 False False False False
TGCS005-S1 False False False False
TGCS006-2012R2 False False False False
TGCS006-2016 False True System.String[] True
TGCS007-2012R2 False False False False
TGCS008 False False False False
TGCS009-2012R2 False False False False
TGCS011 False False True System.String[] True
TGCS012 False False True System.String[] True
TGCS013 False False True System.String[] True
TGCS014-N1 False False False False
TGCS014-N2 False False False False
TGCS015 False False False False
TGCS016 False False False False
TGCS019-2012R2 False False False False
TGCS020-2016 False True System.String[] True
TGCS021-N1 False True System.String[] True
TGCS021-N2 False True System.String[] True
Workstations
Computer CBServicing WindowsUpdate CCMClientSDK PendFileRename PendFileRenVal RebootPending
TGKW001 False True System.String[] True
TGKW004 False False False
TGKW006 False False True System.String[] True
TGKW007-10 False True System.String[] True
TGKW008-10 False False False
TGKW009 False False True System.String[] True
TGKW012 False False False False
body close tag html close tag

 

Same with the body close tag html close tag line

 

Any ideas?

Taking things a little to literal. This forum strips XML and HTML tags, so I was attempting to avoid a Gist post, but:

Rob

 

The changes made and the email looks good but not in html format it is hard to tell the background is white as a normal text email

can we add some style to the code?

Yes, style can be added, which is outlined in the free eBook. Keep in mind that most mail clients are picky and if you want to read the email in multiple clients (e.g. Outlook, mobile, etc.), then keep the style simple and test.

Guys

 

Now have color to the email now

Thank for all your help