Some help needed on script to convert from csv to html

Welcome Forums General PowerShell Q&A Some help needed on script to convert from csv to html

This topic contains 10 replies, has 3 voices, and was last updated by

 
Participant
2 weeks, 5 days ago.

  • Author
    Posts
  • #126650

    Participant
    Points: 117
    Rank: Participant

    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

  • #126653

    Participant
    Points: 333
    Helping Hand
    Rank: Contributor

    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
    "@
    
    ...
    
  • #126660

    Participant
    Points: 117
    Rank: Participant

    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

     

    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,

     

     

     

  • #126696

    Participant
    Points: 889
    Helping Hand
    Rank: Major Contributor

    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
    
    • #126752

      Participant
      Points: 117
      Rank: Participant

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

  • #126737

    Participant
    Points: 333
    Helping Hand
    Rank: Contributor

    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.

    • #126771

      Participant
      Points: 117
      Rank: Participant

      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?

  • #126791

    Participant
    Points: 333
    Helping Hand
    Rank: Contributor

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

  • #126803

    Participant
    Points: 117
    Rank: Participant

    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?

  • #126804

    Participant
    Points: 333
    Helping Hand
    Rank: Contributor

    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.

  • #126812

    Participant
    Points: 117
    Rank: Participant

    Guys

     

    Now have color to the email now

    Thank for all your help

     

You must be logged in to reply to this topic.