Powershell - Check for data in HTML table

[tri_94]

Hi there

 

I have a system in place that checks servers and generates html with a table. If a problem occurs i'd like to be able to check all the files to check when this happened. So i need to check on two criteria's first would be Server name and second would be like cpu usage or offline status.

I'm trying to check 100's of html files.

 

I've tried things like

$file = "D:\test\Status.html"

$content = Get-Content $file

If($content -match 'Server1')

{Write-Host "Match"}

Else

{Write-Host "No Match"}

 

which works fine for server name but due to the format of the html table I can't seem to find a way to check for server name and same server offline.

 

 

 

Any help would be great

Many Thanks

 

 

 

 

 

 

The Html file's are this code format.

 

 

 

Server	CPU Usage	Memory Usage	Disk Usage	Uptime	Site	Group	Status
Server1 Microsoft Windows Server 2019 Datacenter	1.25%	20%	C: 30% E: 43%	1 days 8h 4m	Site1		Online

Generated: 03-July-2020 08:03, Duration: 0 min 34 sec

[fordea]

There's a nice article here about how to convert HTML tables to Objects using Powershell which will make the checks you need simpler and more adaptable: https://sysjam.wordpress.com/2016/01/08/consuming-html-tables-with-powershell/

 

You'll need to modify the function to parse HTML from a file instead of a webpage though. Something such as changing these lines should do the trick:

 

Param(
[uri]$URL = 'https://support.microsoft.com/en-us/lifecycle/search?sort=PN&alpha=windows%2010&Filter=FilterNO',
[boolean]$firstRowHeader = $false
)
#Get the webpage
$page = Invoke-WebRequest $URL

#Filter out only the tables
$tables = $page.ParsedHtml.body.getElementsByTagName('Table')

 

to

 

Param(
[string]$File,
[boolean]$firstRowHeader = $false
)
$html = New-Object -ComObject "HTMLFile"
$html.IHTMLDocument2_write($(Get-Content -Path $File -Raw))
$tables = $html.all.tags("table")

 

Then you should be able to call the function with your sample file and then filter the results with any checks you like:

 

$results = Get-HTMLTables -File D:\test\Status.html -FirstRowHeader $true
$results.0 | Where-Object {$_.Server -match 'Server1' -and $_.Status -eq 'Offline'}

[HPlum78]

Yeah i like the above from @fordea, i was looking at using my old fav Selenium to do this but have been in two meetings at the same time so have not had chance to progress....

[tri_94]

Brill thanks for the reply's, that looks a good way forward.

 

I've had a quick look and get this error. But it still did return

 

Site :

Memory Usage :

Server : Server1

CPU Usage :

Uptime :

Group :

Disk Usage :

Status : Offline

 

You cannot call a method on a null-valued expression.

At line:57 char:1

+ $tables = $page.ParsedHtml.body.getElementsByTagName('Table')

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

+ CategoryInfo : InvalidOperation: ( [], RuntimeException

+ FullyQualifiedErrorId : InvokeMethodOnNull

 

 

Thanks

Edited July 3, 2020 by tri_94

[fordea]

I think you missed changing the $tables line in the function. If you change the $tables assignment in the function to the following it should work

$tables = $html.all.tags("table")

[tri_94]

Yep spot on i missed that part.

 

Thanks that seems to work.

 

Is there an easy way to create a for each for this. I've tried this method but not sure how to pass the filename as $_.Fullname as i get a permission denied "PermissionDenied: (D:\test\01_03_2020\20_16_01.71:String)" as

it will need to be D:\test\01_03_2020\20_16_01.71\Status.html

 

As you can see from the code below i'm hoping to export the details would it be possible to add the filename to the export.

 

Thanks

 

$Path = "D:\test\01_03_2020"

$Server = "Server1"

$Text = "0.5%"

$output = "d:\test\Results.txt"

 

Get-ChildItem $Path -Recurse -Filter "*" -Depth 10 |

ForEach-Object { $results = Get-HTMLTables -File $_.FullName -Depth 10 -FirstRowHeader $true

$results.0 | Where-Object {$_.Server -match $Server -and $_.'CPU Usage' -eq $Text}} | Write-host "$_.Fullname" Out-File $output

Edited July 3, 2020 by tri_94

[fordea]

Are you just trying to output the list of filenames that hit a match with your Where-Object filter into a single txt file or do you need any rows from the input files that match the filter to also be output into that txt file?

[tri_94]

It would be handy to have the information and thinking about at the bottom of the html is a "

Generated: 03-July-2020 08:03, Duration: 0 min 34 sec Edited July 3, 2020 by tri_94

[tri_94]

Are you just trying to output the list of filenames that hit a match with your Where-Object filter into a single txt file or do you need any rows from the input files that match the filter to also be output into that txt file?

 

If its going to be easier to just return file name instead of data then they will work fine.

 

Thanks

[fordea]

Is every input file in the same format? I.e one table row and then the date/time it was generated at the bottom?

[tri_94]

Yes they are all the same

[fordea]

So a quick kludgy way of getting your desired output is to grab the generated value from the HTML center tag (because its the only center tag in your sample HTML) and then add both the Filename and Generated values as properties of the Get-HTMLTables output.

 

Add the following near the top of the Get-HTMLTables function:

 

$dateGenerated = (($html.all.tags("Center") | Select -ExpandProperty innertext) -split ',')[0]

 

Then add both the Filename and dateGenerated variables to the hashPage so that they're output by the function. Put these just before the end of the function before $objPage is created and returned:

 

$hashPage.Add('FileName',$File)
$hashPage.Add('Generated',$dateGenerated)

 

Then it's just a matter of filtering your results again but this time Selecting the Filename, Generated and any other properties you want. So for example to output the FileName, Generated Date, Server and Status:

 

($results.0).Where{$_.Server -match 'Server1' -and $_.Status -eq 'Online'} |
   Select @{n='FileName';e={$results.FileName}},
          @{n='Generated';e={$results.Generated}},
          Server,
          Status

[tri_94]

So i've added the two lines for the dateGenerated here

 

$tables = $html.all.tags("table")  


$dateGenerated = (($html.all.tags("Center") | Select -ExpandProperty innertext) -split ',')[0]


#Get only the tables that have cells

 

So i've added the two lines for the hashpage here

 

   }
   #Add the tables to the hashtable of tables
   $hashPage.Add($tablecount,$arrTable)
   $hashPage.Add('FileName',$File)
   $hashPage.Add('Generated',$dateGenerated)
   $tablecount++
}
$objPage = New-object -TypeName PSCustomObject -Property $hashPage
Return $objPage
}

 

and added the new $results line but i now get the following error

 

Exception calling "Add" with "2" argument(s): "Item has already been added. Key in dictionary: 'Generated' Key being added: 'Generated'"

 

Thanks

[fordea]

You've added the hashPage.Add lines inside the tables loop but it needs to be outside. Should look like so:

 

    $hashPage.Add($tablecount,$arrTable)
   $tablecount++
}
$hashPage.Add('FileName',$File)
$hashPage.Add('Generated',$dateGenerated)
$objPage = New-object -TypeName PSCustomObject -Property $hashPage
Return $objPage
}

[tri_94]

Thanks again for your help

That had seem to have worked.

Edited July 9, 2020 by tri_94