Having trouble making a script work

Experts -

Am I missing something?  I get the folllowing when I do this:

####### WITH THIS DATA (saved as .\etherchannel-summary-test.txt)::

Group  Port-channel  Protocol    Ports
------+-------------+-----------+-----------------------------------------------
1      Po1(SU)          -        Gi0/47(P)   Gi0/48(P)  

######## I RUN THIS SCRIPT:

$separator = "|"
$entete = "Group  PortChannel  Protocol    Ports"
$sourceFile = ".\etherchannel-summary-test.txt"
$targetFile = ".\etherchannel-summary-test.csv"
# FIRST ROW : THE FIELDS NAME
# WE REPLACE THE OLD FILE WITH ">" AND NOT ">>" TO ADD RECORD

echo "Group|PortChannel|Protocol|Ports" > $targetFile
Import-Csv $sourceFile | foreach {
        $line = $_.$entete
        $Group = $line.substring(0,7).trim().trimEnd('$')
        $PortChannel = $line.substring(7,14).trim().trimEnd('$')
        $Protocol =  $line.substring(21,12).trim().trimEnd('$')
        $Ports = $line.substring(33).trim().trimEnd('$')
        $newline = $Group + $separator + $PortChannel + $separator + $Protocol + $separator + $Ports
       
        echo $newline >> $targetFile
}

######## AND GET THESE ERRORS
PS C:\Documents and Settings\cwatts\Desktop\RHUBNA-ACCESS\ps-test> .\etherchannel-summary-test.ps1
You cannot call a method on a null-valued expression.
At C:\Documents and Settings\cwatts\Desktop\RHUBNA-ACCESS\ps-test\etherchannel-summary-test.ps1:11 char:33
+         $Group = $line.substring <<<< (0,7).trim().trimEnd('$')
You cannot call a method on a null-valued expression.
At C:\Documents and Settings\cwatts\Desktop\RHUBNA-ACCESS\ps-test\etherchannel-summary-test.ps1:12 char:39
+         $PortChannel = $line.substring <<<< (7,14).trim().trimEnd('$')
You cannot call a method on a null-valued expression.
At C:\Documents and Settings\cwatts\Desktop\RHUBNA-ACCESS\ps-test\etherchannel-summary-test.ps1:13 char:37
+         $Protocol =  $line.substring <<<< (21,12).trim().trimEnd('$')
You cannot call a method on a null-valued expression.
At C:\Documents and Settings\cwatts\Desktop\RHUBNA-ACCESS\ps-test\etherchannel-summary-test.ps1:14 char:33
+         $Ports = $line.substring <<<< (33).trim().trimEnd('$')

#######  I NEED TO FIGURE OUT WHAT I AM DOING WRONG BEFORE I ALTER ANOTHER TWO SCRIPTS TRYING TO #######  PULL SIMIILAR DATA

Thanks for your help.
colby_wattsAsked:
Who is Participating?
 
Chris DentPowerShell DeveloperCommented:

You're absolutely right about the append to file, I forgot to put that back in, was testing using the console only.

Anyway, just to be annoying, the problem is caused by Import-CSV and how it's reading values in.

It's automatically trimming off the leading spaces from your input. For example, it turns this:

   1    0000.5400.0101    DYNAMIC     Gi0/13

Into this:

1    0000.5400.0101    DYNAMIC     Gi0/13

A minor change, but since you're parsing as fixed width it breaks horribly.

Get-Content on the other hand will preserve the leading spaces and let you split it up using the fixed SubString values above.

Chris
0
 
colby_wattsAuthor Commented:
OK, I made this work myself, but would like to know if there is a way to run it with the original, unaltered input from Cisco sh etherchannel summary.  The problem seemed to be the "Port-channel" field.  I tried escaping the "-" with "\-"  but that did not work.  So, I renamed any reference to Port-channel in the script to PortChannel.  That seemed to do it.

$separator = "|"
$entete = "Group  PortChannel  Protocol    Ports"
$sourceFile = ".\etherchannel-summary-test.txt"
$targetFile = ".\etherchannel-summary-test.csv"
# FIRST ROW : THE FIELDS NAME
# WE REPLACE THE OLD FILE WITH ">" AND NOT ">>" TO ADD RECORD

echo "Group|PortChannel|Protocol|Ports" > $targetFile
Import-Csv $sourceFile | foreach {
        $line = $_.$entete
        $Group = $line.substring(0,7).trim().trimEnd('$')
        $PortChannel = $line.substring(7,14).trim().trimEnd('$')
        $Protocol =  $line.substring(21,12).trim().trimEnd('$')
        $Ports = $line.substring(33).trim().trimEnd('$')
        $newline = $Group + $separator + $PortChannel + $separator + $Protocol + $separator + $Ports
       
        echo $newline >> $targetFile
}      
0
 
Chris DentPowerShell DeveloperCommented:

Hey dude,

You might use Get-Content instead of Import-CSV for this since you're doing all the work yourself.

