I have a script that returns the following information
Analog Phones IP Phones Location
------------- --------- --------
0 883 Site1
413 0 Site1
0 4 Site1
0 258 Site2
9 75 Site2
183 0 Site2
This information is stored in an array $arrayX
I am trying to figure out how to get the ouput to show
Analog Phones IP Phones Location
------------- --------- --------
413 887 Site1
192 333 Site2
The data feeding the array is not presented in such a way; does anyone have any ideas on how to accomplish something like this? I can post the whole script but it's a jumble of java, axl, and powershell right now.
There is a point in the script where the data looks like this
Analog Phones IP Phones Location
------------- --------- --------
413 0 Site1-AGW-DP
0 883 Site1-PHONES-DP
0 4 Site1-PHSRST-DP
The XML files that are being used are output from a java axl program. the results of one of those are shown below:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Header/>
<SOAP-ENV:Body>
<axl:executeSQLQueryResponse xmlns:axl="http://www.cisco.com/AXL/API/6.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" sequence="1394831301468">
<return>
<row>
<analog_phones>0</analog_phones>
<ip_phones>47</ip_phones>
<devicepool>Site20-PHONES-DP</devicepool>
</row>
<row>
<analog_phones>533</analog_phones>
<ip_phones>0</ip_phones>
<devicepool>Site20-AGW-DP</devicepool>
</row>
<row>
<analog_phones>1</analog_phones>
<ip_phones>689</ip_phones>
<devicepool>Site20-PHSRST-DP</devicepool>
</row>
</return>
</axl:executeSQLQueryResponse>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
Powershell script starting after java bit
$files = (Get-ChildItem .\*out.xml).name
foreach ($file in $files){
[xml]$xmlfiles = gc $file
$finalxml += $xmlfiles.envelope.body.executeSQLQueryResponse.return.row
Remove-Item $pwd\$file
}
$outarray = @()
$sitelist = import-csv sites.csv
foreach($item in $finalxml){
if(!$item.devicepool){$sed="BLANK"}
else{$sed=($item.devicepool).substring(0,4)}
$location=($sitelist | where-object {$_.prefix -eq $sed}).Facility
if(!$location){$location=$sed}
$outarray+=New-object psobject -property @{
'Analog Phones' = $item.analog_phones
'IP Phones' = $item.ip_phones
'Location' = $location
}
}
$outarray | Sort-Object 'Analog Phones','IP PHones' -unique | sort-object Location | select-object 'Analog Phones','IP Phones',Location |export-csv $csvout
From the looks of it, you are trying to combine all the results into a single entry per site? e.g. - Total Analog Phones and Total IP Phones at each location.
If that is the case, you should be able to iterate through that array. Total the analog phones and IP phones per site, then use those values to populate a new array.
If you are looking for some code, we would have to know how the array is constructed to build the logic.
Something like this should work :
$result
output is :Here is the logic :
PS : i am not a Powershell Guru, so i think the
getIndex
could be improved. However, it worked for me based on your source code.Hope it will help !
EDIT
Here is the way i have integrated my code into yours :
First i have created the
$outarray
like you (i think), except that i used hard coded values (not parsing a xml file) :From this point, my
$outarray
looks like this :Then i have added my code just after your
export-csv
command :EDIT 2
I've found an issue with your actual code : numbers are interpreted as strings so they cannot be correctly calculated. You have to convert them into integers :