Need assistance in modifying the SQL PS script to do conditional formatting

This topic contains 11 replies, has 5 voices, and was last updated by Profile photo of Wildcat4 Wildcat4 3 weeks, 5 days ago.

  • Author
    Posts
  • #56975
    Profile photo of M1981
    M1981
    Participant

    Hi PS Experts,

    Need assistance in modifying the PS script to do conditional formatting.

    I have a default sql instance and a table created in master db.

    create table diskspace
    (drive char(3),
    SizeGB int,
    FreeGB int,
    PercentFree int
    )
    go
    –dummy data .
    Note: precentfree are just sample values n not real ones.

    insert into diskspace values('C:',500,100,10)
    insert into diskspace values('D:',500,300,50)
    insert into diskspace values('E:',500,250,20)
    insert into diskspace values('F:',500,400,5)
    insert into diskspace values('G:',500,200,60)
    go

    Now, I have powershell script which does conditional formatting for the entire row based on "PercentFree" column.
    If PercentFree < 10 then the entire TABLE row() is displayed in RED indicating its CRITICAL. If PercentFree < 20 then the entire TABLE row() is displayed in YELLOW indicating a Warning. But I want to only change the background column of the PercentFree cell() and not entire row. How to achieve it? Any help ? Complete code =================

    $machine = "Srv-1"
    $Server = New-Object Microsoft.SqlServer.Management.Smo.Server("$machine")
    $qry = @"
    select drive,SizeGB,FreeGB,PercentFree from master..diskspace order by drive
    "@
    $myOutput = Invoke-Sqlcmd -Query $qry `
    -ServerInstance $env:COMPUTERNAME #default instance
    #embed the style, image and title in the html header
    $head = @"
    Space Utilization Report
    body
    {
    background-color:#FFFFFF;
    font-family:Arial;
    font-size:12pt;
    }
    td, th
    {
    border:1px solid black;
    border-collapse:collapse;
    }
    th
    {
    color:white;
    background-color:gray;
    }
    table, tr, td, th { padding: 5px; margin: 0px }
    table { margin-left:50px; }
    .danger {background-color: red}
    .warn {background-color: yellow}
    Disk Space Utilization on $Computername
    "@
    [xml]$html = $myOutput | Select drive,SizeGB,FreeGB,PercentFree | ConvertTo-HTML -fragment
    Clear-Host
    #check each row, skipping the TH header row
    for ($i=1;$i -le $html.table.tr.count-1;$i++)
    {
    $class = $html.CreateAttribute("class")
    #check the value of the last column and assign a class to the row
    #td[3] means 4th column i.e. last column in my table
    # drive - td[0], SizeGB - td[1],FreeGB - td[2],PercentFree - td[3]
    if (($html.table.tr[$i].td[3] -as [int]) -le 10)
    {
    $class.value = "danger"
    $html.table.tr[$i].Attributes.Append($class) | Out-Null
    }
    elseif (($html.table.tr[$i].td[3] -as [int]) -le 20)
    {
    $class.value = "warn"
    $html.table.tr[$i].Attributes.Append($class) | Out-Null
    }
    }
    #create the final report from the innerxml which should be html code
    $body = @"
    $($html.innerxml)
    "@
    #check out the html
    $body
    #create the HTML file
    ConvertTo-HTML -head $head -PostContent "$(Get-date)" -body $body | Out-File "$env:temp\driveSpaceusage.htm" -Encoding ascii
    #view it
    Invoke-Item "$env:temp\driveSpaceusage

  • #56989
    Profile photo of Wildcat4
    Wildcat4
    Participant

    M1981, I just registered in PowerShell.org Would you mind telling me how to post a question? Thx. Wildcat4

    • #56996
      Profile photo of Olaf Soyk
      Olaf Soyk
      Participant

      @Wildcat4 : When you're looking at the list of toppics – scroll down and fill in the 'Create New Topic in “PowerShell Q&A” – form' and hit 'Submit'

  • #56992
    Profile photo of M1981
    M1981
    Participant

    Not sure. Is there anything wrong in the post. Please let me know !!

  • #57002
    Profile photo of Wildcat4
    Wildcat4
    Participant

    Thank you for your quick reply! I found it.

  • #57005
    Profile photo of Stuart Squibb
    Stuart Squibb
    Participant

    Hi,

    You need to change 2 lines (45 and 50). Change the parts that say:

    $html.table.tr[$i].Attributes.Append($class)

    to

    $html.table.tr[$i].td[3].Attributes.Append($class)

    That should give you the result you are looking for.

  • #57022
    Profile photo of M1981
    M1981
    Participant

    Hi Stuart,

    Thank you for the response.

    Made the change but it didn't work!!

  • #57068
    Profile photo of Stuart Squibb
    Stuart Squibb
    Participant

    Hi,

    Sorry about that. This should have worked, but for some reason the table object model breaks once you get down to td elements. Try this instead. Replace the 2 lines in question (45 and 50) with:

    $html.table.tr[$i].GetElementsByTagName("td")[3].Attributes.Append($class) | Out-Null

    This works for me on IE11 and Edge.

    • #57140
      Profile photo of M1981
      M1981
      Participant

      Thank you Sir Stuart. Thank you very much for the help.

  • #57071
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    I just wanted to share a fun way to do reporting with LINQ. Keep in mind that if you are emailing this report, email clients are finicky and you need to keep things as simple as possible. You need to keep as much inline as possible and you should really avoid CSS in a header to have it render in mail and web clients. Anywho, take a look at this approach:

    • #57142
      Profile photo of M1981
      M1981
      Participant

      Hi Rob,

      Its little over my head but thank you for the alternative solution.

  • #57652
    Profile photo of Wildcat4
    Wildcat4
    Participant

    Hello, I got a reply having to do with "Need assistance in modifying the SQL PS script to do conditional formatting," but not dealing with data associated with Exchange/office 365 with PowerShell? Thank you.

You must be logged in to reply to this topic.