That'll remove the need to access the line as a property referenced by $entete, then $_ will be the equivalent to $_.$entete.

I would also be tempted to use [String]::Split( ... ) to chop up the line based on space, using the option to drop empty entries. I think I used that one in the first script I did for you, yell if not and I'll throw together another example.

Chris
0
How do you know if your security is working?

Protecting your business doesn’t have to mean sifting through endless alerts and notifications. With WatchGuard Total Security Suite, you can feel confident that your business is secure, meaning you can get back to the things that have been sitting on your to-do list.

 
colby_wattsAuthor Commented:
Thanks for the input.  When I am on my work computer, I will post my latest on this...I am using Read-Host to get source/dest files and it seems to be working well.  Your first suggestion is a no-brainer, I think.  I will try to use that.  The [string]:::split is something I will play with to see if I can make it work.  The main feature that I am trying to add to this script it that ability to do a simple find/replace on it after the parsing is complete...seems simple enough, but I cannot make it work.  That will be the subject of a follow-on question I think.  
0
 
colby_wattsAuthor Commented:
Chris - Can I ask you to take a look at my other open question...another Cisco ACL-related parsing.  The concept is a little different on this one: data is laid out vertically (as opposed to in nice horizontally-aligned fields), and I don't know how to parse it out that way.  So, the problem conceptually is, as I see it, that instead of processing the data line by line, the data has to be "blocked" from a starting field to an ending field than parsed within that. A little complicated for my abilities at this moment.  Can you give me some ideas on how you might attack that?  Thanks.
0
 
Chris DentPowerShell DeveloperCommented:

Just for reference, [String]::Split() method syntax is here:

http://msdn.microsoft.com/en-us/library/system.string.split.aspx

Once you're happy converting from the C# examples (those are syntactically closest) to PowerShell you're on solid ground.

I went back to the related question and it may not actually be appropriate since the values of some fields contain spaces.

I can't reproduce your error for this one. Although you may find it's simply because there's a few blank lines in the CSV file. Take a look at:

(Import-Csv $sourceFile).Count

Did you mean to leave the separator line in place (----+---- etc)?

Anyway, for the code I used is below (almost no changes).

Chris
$separator = "|"
$entete = "Group  PortChannel  Protocol    Ports"
$sourceFile = "in2.txt"
$targetFile = ".\etherchannel-summary-test.csv"
# FIRST ROW : THE FIELDS NAME
# WE REPLACE THE OLD FILE WITH ">" AND NOT ">>" TO ADD RECORD

echo "Group|PortChannel|Protocol|Ports"
Get-Content $sourceFile | %{
        $Group = $_.substring(0,7).trim().trimEnd('$')
        $PortChannel = $_.substring(7,14).trim().trimEnd('$')
        $Protocol =  $_.substring(21,12).trim().trimEnd('$')
        $Ports = $_.substring(33).trim().trimEnd('$')
        $newline = $Group + $separator + $PortChannel + $separator + $Protocol + $separator + $Ports
       
        echo $newline
}

Open in new window

0
 
colby_wattsAuthor Commented:
Attached you will find some code, input, and output files.  The parsing is not correct, but I cannot for the life of me figure out where it is wrong.  The script works perfectly, just seems to misfire on where to parse the fields...I am sure it is not random, but it sure seems so.   Below are some examples on some scrubbed output.

Incorrect:
SourceDevice|Vlan|MacAddress|Type|Ports
sw2|All    0|200.0eee.eeee    S|TATIC      C|PU
...
sw2|All    f|fff.ffff.ffff    S|TATIC      C|PU
sw2|2    000|0.6e00.0202    DYN|AMIC     Gi0|/6
sw2|2    000|6.00b6.09fe    DYN|AMIC     Gi0|/6
....
sw2|2    000|e.296a.69f6    DYN|AMIC     Po2|

Correct:
SourceDevice|Vlan|MacAddress|Type|Ports
sw2|All|0200.0eee.eeee|STATIC|CPU
...
sw2|All|ffff.ffff.ffff|STATIC|CPU
sw2|2|0000.6e00.0202|DYNAMIC|Gi0/6
sw2|2|0006.00b6.09fe|DYNAMIC|Gi0/6
....
sw2|2|000e.296a.69f6|DYNAMIC|Po2

Thanks in advance for your help.
$sourceFile = Read-Host Source File 
$targetFile = Read-Host Target File
$sourceDevice = Read-Host "Source Device"
$separator = "|"
$entete = "Vlan    Mac Address       Type        Ports" 
#$sourceFile = ".\int-status-test.txt"
#$targetFile = ".\int-status-test.csv" 
# FIRST ROW : THE FIELDS NAME
# REPLACE THE OLD FILE WITH ">", ">>" TO APPEND RECORD TO FILE (IF APPEND, YOU WILL NEED TO CLEAN THE "HEADER LINE" PORTION OF DATA UP LATER)
echo "SourceDevice|Vlan|MacAddress|Type|Ports" >> $targetFile 
Import-CSV $sourceFile | foreach { 
        $line = $_.$entete 
        $Vlan = $line.substring(0,8).trim().trimEnd('$')
        $MacAddress = $line.substring(8,18).trim().trimEnd('$')
        $Type =  $line.substring(26,12).trim().trimEnd('$')
        $Ports = $line.substring(38).trim().trimEnd('$')
        $newline = $sourceDevice + $separator + $Vlan + $separator + $MacAddress + $separator + $Type + $separator + $Ports
        
        echo $newline >> $targetFile
}

