Ivo has asked for the wisdom of the Perl Monks concerning the following question:

Hi everyone,

I am pulling out cli output with expect script from Cisco box in the following format:</> 

Friday April 25 21:06:25 UTC 2014

Total Subscribers:             970260    
Active:                        86806         Dormant:                 
+      883454    
pdsn-simple-ipv4:              212           pdsn-simple-ipv6:        
+      0         
pdsn-mobile-ip:                539792        ha-mobile-ipv6:          
+      0         
hsgw-ipv6:                     234943        hsgw-ipv4:               
+      1977      
hsgw-ipv4-ipv6:                192328        pgw-pmip-ipv6:           
+      0         
pgw-pmip-ipv4:                 0             pgw-pmip-ipv4-ipv6:      
+      0         
pgw-gtp-ipv6:                  0             pgw-gtp-ipv4:            
+      0         
pgw-gtp-ipv4-ipv6:             0             sgw-gtp-ipv6:            
+      0         
sgw-gtp-ipv4:                  0             sgw-gtp-ipv4-ipv6:       
+      0         
sgw-pmip-ipv6:                 0             sgw-pmip-ipv4:           
+      0         
sgw-pmip-ipv4-ipv6:            0             pgw-gtps2b-ipv4:         
+        0         
pgw-gtps2b-ipv6:                 0             pgw-gtps2b-ipv4-ipv6:  
+          0         
mme:                           0         
henbgw:                        0             henbgw-sg:               
+      0         
ipsg-rad-snoop:                0             ipsg-rad-server:         
+      0         
ha-mobile-ip:                  0             ggsn-pdp-type-ppp:       
+      0         
ggsn-pdp-type-ipv4:            0             lns-l2tp:                
+      0         
ggsn-pdp-type-ipv6:            0             ggsn-pdp-type-ipv4v6:    
+      0         
ggsn-mbms-ue-type-ipv4:        0         
pdif-simple-ipv4:              0         
pdif-simple-ipv6:              0             pdif-mobile-ip:          
+      0         
wsg-simple-ipv4:               0             wsg-simple-ipv6:         
+      0         
pdg-simple-ipv4:               0             ttg-ipv4:                
+      0         
pdg-simple-ipv6:               0             ttg-ipv6:                
+      0         
femto-ip:                      0             
epdg-pmip-ipv6:                0             epdg-pmip-ipv4:          
+      0         
epdg-pmip-ipv4-ipv6:           0         
epdg-gtp-ipv6:                 0             epdg-gtp-ipv4:           
+      0         
epdg-gtp-ipv4-ipv6:            0         
sgsn:                          0             sgsn-pdp-type-ppp:       
+      0         
sgsn-pdp-type-ipv4:            0             sgsn-pdp-type-ipv6:      
+      0         
sgsn-pdp-type-ipv4-ipv6:       0             type not determined:     
+      1008      
sgsn-subs-type-gn:             0             sgsn-subs-type-s4:       
+      0         
sgsn-pdp-type-gn:              0             sgsn-pdp-type-s4:        
+      0         
asngw-simple-ipv4:             0             asngw-simple-ipv6:       
+      0         
asngw-mobile-ip:               0         
asngw-non-anchor:              0             asngw-auth-only:         
+      0         
phsgw-simple-ipv4:             0             phsgw-simple-ipv6:       
+      0         
phsgw-mobile-ip:               0         
phsgw-non-anchor:              0         
cdma 1x rtt sessions:          0             cdma evdo sessions:      
+      6538      
cdma evdo rev-a sessions:      963722        cdma 1x rtt active:      
+      0         
cdma evdo active:              240           cdma evdo rev-a active:  
+      86566     
asnpc-idle-mode:               0             phspc-sleep-mode:        
+      0         
hnbgw:                         0             hnbgw-iu:                
+      0         
bng-simple-ipv4:               0         
pcc:                           0         
in bytes dropped:              321802752     out bytes dropped:       
+      1402973   
in packet dropped:             1884423       out packet dropped:      
+      6152      
in packet dropped zero mbr:    0             out packet dropped zero m
+br:   0         
in bytes dropped ovrchrgPtn:   0             out bytes dropped ovrchrg
+Ptn:  0         
in packet dropped ovrchrgPtn:  0             out packet dropped ovrchr
+gPtn: 0         
ipv4 ttl exceeded:             56873         ipv4 bad hdr:            
+      5668      
ipv4 bad length trim:          1098347   
ipv4 frag failure:             0             ipv4 frag sent:          
+      803578    
ipv4 in-acl dropped:           1437985       ipv4 out-acl dropped:    
+      0         
ipv6 bad hdr:                  55            ipv6 bad length trim:    
+      267       
ipv6 in-acl dropped:           0             ipv6 out-acl dropped:    
+      0         
ipv4 in-css-down dropped:      0             ipv4 out-css-down dropped
+:     0         
ipv4 out xoff pkt dropped:     0             ipv6 out xoff pkt dropped
+:     0         
ipv4 xoff bytes dropped:       0             ipv6 xoff bytes dropped: 
+      0         
ipv4 out no-flow dropped:      0         
ipv4 early pdu rcvd:           0             ipv4 icmp packets dropped
+:     0         
ipv6 input ehrpd-access drop:  0             ipv6 output ehrpd-access 
+drop: 0         
dormancy count:                113523050     handoff count:           
+      2680943   
pdsn fwd dynamic flows:        16211         pdsn rev dynamic flows:  
+      15046     
fwd static access-flows:       0             rev static access-flows: 
+      0         
pdsn fwd packet filters:       14869         pdsn rev packet filters: 
+      14869     
traffic flow templates:        6946

