Create Html report using power shell

hi

i trying develop powershell script generate html report sql databse backups status.i got code form net generate as

excel report.but in prod i cant use excel com object .so need modify generate html report.i got successed 50% of that.

can provide me how create html report same functionality excel report using power shell.

below code generate excel report

  #create new excel object using com  $excel = new-object -comobject excel.application  $excel.visible = $true    $excel = $excel.workbooks.add()  $sheet = $excel.worksheets.item(1)    #counter variable rows  $introw = 1    #read thru contents of sql_servers.txt file  foreach ($instance in get-content "d:\sql_servers.txt")  {       #create column headers     $sheet.cells.item($introw,1) = "instance name:"     $sheet.cells.item($introw,2) = $instance     $sheet.cells.item($introw,1).font.bold = $true     $sheet.cells.item($introw,2).font.bold = $true       $introw++       $sheet.cells.item($introw,1) = "database name"     $sheet.cells.item($introw,2) = "last full backup"     $sheet.cells.item($introw,3) = "last log backup"     $sheet.cells.item($introw,4) = "full backup age(days)"     $sheet.cells.item($introw,5) = "log backup age(hours)"       #format column headers     for ($col = 1; $col –le 5; $col++)     {       $sheet.cells.item($introw,$col).font.bold = $true       $sheet.cells.item($introw,$col).interior.colorindex = 48       $sheet.cells.item($introw,$col).font.colorindex = 34     }       $introw++     #######################################################     #this script gets sql server database information using powershell       [system.reflection.assembly]::loadwithpartialname('microsoft.sqlserver.smo') | out-null       # create smo connection instance     $s = new-object ('microsoft.sqlserver.management.smo.server') $instance       $dbs = $s.databases       #formatting using excel      foreach ($db in $dbs)   {    if ($db.name -ne "tempdb") #we not need backup information tempdb database    {      $numdayssincelastfullbackup = ((get-date) - $db.lastbackupdate).days #we use date math extract number of days since last full backup      $numdayssincelastlogbackup = ((get-date) - $db.lastlogbackupdate).totalhours #here use totalhours extract total number of hours            if($db.lastbackupdate -eq "1/1/0001 12:00 am") #this default datetime value databases have not had backups      {        $fullbackupdate="never been backed up"        $fgcolor3="red"      }      else      {        $fullbackupdate="{0:g}" -f $db.lastbackupdate      }          $sheet.cells.item($introw, 1) = $db.name      $sheet.cells.item($introw, 2) = $fullbackupdate           #we use .tostring() method convert value of recovery model string , ignore log backups databases simple recovery model      if ($db.recoverymodel.tostring() -eq "simple")      {        $logbackupdate="n/a"        $numdayssincelastlogbackup="n/a"      }      else      {        if($db.lastlogbackupdate -eq "1/1/0001 12:00 am")         {          $logbackupdate="never been backed up"        }        else        {          $logbackupdate= "{0:g2}" -f $db.lastlogbackupdate        }              }          $sheet.cells.item($introw, 3) = $logbackupdate          #define service-level agreement in terms of days here      if ($numdayssincelastfullbackup -gt 1)      {        $fgcolor = 3      }      else      {        $fgcolor = 0      }          $sheet.cells.item($introw, 4) = $numdayssincelastfullbackup      $sheet.cells.item($introw, 4).interior.colorindex = $fgcolor          $sheet.cells.item($introw, 5) = $numdayssincelastlogbackup                  $introw ++          }    }    $introw ++      }    $sheet.usedrange.entirecolumn.autofit()  cls  

 

 

 

---

sivaprasad.l can achieve

i suggest creating psobject , adding information putting cell properties of object.

  $info = new-object psobject if ($tempdb){ $info | add-member -member noteproperty -name "tempdb" -value $someinfo } else { $info | add-member -member noteproperty -name "tempdb" -value $someotherinfo }  

once done, can convert object html.

  $info | convertto-html > c:\dbs.html  

check here examples

http://gallery.technet.microsoft.com/scriptcenter/en-us/

---

if found post helpful, please give "helpful" vote. if answered question, remember mark "answer".

rich prescott | mcitp, mcts, mcp

blog | twitter: @arposh | powershell client system administration tool

Windows Server  >  Windows PowerShell