1

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
  • You asked about a SQL query a couple of days ago, can you modify it so it gives you the information you want, instead of trying to take the output and rework it in script? e.g. select sum(analog_phones), location from (that SQL) group by location (pseudocode). – TessellatingHeckler Mar 16 '14 at 15:46
  • the reason for the script is that I'm running the same SQL query against a little over 300 call managers. There is a foreach running the a java axl program to do the sql query.. at the top, i just didnt paste that portion since it wasnt relevant to the question. – driz Mar 17 '14 at 19:22
1

Something like this should work :

$result = @()

Function getIndex($location)
{
  for($j = 0; $j -lt $result.Count; $j++) {
    if($result[$j].Location -contains "$location") {
      $index=$j
      break;
    }
  }
  return $index
}

for ($i=0; $i -lt $outarray.length; $i++) {
  $loc=$outarray[$i].Location
  $aphones=$outarray[$i].'Analog Phones'
  $ipphones=$outarray[$i].'IP Phones'
  if(!($result | where-object {$_.Location -eq "$loc"})) {
    $result+=New-object psobject -property @{'Location' = "$loc"; 'Analog Phones' = $aphones; 'IP Phones' = $ipphones}  
    } else {
      $index=getIndex($loc) 
      $result[$index].'Analog Phones' = $result[$index].'Analog Phones' + $aphones
      $result[$index].'IP Phones' = $result[$index].'IP Phones' + $ipphones
   }
}
$result

$result output is :

Analog Phones      IP Phones     Location
-------------      ---------     --------
          413            887     Site1
          192            333     Site2

Here is the logic :

Define an empty $result array to store final results
Iterate over $outarray
  each time a location is found
     if location does not exists in $result then
        add full line from $outarray into $result for this location

     if location already exists in $result then
        retrieve the index for the given location in $result
        update Analog Phones number in $result with values from $outarray + $result for the given location (index)
        update IP Phones number in $result with values from $outarray + $result for the given location (index)

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) :

$outarray  = @()
$outarray+=New-object psobject -property @{'Analog Phones' = 0;'IP Phones' = 883;'Location' = "Site1"}
$outarray+=New-object psobject -property @{'Analog Phones' = 413;'IP Phones' = 0;'Location' = "Site1"}
$outarray+=New-object psobject -property @{'Analog Phones' = 0;'IP Phones' = 4;'Location' = "Site1"}
$outarray+=New-object psobject -property @{'Analog Phones' = 0;'IP Phones' = 258;'Location' = "Site2"}
$outarray+=New-object psobject -property @{'Analog Phones' = 9;'IP Phones' = 75;'Location' = "Site2"}
$outarray+=New-object psobject -property @{'Analog Phones' = 183;'IP Phones' = 0;'Location' = "Site2"}

From this point, my $outarray looks like this :

Analog Phones    IP Phones    Location
-------------    ---------    --------
            0          883    Site1
          413            0    Site1
            0            4    Site1
            0          258    Site2
            9           75    Site2
          183            0    Site2

Then i have added my code just after your export-csv command :

$outarray | Sort-Object............

MY CODE HERE

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 :

foreach($item in $finalxml){
  ...
  ...
  $outarray+=New-object psobject -property @{
        'Analog Phones' = [int]$item.analog_phones
        'IP Phones' = [int]$item.ip_phones
        'Location' = $location
    }
}
  • Can you elaborate on how you used your code within mine? when i attempted to integrate it I got strange numbers back for the counts and my $results look like this IP Phones : 346 Location : CCNS Analog Phones : 00 – driz Mar 17 '14 at 19:52
  • @driz Ok edited ! let me know, or post your full $outarray result, and also a sample sites.csv. So that i could run the xml parsing part also – krisFR Mar 17 '14 at 22:27
  • @driz See my second edit : i think it is your main issue to make my code work for you – krisFR Mar 18 '14 at 0:37
  • awesome! that all worked, thanks for the integer catch. still learning powershell. I appreciate all your assistance with this! – driz Mar 20 '14 at 19:30
1

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.

  • I was thinking along these same lines running a loop to see repeated locations and add the two categories as I go. TBH while i can conceptualize this, i havent been able to convert concept to code yet. I'll add the code im using up above momentarily, thanks for looking. – driz Mar 14 '14 at 21:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.