[download]

I would like to convert it in CSV file with column headers the fileds with the text ending with : and 1st line the numbers after the texts. Also the first column should be the date. The expect script runs every H and I would like to put the data into MySQL database from the .csv file. Please help.

Replies are listed 'Best First'.
Re: Parsing cli output into .csv
by davido (Cardinal) on Apr 26, 2014 at 01:44 UTC

    Step one: Read Writeup Formatting Tips.

    Step two, revisit your original post applying <p></p> tags around paragraphs of text, and <code>...</code> tags around blocks of data and code.

    Step three.... I won't know step three until your post has been made legible by your taking the actions identified in step one and two.


    Dave

[reply]
[d/l]
[select]

      Thanks Dave

      Corrected

[reply]
Re: Parsing cli output into .csv
by CountZero (Bishop) on Apr 25, 2014 at 21:56 UTC
    Nobody will read your post.

    Please put your data inside <code>...</code> tags.

    It would help to show a few lines of your desired output and you'll get even better help if you show some of the code you wrote.

    CountZero

    A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

    My blog: Imperial Deltronics
[reply]
[d/l]
      Nobody will read your post.

      That turned out not to be the case :)

      I was curious how much time it would take me to move such data in a postgres table (with apologies to the OP but I just can't be bothered with the minor oracle stuff).

      It took me 13 minutes.

      Obviously the parsing is guesswork on my part. 

      use strict;
use warnings;
use DBI;
my $t   = "pm1083865_ivo"; # table name
my $dbh = DBI->connect or die "no database connection - $!\n ";
$dbh->{AutoCommit} = 0;
$dbh->{RaiseError} = 1;
$dbh->do("drop table if exists $t");
$dbh->do("create table $t(col0 timestamptz, col1 text, col2 int, col3 
+text, col4 int)");
$dbh->commit;
my $sth1 = $dbh->prepare("insert into $t values(cast(? as timestamptz)
+,?,?)"    );
my $sth2 = $dbh->prepare("insert into $t values(cast(? as timestamptz)
+,?,?,?,?)");
my $date_string ;
while (<>) {
  chomp;
  $date_string = $_;
  last;
}
while (<>) {
  no warnings 'uninitialized';
  next if (/^\s*$/);
  my ($col1, $col2, $col3, $col4);
  s{\r}{};
  chomp;
  if ( /^([^:]+):\s*(\d+)\s*$/ )
  {
    $col1 = $1;
    $col2 = $2;
    $col3 = '';
    $col4 = '';
    $sth1->execute($date_string, $col1,$col2);
  }
  elsif ( /^([^:]+):\s*(\d+)\s*([^:]+):\s*(\d+)\s*$/ )
  {
    $col1 = $1;
    $col2 = $2;
    $col3 = $3;
    $col4 = $4;
    $sth2->execute($date_string,$col1,$col2,$col3,$col4);
  }
  else {
    print ">>>", $_ ,"<<<\n";
    die "oops - unprocessed line!\n";
  }
# print join("\t", $col1, $col2, $col3, $col4), "\n";
}
$dbh->commit;
$dbh->disconnect;


$ wc -l pm1083865_ivo.txt
72 pm1083865_ivo.txt


$ perl ivo.pl pm1083865_ivo.txt && echo "table pm1083865_ivo limit 5; 
+select count(*) from pm1083865_ivo" | psql
Timing is on.
          col0          |       col1        |  col2  |       col3     
+  |  col4
------------------------+-------------------+--------+----------------
+--+--------
 2014-04-25 23:06:25+02 | Total Subscribers | 970260 |                
+  |
 2014-04-25 23:06:25+02 | Active            |  86806 | Dormant        
+  | 883454
 2014-04-25 23:06:25+02 | pdsn-simple-ipv4  |    212 | pdsn-simple-ipv
+6 |      0
 2014-04-25 23:06:25+02 | pdsn-mobile-ip    | 539792 | ha-mobile-ipv6 
+  |      0
 2014-04-25 23:06:25+02 | hsgw-ipv6         | 234943 | hsgw-ipv4      
+  |   1977
(5 rows)

Time: 1.845 ms
 count
-------
    70
(1 row)

Time: 0.820 ms

      [download]

      UPDATE: change col0 to timestamptz; added a commit;

[reply]
[d/l]

        Thanks Eric!

        apology for the format, just first time user

[reply]
Re: Parsing cli output into .csv
by wjw (Priest) on Apr 26, 2014 at 05:39 UTC
    Man! You gotta clean up this post! (as noted by previous posters)

    That aside:

    1. I would use a regex to find /^(Monday||Tuesday||Wednesday||Thursday||Friday||Saturday||Sunday).?UTC\s\d\d\d\d\s$/(which I assume is constant for the beginning of each record, perhaps wrongly assumed). Maybe keep track of the fact that you have found the first one, because you will do that same search subsequently
    2. Execute same search again capturing the pre-match ($`) and the match ($&).
    3. Process the pre-match by finding each /\:\s\d+\s$/, which is probably your value, with your pre-match being the key and shoving key/values into a hash.
    4. Ouput your 'Date" and your keys to the first line of the CSV file(only have to do this once), giving you your header row.
    5. Output your first match and your values to the second row, giving you your first record
    6. Repeat 2 - 5 using the previous match as the first field in the record each time and the values from your hash generated by the processed pre-match as the rest of the fields. (sounds like a subroutine to me)
    This assumes the same amount of fields in each record is spitting out of your Cisco box, that there really are no line endings and that what you show above is really a single record.

    I suspect there are some holes in my logic that need to be filled as I did not examine this closely, but then there are holes in the way this
    problem was presented too.

    I also suspect it might be worth while to look around a bit to see if maybe someone has written something to do this kind of thing already.
    Check sysadmin hangouts and network admin hangouts(find 'em via google) and look at CPAN. Stuff like this has been
    done before in most cases, and you can save yourself a lot of time just doing a decent Google
    search or two.

    If, God forbid, you decide to try what I conceptualized above, make sure you look at Perlreref first! Hope this gives you an idea if nothing else.

    ...the majority is always wrong, and always the last to know about it...
    Insanity: Doing the same thing over and over again and expecting different results...
    wjw
[reply]

      Tanks wjw!

      Sorry for the firs-time-user formatting mistakes

      Those are all good advises and I will dig into it. I am sure somebody already have done this. Just some time is as difficult to find the right sourse and I guess that's why I am here :-)

[reply]
        Nice job cleaning that up! Thanks..

        ...the majority is always wrong, and always the last to know about it...
        Insanity: Doing the same thing over and over again and expecting different results...
        wjw
[reply]

      Done! Apologies

[reply]

Back to Seekers of Perl Wisdom