Automatically enumerate array name based on string contents of a line in a file

[Garacesh]

A piece of building management software we have dumps out reports on a daily basis, but they're fairly unintelligible and full of information that isn't required (such as still listing everything that hasn't experienced an error) so I'm writing a script to filter through it.. Now, originally I just had several nested IF statements building 5 separate arrays before emailing them, but it feels too kludgy, so yet again we have a case of Garacesh fixing what isn't broken.

 

So far I have stripped out the useless junk (their address, our address, etc) and left only the errors which leaves me with a list formatted like this:

Ftg.	99990	TEST ENTRY 00 GOOD ENT        OK
Ftg.	99991	TEST ENTRY 01 GOOD ENT        OK
Ftg.	99992	TEST ENTRY 10 FAULTY E   Warning
               ********** Error - Type 1 **********
Ftg.	99993	TEST ENTRY 02 GOOD ENT
Ftg.	99994	TEST ENTRY 11 FAULTY E   Warning
               ********** Error - Type 3 **********
Ftg.	99995	TEST ENTRY 03 GOOD ENT        OK
Ftg.	99996	TEST ENTRY 12 FAULTY E   Warning
               ********** Error - Type 4 **********
Ftg.	99997	TEST ENTRY 04 GOOD ENT        OK
Ftg.	99998	TEST ENTRY 3 FAULTY EN   Warning
               ********** Error - Type 2 **********
Ftg.	99999	TEST ENTRY 4 FAULTY EN   Warning
               ********** Error - Type 5 **********
Ftg.	00000	TEST ENTRY 05 GOOD ENT        OK

 

What I'm trying to do is write a loop that will, depending on the error, add it into an array named after that error. Now, I could do it like I have been doing previously..

if ($Item -match '`* Type 1 `*') {
	[array]$E1log += $ReportArray[$Counter-1] 
} elseif ($Item -imatch '`* Type 2`*') { 
	[array]$E2log += $ReportArray[$Counter-1] 
} elseif (

et cetera, but I much dislike having to use the same command each time with just slight tweaks (Also I'm trying to learn more things rather than use the same kind of script)

 

What I was hoping to use would be something like the following:

[int]$Counter = 0
ForEach ($Item in [array]$ReportArray) {
if ($Item -match '\*') {
	($(($Item).ToString() -replace ' ', '' -replace '\*', '' -replace '-', '')) += ($ReportArray[$Counter-1])
}
$Counter ++
}

Which in theory would get the contents of $Item (which would be the error), remove all spaces, asterisks and dashes (Leaving ErrorType1, ErrorType2 etc), then (using the counter that's clocking upwards) add the previous line to an array based on that name ($ErrorType1, $ErrorType2 etc).. Later on Send-MailMessage would fire off these errors to the IT and Site Management team only this time the report would be nicely formatted by error type and not include anything that didn't error.

 

As you can guess by me posting here, things aren't going quite to plan.

The closest I've gotten would be (${($Item).ToString() -replace ' ', '' -replace '\*', '' -replace '-', '')} += ($ReportArray[$Counter-1]) (using curled brackets) but that literally creates an array called "($Item).ToString() -replace ' ', '' -replace '\*', '' -replace '-', ''" (which I can reference later by again using curled brackets) rather than actually figuring out the (Item).ToString/Replace part first and getting a name based on that.

 

Anybody have any ideas?

[SYNACK]

This or something similar:

https://www.splunk.com/view/splunk/SP-CAAAG57

[LosOjos]

There's a Scripting Guy blog describing how to do just that here: http://blogs.technet.com/b/heyscriptingguy/archive/2011/12/08/read-a-csv-file-and-build-distinguished-names-on-the-fly-by-using-powershell.aspx

[Garacesh]

I've already tried subexpressions, and they almost work.

 

($(($Item).ToString() -replace ' ', '' -replace '\*', '' -replace '-', '')) does modify ***** Error - Type 1 ***** into ErrorType1 as it should, but what I'm trying to do is then create an array called ErrorType1 (which would be [array]$ErrorType1) and add the items that report that error to it with += ($ReportArray[$Counter-1])

 

So what should happen is..

 

ForEach $Item in $ReportArray

Enter Loop

Read first item (Open IF)

Item does not contain an asterisk (Close IF)

Add 1 to Counter.

Repeat loop

Read second item (Open IF)

Item does contain an asterisk (Continue IF)

Line is ***** Error - Type 3 *****. Convert that to ErrorType3. Add the previous line to an array named $ErrorType3 (Close IF)

Add 1 to Counter

So on and so forth.

 

Now, it needs to be the previous line that's added to the array because the error comes on the line directly after the item (none of the items contain asterisks, all of the errors do, which is why I look for those), that's why the counter is required, too (there might be another way of doing the previous item, but if there is, I don't know of it yet). Items that do not report an error simply have no error line afterwards, the next line is simply the next item in the list, which is why I'm loading the list into an array and using a ForEach loop.

 

So if my 5 errors were "** Seagull in jet engine **", "** Cape snagged in rocket **", "** Medic has Ubercharged **", "**You were eaten by a grue **" and "** Your Dungeon Heart is being attacked **" I would end up with 5 arrays named $Seagullinjetengine, $Capesnaggedinrocket, $Medichasubercharged, $Youwereeatenbyagrue and $YourDungeonHeartisbeingattacked. This means I wouldn't have to have 5 different IF/ELSEIF statements in order to categorise each different type of error into its type. These reports aren't exactly sophisticated.

 

Am I making any sense?

Edited July 15, 2014 by Garacesh

[LosOjos]

OK I'm with you, misunderstood the problem.

 

I don't think I've ever actually heard of dynamically creating named variables before (that's what dynamic arrays are for) but my approach would be to go for a key-value pair type of approach.

 

So, you create an empty array in PowerShell which will hold string values for your new array names; let's call it $key; and a second array called $value which will hold each separate array you want to store. Each time you parse the array name, you check if it already exists in $key and if not then you create it. Once you create the key, you then create the array with he same index in $value and finally add our new value to that array. If the key already exists in $key, we use the index to add data to the corresponding array in $value.

 

I don't do a lot of PowerShell, but if I was tackling this problem, that's how I'd do it.

 

There are a couple of articles here which may help (they were ones I skimmed through to make sure it's even possible to do it this way in PowerShell)

 

Find the Index Number of a Value in a PowerShell Array - Hey, Scripting Guy! Blog - Site Home - TechNet Blogs

Easily Create and Manipulate an Array of Arrays in PowerShell - Hey, Scripting Guy! Blog - Site Home - TechNet Blogs

 

EDIT: if it's possible to do multi-dimensional arrays in PowerShell, then you could make this even leaner by having a single multi-dimension array, the first dimension of which is the key and the second the corresponding arrays of values. That would be the "proper" way to do it.

Edited July 15, 2014 by LosOjos
Clarification of idea

[Garacesh]

Unfortunately, that still isn't working

 

PS M:\> $Item = "*** Error - Type 1 ***"

PS M:\> $Item

*** Error - Type 1 ***

PS M:\> ($Item -replace ' ', '' -replace '\*', '' -replace '-', '')

ErrorType1

PS M:\> $Errorlist += $ErrorType1

PS M:\> $Errorlist.indexof($($Item -replace ' ', '' -replace '\*', '' -replace '-', ''))

-1

PS M:\> $Errorlist.indexof(("$" + ($Item -replace ' ', '' -replace '\*', '' -replace '-', '')))

-1

PS M:\> $Errorlist.indexof($ErrorType1)

0

 

It recognises it if I reference it by it's actual name, but I can't seem to manipulate the input so that the same line could return multiple variables.

Edited July 15, 2014 by Garacesh

[LosOjos]

I'm not sure I explained it very well, plus doing a little research I found Powershell supports hash tables (designed for exactly this purpose)!

 

I've put together an example for you, using a CSV as input that hopefully you can adapt. Here's the CSV:

 

ErrorType,Code
Foo,5467
Tango,5094
Tango,8839
Tango,1476
Tango,7885
Tango,2882
Foo,5137
Tango,9135
Foo,8676
Tango,43
Tango,3202
Fault,3394
Tango,1137
Fault,6424
Tango,1318
Tango,8667
Fault,6441
Tango,1881
Fault,4497
Fault,3025
Fault,9048
Foo,7143
Foo,4569
Fault,6771
Foo,4924
Foo,3221
Foo,9109
Fault,2783
Foo,2449

 

The Powershell script here will iterate through this CSV, check the "ErrorType" and see if it exists in the hash table. If it does, it adds the value from the "Code" column to the value part for that key (which is an array). If it doesn't, it creates the new key and adds the initial value to it. When it's done, you're left with a hash table containing all the various error types as keys, so you can look up any by name (e.g. to get the values for "Fault", you'd use $keyvalue["Fault"]).

 

[color="#FF8C00"]$path[/color] = [color="#A52A2A"]"C:\errors.csv"[/color]
[color="#FF8C00"]$keyvalue[/color] =@{}
[color="#FF8C00"]$file[/color] = [color="#0000FF"]Import-Csv[/color] [color="#0000FF"]-path[/color] [color="#FF8C00"]$path[/color]

[color="#0000FF"]foreach[/color]([color="#FF8C00"]$line[/color] [color="#0000FF"]in[/color] [color="#FF8C00"]$file[/color])
{
   [color="#0000FF"]if[/color]([color="#FF8C00"]$keyvalue[/color].ContainsKey([color="#FF8C00"]$line[/color].ErrorType))
   {
       [color="#FF8C00"]$keyvalue[/color][[color="#FF8C00"]$line[/color].ErrorType] += ,([color="#FF8C00"]$line[/color].Code)
   }
   [color="#0000FF"]else[/color]
   {
       [color="#FF8C00"]$keyvalue[/color].add([color="#FF8C00"]$line[/color].ErrorType, @([color="#FF8C00"]$line[/color].Code))
   }
}

[color="#FF8C00"]$keyvalue[/color]

 

EDIT: just thought I'd make the example complete by posting the output given from the example data above:

 

Name                           Value                                                                                 
----                           -----                                                                                 
Fault                          {3394, 6424, 6441, 4497...}                                                           
Foo                            {5467, 5137, 8676, 7143...}                                                           
Tango                          {5094, 8839, 1476, 7885...}

Edited July 16, 2014 by LosOjos

[Garacesh]

Bloody heck.. I'd completely forgotten about hastables. That's a great way to go.. My Evacuation List uses hashtables to pair staff member to last known location (keyfob use).. How could I forget?!

 

[array]$ReportArray = (Get-Content (Get-ChildItem -File "C:\Comet\report\*.RAP" | Sort LastWriteTime | Select -Last 1))
$keyvalue =@{}
[int]$Counter = 0
ForEach ($Number in (0..15 + -14..-1)) {
[array]$ReportArray[$Number] = $null
}
foreach($line in $ReportArray)
{
if ($line -match '\*') {
	$keyvalue.add($ReportArray[$Counter-1], $line)
}
$Counter ++
}

$keyvalue | Format-Table -AutoSize

That gives me a hashtable of results.. Lamp name on one side, error on the second. From here I can sort-table by Value and that'll certainly be on the right track! Great, I think I'm almost do- ..!

 

Ftg.    16588   2 CIRCULATION [EXR]      Warning                 ***** Battery not charging *****
Ftg.    11826   G CIRCULATION COA 09 [   Warning                 ***** Battery not charging *****
[b][color="#FF0000"]                ***** Check emergency lamp *****                 ***** Reading failure (1x) *****[/color][/b]
Ftg.    15514   G ICT AL01 [A1DE]        Warning                 ***** Check emergency lamp *****

 

Lamps can have multiple errors... Didn't think about that.

Edited July 16, 2014 by Garacesh

[LosOjos]

Lamps can have multiple errors... Didn't think about that.

 

Can't you get around it by using an array for the value and storing each individual error in that array? That's what I did with the CSV example as I expected you'd have multiple errors for the same lamp. Or am I missing the point? (it's not unusual!)

 

Thinking about it, is it the case that you need to store each occurrence of each error type for each lamp? If so, can you set up a hash table for the lamps, then nest a second hash table for the error types in that hash table (or vice-versa depending on how you then want to access that data)? So you'd end up with something like this (purely made up I'm afraid!):

 

Lamp 1		Error Type 1		{ 1001, 1002, 1245 }
	Error Type 2		{ 5687, 7845, 9865, 1254 }
Lamp 2		Error Type 1		{ 7853 }
	Error Type 2		{ 846, 9786, 2165 }
Lamp 3		Error Type 3		{ 8743, 976, 654 }

Edited July 16, 2014 by LosOjos

[Garacesh]

Something like that might work..

Unfortunately, the entry to the (automatically generated) report (that I'm parsing to make it clearer) is as follows:

 

Ftg.     4867   1 CIRCULATION [HE]            OK
Ftg.     8577   1 TEACHING GTL 16 [ADE        OK
Ftg.     7203   1 CIRCULATION [HE]       Warning
               ***** Check emergency lamp *****
               ***** Reading failure (1x) *****
Ftg.     7206   1 CIRCULATION [HE]            OK

 

So unless I make a hash table for each and every lamp..

This looks like I'm going to have to go back to nested IFs and I don't like doing that.. Grumble..

 

Edit: Okay, it looks like hash tables are certainly the way to go..

[array]$ReportArray = (Get-Content (Get-ChildItem -File "C:\Comet\report\*.RAP" | Sort LastWriteTime | Select -Last 1))
$Output = ("M:\Lighting Test " + ((Get-Date).ToString("dd-MM-yy")) + ".txt")
$keyvalue = @{}
[int]$LineCounter = 0
$ReportArray[6..9], $ReportArray[-14..-4] | Out-File -Force $Output
ForEach ($Number in (0..15 + -14..-1)) {
[array]$ReportArray[$Number] = $null
}
foreach($line in $ReportArray) {
if ($line -match '\*') {
	$keyvalue.add((($ReportArray[$LineCounter-1]).ToString()), (($line).ToString() -replace '  ', '' -replace '\*', ''))
}
$LineCounter ++
}

$keyvalue.GetEnumerator() | Sort-Object -Property Value -Descending | Format-Table -AutoSize -Property Name -GroupBy Value -HideTableHeaders | Out-File -Append $Output

 

This gets me results that look like:

   Value: Reading failure - Check supply

Ftg.    11839   1 CIRCULATION COA [FE]     Error
Ftg.    11610   GYM PL02 [CE]              Error
Ftg.    15375   G CIRCULATION COA [EXR     Error
Ftg.    11950   G STAIRS [GE]              Error
Ftg.    11612   GYM PL02 [CE]              Error
Ftg.    14397   G EXTERNAL LIGHT [EX1P     Error

  Value:  Check emergency lamp 

Ftg.    17601   G MUSIC ML01 [A1DE]      Warning
Ftg.     6527   STAIR ENCLOSURE [FE]     Warning
Ftg.    18306   1 SPECIALIST LAB 07 [A   Warning
Ftg.    22116   G ICT AL01 [A1E]         Warning
Ftg.    18309   1 SERVICED LAB 04 [A1D   Warning

 

The only issue I'm having now is the issue of lamps with more than one error, they end up showing on the table like this:

   Value:  Reading failure (1x) 

               ***** Check emergency lamp *****

Which is, of course, not ideal. So! I'm much closer to getting this right with my code looking much nicer than a tonne of nested IFs.

Edited July 16, 2014 by Garacesh