Open in new window

sh-mac-address-table-scrubbed-.csv
sh-mac-address-table-input1-scru.txt
0
 
colby_wattsAuthor Commented:
chris -

By the way, I did not want to put in the Get-Content yet because 1) I want to fix the parsing first, and don't want to muddy the waters too much, and 2) I did not see where the line for outputting (appending) to the target file would take place.  I assume that this should be it:

16: echo $newline >> $targetFile

Otherwise, I think that is a good, pretty clear, and simple change.  Thanks.
0
 
colby_wattsAuthor Commented:
So, Get-Content is my answer...I will give it a shot.  I hope this conversation is at least moderately interesting to you...it helps me immensely.  Just to let you know the practical use of these: I am in the middle of two projects to 1) consolidate and review our global firewall configs and 2) build a database, sort of, of our switched connections from A to Z.  So, we use Excel spreadsheets mostly for manipulating this type of info (which I hope to migrate to an Access or SQL DB at one point), so if I can get it into imported cleanly, then I can use Autofilters, Sorts, VLOOKUPS or whatever else in Excel to build a nice tool.  The reason I am messing around with the PS scripts so much is because i think PS is fun and I also hate to build a process that is not easily repeatable.  So, i will let you know how all this turns out.  
0
 
Chris DentPowerShell DeveloperCommented:

It takes up very little of my time and it's nice to help. I'm bit of a scripting evangelist, I think everyone should learn :)

PS is indeed fun; no where near as confined or limited as VbScript, while requiring less energy than Perl (at least on the MS side).

I can't speak for Access, I have an aversion to it :) PS can interface pretty easily with an SQL database if you do go down that road. It would open up combinations like the .NET SqlClient class with the Grid-View CmdLet is PS 2.0.

Chris
0
 
colby_wattsAuthor Commented:
You were right...that Get-Content worked like a dream.   I have a good template now for the other commands:

sh mac address-table
sh int status
sh cdp neighbors
sh int controller errors

With those parsed, imported and manipulated (in Excel, Access, SQL, etc.) , we can get a good picture which interfaces are throwing errors, what its interface settings are, and trace the connections from switch to switch back to the end device.  

I will close this and give you the points. Thanks.
0
 
colby_wattsAuthor Commented:
OK, had this problem with the trim on the substring when the int speed autonegotiated to 1000 (i.e., a-1000):
sw1|Gi0/39|Pri PIX DMZ Trunk|connected|trunk|a-full|a-100 10/100/1000BaseTX
sw1|Gi0/40|Pri PIX Outside -|connected|2|a-full a|-1000 10/100/1000BaseTX

I fixed it by counting the trim not from the header but rather from the "a-1000..." data in the field.  It worked correctly after that.  it is a good template and can be used for other similar commands.

#File: sh_int_status-test.ps1
#Description: Pulls the "sh int status test" output from a Cisco switch, creates a field that User populates with device name, parses output in order put it into a CSV-importable file.

$sourceFile = Read-Host Source File 
$targetFile = Read-Host Target File
$sourceDevice = Read-Host "Source Device"
$separator = "|"
$entete = "Port      Name               Status       Vlan       Duplex  Speed Type" 
#$sourceFile = ".\int-status-test.txt"
#$targetFile = ".\int-status-test.csv" 

# FIRST ROW : THE FIELDS NAME
# REPLACE THE OLD FILE WITH ">", ">>" TO APPEND RECORD TO FILE (IF APPEND, YOU WILL NEED TO CLEAN THE "HEADER LINE" PORTION OF DATA UP LATER)

echo "SourceDevice|Port|Name|Status|Vlan|Duplex|SpeedType" >> $targetFile 
Get-Content $sourceFile | foreach { 
        $Port = $_.substring(0,10).trim().trimEnd('$')
        $Name = $_.substring(10,19).trim().trimEnd('$')
        $Status = $_.substring(29,13).trim().trimEnd('$')
        $Vlan = $_.substring(42,11).trim().trimEnd('$')
        $Duplex = $_.substring(53,7).trim().trimEnd('$')
        $SpeedType = $_.substring(60).trim().trimEnd('$')
        $newline = $sourceDevice + $separator + $Port + $separator + $Name + $separator + $Status + $separator + $Vlan + $separator + $Duplex + $separator + $SpeedType
        
        echo $newline >> $targetFile
}

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.