Gheek.net

September 11, 2015

Use Perl to Create Multi-page or Multi-Document Visio(s) using Visio Plugin for PowerShell

Filed under: perl, PowerShell, Visio — lancevermilion @ 12:36 pm

I draw Visio diagram very regular but have been very motivated to create more and more dynamic diagram that only require the artistic touch to make the diagrams look the way you want. There should be no reason to waste time typing in data if you already have it in a Excel Spreadsheet, SharePoint Foundation List, or DB. I am using Excel Spreadsheets for this example but you can use whatever you want. There is additional things you would need to do to make it work obviously. To do all the drawing you can do it in VBA, PowerShell using the Visio module/plugin, or C# (I will learn this one day). I have decided to use the Visio module/plugin for PowerShell by Saveen Reddy because it is the quickly to learn and use for me. He is the author of a larger project call Visio Automation.

I did create my own Stencil Pack and added my Data Graphics to it. I prefer to create diagram that has simple hostnames (that are unique) so they can be linked using the external data concept in Visio and use the Excel Spreadsheet, SharePoint Foundation List, or DB options. Since I use the unique hostname as the key in the XLS I can match that to the name of the shape on the page. I can then populate the Data Graphic with all the data from the external data. I also keep a connection list in a format of C followed by any number of digits. This allows the WAN links to be linked to the external data.

Here is a little of useful powershell to quickly select the specific shapes all at once.

# Select all rectangles (aka didn't match criteria for specific stencil)
Select-VisioShape None
$shapes = Get-VisioShape *
$shapeids = $shapes | where { $_.NameU -like "*rectangle*"} | Select ID
if ($shapeids) { Select-VisioShape $shapeids.ID }

# Select all Dynamic connectors
Select-VisioShape None
$shapes = Get-VisioShape *
$shapeids = $shapes | where { $_.NameU -like "*Dynamic connector*"} | Select ID
if ($shapeids) { Select-VisioShape $shapeids.ID }

Resize a rectangle shape (default shape in the code that draws the diagrams automatically) on all pages

#
# Go page by page and resize each rectangle and set other page attributes
#

# If the Visio plugin for PowerShell doesn't see a connection to Visio connect to it.
if (-Not (Test-VisioApplication) ) 
{
    Connect-VisioApplication
}

# Sometimes you will need to also attach to the document.
$app = Get-VisioApplication


$pages = Get-VisioPage | where { $_.Name } | Select Name
foreach ( $page in $pages )
{
    if ( "background" -eq $page.Name ) {break}
    Write-Host Processing: $page.Name
    Set-VisioPage -Name $page.Name
    $shapes = Get-VisioShape *
    $rectids = $shapes | where { $_.NameU -like "*rectangle*"} | Select ID
    if ($rectids)
    {
        Write-Host $rectids.ID
        Select-VisioShape $rectids.ID
        Set-VisioShapeCell -Width 0.6049 -Height 0.475
        Set-VisioPageLayout -Orientation Landscape -BackgroundPage background
        Set-VisioPageCell -PageWidth 11.0 -PageHeight 8.5
    }
    Select-VisioShape None
}

Because I am not a decent PowerShell programmer/script/hacker/etc I wrote a Perl script to output a PowerShell script to put in PowerShell ISE and do my dirty work for me. 🙂

My Script requires input from a file in the following format.

label = "Name of Page/Document in Visio"
DEV1,DEV2,Connector_Label,Connector_Type

Real Data

label = "Site A"
ATTCLOUD,DEV1,Connector_Label,Connector_Type
DEV1,DEV2,Connector_Label,Connector_Type
DEV2,DEV3,Connector_Label,Connector_Type
DEV2,DEV5,Connector_Label,Connector_Type
DEV2,DEV4,Connector_Label,Connector_Type
XOCLOUD,DEV4,Connector_Label,Connector_Type

Currently Connector_Label is ignored but could be used for different implementations.
Connector Types are: Straight, RightAngle, or Curved (this is the default)

Perl Script to Create a PowerShell Script to Dynaimcally draw Diagrams

#!/usr/local/bin/perl

my $filename = $ARGV[0];
my @arr = do {
    open my $fh, "<", $filename
        or die "could not open $filename: $!";
    <$fh>;
};

my $hash = {};
my $save_location = 'c:';

# Where to place each diagram
# 0 = Each Diagram goes on new page
# 1 = Each Diagram goes in its own Visio file
my $newdocper = 0;

my $site = '';
my $site_clean = '';
my $cnt = 0;
for my $line (@arr)
{
  chomp($line);
  if ( $line =~ /label/ )
  {
    $site = '';
    (undef, $site) = split(/ = /, $line);
    $site_clean = $site;
    $site_clean =~ s/"//g;
    $site_clean =~ s/-/_/g;
    $site_clean =~ s/#/NUM/g;
    $site_clean =~ s/&/_/g;
    $site_clean =~ s/\(//g;
    $site_clean =~ s/\)//g;
    $site_clean =~ s/\//_/g;
    $site_clean =~ s/ /_/g;
    $site_clean =~ s/\./_/g;
  }
  else
  {
    $cnt++;
    my ($from, $to, $label, $linetype) = split(/,/, $line);
    my $to_clean = $to;
    $to_clean =~ s/"//g;
    $to_clean =~ s/-/_/g;
    $to_clean =~ s/#/NUM/g;
    $to_clean =~ s/&/_/g;
    $to_clean =~ s/\(//g;
    $to_clean =~ s/\)//g;
    $to_clean =~ s/\//_/g;
    $to_clean =~ s/ /_/g;
    $to_clean =~ s/\./_/g;
    my $to_normal = $to;
    my $from_clean = $from;
    $from_clean =~ s/"//g;
    $from_clean =~ s/-/_/g;
    $from_clean =~ s/#/NUM/g;
    $from_clean =~ s/&/_/g;
    $from_clean =~ s/\(//g;
    $from_clean =~ s/\)//g;
    $from_clean =~ s/\//_/g;
    $from_clean =~ s/ /_/g;
    $from_clean =~ s/\./_/g;
    my $from_normal = $from;

    # Build Node portion of hash
    $hash->{$site_clean}->{'nodes'}->{$from}->{'clean'} = $from_clean;
    $hash->{$site_clean}->{'nodes'}->{$from}->{'normal'} = $from_normal;
    $hash->{$site_clean}->{'nodes'}->{$to}->{'clean'} = $to_clean;
    $hash->{$site_clean}->{'nodes'}->{$to}->{'normal'} = $to_normal;

    # Build links portion of hash
    $hash->{$site_clean}->{'links'}->{$cnt}->{'from'} = $from_clean;
    $hash->{$site_clean}->{'links'}->{$cnt}->{'to'} = $to_clean;
    $hash->{$site_clean}->{'links'}->{$cnt}->{'label'} = $label;
    $hash->{$site_clean}->{'links'}->{$cnt}->{'linetype'} = $linetype;
    
  }
}

print "Set-StrictMode -Version 2\n";
print "\$ErrorActionPreference = \"Stop\"\n";
print "Import-Module Visio\n";
print "\$options = New-Object VisioAutomation.Models.DirectedGraph.MSAGLLayoutOptions\n";
print "\$d = New-VisioDirectedGraph\n";
print "\$app = New-Object -ComObject Visio.Application \n";
print "\$app.visible = \$true \n";
print "\$docs = \$app.Documents \n";
print "\$doc = \$docs.Add(\"DTLNET_U.vst\") \n";
print "\$pages = \$app.ActiveDocument.Pages \n";
print "\$page = \$pages.Item(1)\n";
print "\$stencil = \$app.Documents.Add(\"My_Network_Stencil_Pack.vss\")\n";
print "\$backgroundborder = \$stencil.Masters.Item(\"Background Border\")\n";
print "\$infobar = \$stencil.Masters.Item(\"Info Bar on Background\")\n";
print "\$page.Name = \"background\"\n";
print "\$page.AutoSize = \$false\n";
print "\$page.Background = \$true\n";
print "\$page.Document.PrintLandscape = \$true\n";
print "\$page.document.PrintFitOnPages = \$true\n";
print "\$bg = \$page.Drop(\$backgroundborder, 5.5, 4.25) \n";
print "\$bginfobar = \$page.Drop(\$infobar, 8.0646, 0.95) \n";
print "\$page.CenterDrawing\n";
print "if (-Not (Test-VisioApplication) ) \n";
print "{\n";
print "    Connect-VisioApplication\n";
print "}\n";
print "if (-Not (Test-VisioDocument) )\n";
print "{\n";
print "    Set-VisioDocument -Name Drawing1\n";
print "}\n";
print "Set-VisioPageCell -PageWidth 11.0 -PageHeight 8.5\n";
for my $sitekey ( sort keys %$hash )
{
  print "\$d = New-VisioDirectedGraph\n";
  #my $nodecnt = 1;
  #my @custprops = ();
  # Print out all Nodes per Site
  for my $node ( sort keys %{$hash->{$sitekey}->{'nodes'}} )
  {
    my $node_label = $hash->{$sitekey}->{'nodes'}->{$node}->{'normal'};
    my $node_name = $hash->{$sitekey}->{'nodes'}->{$node}->{'clean'};
    my $node_stencil = "BASIC_U.VSS";
    my $node_shape = "Rectangle";
    my $node_stencil_my = "My_Network_Stencil_Pack.vss";
    my $node_shape_l2switch = "Cisco L2 Switch DG";
    my $node_shape_l3switch = "Cisco L3 Switch DG";
    my $node_shape_rtr = "Cisco Router DG";
    my $node_shape_fw = "Cisco ASA 5500 Series DG";
    my $node_shape_cloud = "Cloud";
    my $node_shape_rectangle = "Rectangle DG";
    my $nodename = "\$$node_name = \$d.AddShape(\"$node_name\",\"$node_label\", \"$node_stencil\", \"$node_shape\")\n";
    #my $nodename = "\$$node_name = \$d.AddShape(\"$node_name\",\"$node_label\", \"$node_stencil_my\", \"$node_shape_rectangle\")\n";
    $nodename = "\$$node_name = \$d.AddShape(\"$node_name\",\"$node_label\", \"$node_stencil_my\", \"$node_shape_cloud\")\n" if ( $node_name =~ /qmoe/i );
    $nodename = "\$$node_name = \$d.AddShape(\"$node_name\",\"$node_label\", \"$node_stencil_my\", \"$node_shape_l3switch\")\n" if ( $node_name =~ /3850/ );
    $nodename = "\$$node_name = \$d.AddShape(\"$node_name\",\"$node_label\", \"$node_stencil_my\", \"$node_shape_l3switch\")\n" if ( $node_name =~ /6500/ );
    $nodename = "\$$node_name = \$d.AddShape(\"$node_name\",\"$node_label\", \"$node_stencil_my\", \"$node_shape_l2switch\")\n" if ( $node_name =~ /as/ );
    $nodename = "\$$node_name = \$d.AddShape(\"$node_name\",\"$node_label\", \"$node_stencil_my\", \"$node_shape_rtr\")\n" if ( $node_name =~ /wr\d\d/i );
    $nodename = "\$$node_name = \$d.AddShape(\"$node_name\",\"$node_label\", \"$node_stencil_my\", \"$node_shape_rtr\")\n" if ( $node_name =~ /vrf_/i );
    $nodename = "\$$node_name = \$d.AddShape(\"$node_name\",\"$node_label\", \"$node_stencil_my\", \"$node_shape_rtr\")\n" if ( $node_name =~ /rt\d\d/i );
    $nodename = "\$$node_name = \$d.AddShape(\"$node_name\",\"$node_label\", \"$node_stencil_my\", \"$node_shape_fw\")\n" if ( $node_name =~ /fw\d\d/i );
    print $nodename;
  }

  for my $linknum ( sort keys %{$hash->{$sitekey}->{'links'}} )
  {
    # Print out all links per Site
    my $linknum_ = "C" . $linknum;
    my $from_ = $hash->{$sitekey}->{'links'}->{$linknum}->{'from'};
    my $to_ = $hash->{$sitekey}->{'links'}->{$linknum}->{'to'};
    my $label_ = $hash->{$sitekey}->{'links'}->{$linknum}->{'label'};
    my $linetype_ = $hash->{$sitekey}->{'links'}->{$linknum}->{'linetype'};
    print "\$d.AddConnection(\"$linknum_\",\$$from_,\$$to_,\"$linknum_\",\"$linetype_\")\n";
  }
  print "New-VisioDocument\n" if ( $newdocper );
  print "\$p = New-VisioPage -Name \"$sitekey\"  -Height 8.5 -Width 11\n" if ( ! $newdocper );
  print "\$d.Render(\$p,\$options)\n";
  print "\$shapes = Get-VisioShape *\n";
  print "\$rectids = \$shapes | where { \$_.NameU -like \"*rectangle*\"} | Select ID\n";
  print "if (\$rectids)\n";
  print "{\n";
  print "    Select-VisioShape \$rectids.ID\n";
  print "    Set-VisioShapeCell -Width 0.6049 -Height 0.475\n";
  print "    Set-VisioPageLayout -Orientation Landscape -BackgroundPage background\n";
  print "    Set-VisioPageCell -PageWidth 11.0 -PageHeight 8.5\n";
  print "}\n";
  print "Select-VisioShape None\n";

  print "Save-VisioDocument \"$save_location\\$sitekey.vsd\"\n" if ( $newdocper );
  print "\$od = Get-VisioDocument -ActiveDocument\n" if ( $newdocper );
  print "Close-VisioDocument -Documents \$od\n" if ( $newdocper );
}

Example of Generated PowerShell

Set-StrictMode -Version 2
$ErrorActionPreference = "Stop"
Import-Module Visio
$options = New-Object VisioAutomation.Models.DirectedGraph.MSAGLLayoutOptions
$d = New-VisioDirectedGraph
$app = New-Object -ComObject Visio.Application 
$app.visible = $true 
$docs = $app.Documents 
$doc = $docs.Add("DTLNET_U.vst") 
$pages = $app.ActiveDocument.Pages 
$page = $pages.Item(1)
# You would have to comment out the following three lines since you likely don't have them in a Stencil Pack like I do.
$stencil = $app.Documents.Add("My_Network_Stencil_Pack.vss")
$backgroundborder = $stencil.Masters.Item("Background Border")
$infobar = $stencil.Masters.Item("Info Bar on Background")
$page.Name = "background"
$page.AutoSize = $false
$page.Background = $true
$page.Document.PrintLandscape = $true
$page.document.PrintFitOnPages = $true
# You would have to comment out the following two lines since you likely don't have them in a Stencil Pack like I do.
$bg = $page.Drop($backgroundborder, 5.5, 4.25) 
$bginfobar = $page.Drop($infobar, 8.0646, 0.95) 
$page.CenterDrawing
if (-Not (Test-VisioApplication) ) 
{
    Connect-VisioApplication
}
if (-Not (Test-VisioDocument) )
{
    Set-VisioDocument -Name Drawing1
}
Set-VisioPageCell -PageWidth 11.0 -PageHeight 8.5
$d = New-VisioDirectedGraph
$ATTCLOUD = $d.AddShape("ATTCLOUD","ATTCLOUD", "BASIC_U.VSS", "Rectangle")
$DEV1 = $d.AddShape("DEV1","DEV1", "BASIC_U.VSS", "Rectangle")
$DEV2 = $d.AddShape("DEV2","DEV2", "BASIC_U.VSS", "Rectangle")
$DEV3 = $d.AddShape("DEV3","DEV3", "BASIC_U.VSS", "Rectangle")
$DEV4 = $d.AddShape("DEV4","DEV4", "BASIC_U.VSS", "Rectangle")
$DEV5 = $d.AddShape("DEV5","DEV5", "BASIC_U.VSS", "Rectangle")
$XOCLOUD = $d.AddShape("XOCLOUD","XOCLOUD", "BASIC_U.VSS", "Rectangle")
$d.AddConnection("C1",$ATTCLOUD,$DEV1,"C1","Straight")
$d.AddConnection("C2",$DEV1,$DEV2,"C2","Straight")
$d.AddConnection("C3",$DEV2,$DEV3,"C3","Straight")
$d.AddConnection("C4",$DEV2,$DEV5,"C4","Straight")
$d.AddConnection("C5",$DEV2,$DEV4,"C5","Straight")
$d.AddConnection("C6",$XOCLOUD,$DEV4,"C6","Straight")
$p = New-VisioPage -Name "Site_A"  -Height 8.5 -Width 11
$d.Render($p,$options)
$shapes = Get-VisioShape *
$rectids = $shapes | where { $_.NameU -like "*rectangle*"} | Select ID
if ($rectids)
{
    Select-VisioShape $rectids.ID
    Set-VisioShapeCell -Width 0.6049 -Height 0.475
    Set-VisioPageLayout -Orientation Landscape -BackgroundPage background
    Set-VisioPageCell -PageWidth 11.0 -PageHeight 8.5
}
Select-VisioShape None

Here is a picture of what it would draw.

Dummy_Net_Diagram

Advertisements

using Perl, create a CSV of data if it is in a list format

Filed under: perl — lancevermilion @ 11:40 am

I needed to convert a list of data from a list of data that was already setup in a key/value format. The problem was the same keys did not exist for each chunk of data that made up the list. For example if I have a sample list of what is below I would expect to get the following.

Sample List

start data chunk abc
  key1 value1
  key2 value2
start data chunk def
  key2 value2
start data chunk ghi
  key1 value1
start data chunk jkl
  key1 value1
  key2 value2

Desired Sample CSV output

start data chunk,abc,def,ghi,jkl
key1,value1,,value1,value1
key2,value2,value2,,value2

I wrote some Perl code to accomplish this. Not pretty and I am sure people will say it can be done better, faster, and with less lines. This works for my purposes. Enjoy the use of it if it helps you.

Here is the code

#!/usr/local/bin/perl
use strict;

my $filename = $ARGV[0];
my @arr = do {
    open my $fh, "<", $filename
        or die "could not open $filename: $!";
    <$fh>;
};

# Unique set of Keys
my $href_keys = {};
# List of Key1/Key2/Values in data provided
my $href_data = {};
# New list of Key/Values as an array
# If Key2 does not exist for Key1 it will be added as a blank values. This allows a proper CSV to be presented.
my $href_csv = {};

my $tmpkey = '';

for my $line (@arr)
{
  chomp($line);
  $line =~ s/\r//g; #Just in case you have CR
  $line =~ s/\n//g; #Just in case you have New Line / LF
  if ( $line =~ /^(start data chunk) (.*)/ )
  {
    $tmpkey = $2;
    $href_keys->{$tmpkey}->{$1} = $2;
    $href_data->{$1} = '';
  }
  if ( $line =~ /^\s+(.*) (value\d.*)/ )
  {
    $href_keys->{$tmpkey}->{$1} = $2;
    $href_data->{$1} = '';
  }
}


for my $href_data_k1 ( sort keys %$href_data )
{
  my $newkey = $href_data_k1;
  $newkey =~ s/(start data chunk)/_$1/g;
  for my $href_keys_k1 ( sort keys %$href_keys )
  {
    my $val = '';
    $val = $href_keys->{$href_keys_k1}->{$href_data_k1} if ( $href_keys->{$href_keys_k1}->{$href_data_k1} );
    push(@{$href_csv->{"$newkey"}}, $val);
  }
}

for my $href_csv_k1 ( sort keys %$href_csv )
{
  my $href_csv_newk1 = $href_csv_k1;
  $href_csv_newk1 =~ s/_//g;
  print "$href_csv_newk1,", join(",", @{$href_csv->{$href_csv_k1}}), "\n";
}

September 12, 2012

iotop2csv – One way to graph the batch output of iotop

Filed under: linux, perl — lancevermilion @ 5:38 pm

I had a need to know what was writing disk, how much it was writing, and how often it was writing. iotop does a pretty good job of giving you a top like view of disk I/O activity. I decided I wanted to graph this to hopefully make it easier for me to see how heavy writing to the disk might correlate to sluggish CLI response.

The output is in CSV so you can drop it in Excel (if you so wish) and create a simple PivotChart with it. You can do so by selecting all data from the csv (after it is imported/converted to rows/columns using data import or text to columns) then choose insert pivotchart.

Choose fields timestamp, tid, read, write and cmd.
Timestamp goes in AxisFields Categories.
Values, cmd, and tid go in Legend Fields
Sum of read and Sum of write goes in Values.

I never quite got to the smoking gun (just yet) but I was able to see what applications were busy little apps writing to the disk.

I do no warranty this code in any way. Use it as you wish. If you find it useful then please write back saying so. If you find it incorrect or improve/fix it please write back on that as well.

Here is the code

#/usr/bin/perl -w
use strict;
# Enabling strict with strict refs breaks the summary output (when ctrl+c is pressed)
# because of the error Can't use string ("") as a HASH ref while "strict refs" in use.
no strict 'refs';

#Initialize an empty hashref
my $hash_ref = {};

#Catch CTRL+C and then call printsummary subroutine
$SIG{'INT'} = \&printsummary;

#Pipe the output from iotop to a file handle
open fh, "iotop -bt|" or die $!;

# Build the initial Total Value key/values
$hash_ref->{'tsample'} = 0;
$hash_ref->{'tread'} = 0;
$hash_ref->{'twrite'} = 0;

# Divider of columns
my $div = ",";

#Print the header
print "#" x 50 . "\n";
print "Output from iotop -bt\n";
print "#" x 50 . "\n";
print "timestamp" . $div . "tid" . $div . "user" . $div . "read" . $div . "total read each tid" . $div . "write" . $div . "total write each tid" . $div . "cmd" . $div . "sample number" . $div . "Percent of Total Read" . $div . "Percent of Total Write" . "\n";

#While data from iotop process it. Since we get lots of 0.00 read and write values
#don't waste time storing/printing values that are useless. Only store lines that
#have a value to read/write.
while(<fh>) {
  my ($ts,$tid,$prio,$user,$read,undef,$write,undef,undef,undef,undef,undef,$cmd) = split(/\s+/, "$_");
  chomp($cmd);

  #Make sure we fill each tid with dummy values if the tid is a real valid number
  if ( !$hash_ref->{$tid} && ( $tid =~ m/^-?\d+$/ || $tid =~ m/^-?\d+[\/|\.]\d+$/ ) )
  {
    $hash_ref->{$tid}->{'user'} = 'null';
    $hash_ref->{$tid}->{'read'} = 0;
    $hash_ref->{$tid}->{'write'} = 0;
    $hash_ref->{$tid}->{'cmd'} = 'null';
    $hash_ref->{$tid}->{'samples'} = 0;
  }

  #Continue if we have already created a key for the tid and the tid is a real valid number
  if ( $hash_ref->{$tid} && ( $tid =~ m/^-?\d+$/ || $tid =~ m/^-?\d+[\/|\.]\d+$/ ) )
  {
    $hash_ref->{$tid}->{'user'} = $user;
    $hash_ref->{$tid}->{'read'} += $read if $read =~ m/^-?\d+$/ || $read =~ m/^-?\d+[\/|\.]\d+$/;
    $hash_ref->{$tid}->{'write'} += $write if $write =~ m/^-?\d+$/ || $write =~ m/^-?\d+[\/|\.]\d+$/;
    $hash_ref->{$tid}->{'cmd'} = $cmd;
    $hash_ref->{$tid}->{'samples'}++;

    # create totals key/value pair
    $hash_ref->{'tsample'}++;
    $hash_ref->{'tread'} += $read if $read =~ m/^-?\d+$/ || $read =~ m/^-?\d+[\/|\.]\d+$/;
    $hash_ref->{'twrite'} += $write if $write =~ m/^-?\d+$/ || $write =~ m/^-?\d+[\/|\.]\d+$/;

    # Print values of the read/write as they happen
    if ( ( $read =~ m/^-?\d+$/ || $read =~ m/^-?\d+[\/|\.]\d+$/ || $write =~ m/^-?\d+$/ || $write =~ m/^-?\d+[\/|\.]\d+$/ ) && ( $read > 0 || $write > 0 ) )
    {
      my $ptr = 0;
      my $ptw = 0;
      $ptr = ( $hash_ref->{$tid}->{'read'} / $hash_ref->{'tread'} ) * 100 if ( $read > 0 ) && ( $hash_ref->{'tread'} > 0 );
      $ptw = ( $hash_ref->{$tid}->{'write'} / $hash_ref->{'twrite'} ) * 100 if ( $write > 0 ) && ( $hash_ref->{'twrite'} > 0 );

      print $ts . "$div";
      print $tid . "$div";
      print $hash_ref->{$tid}->{'user'} . "$div";
      print $read . "$div";
      print "$hash_ref->{$tid}->{'read'}" . "$div";
      print $write . "$div";
      print "$hash_ref->{$tid}->{'write'}" . "$div";
      print $hash_ref->{$tid}->{'cmd'} . "$div";
      print $hash_ref->{$tid}->{'samples'} . "$div";
      print sprintf("%.2f", $ptr) . "$div";
      print sprintf("%.2f", $ptw) . "\n";
    }
  }
}

# Close the filehandle when we are done.
close(fh);

#Sub routine to print out a summary of disk I/O.
sub printsummary {
print "#" x 50 . "\n";
print "Caught Ctrl+C. Printing Disk I/O summary.\n";
print "#" x 50 . "\n";
print "Total Bytes Read:  $hash_ref->{'tread'}\n";
print "Total Bytes Write: $hash_ref->{'twrite'}\n";
print "#" x 50 . "\n";

for my $id ( keys %$hash_ref )
  {
  if ( $hash_ref->{$id}->{'read'} > 0 || $hash_ref->{$id}->{'write'} > 0 )
    {
      my $psptr = 0;
      my $psptw = 0;
      $psptr = ( $hash_ref->{$id}->{'read'} / $hash_ref->{'tread'} ) * 100 if ( $hash_ref->{'tread'} > 0 );
      $psptw = ( $hash_ref->{$id}->{'write'} / $hash_ref->{'twrite'} ) * 100 if ( $hash_ref->{'twrite'} > 0 );

      print "$id" . " ";
      print $hash_ref->{$id}->{'user'} . " ";
      print $hash_ref->{$id}->{'read'} . " ";
      print $hash_ref->{$id}->{'write'} . " ";
      print $hash_ref->{$id}->{'cmd'} . " ";
      print $hash_ref->{$id}->{'samples'} . " ";
      print sprintf("%.2f", $psptr) . " ";
      print sprintf("%.2f", $psptw);
      print "\n"
    }
  }
  print "#" x 50 . "\n";
}

Here is the csv output

sudo perl iotop2csv.pl 
##################################################
Output from iotop -bt
##################################################
timestamp,tid,user,read,total read each tid,write,total write each tid,cmd,sample number,Percent of Total Read,Percent of Total Write
16:27:00,6003,mysql,0.00,0,318.04,318.04,mysqld,2,0.00,100.00
16:27:00,4959,root,0.00,0,3.74,3.74,nailslogd,2,0.00,1.16
16:27:00,5992,mysql,0.00,0,3.74,3.74,mysqld,2,0.00,1.15
16:27:00,5994,mysql,0.00,0,314.30,314.3,mysqld,2,0.00,49.12
16:27:01,511,root,0.00,0,101.83,101.83,[kjournald],3,0.00,13.73
16:27:01,6557,mysql,0.00,0,3.77,3.77,mysqld,3,0.00,0.51
16:27:02,6530,mysql,0.00,0,7.55,7.55,mysqld,4,0.00,1.00
16:27:03,6009,mysql,0.00,0,7.54,7.54,mysqld,5,0.00,0.99
16:27:03,25220,mysql,3.77,3.77,11.31,11.31,mysqld,5,100.00,1.47
16:27:04,6534,mysql,3.77,3.77,97.94,97.94,mysqld,6,50.00,11.26
16:27:04,1864,root,0.00,0,199.65,199.65,[kjournald],6,0.00,18.67

Here is an example graph via Excel

May 24, 2012

How to find files with permissions that are more permissive than 0640

Filed under: linux, perl — lancevermilion @ 6:40 pm

I always seem to need a way to dig through directories and find permissions that are more or less restrictive. I couldn’t seem to figure out how to do it with “find -perm” so I decided to write a perl script to utilize a simple find piped to ls -la.

Enjoy.

As a perl script

#!/usr/bin/perl
my @array = `find $ARGV[0] -type f \\( ! -iname "." -or ! -iname ".." \\) | xargs ls -la`;
foreach(@array)
{
  my @line = split(/\s+/, $_);
  my $perms = $line[0];
  my $file = $line[8];
  chomp($file);
  my @perm = split(//, $perms);
  my $match = 0;
  $match++ if ( $perm[0] ne '-' );
  $match ++ if ( $perm[3] ne '-' );
  $match++ if ( "$perm[5]$perm[6]" ne '--' );
  $match++ if ( "$perm[7]$perm[8]$perm[9]" ne '---' );
  $permstr =  join '', @perm;
  print "$file ($permstr More Permissive than -rw-r-----)\n" if ( $match > 0 );
}

A one liner

perl -e 'my @array = `find /var/log/ -type f \\( ! -iname "." -or ! -iname ".." \\) | xargs ls -la`;foreach(@array){my @line = split(/\s+/, $_);my $perms = $line[0];my $file = $line[8];chomp($file);my @perm = split(//, $perms);my $match = 0;$match++ if ( $perm[0] ne "-" );$match ++ if ( $perm[3] ne "-" );$match++ if ( "$perm[5]$perm[6]" ne "--" );$match++ if ( "$perm[7]$perm[8]$perm[9]" ne "---" );$permstr =  join "", @perm;print "$file ($permstr More permissive than -rw-r-----)\n" if ( $match > 0 );}'

Here is an example output

/var/log/sa/sa16 (-rw-r--r-- More Permissive than -rw-r-----)
/var/log/sa/sa17 (-rw-r--r-- More Permissive than -rw-r-----)
/var/log/sa/sa18 (-rw-r--r-- More Permissive than -rw-r-----)
/var/log/sa/sa19 (-rw-r--r-- More Permissive than -rw-r-----)
/var/log/sa/sa20 (-rw-r--r-- More Permissive than -rw-r-----)
/var/log/sa/sa21 (-rw-r--r-- More Permissive than -rw-r-----)
/var/log/sa/sa22 (-rw-r--r-- More Permissive than -rw-r-----)
/var/log/sa/sa23 (-rw-r--r-- More Permissive than -rw-r-----)
/var/log/sa/sa24 (-rw-r--r-- More Permissive than -rw-r-----)
/var/log/sa/sar15 (-rw-r--r-- More Permissive than -rw-r-----)
/var/log/sa/sar16 (-rw-r--r-- More Permissive than -rw-r-----)
/var/log/sa/sar17 (-rw-r--r-- More Permissive than -rw-r-----)
/var/log/sa/sar18 (-rw-r--r-- More Permissive than -rw-r-----)
/var/log/sa/sar19 (-rw-r--r-- More Permissive than -rw-r-----)
/var/log/sa/sar20 (-rw-r--r-- More Permissive than -rw-r-----)
/var/log/sa/sar21 (-rw-r--r-- More Permissive than -rw-r-----)
/var/log/sa/sar22 (-rw-r--r-- More Permissive than -rw-r-----)
/var/log/sa/sar23 (-rw-r--r-- More Permissive than -rw-r-----)
/var/log/wtmp (-rw-rw-r-- More Permissive than -rw-r-----)

May 3, 2012

pt-summary from Percona Toolkit

Filed under: Freemind, linux, Percona Toolkit, perl — lancevermilion @ 4:04 pm

Here is the bash script ‘pt-summary’ from the Percona Toolkit. I have written a perl script (pt2mm.pl) that slurps in a lot of this data (and other info) and generates xml to be used with Freemind.

#!/bin/sh

# This program is part of Percona Toolkit: http://www.percona.com/software/
# See "COPYRIGHT, LICENSE, AND WARRANTY" at the end of this file for legal
# notices and disclaimers.

set -u

# ########################################################################
# Globals, settings, helper functions
# ########################################################################
TOOL="pt-summary"
POSIXLY_CORRECT=1
export POSIXLY_CORRECT

# ###########################################################################
# log_warn_die package
# This package is a copy without comments from the original.  The original
# with comments and its test file can be found in the Bazaar repository at,
#   lib/bash/log_warn_die.sh
#   t/lib/bash/log_warn_die.sh
# See https://launchpad.net/percona-toolkit for more information.
# ###########################################################################


set -u

PTFUNCNAME=""
PTDEBUG="${PTDEBUG:-""}"
EXIT_STATUS=0

log() {
   TS=$(date +%F-%T | tr :- _);
   echo "$TS $*"
}

warn() {
   log "$*" >&2
   EXIT_STATUS=1
}

die() {
   warn "$*"
   exit 1
}

_d () {
   [ "$PTDEBUG" ] && echo "# $PTFUNCNAME: $(log "$*")" >&2
}

# ###########################################################################
# End log_warn_die package
# ###########################################################################

# ###########################################################################
# parse_options package
# This package is a copy without comments from the original.  The original
# with comments and its test file can be found in the Bazaar repository at,
#   lib/bash/parse_options.sh
#   t/lib/bash/parse_options.sh
# See https://launchpad.net/percona-toolkit for more information.
# ###########################################################################





set -u

ARGV=""           # Non-option args (probably input files)
EXT_ARGV=""       # Everything after -- (args for an external command)
HAVE_EXT_ARGV=""  # Got --, everything else is put into EXT_ARGV
OPT_ERRS=0        # How many command line option errors
OPT_VERSION=""    # If --version was specified
OPT_HELP=""       # If --help was specified
PO_DIR=""         # Directory with program option spec files

usage() {
   local file="$1"

   local usage=$(grep '^Usage: ' "$file")
   echo $usage
   echo
   echo "For more information, 'man $TOOL' or 'perldoc $file'."
}

usage_or_errors() {
   local file="$1"

   if [ "$OPT_VERSION" ]; then
      local version=$(grep '^pt-[^ ]\+ [0-9]' "$file")
      echo "$version"
      return 1
   fi

   if [ "$OPT_HELP" ]; then
      usage "$file"
      echo
      echo "Command line options:"
      echo
      perl -e '
         use strict;
         use warnings FATAL => qw(all);
         my $lcol = 20;         # Allow this much space for option names.
         my $rcol = 80 - $lcol; # The terminal is assumed to be 80 chars wide.
         my $name;
         while ( <> ) {
            my $line = $_;
            chomp $line;
            if ( $line =~ s/^long:/  --/ ) {
               $name = $line;
            }
            elsif ( $line =~ s/^desc:// ) {
               $line =~ s/ +$//mg;
               my @lines = grep { $_      }
                           $line =~ m/(.{0,$rcol})(?:\s+|\Z)/g;
               if ( length($name) >= $lcol ) {
                  print $name, "\n", (q{ } x $lcol);
               }
               else {
                  printf "%-${lcol}s", $name;
               }
               print join("\n" . (q{ } x $lcol), @lines);
               print "\n";
            }
         }
      ' "$PO_DIR"/*
      echo
      echo "Options and values after processing arguments:"
      echo
      for opt in $(ls "$PO_DIR"); do
         local varname="OPT_$(echo "$opt" | tr a-z- A-Z_)"
         local varvalue="${!varname}"
         printf -- "  --%-30s %s" "$opt" "${varvalue:-(No value)}"
         echo
      done
      return 1
   fi

   if [ $OPT_ERRS -gt 0 ]; then
      echo
      usage "$file"
      return 1
   fi

   return 0
}

option_error() {
   local err="$1"
   OPT_ERRS=$(($OPT_ERRS + 1))
   echo "$err" >&2
}

parse_options() {
   local file="$1"
   shift

   ARGV=""
   EXT_ARGV=""
   HAVE_EXT_ARGV=""
   OPT_ERRS=0
   OPT_VERSION=""
   OPT_HELP=""
   PO_DIR="$TMPDIR/po"

   if [ ! -d "$PO_DIR" ]; then
      mkdir "$PO_DIR"
      if [ $? -ne 0 ]; then
         echo "Cannot mkdir $PO_DIR" >&2
         exit 1
      fi
   fi

   rm -rf "$PO_DIR"/*
   if [ $? -ne 0 ]; then
      echo "Cannot rm -rf $PO_DIR/*" >&2
      exit 1
   fi

   _parse_pod "$file"  # Parse POD into program option (po) spec files
   _eval_po            # Eval po into existence with default values

   if [ $# -ge 2 ] &&  [ "$1" = "--config" ]; then
      shift  # --config
      local user_config_files="$1"
      shift  # that ^
      local IFS=","
      for user_config_file in $user_config_files; do
         _parse_config_files "$user_config_file"
      done
   else
      _parse_config_files "/etc/percona-toolkit/percona-toolkit.conf" "/etc/percona-toolkit/$TOOL.conf" "$HOME/.percona-toolkit.conf" "$HOME/.$TOOL.conf"
   fi

   _parse_command_line "$@"
}

_parse_pod() {
   local file="$1"

   cat "$file" | PO_DIR="$PO_DIR" perl -ne '
      BEGIN { $/ = ""; }
      next unless $_ =~ m/^=head1 OPTIONS/;
      while ( defined(my $para = <>) ) {
         last if $para =~ m/^=head1/;
         chomp;
         if ( $para =~ m/^=item --(\S+)/ ) {
            my $opt  = $1;
            my $file = "$ENV{PO_DIR}/$opt";
            open my $opt_fh, ">", $file or die "Cannot open $file: $!";
            print $opt_fh "long:$opt\n";
            $para = <>;
            chomp;
            if ( $para =~ m/^[a-z ]+:/ ) {
               map {
                  chomp;
                  my ($attrib, $val) = split(/: /, $_);
                  print $opt_fh "$attrib:$val\n";
               } split(/; /, $para);
               $para = <>;
               chomp;
            }
            my ($desc) = $para =~ m/^([^?.]+)/;
            print $opt_fh "desc:$desc.\n";
            close $opt_fh;
         }
      }
      last;
   '
}

_eval_po() {
   local IFS=":"
   for opt_spec in "$PO_DIR"/*; do
      local opt=""
      local default_val=""
      local neg=0
      local size=0
      while read key val; do
         case "$key" in
            long)
               opt=$(echo $val | sed 's/-/_/g' | tr [:lower:] [:upper:])
               ;;
            default)
               default_val="$val"
               ;;
            "short form")
               ;;
            type)
               [ "$val" = "size" ] && size=1
               ;;
            desc)
               ;;
            negatable)
               if [ "$val" = "yes" ]; then
                  neg=1
               fi
               ;;
            *)
               echo "Invalid attribute in $opt_spec: $line" >&2
               exit 1
         esac 
      done < "$opt_spec"

      if [ -z "$opt" ]; then
         echo "No long attribute in option spec $opt_spec" >&2
         exit 1
      fi

      if [ $neg -eq 1 ]; then
         if [ -z "$default_val" ] || [ "$default_val" != "yes" ]; then
            echo "Option $opt_spec is negatable but not default: yes" >&2
            exit 1
         fi
      fi

      if [ $size -eq 1 -a -n "$default_val" ]; then
         default_val=$(size_to_bytes $default_val)
      fi

      eval "OPT_${opt}"="$default_val"
   done
}

_parse_config_files() {

   for config_file in "$@"; do
      test -f "$config_file" || continue

      while read config_opt; do

         echo "$config_opt" | grep '^[ ]*[^#]' >/dev/null 2>&1 || continue

         config_opt="$(echo "$config_opt" | sed -e 's/^ *//g' -e 's/ *$//g' -e 's/[ ]*=[ ]*/=/' -e 's/[ ]*#.*$//')"

         [ "$config_opt" = "" ] && continue

         if ! [ "$HAVE_EXT_ARGV" ]; then
            config_opt="--$config_opt"
         fi

         _parse_command_line "$config_opt"

      done < "$config_file"

      HAVE_EXT_ARGV=""  # reset for each file

   done
}

_parse_command_line() {
   local opt=""
   local val=""
   local next_opt_is_val=""
   local opt_is_ok=""
   local opt_is_negated=""
   local real_opt=""
   local required_arg=""
   local spec=""

   for opt in "$@"; do
      if [ "$opt" = "--" -o "$opt" = "----" ]; then
         HAVE_EXT_ARGV=1
         continue
      fi
      if [ "$HAVE_EXT_ARGV" ]; then
         if [ "$EXT_ARGV" ]; then
            EXT_ARGV="$EXT_ARGV $opt"
         else
            EXT_ARGV="$opt"
         fi
         continue
      fi

      if [ "$next_opt_is_val" ]; then
         next_opt_is_val=""
         if [ $# -eq 0 ] || [ $(expr "$opt" : "-") -eq 1 ]; then
            option_error "$real_opt requires a $required_arg argument"
            continue
         fi
         val="$opt"
         opt_is_ok=1
      else
         if [ $(expr "$opt" : "-") -eq 0 ]; then
            if [ -z "$ARGV" ]; then
               ARGV="$opt"
            else
               ARGV="$ARGV $opt"
            fi
            continue
         fi

         real_opt="$opt"

         if $(echo $opt | grep '^--no-' >/dev/null); then
            opt_is_negated=1
            opt=$(echo $opt | sed 's/^--no-//')
         else
            opt_is_negated=""
            opt=$(echo $opt | sed 's/^-*//')
         fi

         if $(echo $opt | grep '^[a-z-][a-z-]*=' >/dev/null 2>&1); then
            val="$(echo $opt | awk -F= '{print $2}')"
            opt="$(echo $opt | awk -F= '{print $1}')"
         fi

         if [ -f "$TMPDIR/po/$opt" ]; then
            spec="$TMPDIR/po/$opt"
         else
            spec=$(grep "^short form:-$opt\$" "$TMPDIR"/po/* | cut -d ':' -f 1)
            if [ -z "$spec"  ]; then
               option_error "Unknown option: $real_opt"
               continue
            fi
         fi

         required_arg=$(cat "$spec" | awk -F: '/^type:/{print $2}')
         if [ "$required_arg" ]; then
            if [ "$val" ]; then
               opt_is_ok=1
            else
               next_opt_is_val=1
            fi
         else
            if [ "$val" ]; then
               option_error "Option $real_opt does not take a value"
               continue
            fi 
            if [ "$opt_is_negated" ]; then
               val=""
            else
               val="yes"
            fi
            opt_is_ok=1
         fi
      fi

      if [ "$opt_is_ok" ]; then
         opt=$(cat "$spec" | grep '^long:' | cut -d':' -f2 | sed 's/-/_/g' | tr [:lower:] [:upper:])

         if grep "^type:size" "$spec" >/dev/null; then
            val=$(size_to_bytes $val)
         fi

         eval "OPT_$opt"="'$val'"

         opt=""
         val=""
         next_opt_is_val=""
         opt_is_ok=""
         opt_is_negated=""
         real_opt=""
         required_arg=""
         spec=""
      fi
   done
}

size_to_bytes() {
   local size="$1"
   echo $size | perl -ne '%f=(B=>1, K=>1_024, M=>1_048_576, G=>1_073_741_824, T=>1_099_511_627_776); m/^(\d+)([kMGT])?/i; print $1 * $f{uc($2 || "B")};'
}

# ###########################################################################
# End parse_options package
# ###########################################################################

# ###########################################################################
# tmpdir package
# This package is a copy without comments from the original.  The original
# with comments and its test file can be found in the Bazaar repository at,
#   lib/bash/tmpdir.sh
#   t/lib/bash/tmpdir.sh
# See https://launchpad.net/percona-toolkit for more information.
# ###########################################################################


set -u

TMPDIR=""

mk_tmpdir() {
   local dir="${1:-""}"

   if [ -n "$dir" ]; then
      if [ ! -d "$dir" ]; then
         mkdir "$dir" || die "Cannot make tmpdir $dir"
      fi
      TMPDIR="$dir"
   else
      local tool="${0##*/}"
      local pid="$$"
      TMPDIR=`mktemp -d /tmp/${tool}.${pid}.XXXXX` \
         || die "Cannot make secure tmpdir"
   fi
}

rm_tmpdir() {
   if [ -n "$TMPDIR" ] && [ -d "$TMPDIR" ]; then
      rm -rf "$TMPDIR"
   fi
   TMPDIR=""
}

# ###########################################################################
# End tmpdir package
# ###########################################################################

# ###########################################################################
# alt_cmds package
# This package is a copy without comments from the original.  The original
# with comments and its test file can be found in the Bazaar repository at,
#   lib/bash/alt_cmds.sh
#   t/lib/bash/alt_cmds.sh
# See https://launchpad.net/percona-toolkit for more information.
# ###########################################################################


set -u

_seq() {
   local i="$1"
   awk "BEGIN { for(i=1; i<=$i; i++) print i; }"
}

_pidof() {
   local cmd="$1"
   if ! pidof "$cmd" 2>/dev/null; then
      ps -eo pid,ucomm | awk -v comm="$cmd" '$2 == comm { print $1 }'
   fi
}

_lsof() {
   local pid="$1"
   if ! lsof -p $pid 2>/dev/null; then
      /bin/ls -l /proc/$pid/fd 2>/dev/null
   fi
}



_which() {
   if [ -x /usr/bin/which ]; then
      /usr/bin/which "$1" 2>/dev/null | awk '{print $1}'
   elif which which 1>/dev/null 2>&1; then
      which "$1" 2>/dev/null | awk '{print $1}'
   else
      echo "$1"
   fi
}

# ###########################################################################
# End alt_cmds package
# ###########################################################################

# ###########################################################################
# summary_common package
# This package is a copy without comments from the original.  The original
# with comments and its test file can be found in the Bazaar repository at,
#   lib/bash/summary_common.sh
#   t/lib/bash/summary_common.sh
# See https://launchpad.net/percona-toolkit for more information.
# ###########################################################################


set -u

CMD_FILE="$( _which file 2>/dev/null )"
CMD_NM="$( _which nm 2>/dev/null )"
CMD_OBJDUMP="$( _which objdump 2>/dev/null )"

get_nice_of_pid () {
   local pid="$1"
   local niceness="$(ps -p $pid -o nice | awk '$1 !~ /[^0-9]/ {print $1; exit}')"

   if [ -n "${niceness}" ]; then
      echo $niceness
   else
      local tmpfile="$TMPDIR/nice_through_c.tmp.c"
      _d "Getting the niceness from ps failed, somehow. We are about to try this:"
      cat <<EOC > "$tmpfile"

int main(void) {
   int priority = getpriority(PRIO_PROCESS, $pid);
   if ( priority == -1 && errno == ESRCH ) {
      return 1;
   }
   else {
      printf("%d\\n", priority);
      return 0;
   }
}

EOC
      local c_comp=$(_which gcc)
      if [ -z "${c_comp}" ]; then
         c_comp=$(_which cc)
      fi
      _d "$tmpfile: $( cat "$tmpfile" )"
      _d "$c_comp -xc \"$tmpfile\" -o \"$tmpfile\" && eval \"$tmpfile\""
      $c_comp -xc "$tmpfile" -o "$tmpfile" 2>/dev/null && eval "$tmpfile" 2>/dev/null
      if [ $? -ne 0 ]; then
         echo "?"
         _d "Failed to get a niceness value for $pid"
      fi
   fi
}

get_oom_of_pid () {
   local pid="$1"
   local oom_adj=""

   if [ -n "${pid}" -a -e /proc/cpuinfo ]; then
      if [ -s "/proc/$pid/oom_score_adj" ]; then
         oom_adj=$(cat "/proc/$pid/oom_score_adj" 2>/dev/null)
         _d "For $pid, the oom value is $oom_adj, retreived from oom_score_adj"
      else
         oom_adj=$(cat "/proc/$pid/oom_adj" 2>/dev/null)
         _d "For $pid, the oom value is $oom_adj, retreived from oom_adj"
      fi
   fi

   if [ -n "${oom_adj}" ]; then
      echo "${oom_adj}"
   else
      echo "?"
      _d "Can't find the oom value for $pid"
   fi
}

has_symbols () {
   local executable="$(_which "$1")"
   local has_symbols=""

   if    [ "${CMD_FILE}" ] \
      && [ "$($CMD_FILE "${executable}" | grep 'not stripped' )" ]; then
      has_symbols=1
   elif    [ "${CMD_NM}" ] \
        || [ "${CMD_OBJDMP}" ]; then
      if    [ "${CMD_NM}" ] \
         && [ !"$("${CMD_NM}" -- "${executable}" 2>&1 | grep 'File format not recognized' )" ]; then
         if [ -z "$( $CMD_NM -- "${executable}" 2>&1 | grep ': no symbols' )" ]; then
            has_symbols=1
         fi
      elif [ -z "$("${CMD_OBJDUMP}" -t -- "${executable}" | grep '^no symbols$' )" ]; then
         has_symbols=1
      fi
   fi

   if [ "${has_symbols}" ]; then
      echo "Yes"
   else
      echo "No"
   fi
}

setup_data_dir () {
   local existing_dir="$1"
   local data_dir=""
   if [ -z "$existing_dir" ]; then
      mkdir "$TMPDIR/data" || die "Cannot mkdir $TMPDIR/data"
      data_dir="$TMPDIR/data"
   else
      if [ ! -d "$existing_dir" ]; then
         mkdir "$existing_dir" || die "Cannot mkdir $existing_dir"
      elif [ "$( ls -A "$existing_dir" )" ]; then
         die "--save-samples directory isn't empty, halting."
      fi
      touch "$existing_dir/test" || die "Cannot write to $existing_dir"
      rm "$existing_dir/test"    || die "Cannot rm $existing_dir/test"
      data_dir="$existing_dir"
   fi
   echo "$data_dir"
}

get_var () {
   local varname="$1"
   local file="$2"
   awk -v pattern="${varname}" '$1 == pattern { if (length($2)) { len = length($1); print substr($0, len+index(substr($0, len+1), $2)) } }' "${file}"
}

# ###########################################################################
# End summary_common package
# ###########################################################################

# ###########################################################################
# report_formatting package
# This package is a copy without comments from the original.  The original
# with comments and its test file can be found in the Bazaar repository at,
#   lib/bash/report_formatting.sh
#   t/lib/bash/report_formatting.sh
# See https://launchpad.net/percona-toolkit for more information.
# ###########################################################################


set -u

POSIXLY_CORRECT=1
export POSIXLY_CORRECT

fuzzy_formula='
   rounded = 0;
   if (fuzzy_var <= 10 ) {
      rounded   = 1;
   }
   factor = 1;
   while ( rounded == 0 ) {
      if ( fuzzy_var <= 50 * factor ) {
         fuzzy_var = sprintf("%.0f", fuzzy_var / (5 * factor)) * 5 * factor;
         rounded   = 1;
      }
      else if ( fuzzy_var <= 100  * factor) {
         fuzzy_var = sprintf("%.0f", fuzzy_var / (10 * factor)) * 10 * factor;
         rounded   = 1;
      }
      else if ( fuzzy_var <= 250  * factor) {
         fuzzy_var = sprintf("%.0f", fuzzy_var / (25 * factor)) * 25 * factor;
         rounded   = 1;
      }
      factor = factor * 10;
   }'

fuzz () {
   awk -v fuzzy_var="$1" "BEGIN { ${fuzzy_formula} print fuzzy_var;}"
}

fuzzy_pct () {
   local pct="$(awk -v one="$1" -v two="$2" 'BEGIN{ if (two > 0) { printf "%d", one/two*100; } else {print 0} }')";
   echo "$(fuzz "${pct}")%"
}

section () {
   local str="$1"
   awk -v var="${str} _" 'BEGIN {
      line = sprintf("# %-60s", var);
      i = index(line, "_");
      x = substr(line, i);
      gsub(/[_ \t]/, "#", x);
      printf("%s%s\n", substr(line, 1, i-1), x);
   }'
}

NAME_VAL_LEN=12
name_val () {
   printf "%+*s | %s\n" "${NAME_VAL_LEN}" "$1" "$2"
}

shorten() {
   local num="$1"
   local prec="${2:-2}"
   local div="${3:-1024}"

   echo "$num" | awk -v prec="$prec" -v div="$div" '
   {
      size = 4;
      val  = $1;

      unit = val >= 1099511627776 ? "T" : val >= 1073741824 ? "G" : val >= 1048576 ? "M" : val >= 1024 ? "k" : "";

      while ( int(val) && !(val % 1024) ) {
         val /= 1024;
      }

      while ( val > 1000 ) {
         val /= div;
      }

      printf "%.*f%s", prec, val, unit;
   }
   '
}

group_concat () {
   sed -e '{H; $!d;}' -e 'x' -e 's/\n[[:space:]]*\([[:digit:]]*\)[[:space:]]*/, \1x/g' -e 's/[[:space:]][[:space:]]*/ /g' -e 's/, //' "${1}"
}

# ###########################################################################
# End report_formatting package
# ###########################################################################

# ###########################################################################
# collect_system_info package
# This package is a copy without comments from the original.  The original
# with comments and its test file can be found in the Bazaar repository at,
#   lib/bash/collect_system_info.sh
#   t/lib/bash/collect_system_info.sh
# See https://launchpad.net/percona-toolkit for more information.
# ###########################################################################



set -u

setup_commands () {
   CMD_SYSCTL="$(_which sysctl 2>/dev/null )"
   CMD_DMIDECODE="$(_which dmidecode 2>/dev/null )"
   CMD_ZONENAME="$(_which zonename 2>/dev/null )"
   CMD_DMESG="$(_which dmesg 2>/dev/null )"
   CMD_FILE="$(_which file 2>/dev/null )"
   CMD_LSPCI="$(_which lspci 2>/dev/null )"
   CMD_PRTDIAG="$(_which prtdiag 2>/dev/null )"
   CMD_SMBIOS="$(_which smbios 2>/dev/null )"
   CMD_GETENFORCE="$(_which getenforce 2>/dev/null )"
   CMD_PRTCONF="$(_which prtconf 2>/dev/null )"
   CMD_LVS="$(_which lvs 2>/dev/null)"
   CMD_VGS="$(_which vgs 2>/dev/null)"
   CMD_PRSTAT="$(_which prstat 2>/dev/null)"
   CMD_ISAINFO="$(_which isainfo 2>/dev/null)"
   CMD_TOP="$(_which top 2>/dev/null)"
   CMD_ARCCONF="$( _which arcconf 2>/dev/null )"
   CMD_HPACUCLI="$( _which hpacucli 2>/dev/null )"
   CMD_MEGACLI64="$( _which MegaCli64 2>/dev/null )"
   CMD_VMSTAT="$(_which vmstat 2>/dev/null)"
   CMD_IP="$( _which ip 2>/dev/null )"
   CMD_NETSTAT="$( _which netstat 2>/dev/null )"
   CMD_PSRINFO="$( _which psrinfo 2>/dev/null )"
   CMD_SWAPCTL="$( _which swapctl 2>/dev/null )"
   CMD_LSB_RELEASE="$( _which lsb_release 2>/dev/null )"
   CMD_ETHTOOL="$( _which ethtool 2>/dev/null )"
   CMD_GETCONF="$( _which getconf 2>/dev/null )"
}

collect_system_data () { local PTFUNCNAME=collect_system_data;
   local data_dir="$1"

   if [ -r /var/log/dmesg -a -s /var/log/dmesg ]; then
      cat "/var/log/dmesg" > "$data_dir/dmesg_file"
   fi

   $CMD_SYSCTL -a > "$data_dir/sysctl" 2>/dev/null

   if [ "${CMD_LSPCI}" ]; then
      $CMD_LSPCI > "$data_dir/lspci_file" 2>/dev/null
   fi

   local platform="$(uname -s)"
   echo "platform    $platform"   >> "$data_dir/summary"
   echo "hostname    $(uname -n)" >> "$data_dir/summary"
   uptime >> "$data_dir/uptime"

   processor_info "$data_dir"
   find_release_and_kernel "$platform" >> "$data_dir/summary"
   cpu_and_os_arch "$platform"         >> "$data_dir/summary"
   find_virtualization "$platform" "$data_dir/dmesg_file" "$data_dir/lspci_file" >> "$data_dir/summary"
   dmidecode_system_info               >> "$data_dir/summary"

   if [ "${platform}" = "SunOS" -a "${CMD_ZONENAME}" ]; then
      echo "zonename    $($CMD_ZONENAME)" >> "$data_dir/summary"
   fi

   if [ -x /lib/libc.so.6 ]; then
      echo "compiler    $(/lib/libc.so.6 | grep 'Compiled by' | cut -c13-)" >> "$data_dir/summary"
   fi

   local rss=$(ps -eo rss 2>/dev/null | awk '/[0-9]/{total += $1 * 1024} END {print total}')
   echo "rss    ${rss}" >> "$data_dir/summary"

   [ "$CMD_DMIDECODE" ] && $CMD_DMIDECODE > "$data_dir/dmidecode" 2>/dev/null

   find_memory_stats "$platform" > "$data_dir/memory"
   [ "$OPT_SUMMARIZE_MOUNTS" ] && mounted_fs_info "$platform" > "$data_dir/mounted_fs"
   raid_controller   "$data_dir/dmesg_file" "$data_dir/lspci_file" >> "$data_dir/summary"

   local controller="$(get_var raid_controller "$data_dir/summary")"
   propietary_raid_controller "$data_dir/raid-controller" "$data_dir/summary" "$data_dir" "$controller"

   [ "${platform}" = "Linux" ] && linux_exclusive_collection "$data_dir"

   if [ "$CMD_IP" -a "$OPT_SUMMARIZE_NETWORK" ]; then
      $CMD_IP -s link > "$data_dir/ip"
      network_device_info "$data_dir/ip" > "$data_dir/network_devices"
   fi

   [ "$CMD_SWAPCTL" ] && $CMD_SWAPCTL -s > "$data_dir/swapctl"

   if [ "$OPT_SUMMARIZE_PROCESSES" ]; then
      top_processes > "$data_dir/processes"
      notable_processes_info > "$data_dir/notable_procs"

      if [ "$CMD_VMSTAT" ]; then
         touch "$data_dir/vmstat"
         (
            $CMD_VMSTAT 1 $OPT_SLEEP > "$data_dir/vmstat"
         ) &
      fi
   fi

   for file in $data_dir/*; do
      [ "$file" = "vmstat" ] && continue
      [ ! -s "$file" ] && rm "$file"
   done
}

linux_exclusive_collection () { local PTFUNCNAME=linux_exclusive_collection;
   local data_dir="$1"

   echo "threading    $(getconf GNU_LIBPTHREAD_VERSION)" >> "$data_dir/summary"

   local getenforce=""
   [ "$CMD_GETENFORCE" ] && getenforce="$($CMD_GETENFORCE 2>&1)"
   echo "getenforce    ${getenforce:-"No SELinux detected"}" >> "$data_dir/summary"

   echo "swappiness    $(awk '/vm.swappiness/{print $3}' "$data_dir/sysctl")" >> "$data_dir/summary"

   local dirty_ratio="$(awk '/vm.dirty_ratio/{print $3}' "$data_dir/sysctl")"
   local dirty_bg_ratio="$(awk '/vm.dirty_background_ratio/{print $3}' "$data_dir/sysctl")"
   if [ "$dirty_ratio" -a "$dirty_bg_ratio" ]; then
      echo "dirtypolicy    $dirty_ratio, $dirty_bg_ratio" >> "$data_dir/summary"
   fi

   local dirty_bytes="$(awk '/vm.dirty_bytes/{print $3}' "$data_dir/sysctl")"
   if [ "$dirty_bytes" ]; then
      echo "dirtystatus     $(awk '/vm.dirty_bytes/{print $3}' "$data_dir/sysctl"), $(awk '/vm.dirty_background_bytes/{print $3}' "$data_dir/sysctl")" >> "$data_dir/summary"
   fi

   schedulers_and_queue_size "$data_dir/summary" > "$data_dir/partitioning"

   for file in dentry-state file-nr inode-nr; do
      echo "${file}    $(cat /proc/sys/fs/${file} 2>&1)" >> "$data_dir/summary"
   done

   [ "$CMD_LVS" -a -x "$CMD_LVS" ] && $CMD_LVS 1>"$data_dir/lvs" 2>"$data_dir/lvs.stderr"

   [ "$CMD_VGS" -a -x "$CMD_VGS" ] && \
      $CMD_VGS -o vg_name,vg_size,vg_free 2>/dev/null > "$data_dir/vgs"

   [ "$CMD_NETSTAT" -a "$OPT_SUMMARIZE_NETWORK" ] && \
      $CMD_NETSTAT -antp > "$data_dir/netstat" 2>/dev/null
}

network_device_info () {
   local ip_minus_s_file="$1"

   if [ "$CMD_ETHTOOL" ]; then
      local tempfile="$TMPDIR/ethtool_output_temp"
      for device in $( awk '/^[1-9]/{ print $2 }'  "$ip_minus_s_file" \
                        | awk -F: '{print $1}'     \
                        | grep -v '^lo\|^in\|^gr'  \
                        | sort -u ); do
         ethtool $device > "$tempfile" 2>/dev/null

         if ! grep -q 'No data available' "$tempfile"; then
            cat "$tempfile"
         fi
      done
   fi
}

find_release_and_kernel () { local PTFUNCNAME=find_release_and_kernel;
   local platform="$1"

   local kernel=""
   local release=""
   if [ "${platform}" = "Linux" ]; then
      kernel="$(uname -r)"
      if [ -e /etc/fedora-release ]; then
         release=$(cat /etc/fedora-release);
      elif [ -e /etc/redhat-release ]; then
         release=$(cat /etc/redhat-release);
      elif [ -e /etc/system-release ]; then
         release=$(cat /etc/system-release);
      elif [ "$CMD_LSB_RELEASE" ]; then
         release="$($CMD_LSB_RELEASE -ds) ($($CMD_LSB_RELEASE -cs))"
      elif [ -e /etc/lsb-release ]; then
         release=$(grep DISTRIB_DESCRIPTION /etc/lsb-release |awk -F'=' '{print $2}' |sed 's#"##g');
      elif [ -e /etc/debian_version ]; then
         release="Debian-based version $(cat /etc/debian_version)";
         if [ -e /etc/apt/sources.list ]; then
             local code=` awk  '/^deb/ {print $3}' /etc/apt/sources.list       \
                        | awk -F/ '{print $1}'| awk 'BEGIN {FS="|"}{print $1}' \
                        | sort | uniq -c | sort -rn | head -n1 | awk '{print $2}'`
             release="${release} (${code})"
      fi
      elif ls /etc/*release >/dev/null 2>&1; then
         if grep -q DISTRIB_DESCRIPTION /etc/*release; then
            release=$(grep DISTRIB_DESCRIPTION /etc/*release | head -n1);
         else
            release=$(cat /etc/*release | head -n1);
         fi
      fi
   elif     [ "${platform}" = "FreeBSD" ] \
         || [ "${platform}" = "NetBSD"  ] \
         || [ "${platform}" = "OpenBSD" ]; then
      release="$(uname -r)"
      kernel="$($CMD_SYSCTL -n "kern.osrevision")"
   elif [ "${platform}" = "SunOS" ]; then
      release="$(head -n1 /etc/release)"
      if [ -z "${release}" ]; then
         release="$(uname -r)"
      fi
      kernel="$(uname -v)"
   fi
   echo "kernel    $kernel"
   echo "release    $release"
}

cpu_and_os_arch () { local PTFUNCNAME=cpu_and_os_arch;
   local platform="$1"

   local CPU_ARCH='32-bit'
   local OS_ARCH='32-bit'
   if [ "${platform}" = "Linux" ]; then
      if grep -q ' lm ' /proc/cpuinfo; then
         CPU_ARCH='64-bit'
      fi
   elif [ "${platform}" = "FreeBSD" ] || [ "${platform}" = "NetBSD" ]; then
      if $CMD_SYSCTL "hw.machine_arch" | grep -v 'i[36]86' >/dev/null; then
         CPU_ARCH='64-bit'
      fi
   elif [ "${platform}" = "OpenBSD" ]; then
      if $CMD_SYSCTL "hw.machine" | grep -v 'i[36]86' >/dev/null; then
         CPU_ARCH='64-bit'
      fi
   elif [ "${platform}" = "SunOS" ]; then
      if $CMD_ISAINFO -b | grep 64 >/dev/null ; then
         CPU_ARCH="64-bit"
      fi
   fi
   if [ -z "$CMD_FILE" ]; then
      if [ "$CMD_GETCONF" ] && $CMD_GETCONF LONG_BIT 1>/dev/null 2>&1; then
         OS_ARCH="$($CMD_GETCONF LONG_BIT 2>/dev/null)-bit"
      else
         OS_ARCH='N/A'
      fi
   elif $CMD_FILE /bin/sh | grep '64-bit' >/dev/null; then
       OS_ARCH='64-bit'
   fi

   echo "CPU_ARCH    $CPU_ARCH"
   echo "OS_ARCH    $OS_ARCH"
}

find_virtualization () { local PTFUNCNAME=find_virtualization;
   local platform="$1"
   local dmesg_file="$2"
   local lspci_file="$3"

   local tempfile="$TMPDIR/find_virtualziation.tmp"

   local virt=""
   if [ -s "$dmesg_file" ]; then
      virt="$(find_virtualization_dmesg "$dmesg_file")"
   fi
   if [ -z "${virt}" ] && [ -s "$lspci_file" ]; then
      if grep -qi "virtualbox" "$lspci_file" ; then
         virt="VirtualBox"
      elif grep -qi "vmware" "$lspci_file" ; then
         virt="VMWare"
      fi
   elif [ "${platform}" = "FreeBSD" ]; then
      if ps -o stat | grep J ; then
         virt="FreeBSD Jail"
      fi
   elif [ "${platform}" = "SunOS" ]; then
      if [ "$CMD_PRTDIAG" ] && $CMD_PRTDIAG > "$tempfile" 2>/dev/null; then
         virt="$(find_virtualization_generic "$tempfile" )"
      elif [ "$CMD_SMBIOS" ] && $CMD_SMBIOS > "$tempfile" 2>/dev/null; then
         virt="$(find_virtualization_generic "$tempfile" )"
      fi
   elif [ -e /proc/user_beancounters ]; then
      virt="OpenVZ/Virtuozzo"
   fi
   echo "virt    ${virt:-"No virtualization detected"}"
}

find_virtualization_generic() { local PTFUNCNAME=find_virtualization_generic;
   local file="$1"
   if grep -i -e "virtualbox" "$file" >/dev/null; then
      echo "VirtualBox"
   elif grep -i -e "vmware" "$file" >/dev/null; then
      echo "VMWare"
   fi
}

find_virtualization_dmesg () { local PTFUNCNAME=find_virtualization_dmesg;
   local file="$1"
   if grep -qi -e "vmware" -e "vmxnet" -e 'paravirtualized kernel on vmi' "${file}"; then
      echo "VMWare";
   elif grep -qi -e 'paravirtualized kernel on xen' -e 'Xen virtual console' "${file}"; then
      echo "Xen";
   elif grep -qi "qemu" "${file}"; then
      echo "QEmu";
   elif grep -qi 'paravirtualized kernel on KVM' "${file}"; then
      echo "KVM";
   elif grep -q "VBOX" "${file}"; then
      echo "VirtualBox";
   elif grep -qi 'hd.: Virtual .., ATA.*drive' "${file}"; then
      echo "Microsoft VirtualPC";
   fi
}

dmidecode_system_info () { local PTFUNCNAME=dmidecode_system_info;
   if [ "${CMD_DMIDECODE}" ]; then
      local vendor="$($CMD_DMIDECODE -s "system-manufacturer" 2>/dev/null | sed 's/ *$//g')"
      echo "vendor    ${vendor}"
      if [ "${vendor}" ]; then
         local product="$($CMD_DMIDECODE -s "system-product-name" 2>/dev/null | sed 's/ *$//g')"
         local version="$($CMD_DMIDECODE -s "system-version" 2>/dev/null | sed 's/ *$//g')"
         local chassis="$($CMD_DMIDECODE -s "chassis-type" 2>/dev/null | sed 's/ *$//g')"
         local servicetag="$($CMD_DMIDECODE -s "system-serial-number" 2>/dev/null | sed 's/ *$//g')"
         local system="${vendor}; ${product}; v${version} (${chassis})"

         echo "system    ${system}"
         echo "servicetag    ${servicetag:-"Not found"}"
      fi
   fi
}

find_memory_stats () { local PTFUNCNAME=find_memory_stats;
   local platform="$1"

   if [ "${platform}" = "Linux" ]; then
      free -b
      cat /proc/meminfo
   elif [ "${platform}" = "SunOS" ]; then
      $CMD_PRTCONF | awk -F: '/Memory/{print $2}'
   fi
}

mounted_fs_info () { local PTFUNCNAME=mounted_fs_info;
   local platform="$1"

   if [ "${platform}" != "SunOS" ]; then
      local cmd="df -h"
      if [ "${platform}" = "Linux" ]; then
         cmd="df -h -P"
      fi
      $cmd  | sort > "$TMPDIR/mounted_fs_info.tmp"
      mount | sort | join "$TMPDIR/mounted_fs_info.tmp" -
   fi
}

raid_controller () { local PTFUNCNAME=raid_controller;
   local dmesg_file="$1"
   local lspci_file="$2"

   local tempfile="$TMPDIR/raid_controller.tmp"

   local controller=""
   if [ -s "$lspci_file" ]; then
      controller="$(find_raid_controller_lspci "$lspci_file")"
   fi
   if [ -z "${controller}" ] && [ -s "$dmesg_file" ]; then
      controller="$(find_raid_controller_dmesg "$dmesg_file")"
   fi

   echo "raid_controller    ${controller:-"No RAID controller detected"}"
}

find_raid_controller_dmesg () { local PTFUNCNAME=find_raid_controller_dmesg;
   local file="$1"
   local pat='scsi[0-9].*: .*'
   if grep -qi "${pat}megaraid" "${file}"; then
      echo 'LSI Logic MegaRAID SAS'
   elif grep -q "Fusion MPT SAS" "${file}"; then
      echo 'Fusion-MPT SAS'
   elif grep -q "${pat}aacraid" "${file}"; then
      echo 'AACRAID'
   elif grep -q "${pat}3ware [0-9]* Storage Controller" "${file}"; then
      echo '3Ware'
   fi
}

find_raid_controller_lspci () { local PTFUNCNAME=find_raid_controller_lspci;
   local file="$1"
   if grep -q "RAID bus controller: LSI Logic / Symbios Logic MegaRAID SAS" "${file}"; then
      echo 'LSI Logic MegaRAID SAS'
   elif grep -q "Fusion-MPT SAS" "${file}"; then
      echo 'Fusion-MPT SAS'
   elif grep -q "RAID bus controller: LSI Logic / Symbios Logic Unknown" "${file}"; then
      echo 'LSI Logic Unknown'
   elif grep -q "RAID bus controller: Adaptec AAC-RAID" "${file}"; then
      echo 'AACRAID'
   elif grep -q "3ware [0-9]* Storage Controller" "${file}"; then
      echo '3Ware'
   elif grep -q "Hewlett-Packard Company Smart Array" "${file}"; then
      echo 'HP Smart Array'
   elif grep -q " RAID bus controller: " "${file}"; then
      awk -F: '/RAID bus controller\:/ {print $3" "$5" "$6}' "${file}"
   fi
}

schedulers_and_queue_size () { local PTFUNCNAME=schedulers_and_queue_size;
   local file="$1"

   local disks="$(ls /sys/block/ | grep -v -e ram -e loop -e 'fd[0-9]' | xargs echo)"
   echo "internal::disks    $disks" >> "$file"

   for disk in $disks; do
      if [ -e "/sys/block/${disk}/queue/scheduler" ]; then
         echo "internal::${disk}    $(cat /sys/block/${disk}/queue/scheduler | grep -o '\[.*\]') $(cat /sys/block/${disk}/queue/nr_requests)" >> "$file"
         fdisk -l "/dev/${disk}" 2>/dev/null
      fi
   done
}

top_processes () { local PTFUNCNAME=top_processes;
   if [ "$CMD_PRSTAT" ]; then
      $CMD_PRSTAT | head
   elif [ "$CMD_TOP" ]; then
      local cmd="$CMD_TOP -bn 1"
      if    [ "${platform}" = "FreeBSD" ] \
         || [ "${platform}" = "NetBSD"  ] \
         || [ "${platform}" = "OpenBSD" ]; then
         cmd="$CMD_TOP -b -d 1"
      fi
      $cmd \
         | sed -e 's# *$##g' -e '/./{H;$!d;}' -e 'x;/PID/!d;' \
         | grep . \
         | head
   fi
}

notable_processes_info () { local PTFUNCNAME=notable_processes_info;
   local format="%5s    %+2d    %s\n"
   local sshd_pid=$(ps -eo pid,args | awk '$2 ~ /\/usr\/sbin\/sshd/ { print $1; exit }')

   echo "  PID    OOM    COMMAND"

   if [ "$sshd_pid" ]; then
      printf "$format" "$sshd_pid" "$(get_oom_of_pid $sshd_pid)" "sshd"
   else
      printf "%5s    %3s    %s\n" "?" "?" "sshd doesn't appear to be running"
   fi

   local PTDEBUG=""
   ps -eo pid,ucomm | grep '^[0-9]' | while read pid proc; do
      [ "$sshd_pid" ] && [ "$sshd_pid" = "$pid" ] && continue
      local oom="$(get_oom_of_pid $pid)"
      if [ "$oom" ] && [ "$oom" != "?" ] && [ "$oom" = "-17" ]; then
         printf "$format" "$pid" "$oom" "$proc"
      fi
   done
}

processor_info () { local PTFUNCNAME=processor_info;
   local data_dir="$1"
   if [ -f /proc/cpuinfo ]; then
      cat /proc/cpuinfo > "$data_dir/proc_cpuinfo_copy" 2>/dev/null
   elif [ "${platform}" = "SunOS" ]; then
      $CMD_PSRINFO -v > "$data_dir/psrinfo_minus_v"
   fi 
}

propietary_raid_controller () { local PTFUNCNAME=propietary_raid_controller;
   local file="$1"
   local variable_file="$2"
   local data_dir="$3"
   local controller="$4"

   notfound=""
   if [ "${controller}" = "AACRAID" ]; then
      if [ -z "$CMD_ARCCONF" ]; then
         notfound="e.g. http://www.adaptec.com/en-US/support/raid/scsi_raid/ASR-2120S/"
      elif $CMD_ARCCONF getconfig 1 > "$file" 2>/dev/null; then
         echo "internal::raid_opt    1" >> "$variable_file"
      fi
   elif [ "${controller}" = "HP Smart Array" ]; then
      if [ -z "$CMD_HPACUCLI" ]; then
         notfound="your package repository or the manufacturer's website"
      elif $CMD_HPACUCLI ctrl all show config > "$file" 2>/dev/null; then
         echo "internal::raid_opt    2" >> "$variable_file"
      fi
   elif [ "${controller}" = "LSI Logic MegaRAID SAS" ]; then
      if [ -z "$CMD_MEGACLI64" ]; then 
         notfound="your package repository or the manufacturer's website"
      else
         echo "internal::raid_opt    3" >> "$variable_file"
         $CMD_MEGACLI64 -AdpAllInfo -aALL -NoLog > "$data_dir/lsi_megaraid_adapter_info.tmp" 2>/dev/null
         $CMD_MEGACLI64 -AdpBbuCmd -GetBbuStatus -aALL -NoLog > "$data_dir/lsi_megaraid_bbu_status.tmp" 2>/dev/null
         $CMD_MEGACLI64 -LdPdInfo -aALL -NoLog > "$data_dir/lsi_megaraid_devices.tmp" 2>/dev/null
      fi
   fi

   if [ "${notfound}" ]; then
      echo "internal::raid_opt    0" >> "$variable_file"
      echo "   RAID controller software not found; try getting it from" > "$file"
      echo "   ${notfound}" >> "$file"
   fi
}

# ###########################################################################
# End collect_system_info package
# ###########################################################################

# ###########################################################################
# report_system_info package
# This package is a copy without comments from the original.  The original
# with comments and its test file can be found in the Bazaar repository at,
#   lib/bash/report_system_info.sh
#   t/lib/bash/report_system_info.sh
# See https://launchpad.net/percona-toolkit for more information.
# ###########################################################################


set -u

   
parse_proc_cpuinfo () { local PTFUNCNAME=parse_proc_cpuinfo;
   local file="$1"
   local virtual="$(grep -c ^processor "${file}")";
   local physical="$(grep 'physical id' "${file}" | sort -u | wc -l)";
   local cores="$(grep 'cpu cores' "${file}" | head -n 1 | cut -d: -f2)";

   [ "${physical}" = "0" ] && physical="${virtual}"
   [ -z "${cores}" ] && cores=0

   cores=$((${cores} * ${physical}));
   local htt=""
   if [ ${cores} -gt 0 -a $cores -lt $virtual ]; then htt=yes; else htt=no; fi

   name_val "Processors" "physical = ${physical}, cores = ${cores}, virtual = ${virtual}, hyperthreading = ${htt}"

   awk -F: '/cpu MHz/{print $2}' "${file}" \
      | sort | uniq -c > "$TMPDIR/parse_proc_cpuinfo_cpu.unq"
   name_val "Speeds" "$(group_concat "$TMPDIR/parse_proc_cpuinfo_cpu.unq")"

   awk -F: '/model name/{print $2}' "${file}" \
      | sort | uniq -c > "$TMPDIR/parse_proc_cpuinfo_model.unq"
   name_val "Models" "$(group_concat "$TMPDIR/parse_proc_cpuinfo_model.unq")"

   awk -F: '/cache size/{print $2}' "${file}" \
      | sort | uniq -c > "$TMPDIR/parse_proc_cpuinfo_cache.unq"
   name_val "Caches" "$(group_concat "$TMPDIR/parse_proc_cpuinfo_cache.unq")"
}

parse_sysctl_cpu_freebsd() { local PTFUNCNAME=parse_sysctl_cpu_freebsd;
   local file="$1"
   [ -e "$file" ] || return;
   local virtual="$(awk '/hw.ncpu/{print $2}' "$file")"
   name_val "Processors" "virtual = ${virtual}"
   name_val "Speeds" "$(awk '/hw.clockrate/{print $2}' "$file")"
   name_val "Models" "$(awk -F: '/hw.model/{print substr($2, 2)}' "$file")"
}

parse_sysctl_cpu_netbsd() { local PTFUNCNAME=parse_sysctl_cpu_netbsd;
   local file="$1"

   [ -e "$file" ] || return

   local virtual="$(awk '/hw.ncpu /{print $NF}' "$file")"
   name_val "Processors" "virtual = ${virtual}"
   name_val "Models" "$(awk -F: '/hw.model/{print $3}' "$file")"
}

parse_sysctl_cpu_openbsd() { local PTFUNCNAME=parse_sysctl_cpu_openbsd;
   local file="$1"

   [ -e "$file" ] || return

   name_val "Processors" "$(awk -F= '/hw.ncpu=/{print $2}' "$file")"
   name_val "Speeds" "$(awk -F= '/hw.cpuspeed/{print $2}' "$file")"
   name_val "Models" "$(awk -F= '/hw.model/{print substr($2, 1, index($2, " "))}' "$file")"
}

parse_psrinfo_cpus() { local PTFUNCNAME=parse_psrinfo_cpus;
   local file="$1"

   [ -e "$file" ] || return

   name_val "Processors" "$(grep -c 'Status of .* processor' "$file")"
   awk '/operates at/ {
      start = index($0, " at ") + 4;
      end   = length($0) - start - 4
      print substr($0, start, end);
   }' "$file" | sort | uniq -c > "$TMPDIR/parse_psrinfo_cpus.tmp"
   name_val "Speeds" "$(group_concat "$TMPDIR/parse_psrinfo_cpus.tmp")"
}

parse_free_minus_b () { local PTFUNCNAME=parse_free_minus_b;
   local file="$1"

   [ -e "$file" ] || return

   local physical=$(awk '/Mem:/{print $3}' "${file}")
   local swap_alloc=$(awk '/Swap:/{print $2}' "${file}")
   local swap_used=$(awk '/Swap:/{print $3}' "${file}")
   local virtual=$(shorten $(($physical + $swap_used)) 1)

   name_val "Total"   $(shorten $(awk '/Mem:/{print $2}' "${file}") 1)
   name_val "Free"    $(shorten $(awk '/Mem:/{print $4}' "${file}") 1)
   name_val "Used"    "physical = $(shorten ${physical} 1), swap allocated = $(shorten ${swap_alloc} 1), swap used = $(shorten ${swap_used} 1), virtual = ${virtual}"
   name_val "Buffers" $(shorten $(awk '/Mem:/{print $6}' "${file}") 1)
   name_val "Caches"  $(shorten $(awk '/Mem:/{print $7}' "${file}") 1)
   name_val "Dirty"  "$(awk '/Dirty:/ {print $2, $3}' "${file}")"
}

parse_memory_sysctl_freebsd() { local PTFUNCNAME=parse_memory_sysctl_freebsd;
   local file="$1"

   [ -e "$file" ] || return

   local physical=$(awk '/hw.realmem:/{print $2}' "${file}")
   local mem_hw=$(awk '/hw.physmem:/{print $2}' "${file}")
   local mem_used=$(awk '
      /hw.physmem/                   { mem_hw       = $2; }
      /vm.stats.vm.v_inactive_count/ { mem_inactive = $2; }
      /vm.stats.vm.v_cache_count/    { mem_cache    = $2; }
      /vm.stats.vm.v_free_count/     { mem_free     = $2; }
      /hw.pagesize/                  { pagesize     = $2; }
      END {
         mem_inactive *= pagesize;
         mem_cache    *= pagesize;
         mem_free     *= pagesize;
         print mem_hw - mem_inactive - mem_cache - mem_free;
      }
   ' "$file");
   name_val "Total"   $(shorten ${mem_hw} 1)
   name_val "Virtual" $(shorten ${physical} 1)
   name_val "Used"    $(shorten ${mem_used} 1)
}

parse_memory_sysctl_netbsd() { local PTFUNCNAME=parse_memory_sysctl_netbsd;
   local file="$1"
   local swapctl_file="$2"

   [ -e "$file" -a -e "$swapctl_file" ] || return

   local swap_mem="$(echo "$(awk '{print $2;}' "$swapctl_file")*512" | bc -l)"
   name_val "Total"   $(shorten "$(awk '/hw.physmem /{print $NF}' "$file")" 1)
   name_val "User"    $(shorten "$(awk '/hw.usermem /{print $NF}' "$file")" 1)
   name_val "Swap"    $(shorten ${swap_mem} 1)
}

parse_memory_sysctl_openbsd() { local PTFUNCNAME=parse_memory_sysctl_openbsd;
   local file="$1"
   local swapctl_file="$2"

   [ -e "$file" -a -e "$swapctl_file" ] || return

   local swap_mem="$(echo "$(awk '{print $2;}' "$swapctl_file")*512" | bc -l)"
   name_val "Total"   $(shorten "$(awk -F= '/hw.physmem/{print $2}' "$file")" 1)
   name_val "User"    $(shorten "$(awk -F= '/hw.usermem/{print $2}' "$file")" 1)
   name_val "Swap"    $(shorten ${swap_mem} 1)
}

parse_dmidecode_mem_devices () { local PTFUNCNAME=parse_dmidecode_mem_devices;
   local file="$1"

   [ -e "$file" ] || return

   echo "  Locator   Size     Speed             Form Factor   Type          Type Detail"
   echo "  ========= ======== ================= ============= ============= ==========="
   sed    -e '/./{H;$!d;}' \
          -e 'x;/Memory Device\n/!d;' \
          -e 's/: /:/g' \
          -e 's/</{/g' \
          -e 's/>/}/g' \
          -e 's/[ \t]*\n/\n/g' \
       "${file}" \
       | awk -F: '/Size|Type|Form.Factor|Type.Detail|[^ ]Locator/{printf("|%s", $2)}/Speed/{print "|" $2}' \
       | sed -e 's/No Module Installed/{EMPTY}/' \
       | sort \
       | awk -F'|' '{printf("  %-9s %-8s %-17s %-13s %-13s %-8s\n", $4, $2, $7, $3, $5, $6);}'
}

parse_ip_s_link () { local PTFUNCNAME=parse_ip_s_link;
   local file="$1"

   [ -e "$file" ] || return

   echo "  interface  rx_bytes rx_packets  rx_errors   tx_bytes tx_packets  tx_errors"
   echo "  ========= ========= ========== ========== ========== ========== =========="

   awk "/^[1-9][0-9]*:/ {
      save[\"iface\"] = substr(\$2, 1, index(\$2, \":\") - 1);
      new = 1;
   }
   \$0 !~ /[^0-9 ]/ {
      if ( new == 1 ) {
         new = 0;
         fuzzy_var = \$1; ${fuzzy_formula} save[\"bytes\"] = fuzzy_var;
         fuzzy_var = \$2; ${fuzzy_formula} save[\"packs\"] = fuzzy_var;
         fuzzy_var = \$3; ${fuzzy_formula} save[\"errs\"]  = fuzzy_var;
      }
      else {
         fuzzy_var = \$1; ${fuzzy_formula} tx_bytes   = fuzzy_var;
         fuzzy_var = \$2; ${fuzzy_formula} tx_packets = fuzzy_var;
         fuzzy_var = \$3; ${fuzzy_formula} tx_errors  = fuzzy_var;
         printf \"  %-8s %10.0f %10.0f %10.0f %10.0f %10.0f %10.0f\\n\", save[\"iface\"], save[\"bytes\"], save[\"packs\"], save[\"errs\"], tx_bytes, tx_packets, tx_errors;
      }
   }" "$file"
}

parse_ethtool () {
   local file="$1"

   [ -e "$file" ] || return

   echo "  Device    Speed     Duplex"
   echo "  ========= ========= ========="


   awk '
      /^Settings for / {
         device               = substr($3, 1, index($3, ":") ? index($3, ":")-1 : length($3));
         device_names[device] = device;
      }
      /Speed:/  { devices[device ",speed"]  = $2 }
      /Duplex:/ { devices[device ",duplex"] = $2 }
      END {
         for ( device in device_names ) {
            printf("  %-10s %-10s %-10s\n",
               device,
               devices[device ",speed"],
               devices[device ",duplex"]);
         }
      }
   ' "$file"

}

parse_netstat () { local PTFUNCNAME=parse_netstat;
   local file="$1"

   [ -e "$file" ] || return

   echo "  Connections from remote IP addresses"
   awk '$1 ~ /^tcp/ && $5 ~ /^[1-9]/ {
      print substr($5, 1, index($5, ":") - 1);
   }' "${file}" | sort | uniq -c \
      | awk "{
         fuzzy_var=\$1;
         ${fuzzy_formula}
         printf \"    %-15s %5d\\n\", \$2, fuzzy_var;
         }" \
      | sort -n -t . -k 1,1 -k 2,2 -k 3,3 -k 4,4
   echo "  Connections to local IP addresses"
   awk '$1 ~ /^tcp/ && $5 ~ /^[1-9]/ {
      print substr($4, 1, index($4, ":") - 1);
   }' "${file}" | sort | uniq -c \
      | awk "{
         fuzzy_var=\$1;
         ${fuzzy_formula}
         printf \"    %-15s %5d\\n\", \$2, fuzzy_var;
         }" \
      | sort -n -t . -k 1,1 -k 2,2 -k 3,3 -k 4,4
   echo "  Connections to top 10 local ports"
   awk '$1 ~ /^tcp/ && $5 ~ /^[1-9]/ {
      print substr($4, index($4, ":") + 1);
   }' "${file}" | sort | uniq -c | sort -rn | head -n10 \
      | awk "{
         fuzzy_var=\$1;
         ${fuzzy_formula}
         printf \"    %-15s %5d\\n\", \$2, fuzzy_var;
         }" | sort
   echo "  States of connections"
   awk '$1 ~ /^tcp/ {
      print $6;
   }' "${file}" | sort | uniq -c | sort -rn \
      | awk "{
         fuzzy_var=\$1;
         ${fuzzy_formula}
         printf \"    %-15s %5d\\n\", \$2, fuzzy_var;
         }" | sort
}

parse_filesystems () { local PTFUNCNAME=parse_filesystems;
   local file="$1"
   local platform="$2"

   [ -e "$file" ] || return

   local spec="$(awk "
      BEGIN {
         device     = 10;
         fstype     = 4;
         options    = 4;
      }
      /./ {
         f_device     = \$1;
         f_fstype     = \$10;
         f_options    = substr(\$11, 2, length(\$11) - 2);
         if ( \"$2\" ~ /(Free|Open|Net)BSD/ ) {
            f_fstype  = substr(\$9, 2, length(\$9) - 2);
            f_options = substr(\$0, index(\$0, \",\") + 2);
            f_options = substr(f_options, 1, length(f_options) - 1);
         }
         if ( length(f_device) > device ) {
            device=length(f_device);
         }
         if ( length(f_fstype) > fstype ) {
            fstype=length(f_fstype);
         }
         if ( length(f_options) > options ) {
            options=length(f_options);
         }
      }
      END{
         print \"%-\" device \"s %5s %4s %-\" fstype \"s %-\" options \"s %s\";
      }
   " "${file}")"

   awk "
      BEGIN {
         spec=\"  ${spec}\\n\";
         printf spec, \"Filesystem\", \"Size\", \"Used\", \"Type\", \"Opts\", \"Mountpoint\";
      }
      {
         f_fstype     = \$10;
         f_options    = substr(\$11, 2, length(\$11) - 2);
         if ( \"$2\" ~ /(Free|Open|Net)BSD/ ) {
            f_fstype  = substr(\$9, 2, length(\$9) - 2);
            f_options = substr(\$0, index(\$0, \",\") + 2);
            f_options = substr(f_options, 1, length(f_options) - 1);
         }
         printf spec, \$1, \$2, \$5, f_fstype, f_options, \$6;
      }
   " "${file}"
}

parse_fdisk () { local PTFUNCNAME=parse_fdisk;
   local file="$1"

   [ -e "$file" -a -s "$file" ] || return

   awk '
      BEGIN {
         format="%-12s %4s %10s %10s %18s\n";
         printf(format, "Device", "Type", "Start", "End", "Size");
         printf(format, "============", "====", "==========", "==========", "==================");
      }
      /Disk.*bytes/ {
         disk = substr($2, 1, length($2) - 1);
         size = $5;
         printf(format, disk, "Disk", "", "", size);
      }
      /Units/ {
         units = $9;
      }
      /^\/dev/ {
         if ( $2 == "*" ) {
            start = $3;
            end   = $4;
         }
         else {
            start = $2;
            end   = $3;
         }
         printf(format, $1, "Part", start, end, sprintf("%.0f", (end - start) * units));
      }
   ' "${file}"
}

parse_ethernet_controller_lspci () { local PTFUNCNAME=parse_ethernet_controller_lspci;
   local file="$1"

   [ -e "$file" ] || return

   grep -i ethernet "${file}" | cut -d: -f3 | while read line; do
      name_val "Controller" "${line}"
   done
}

parse_hpacucli () { local PTFUNCNAME=parse_hpacucli;
   local file="$1"
   [ -e "$file" ] || return
   grep 'logicaldrive\|physicaldrive' "${file}"
}

parse_arcconf () { local PTFUNCNAME=parse_arcconf;
   local file="$1"

   [ -e "$file" ] || return

   local model="$(awk -F: '/Controller Model/{print $2}' "${file}")"
   local chan="$(awk -F: '/Channel description/{print $2}' "${file}")"
   local cache="$(awk -F: '/Installed memory/{print $2}' "${file}")"
   local status="$(awk -F: '/Controller Status/{print $2}' "${file}")"
   name_val "Specs" "$(echo "$model" | sed -e 's/ //'),${chan},${cache} cache,${status}"

   local battery=""
   if grep -q "ZMM" "$file"; then
      battery="$(grep -A2 'Controller ZMM Information' "$file" \
                  | awk '/Status/ {s=$4}
                         END      {printf "ZMM %s", s}')"
   else
      battery="$(grep -A5 'Controller Battery Info' "${file}" \
         | awk '/Capacity remaining/ {c=$4}
               /Status/             {s=$3}
               /Time remaining/     {t=sprintf("%dd%dh%dm", $7, $9, $11)}
               END                  {printf("%d%%, %s remaining, %s", c, t, s)}')"
   fi
   name_val "Battery" "${battery}"

   echo
   echo "  LogicalDev Size      RAID Disks Stripe Status  Cache"
   echo "  ========== ========= ==== ===== ====== ======= ======="
   for dev in $(awk '/Logical device number/{print $4}' "${file}"); do
      sed -n -e "/^Logical device .* ${dev}$/,/^$\|^Logical device number/p" "${file}" \
      | awk '
         /Logical device name/               {d=$5}
         /Size/                              {z=$3 " " $4}
         /RAID level/                        {r=$4}
         /Group [0-9]/                       {g++}
         /Stripe-unit size/                  {p=$4 " " $5}
         /Status of logical/                 {s=$6}
         /Write-cache mode.*Ena.*write-back/ {c="On (WB)"}
         /Write-cache mode.*Ena.*write-thro/ {c="On (WT)"}
         /Write-cache mode.*Disabled/        {c="Off"}
         END {
            printf("  %-10s %-9s %4d %5d %-6s %-7s %-7s\n",
               d, z, r, g, p, s, c);
         }'
   done

   echo
   echo "  PhysiclDev State   Speed         Vendor  Model        Size        Cache"
   echo "  ========== ======= ============= ======= ============ =========== ======="

   local tempresult=""
   sed -n -e '/Physical Device information/,/^$/p' "${file}" \
      | awk -F: '
         /Device #[0-9]/ {
            device=substr($0, index($0, "#"));
            devicenames[device]=device;
         }
         /Device is a/ {
            devices[device ",isa"] = substr($0, index($0, "is a") + 5);
         }
         /State/ {
            devices[device ",state"] = substr($2, 2);
         }
         /Transfer Speed/ {
            devices[device ",speed"] = substr($2, 2);
         }
         /Vendor/ {
            devices[device ",vendor"] = substr($2, 2);
         }
         /Model/ {
            devices[device ",model"] = substr($2, 2);
         }
         /Size/ {
            devices[device ",size"] = substr($2, 2);
         }
         /Write Cache/ {
            if ( $2 ~ /Enabled .write-back./ )
               devices[device ",cache"] = "On (WB)";
            else
               if ( $2 ~ /Enabled .write-th/ )
                  devices[device ",cache"] = "On (WT)";
               else
                  devices[device ",cache"] = "Off";
         }
         END {
            for ( device in devicenames ) {
               if ( devices[device ",isa"] ~ /Hard drive/ ) {
                  printf("  %-10s %-7s %-13s %-7s %-12s %-11s %-7s\n",
                     devices[device ",isa"],
                     devices[device ",state"],
                     devices[device ",speed"],
                     devices[device ",vendor"],
                     devices[device ",model"],
                     devices[device ",size"],
                     devices[device ",cache"]);
               }
            }
         }'
}

parse_fusionmpt_lsiutil () { local PTFUNCNAME=parse_fusionmpt_lsiutil;
   local file="$1"
   echo
   awk '/LSI.*Firmware/ { print " ", $0 }' "${file}"
   grep . "${file}" | sed -n -e '/B___T___L/,$ {s/^/  /; p}'
}

parse_lsi_megaraid_adapter_info () { local PTFUNCNAME=parse_lsi_megaraid_adapter_info;
   local file="$1"

   [ -e "$file" ] || return

   local name="$(awk -F: '/Product Name/{print substr($2, 2)}' "${file}")";
   local int=$(awk '/Host Interface/{print $4}' "${file}");
   local prt=$(awk '/Number of Backend Port/{print $5}' "${file}");
   local bbu=$(awk '/^BBU             :/{print $3}' "${file}");
   local mem=$(awk '/Memory Size/{print $4}' "${file}");
   local vdr=$(awk '/Virtual Drives/{print $4}' "${file}");
   local dvd=$(awk '/Degraded/{print $3}' "${file}");
   local phy=$(awk '/^  Disks/{print $3}' "${file}");
   local crd=$(awk '/Critical Disks/{print $4}' "${file}");
   local fad=$(awk '/Failed Disks/{print $4}' "${file}");

   name_val "Model" "${name}, ${int} interface, ${prt} ports"
   name_val "Cache" "${mem} Memory, BBU ${bbu}"
}

parse_lsi_megaraid_bbu_status () { local PTFUNCNAME=parse_lsi_megaraid_bbu_status;
   local file="$1"

   [ -e "$file" ] || return

   local charge=$(awk '/Relative State/{print $5}' "${file}");
   local temp=$(awk '/^Temperature/{print $2}' "${file}");
   local soh=$(awk '/isSOHGood:/{print $2}' "${file}");
   name_val "BBU" "${charge}% Charged, Temperature ${temp}C, isSOHGood=${soh}"
}

format_lvs () { local PTFUNCNAME=format_lvs;
   local file="$1"
   if [ -e "$file" ]; then
      grep -v "open failed" "$file"
   else
      echo "Unable to collect information";
   fi
}

parse_lsi_megaraid_devices () { local PTFUNCNAME=parse_lsi_megaraid_devices;
   local file="$1"

   [ -e "$file" ] || return

   echo
   echo "  PhysiclDev Type State   Errors Vendor  Model        Size"
   echo "  ========== ==== ======= ====== ======= ============ ==========="
   for dev in $(awk '/Device Id/{print $3}' "${file}"); do
      sed -e '/./{H;$!d;}' -e "x;/Device Id: ${dev}/!d;" "${file}" \
      | awk '
         /Media Type/                        {d=substr($0, index($0, ":") + 2)}
         /PD Type/                           {t=$3}
         /Firmware state/                    {s=$3}
         /Media Error Count/                 {me=$4}
         /Other Error Count/                 {oe=$4}
         /Predictive Failure Count/          {pe=$4}
         /Inquiry Data/                      {v=$3; m=$4;}
         /Raw Size/                          {z=$3}
         END {
            printf("  %-10s %-4s %-7s %6s %-7s %-12s %-7s\n",
               substr(d, 1, 10), t, s, me "/" oe "/" pe, v, m, z);
         }'
   done
}

parse_lsi_megaraid_virtual_devices () { local PTFUNCNAME=parse_lsi_megaraid_virtual_devices;
   local file="$1"

   [ -e "$file" ] || return

   echo
   echo "  VirtualDev Size      RAID Level Disks SpnDpth Stripe Status  Cache"
   echo "  ========== ========= ========== ===== ======= ====== ======= ========="
   awk '
      /^Virtual (Drive|Disk):/ {
         device              = $3;
         devicenames[device] = device;
      }
      /Number Of Drives/ {
         devices[device ",numdisks"] = substr($0, index($0, ":") + 1);
      }
      /^Name/ {
         devices[device ",name"] = substr($0, index($0, ":") + 1) > "" ? substr($0, index($0, ":") + 1) : "(no name)";
      }
      /RAID Level/ {
         devices[device ",primary"]   = substr($3, index($3, "-") + 1, 1);
         devices[device ",secondary"] = substr($4, index($4, "-") + 1, 1);
         devices[device ",qualifier"] = substr($NF, index($NF, "-") + 1, 1);
      }
      /Span Depth/ {
         devices[device ",spandepth"] = substr($2, index($2, ":") + 1);
      }
      /Number of Spans/ {
         devices[device ",numspans"] = $4;
      }
      /^Size/ {
         devices[device ",size"] = substr($0, index($0, ":") + 1);
      }
      /^State/ {
         devices[device ",state"] = substr($0, index($0, ":") + 2);
      }
      /^Stripe? Size/ {
         devices[device ",stripe"] = substr($0, index($0, ":") + 1);
      }
      /^Current Cache Policy/ {
         devices[device ",wpolicy"] = $4 ~ /WriteBack/ ? "WB" : "WT";
         devices[device ",rpolicy"] = $5 ~ /ReadAheadNone/ ? "no RA" : "RA";
      }
      END {
         for ( device in devicenames ) {
            raid = 0;
            if ( devices[device ",primary"] == 1 ) {
               raid = 1;
               if ( devices[device ",secondary"] == 3 ) {
                  raid = 10;
               }
            }
            else {
               if ( devices[device ",primary"] == 5 ) {
                  raid = 5;
               }
            }
            printf("  %-10s %-9s %-10s %5d %7s %6s %-7s %s\n",
               device devices[device ",name"],
               devices[device ",size"],
               raid " (" devices[device ",primary"] "-" devices[device ",secondary"] "-" devices[device ",qualifier"] ")",
               devices[device ",numdisks"],
               devices[device ",spandepth"] "-" devices[device ",numspans"],
               devices[device ",stripe"], devices[device ",state"],
               devices[device ",wpolicy"] ", " devices[device ",rpolicy"]);
         }
      }' "${file}"
}

format_vmstat () { local PTFUNCNAME=format_vmstat;
   local file="$1"

   [ -e "$file" ] || return

   awk "
      BEGIN {
         format = \"  %2s %2s  %4s %4s %5s %5s %6s %6s %3s %3s %3s %3s %3s\n\";
      }
      /procs/ {
         print  \"  procs  ---swap-- -----io---- ---system---- --------cpu--------\";
      }
      /bo/ {
         printf format, \"r\", \"b\", \"si\", \"so\", \"bi\", \"bo\", \"ir\", \"cs\", \"us\", \"sy\", \"il\", \"wa\", \"st\";
      }
      \$0 !~ /r/ {
            fuzzy_var = \$1;   ${fuzzy_formula}  r   = fuzzy_var;
            fuzzy_var = \$2;   ${fuzzy_formula}  b   = fuzzy_var;
            fuzzy_var = \$7;   ${fuzzy_formula}  si  = fuzzy_var;
            fuzzy_var = \$8;   ${fuzzy_formula}  so  = fuzzy_var;
            fuzzy_var = \$9;   ${fuzzy_formula}  bi  = fuzzy_var;
            fuzzy_var = \$10;  ${fuzzy_formula}  bo  = fuzzy_var;
            fuzzy_var = \$11;  ${fuzzy_formula}  ir  = fuzzy_var;
            fuzzy_var = \$12;  ${fuzzy_formula}  cs  = fuzzy_var;
            fuzzy_var = \$13;                    us  = fuzzy_var;
            fuzzy_var = \$14;                    sy  = fuzzy_var;
            fuzzy_var = \$15;                    il  = fuzzy_var;
            fuzzy_var = \$16;                    wa  = fuzzy_var;
            fuzzy_var = \$17;                    st  = fuzzy_var;
            printf format, r, b, si, so, bi, bo, ir, cs, us, sy, il, wa, st;
         }
   " "${file}"
}

processes_section () { local PTFUNCNAME=processes_section;
   local top_process_file="$1"
   local notable_procs_file="$2"
   local vmstat_file="$3"
   local platform="$4"

   section "Top Processes"
   cat "$top_process_file"
   section "Notable Processes"
   cat "$notable_procs_file"
   if [ -e "$vmstat_file" ]; then
      section "Simplified and fuzzy rounded vmstat (wait please)"
      wait # For the process we forked that was gathering vmstat samples
      if [ "${platform}" = "Linux" ]; then
         format_vmstat "$vmstat_file"
      else
         cat "$vmstat_file"
      fi
   fi
}

section_Processor () {
   local platform="$1"
   local data_dir="$2"

   section "Processor"

   if [ -e "$data_dir/proc_cpuinfo_copy" ]; then
      parse_proc_cpuinfo "$data_dir/proc_cpuinfo_copy"
   elif [ "${platform}" = "FreeBSD" ]; then
      parse_sysctl_cpu_freebsd "$data_dir/sysctl"
   elif [ "${platform}" = "NetBSD" ]; then
      parse_sysctl_cpu_netbsd "$data_dir/sysctl"
   elif [ "${platform}" = "OpenBSD" ]; then
      parse_sysctl_cpu_openbsd "$data_dir/sysctl"
   elif [ "${platform}" = "SunOS" ]; then
      parse_psrinfo_cpus "$data_dir/psrinfo_minus_v"
   fi
}

section_Memory () {
   local platform="$1"
   local data_dir="$2"

   section "Memory"
   if [ "${platform}" = "Linux" ]; then
      parse_free_minus_b "$data_dir/memory"
   elif [ "${platform}" = "FreeBSD" ]; then
      parse_memory_sysctl_freebsd "$data_dir/sysctl"
   elif [ "${platform}" = "NetBSD" ]; then
      parse_memory_sysctl_netbsd "$data_dir/sysctl" "$data_dir/swapctl"
   elif [ "${platform}" = "OpenBSD" ]; then
      parse_memory_sysctl_openbsd "$data_dir/sysctl" "$data_dir/swapctl"
   elif [ "${platform}" = "SunOS" ]; then
      name_val "Memory" "$(cat "$data_dir/memory")"
   fi

   local rss=$( get_var "rss" "$data_dir/summary" )
   name_val "UsedRSS" "$(shorten ${rss} 1)"

   if [ "${platform}" = "Linux" ]; then
      name_val "Swappiness" "$(get_var "swappiness" "$data_dir/summary")"
      name_val "DirtyPolicy" "$(get_var "dirtypolicy" "$data_dir/summary")"
      local dirty_status="$(get_var "dirtystatus" "$data_dir/summary")"
      if [ -n "$dirty_status" ]; then
         name_val "DirtyStatus" "$dirty_status"
      fi
   fi

   if [ -s "$data_dir/dmidecode" ]; then
      parse_dmidecode_mem_devices "$data_dir/dmidecode"
   fi
}

parse_uptime () {
   local file="$1"

   awk ' / up / {
            printf substr($0, index($0, " up ")+4 );
         }
         !/ up / {
            printf $0;
         }
' "$file"
}

report_system_summary () { local PTFUNCNAME=report_system_summary;
   local data_dir="$1"

   section "Percona Toolkit System Summary Report"


   [ -e "$data_dir/summary" ] \
      || die "The data directory doesn't have a summary file, exiting."

   local platform="$(get_var "platform" "$data_dir/summary")"
   name_val "Date" "`date -u +'%F %T UTC'` (local TZ: `date +'%Z %z'`)"
   name_val "Hostname" "$(get_var hostname "$data_dir/summary")"
   name_val "Uptime" "$(parse_uptime "$data_dir/uptime")"

   if [ "$(get_var "vendor" "$data_dir/summary")" ]; then
      name_val "System" "$(get_var "system" "$data_dir/summary")";
      name_val "Service Tag" "$(get_var "servicetag" "$data_dir/summary")";
   fi

   name_val "Platform" "${platform}"
   local zonename="$(get_var zonename "$data_dir/summary")";
   [ -n "${zonename}" ] && name_val "Zonename" "$zonename"

   name_val "Release" "$(get_var "release" "$data_dir/summary")"
   name_val "Kernel" "$(get_var "kernel" "$data_dir/summary")"

   name_val "Architecture" "CPU = $(get_var "CPU_ARCH" "$data_dir/summary"), OS = $(get_var "OS_ARCH" "$data_dir/summary")"

   local threading="$(get_var threading "$data_dir/summary")"
   local compiler="$(get_var compiler "$data_dir/summary")"
   [ -n "$threading" ] && name_val "Threading" "$threading"
   [ -n "$compiler"  ] && name_val "Compiler" "$compiler"

   local getenforce="$(get_var getenforce "$data_dir/summary")"
   [ -n "$getenforce" ] && name_val "SELinux" "${getenforce}";

   name_val "Virtualized" "$(get_var "virt" "$data_dir/summary")"

   section_Processor "$platform" "$data_dir"

   section_Memory    "$platform" "$data_dir"


   if [ -s "$data_dir/mounted_fs" ]; then
      section "Mounted Filesystems"
      parse_filesystems "$data_dir/mounted_fs" "${platform}"
   fi

   if [ "${platform}" = "Linux" ]; then

      section "Disk Schedulers And Queue Size"
      local disks="$( get_var "internal::disks" "$data_dir/summary" )"
      for disk in $disks; do
         local scheduler="$( get_var "internal::${disk}" "$data_dir/summary" )"
         name_val "${disk}" "${scheduler:-"UNREADABLE"}"
      done

      section "Disk Partioning"
      parse_fdisk "$data_dir/partitioning"

      section "Kernel Inode State"
      for file in dentry-state file-nr inode-nr; do
         name_val "${file}" "$(get_var "${file}" "$data_dir/summary")"
      done

      section "LVM Volumes"
      format_lvs "$data_dir/lvs"
      section "LVM Volume Groups"
      format_lvs "$data_dir/vgs"
   fi

   section "RAID Controller"
   local controller="$(get_var "raid_controller" "$data_dir/summary")"
   name_val "Controller" "$controller"
   local key="$(get_var "internal::raid_opt" "$data_dir/summary")"
   case "$key" in
      0)
         cat "$data_dir/raid-controller"
         ;;
      1)
         parse_arcconf "$data_dir/raid-controller"
         ;;
      2)
         parse_hpacucli "$data_dir/raid-controller"
         ;;
      3)
         [ -e "$data_dir/lsi_megaraid_adapter_info.tmp" ] && \
            parse_lsi_megaraid_adapter_info "$data_dir/lsi_megaraid_adapter_info.tmp"
         [ -e "$data_dir/lsi_megaraid_bbu_status.tmp" ] && \
            parse_lsi_megaraid_bbu_status "$data_dir/lsi_megaraid_bbu_status.tmp"
         if [ -e "$data_dir/lsi_megaraid_devices.tmp" ]; then
            parse_lsi_megaraid_virtual_devices "$data_dir/lsi_megaraid_devices.tmp"
            parse_lsi_megaraid_devices "$data_dir/lsi_megaraid_devices.tmp"
         fi
         ;;
   esac

   if [ "${OPT_SUMMARIZE_NETWORK}" ]; then
      if [ "${platform}" = "Linux" ]; then
         section "Network Config"
         if [ -s "$data_dir/lspci_file" ]; then
            parse_ethernet_controller_lspci "$data_dir/lspci_file"
         fi
         if grep "net.ipv4.tcp_fin_timeout" "$data_dir/sysctl" > /dev/null 2>&1; then
            name_val "FIN Timeout" "$(awk '/net.ipv4.tcp_fin_timeout/{print $NF}' "$data_dir/sysctl")"
            name_val "Port Range" "$(awk '/net.ipv4.ip_local_port_range/{print $NF}' "$data_dir/sysctl")"
         fi
      fi


      if [ -s "$data_dir/ip" ]; then
         section "Interface Statistics"
         parse_ip_s_link "$data_dir/ip"
      fi

      if [ -s "$data_dir/network_devices" ]; then
         section "Network Devices"
         parse_ethtool "$data_dir/network_devices"
      fi

      if [ "${platform}" = "Linux" -a -e "$data_dir/netstat" ]; then
         section "Network Connections"
         parse_netstat "$data_dir/netstat"
      fi
   fi

   [ "$OPT_SUMMARIZE_PROCESSES" ] && processes_section           \
                                       "$data_dir/processes"     \
                                       "$data_dir/notable_procs" \
                                       "$data_dir/vmstat"        \
                                       "$platform"

   section "The End"
}

# ###########################################################################
# End report_system_info package
# ###########################################################################

# ##############################################################################
# The main() function is called at the end of the script.  This makes it
# testable.  Major bits of parsing are separated into functions for testability.
# ##############################################################################
main () { local PTFUNCNAME=main;
   trap sigtrap HUP INT TERM

   local RAN_WITH="--sleep=$OPT_SLEEP --save-samples=$OPT_SAVE_SAMPLES --read-samples=$OPT_READ_SAMPLES"

   # Begin by setting the $PATH to include some common locations that are not
   # always in the $PATH, including the "sbin" locations, and some common
   # locations for proprietary management software, such as RAID controllers.
   export PATH="${PATH}:/usr/local/bin:/usr/bin:/bin:/usr/libexec"
   export PATH="${PATH}:/usr/local/sbin:/usr/sbin:/sbin"
   export PATH="${PATH}:/usr/StorMan/:/opt/MegaRAID/MegaCli/"

   setup_commands

   _d "Starting $0 $RAN_WITH"

   # Set up temporary files.
   mk_tmpdir

   local data_dir="$(setup_data_dir "${OPT_SAVE_SAMPLES:-""}")"

   if [ -n "$OPT_READ_SAMPLES" -a -d "$OPT_READ_SAMPLES" ]; then
      data_dir="$OPT_READ_SAMPLES"
   else
      collect_system_data "$data_dir" 2>"$data_dir/collect.err"
   fi

   report_system_summary "$data_dir"

   rm_tmpdir
}

sigtrap() { local PTFUNCNAME=sigtrap;
   warn "Caught signal, forcing exit"
   rm_tmpdir
   exit $EXIT_STATUS
}

# Execute the program if it was not included from another file.  This makes it
# possible to include without executing, and thus test.
if    [ "${0##*/}" = "$TOOL" ] \
   || [ "${0##*/}" = "bash" -a "$_" = "$0" ]; then

   # Set up temporary dir.
   mk_tmpdir
   # Parse command line options.
   parse_options "$0" "$@"
   usage_or_errors "$0"
   po_status=$?
   rm_tmpdir

   if [ $po_status -ne 0 ]; then
      exit $po_status
   fi

   main "$@"
fi


# ############################################################################
# Documentation
# ############################################################################
:<<'DOCUMENTATION'
=pod

=head1 NAME

pt-summary - Summarize system information nicely.

=head1 SYNOPSIS

Usage: pt-summary

pt-summary conveniently summarizes the status and configuration of a server.
It is not a tuning tool or diagnosis tool.  It produces a report that is easy
to diff and can be pasted into emails without losing the formatting.  This
tool works well on many types of Unix systems.

Download and run:

   wget http://percona.com/get/pt-summary
   bash ./pt-summary

=head1 RISKS

The following section is included to inform users about the potential risks,
whether known or unknown, of using this tool.  The two main categories of risks
are those created by the nature of the tool (e.g. read-only tools vs. read-write
tools) and those created by bugs.

pt-summary is a read-only tool.  It should be very low-risk.

At the time of this release, we know of no bugs that could harm users.

The authoritative source for updated information is always the online issue
tracking system.  Issues that affect this tool will be marked as such.  You can
see a list of such issues at the following URL:
L<http://www.percona.com/bugs/pt-summary>.

See also L<"BUGS"> for more information on filing bugs and getting help.

=head1 DESCRIPTION

pt-summary runs a large variety of commands to inspect system status and
configuration, saves the output into files in a temporary directory, and
then runs Unix commands on these results to format them nicely.  It works
best when executed as a privileged user, but will also work without privileges,
although some output might not be possible to generate without root.

=head1 OUTPUT

Many of the outputs from this tool are deliberately rounded to show their
magnitude but not the exact detail. This is called fuzzy-rounding. The idea is
that it doesn't matter whether a particular counter is 918 or 921; such a small
variation is insignificant, and only makes the output hard to compare to other
servers. Fuzzy-rounding rounds in larger increments as the input grows. It
begins by rounding to the nearest 5, then the nearest 10, nearest 25, and then
repeats by a factor of 10 larger (50, 100, 250), and so on, as the input grows.

The following is a simple report generated from a CentOS virtual machine,
broken into sections with commentary following each section. Some long lines
are reformatted for clarity when reading this documentation as a manual page in
a terminal.

 # Percona Toolkit System Summary Report ######################
         Date | 2012-03-30 00:58:07 UTC (local TZ: EDT -0400)
     Hostname | localhost.localdomain
       Uptime | 20:58:06 up 1 day, 20 min, 1 user,
                load average: 0.14, 0.18, 0.18
       System | innotek GmbH; VirtualBox; v1.2 ()
  Service Tag | 0
     Platform | Linux
      Release | CentOS release 5.5 (Final)
       Kernel | 2.6.18-194.el5
 Architecture | CPU = 32-bit, OS = 32-bit
    Threading | NPTL 2.5
     Compiler | GNU CC version 4.1.2 20080704 (Red Hat 4.1.2-48).
      SELinux | Enforcing
  Virtualized | VirtualBox

This section shows the current date and time, and a synopsis of the server and
operating system.

 # Processor ##################################################
   Processors | physical = 1, cores = 0, virtual = 1, hyperthreading = no
       Speeds | 1x2510.626
       Models | 1xIntel(R) Core(TM) i5-2400S CPU @ 2.50GHz
       Caches | 1x6144 KB

This section is derived from F</proc/cpuinfo>.

 # Memory #####################################################
        Total | 503.2M
         Free | 29.0M
         Used | physical = 474.2M, swap allocated = 1.0M,
                swap used = 16.0k, virtual = 474.3M
      Buffers | 33.9M
       Caches | 262.6M
        Dirty | 396 kB
      UsedRSS | 201.9M
   Swappiness | 60
  DirtyPolicy | 40, 10
  Locator  Size  Speed    Form Factor  Type    Type Detail
  =======  ====  =====    ===========  ====    ===========

Information about memory is gathered from C<free>. The Used statistic is the
total of the rss sizes displayed by C<ps>. The Dirty statistic for the cached
value comes from F</proc/meminfo>. On Linux, the swappiness settings are
gathered from C<sysctl>. The final portion of this section is a table of the
DIMMs, which comes from C<dmidecode>. In this example there is no output.

 # Mounted Filesystems ########################################
   Filesystem                       Size Used Type  Opts Mountpoint
   /dev/mapper/VolGroup00-LogVol00   15G  17% ext3  rw   /
   /dev/sda1                         99M  13% ext3  rw   /boot
   tmpfs                            252M   0% tmpfs rw   /dev/shm

The mounted filesystem section is a combination of information from C<mount> and
C<df>. This section is skipped if you disable L<"--summarize-mounts">.

 # Disk Schedulers And Queue Size #############################
         dm-0 | UNREADABLE
         dm-1 | UNREADABLE
          hdc | [cfq] 128
          md0 | UNREADABLE
          sda | [cfq] 128

The disk scheduler information is extracted from the F</sys> filesystem in
Linux.

 # Disk Partioning ############################################
 Device       Type      Start        End               Size
 ============ ==== ========== ========== ==================
 /dev/sda     Disk                              17179869184
 /dev/sda1    Part          1         13           98703360
 /dev/sda2    Part         14       2088        17059230720

Information about disk partitioning comes from C<fdisk -l>.

 # Kernel Inode State #########################################
 dentry-state | 10697 8559  45 0  0  0
      file-nr | 960   0  50539
     inode-nr | 14059 8139

These lines are from the files of the same name in the F</proc/sys/fs>
directory on Linux. Read the C<proc> man page to learn about the meaning of
these files on your system.

 # LVM Volumes ################################################
 LV       VG         Attr   LSize   Origin Snap% Move Log Copy% Convert
 LogVol00 VolGroup00 -wi-ao 269.00G                                      
 LogVol01 VolGroup00 -wi-ao   9.75G   

This section shows the output of C<lvs>.

 # RAID Controller ############################################
   Controller | No RAID controller detected

The tool can detect a variety of RAID controllers by examining C<lspci> and
C<dmesg> information. If the controller software is installed on the system, in
many cases it is able to execute status commands and show a summary of the RAID
controller's status and configuration. If your system is not supported, please
file a bug report.

 # Network Config #############################################
   Controller | Intel Corporation 82540EM Gigabit Ethernet Controller
  FIN Timeout | 60
   Port Range | 61000

The network controllers attached to the system are detected from C<lspci>. The
TCP/IP protocol configuration parameters are extracted from C<sysctl>. You can skip this section by disabling the L<"--summarize-network"> option.

 # Interface Statistics #######################################
 interface rx_bytes rx_packets rx_errors tx_bytes tx_packets tx_errors
 ========= ======== ========== ========= ======== ========== =========
 lo        60000000      12500         0 60000000      12500         0
 eth0      15000000      80000         0  1500000      10000         0
 sit0             0          0         0        0          0         0

Interface statistics are gathered from C<ip -s link> and are fuzzy-rounded. The
columns are received and transmitted bytes, packets, and errors.  You can skip
this section by disabling the L<"--summarize-network"> option.

 # Network Connections ########################################
   Connections from remote IP addresses
     127.0.0.1           2
   Connections to local IP addresses
     127.0.0.1           2
   Connections to top 10 local ports
     38346               1
     60875               1
   States of connections
     ESTABLISHED         5
     LISTEN              8

This section shows a summary of network connections, retrieved from C<netstat>
and "fuzzy-rounded" to make them easier to compare when the numbers grow large.
There are two sub-sections showing how many connections there are per origin
and destination IP address, and a sub-section showing the count of ports in
use.  The section ends with the count of the network connections' states.  You
can skip this section by disabling the L<"--summarize-network"> option.

 # Top Processes ##############################################
   PID USER  PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
     1 root  15   0  2072  628  540 S  0.0  0.1   0:02.55 init
     2 root  RT  -5     0    0    0 S  0.0  0.0   0:00.00 migration/0
     3 root  34  19     0    0    0 S  0.0  0.0   0:00.03 ksoftirqd/0
     4 root  RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/0
     5 root  10  -5     0    0    0 S  0.0  0.0   0:00.97 events/0
     6 root  10  -5     0    0    0 S  0.0  0.0   0:00.00 khelper
     7 root  10  -5     0    0    0 S  0.0  0.0   0:00.00 kthread
    10 root  10  -5     0    0    0 S  0.0  0.0   0:00.13 kblockd/0
    11 root  20  -5     0    0    0 S  0.0  0.0   0:00.00 kacpid
 # Notable Processes ##########################################
   PID    OOM    COMMAND
  2028    +0    sshd

This section shows the first few lines of C<top> so that you can see what
processes are actively using CPU time.  The notable processes include the SSH
daemon and any process whose out-of-memory-killer priority is set to 17. You
can skip this section by disabling the L<"--summarize-processes"> option.

 # Simplified and fuzzy rounded vmstat (wait please) ##########
   procs  ---swap-- -----io---- ---system---- --------cpu--------
    r  b    si   so    bi    bo     ir     cs  us  sy  il  wa  st
    2  0     0    0     3    15     30    125   0   0  99   0   0
    0  0     0    0     0     0   1250    800   6  10  84   0   0
    0  0     0    0     0     0   1000    125   0   0 100   0   0
    0  0     0    0     0     0   1000    125   0   0 100   0   0
    0  0     0    0     0   450   1000    125   0   1  88  11   0
 # The End ####################################################

This section is a trimmed-down sample of C<vmstat 1 5>, so you can see the
general status of the system at present. The values in the table are
fuzzy-rounded, except for the CPU columns.  You can skip this section by
disabling the L<"--summarize-processes"> option.

=head1 OPTIONS

=over

=item --config

type: string

Read this comma-separated list of config files.  If specified, this must be the
first option on the command line.

=item --help

Print help and exit.

=item --save-samples

type: string

Save the collected data in this directory.

=item --read-samples

type: string

Create a report from the files in this directory.

=item --summarize-mounts

default: yes; negatable: yes

Report on mounted filesystems and disk usage.

=item --summarize-network

default: yes; negatable: yes

Report on network controllers and configuration.

=item --summarize-processes

default: yes; negatable: yes

Report on top processes and C<vmstat> output.

=item --sleep

type: int; default: 5

How long to sleep when gathering samples from vmstat.

=item --version

Print tool's version and exit.

=back

=head1 SYSTEM REQUIREMENTS

This tool requires the Bourne shell (F</bin/sh>).

=head1 BUGS

For a list of known bugs, see L<http://www.percona.com/bugs/pt-summary>.

Please report bugs at L<https://bugs.launchpad.net/percona-toolkit>.
Include the following information in your bug report:

=over

=item * Complete command-line used to run the tool

=item * Tool L<"--version">

=item * MySQL version of all servers involved

=item * Output from the tool including STDERR

=item * Input files (log/dump/config files, etc.)

=back

If possible, include debugging output by running the tool with C<PTDEBUG>;
see L<"ENVIRONMENT">.

=head1 DOWNLOADING

Visit L<http://www.percona.com/software/percona-toolkit/> to download the
latest release of Percona Toolkit.  Or, get the latest release from the
command line:

   wget percona.com/get/percona-toolkit.tar.gz

   wget percona.com/get/percona-toolkit.rpm

   wget percona.com/get/percona-toolkit.deb

You can also get individual tools from the latest release:

   wget percona.com/get/TOOL

Replace C<TOOL> with the name of any tool.

=head1 AUTHORS

Baron Schwartz and Kevin van Zonneveld (http://kevin.vanzonneveld.net)

=head1 ABOUT PERCONA TOOLKIT

This tool is part of Percona Toolkit, a collection of advanced command-line
tools developed by Percona for MySQL support and consulting.  Percona Toolkit
was forked from two projects in June, 2011: Maatkit and Aspersa.  Those
projects were created by Baron Schwartz and developed primarily by him and
Daniel Nichter, both of whom are employed by Percona.  Visit
L<http://www.percona.com/software/> for more software developed by Percona.

=head1 COPYRIGHT, LICENSE, AND WARRANTY

This program is copyright 2010-2011 Baron Schwartz, 2011-2012 Percona Inc.
Feedback and improvements are welcome.

THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.

This program is free software; you can redistribute it and/or modify it under
the terms of the GNU General Public License as published by the Free Software
Foundation, version 2; OR the Perl Artistic License.  On UNIX and similar
systems, you can issue `man perlgpl' or `man perlartistic' to read these
licenses.

You should have received a copy of the GNU General Public License along with
this program; if not, write to the Free Software Foundation, Inc., 59 Temple
Place, Suite 330, Boston, MA  02111-1307  USA.

=head1 VERSION

pt-summary 2.1.1

=cut

DOCUMENTATION

pt2mm – Perl Script to slurp pt-summary and make xml ready for Freemind

Filed under: Freemind, linux, Percona Toolkit, perl — lancevermilion @ 4:03 pm

I copied the pt-summary script from the Percona Toolkit and put it in the post below in case you don’t want to install the whole toolkit.

Percona Toolkit – pt-summary shell script
https://gheeknet.wordpress.com/?p=518

I wrote this so someone can do a poor mans manually scripted inventory / documentation of your Linux server(s) (RHEL based). The output from this script will create a nicely formatted XML that can be dropped right into a Freemind file.

Find your .mm file that you will use in Freemind (pick your location in the file and copy/paste the node info) or create a clean one like the example below.

Example:

<map version="0.9.0">
<!-- To view this file, download free mind mapping software FreeMind from http://freemind.sourceforge.net -->
<node COLOR="#338800" CREATED="1335892427459" ID="ID_1651254375" MODIFIED="1335914182567" TEXT="My Linux Servers">
  <node CREATED="1336084328712" FOLDED="true" ID="ID_545525996" STYLE="fork" TEXT="MON01">
    <node CREATED="1336084330688" FOLDED="true" ID="ID_565609429"  TEXT="System Summary">
      <node CREATED="1336084330688" FOLDED="true" ID="ID_392279737"  TEXT="System Summary">
        <attribute_layout NAME_WIDTH="79" VALUE_WIDTH="450"/>
        <attribute NAME="        Date" VALUE="2012-05-03 22:32:10 UTC (local TZ: MST -0700)"/>
        <attribute NAME="    Hostname" VALUE="centos54.linux.domain"/>
        <attribute NAME="      Uptime" VALUE="41 days, 23:39,  1 user,  load average: 0.68, 0.38, 0.31"/>
        <attribute NAME="      System" VALUE="Dell Inc.; PowerEdge 1950; vNot Specified (<OUT OF SPEC>)"/>
        <attribute NAME=" Service Tag" VALUE="some service tag id"/>
        <attribute NAME="    Platform" VALUE="Linux"/>
        <attribute NAME="     Release" VALUE="CentOS release 5.4 (Final)"/>
        <attribute NAME="      Kernel" VALUE="2.6.18-164.el5PAE"/>
        <attribute NAME="Architecture" VALUE="CPU = 64-bit, OS = 32-bit"/>
        <attribute NAME="   Threading" VALUE="NPTL 2.5"/>
        <attribute NAME="    Compiler" VALUE="GNU CC version 4.1.2 20080704 (Red Hat 4.1.2-44)."/>
        <attribute NAME="     SELinux" VALUE="Permissive"/>
        <attribute NAME=" Virtualized" VALUE="No virtualization detected"/>
      </node>
    </node>
    <node CREATED="1336084330689" FOLDED="true" ID="ID_9081042188"  TEXT="Processor">
      <node CREATED="1336084330689" FOLDED="true" ID="ID_3133169832"  TEXT="Processor">
        <attribute_layout NAME_WIDTH="79" VALUE_WIDTH="450"/>
        <attribute NAME="  Processors" VALUE="physical = 1, cores = 4, virtual = 4, hyperthreading = no"/>
        <attribute NAME="      Speeds" VALUE="4x1995.049"/>
        <attribute NAME="      Models" VALUE="4xIntel(R) Xeon(R) CPU E5335 @ 2.00GHz"/>
        <attribute NAME="      Caches" VALUE="4x4096 KB"/>
      </node>
    </node>
    <node CREATED="1336084330689" FOLDED="true" ID="ID_4516226642" STYLE="fork" TEXT="Memory">
      <node CREATED="1336084330689" FOLDED="true" ID="ID_9303160679" TEXT="Memory Summary">
        <node CREATED="1336084330689" FOLDED="true" ID="ID_6187080024" TEXT="Memory Summary">
          <attribute_layout NAME_WIDTH="79" VALUE_WIDTH="450"/>
          <attribute NAME="       Total" VALUE="2.0G"/>
          <attribute NAME="        Free" VALUE="81.9M"/>
          <attribute NAME="        Used" VALUE="physical = 1.9G, swap allocated = 2.0G, swap used = 780.0k, virtual = 1.9G"/>
          <attribute NAME="     Buffers" VALUE="183.9M"/>
          <attribute NAME="      Caches" VALUE="777.1M"/>
          <attribute NAME="       Dirty" VALUE="6316 kB"/>
          <attribute NAME="     UsedRSS" VALUE="1.8G"/>
          <attribute NAME="  Swappiness" VALUE="60"/>
          <attribute NAME=" DirtyPolicy" VALUE="40, 10"/>
        </node>
      </node>
      <node CREATED="1336084330689" FOLDED="true" ID="ID_7505443526" TEXT="Memory Banks">
        <node CREATED="1336084330689" FOLDED="true" ID="ID_2992848714" TEXT="Memory Banks">
          <attribute_layout NAME_WIDTH="79" VALUE_WIDTH="450"/>
          <attribute NAME="Locator" VALUE="   Size     Speed             Form Factor   Type          Type Detail"/>
          <attribute NAME="DIMM1" VALUE="     1024 MB  667 MHz           FB-DIMM       DDR2 FB-DIMM  Synchronous"/>
          <attribute NAME="DIMM2" VALUE="     1024 MB  667 MHz           FB-DIMM       DDR2 FB-DIMM  Synchronous"/>
          <attribute NAME="DIMM3" VALUE="     {EMPTY}  Unknown           FB-DIMM       DDR2 FB-DIMM  Synchronous"/>
          <attribute NAME="DIMM4" VALUE="     {EMPTY}  Unknown           FB-DIMM       DDR2 FB-DIMM  Synchronous"/>
          <attribute NAME="DIMM5" VALUE="     {EMPTY}  Unknown           FB-DIMM       DDR2 FB-DIMM  Synchronous"/>
          <attribute NAME="DIMM6" VALUE="     {EMPTY}  Unknown           FB-DIMM       DDR2 FB-DIMM  Synchronous"/>
          <attribute NAME="DIMM7" VALUE="     {EMPTY}  Unknown           FB-DIMM       DDR2 FB-DIMM  Synchronous"/>
          <attribute NAME="DIMM8" VALUE="     {EMPTY}  Unknown           FB-DIMM       DDR2 FB-DIMM  Synchronous"/>
        </node>
      </node>
    </node>
    <node CREATED="1336084330689" FOLDED="true" ID="ID_5249004985" TEXT="Mounted Filesystems">
      <node CREATED="1336084330689" FOLDED="true" ID="ID_7641022705" TEXT="Mounted Filesystems">
        <attribute_layout NAME_WIDTH="79" VALUE_WIDTH="450"/>
        <attribute NAME="Mountpoint" VALUE="Filesystem, Size, Used, Type, Opts"/>
        <attribute NAME="/boot" VALUE="/dev/sda1, 99M, 18%, ext3, rw,nosuid"/>
        <attribute NAME="/var" VALUE="/dev/sda3, 21G, 40%, ext3, rw"/>
        <attribute NAME="/tmp" VALUE="/dev/sda5, 494M, 24%, ext3, rw"/>
        <attribute NAME="/home" VALUE="/dev/sda6, 1.9G, 53%, ext3, rw,nosuid"/>
        <attribute NAME="/" VALUE="/dev/sda7, 3.8G, 33%, ext3, rw"/>
        <attribute NAME="/usr" VALUE="/dev/sda8, 3.8G, 35%, ext3, rw"/>
        <attribute NAME="/dev/shm" VALUE="tmpfs, 1014M, 0%, tmpfs, rw"/>
      </node>
    </node>
    <node CREATED="1336084330689" FOLDED="true" ID="ID_5445514046" STYLE="fork" TEXT="Network">
      <node CREATED="1336084330699" FOLDED="true" ID="ID_931470206" TEXT="bond0">
        <node CREATED="1336084330699" FOLDED="true" ID="ID_4868000148" TEXT="bond0">
          <attribute_layout NAME_WIDTH="79" VALUE_WIDTH="450"/>
          <attribute NAME="MIITOOL" VALUE="10 Mbit, half duplex, link ok"/>
          <attribute NAME="ONBOOT" VALUE="yes"/>
          <attribute NAME="IPADDR" VALUE="10.0.1.70"/>
        </node>
      </node>
      <node CREATED="1336084330708" FOLDED="true" ID="ID_3056584660" TEXT="bond1">
        <node CREATED="1336084330708" FOLDED="true" ID="ID_5465277235" TEXT="bond1">
          <attribute_layout NAME_WIDTH="79" VALUE_WIDTH="450"/>
          <attribute NAME="MIITOOL" VALUE="10 Mbit, half duplex, link ok"/>
          <attribute NAME="ONBOOT" VALUE="yes"/>
          <attribute NAME="IPADDR" VALUE="10.0.6.7"/>
        </node>
      </node>
      <node CREATED="1336084330717" FOLDED="true" ID="ID_5479778297" TEXT="eth0">
        <node CREATED="1336084330717" FOLDED="true" ID="ID_5414246572" TEXT="eth0">
          <attribute_layout NAME_WIDTH="79" VALUE_WIDTH="450"/>
          <attribute NAME="MIITOOL" VALUE="negotiated, link ok"/>
          <attribute NAME="HWADDR" VALUE="00:19:B9:E6:2B:31"/>
          <attribute NAME="ONBOOT" VALUE="yes"/>
          <attribute NAME="MASTER" VALUE="bond0"/>
        </node>
      </node>
      <node CREATED="1336084330726" FOLDED="true" ID="ID_6661200172" TEXT="eth1">
        <node CREATED="1336084330726" FOLDED="true" ID="ID_2473564425" TEXT="eth1">
          <attribute_layout NAME_WIDTH="79" VALUE_WIDTH="450"/>
          <attribute NAME="MIITOOL" VALUE="negotiated, link ok"/>
          <attribute NAME="HWADDR" VALUE="00:19:B9:E6:2B:33"/>
          <attribute NAME="ONBOOT" VALUE="yes"/>
          <attribute NAME="MASTER" VALUE="bond1"/>
        </node>
      </node>
      <node CREATED="1336084330734" FOLDED="true" ID="ID_6276371651" TEXT="eth2">
        <node CREATED="1336084330734" FOLDED="true" ID="ID_7696366231" TEXT="eth2">
          <attribute_layout NAME_WIDTH="79" VALUE_WIDTH="450"/>
          <attribute NAME="MIITOOL" VALUE="10 Mbit, half duplex, no link"/>
          <attribute NAME="HWADDR" VALUE="00:15:17:2E:B6:CE"/>
          <attribute NAME="ONBOOT" VALUE="no"/>
        </node>
      </node>
      <node CREATED="1336084330742" FOLDED="true" ID="ID_2722883920" TEXT="eth3">
        <node CREATED="1336084330742" FOLDED="true" ID="ID_1804908795" TEXT="eth3">
          <attribute_layout NAME_WIDTH="79" VALUE_WIDTH="450"/>
          <attribute NAME="MIITOOL" VALUE="10 Mbit, half duplex, no link"/>
          <attribute NAME="HWADDR" VALUE="00:15:17:2E:B6:CF"/>
          <attribute NAME="ONBOOT" VALUE="no"/>
        </node>
      </node>
    </node>
    <node CREATED="1336084330743" FOLDED="true" ID="ID_5436431323" TEXT="Applications">
      <node CREATED="1336084330743" FOLDED="true" ID="ID_9595296346" TEXT="Applications">
        <attribute_layout NAME_WIDTH="125" VALUE_WIDTH="450"/>
        <attribute NAME="aide" VALUE="RPM: aide-0.13.1-6.el5 InstallDate: Wed 04 Apr 2012 11:04:38 AM MST"/>
        <attribute NAME="bind-libs" VALUE="RPM: bind-libs-9.3.6-4.P1.el5 InstallDate: Thu 08 Dec 2011 08:16:01 PM MST"/>
        <attribute NAME="expect" VALUE="RPM: expect-5.43.0-5.1 InstallDate: Thu 08 Dec 2011 08:15:09 PM MST"/>
        <attribute NAME="iplike" VALUE="RPM: iplike-1.0.9-1 InstallDate: Fri 09 Dec 2011 04:10:44 PM MST"/>
        <attribute NAME="java-1.6.0-openjdk" VALUE="RPM: java-1.6.0-openjdk-1.6.0.0-1.25.1.10.6.el5_8 InstallDate: Mon 19 Mar 2012 05:54:07 PM MST"/>
        <attribute NAME="kernel-PAE" VALUE="RPM: kernel-PAE-2.6.18-164.el5 InstallDate: Thu 08 Dec 2011 08:18:02 PM MST"/>
        <attribute NAME="libpcap" VALUE="RPM: libpcap-0.9.4-14.el5 InstallDate: Thu 08 Dec 2011 08:16:02 PM MST"/>
        <attribute NAME="McAfeeVSEForLinux" VALUE="RPM: McAfeeVSEForLinux-1.7.0-28611 InstallDate: Mon 19 Mar 2012 06:06:42 PM MST"/>
        <attribute NAME="MFEcma" VALUE="RPM: MFEcma-4.6.0-1694 InstallDate: Mon 19 Mar 2012 06:02:44 PM MST"/>
        <attribute NAME="MFErt" VALUE="RPM: MFErt-2.0-0 InstallDate: Mon 19 Mar 2012 06:02:43 PM MST"/>
        <attribute NAME="minicom" VALUE="RPM: minicom-2.1-3 InstallDate: Mon 26 Mar 2012 06:18:35 PM MST"/>
        <attribute NAME="net-snmp-utils" VALUE="RPM: net-snmp-utils-5.3.2.2-14.el5_7.1 InstallDate: Mon 06 Feb 2012 09:59:29 AM MST"/>
        <attribute NAME="ntp" VALUE="RPM: ntp-4.2.2p1-9.el5.centos.2 InstallDate: Thu 08 Dec 2011 08:16:38 PM MST"/>
        <attribute NAME="opennms" VALUE="RPM: opennms-1.8.4-1 InstallDate: Thu 08 Dec 2011 08:17:51 PM MST"/>
        <attribute NAME="opennms-core" VALUE="RPM: opennms-core-1.8.4-1 InstallDate: Thu 08 Dec 2011 08:17:10 PM MST"/>
        <attribute NAME="opennms-docs" VALUE="RPM: opennms-docs-1.8.4-1 InstallDate: Thu 08 Dec 2011 08:15:35 PM MST"/>
        <attribute NAME="opennms-plugin-provisioning-rancid" VALUE="RPM: opennms-plugin-provisioning-rancid-1.8.4-1 InstallDate: Thu 08 Dec 2011 08:17:31 PM MST"/>
        <attribute NAME="opennms-webapp-jetty" VALUE="RPM: opennms-webapp-jetty-1.8.4-1 InstallDate: Thu 08 Dec 2011 08:17:30 PM MST"/>
        <attribute NAME="openssh" VALUE="RPM: openssh-4.3p2-36.el5 InstallDate: Thu 08 Dec 2011 08:17:33 PM MST"/>
        <attribute NAME="openssh-server" VALUE="RPM: openssh-server-4.3p2-36.el5 InstallDate: Thu 08 Dec 2011 08:17:39 PM MST"/>
        <attribute NAME="openssl" VALUE="RPM: openssl-0.9.8e-12.el5 InstallDate: Thu 08 Dec 2011 08:15:44 PM MST"/>
        <attribute NAME="postgresql-server" VALUE="RPM: postgresql-server-8.4.3-1PGDG.rhel5 InstallDate: Thu 08 Dec 2011 08:17:36 PM MST"/>
        <attribute NAME="rancid" VALUE="RPM: rancid-2.3.4-1 InstallDate: Thu 08 Dec 2011 08:16:06 PM MST"/>
        <attribute NAME="security-blanket" VALUE="RPM: security-blanket-4.0.8-r17082.el5 InstallDate: Mon 19 Mar 2012 06:08:18 PM MST"/>
        <attribute NAME="syslog-ng-premium-edition" VALUE="RPM: syslog-ng-premium-edition-3.0.5-1.rhel5 InstallDate: Tue 20 Dec 2011 01:58:31 PM MST"/>
        <attribute NAME="viewvc" VALUE="RPM: viewvc-1.0.12-1.el5.rf InstallDate: Thu 08 Dec 2011 08:17:54 PM MST"/>
      </node>
    </node>
  </node>
</node>

Here is the actual script “pt2mm.pl” that does all the work.

#!/usr/bin/perl
# Author: Lance Vermilion <scripting(a)gheek.net>
# Purpose: Convert output from pt-summary (Percona Toolkit script)
#          to xml that is then directly able to be pasted into the 
#          <freemind_file>.mm.
# Date: May 2, 2012
# Version: 0.1
use strict;
use warnings;
use Sys::Hostname;
use Time::HiRes qw(gettimeofday);

# The applist is the only configurable section here. Leave everythign else alone
my @applist = ( 'McAfeeVSEForLinux','MFEcma','MFErt','mpr','syslog-ng-premium-edition','java-1.6.0-openjdk','heartbeat','security-blanket','tomcat5','tomcat6','mysql-server','postgresql-server','drbd','kernel-PAE','openssh','openssh-server','openssl','kmod-drbd-PAE','bind-chroot','bind','bind-libs','net-snmp-utils','kmod-drbd','caching-nameserver','opennms','opennms-core','opennms-webapp-jetty','opennms-docs','opennms-plugin-provisioning-rancid','iplike','minicom','expect','aide','ntp','viewvc','rancid','libpcap','libpcap-devel' );

my $node_started = 0;
my $begin_skip = 0;
my $tmpsection = '';
my $host = hostname;
$host =~ s/\..*$//g;
my @network = `egrep "^DEVICE|^HWADDR|^IPADDR|^MASTER|^ONBOOT" /etc/sysconfig/network-scripts/ifcfg-[eb]*`;
my @allrpm = `rpm -qa --queryformat '%{name}|RPM: %{name}-%{version}-%{release} InstallDate: %{installtime:date}\n'`;


my $pad2 = "  ";
my $pad4 = "    ";
my $pad6 = "      ";
my $pad8 = "        ";
my $pad10 = "          ";

print "$pad2<node CREATED=\"" . int (gettimeofday * 1000) . "\" FOLDED=\"true\" ID=\"ID_" . int(rand(10000000000)) . "\" STYLE=\"fork\" TEXT=\"".uc($host)."\">\n";
for my $line (`sudo pt-summary --no-summarize-processes --no-summarize-network`)
{
  chomp ($line);
  if ( $line =~ /^# Disk Schedulers And Queue Size/ ) 
  {
    $begin_skip = 1;
  }
  elsif ( $line =~ /^# (.*) #+/ && $begin_skip != 1 ) 
  {
    my $SECTION_HEADER = $1;
    $SECTION_HEADER =~ s/Percona Toolkit //g;
    $SECTION_HEADER =~ s/ Report//g;
    my $STYLE='';

    if ( $SECTION_HEADER =~ /System Summary/ )
    {
      print "$pad4<node CREATED=\"" . int (gettimeofday * 1000) . "\" FOLDED=\"true\" ID=\"ID_" . int(rand(10000000000)) . "\" $STYLE TEXT=\"$SECTION_HEADER\">\n";
      print "$pad6<node CREATED=\"" . int (gettimeofday * 1000) . "\" FOLDED=\"true\" ID=\"ID_" . int(rand(10000000000)) . "\" $STYLE TEXT=\"$SECTION_HEADER\">\n";
      print "$pad8<attribute_layout NAME_WIDTH=\"79\" VALUE_WIDTH=\"450\"/>\n";
    }
    elsif ( $SECTION_HEADER =~ /Processor/ )
    {
      if ( $node_started == 1 )
      {
        print "$pad6</node>\n";
        print "$pad4</node>\n";
      }
      print "$pad4<node CREATED=\"" . int (gettimeofday * 1000) . "\" FOLDED=\"true\" ID=\"ID_" . int(rand(10000000000)) . "\" $STYLE TEXT=\"$SECTION_HEADER\">\n";
      print "$pad6<node CREATED=\"" . int (gettimeofday * 1000) . "\" FOLDED=\"true\" ID=\"ID_" . int(rand(10000000000)) . "\" $STYLE TEXT=\"$SECTION_HEADER\">\n";
      print "$pad8<attribute_layout NAME_WIDTH=\"79\" VALUE_WIDTH=\"450\"/>\n";
    }
    elsif ( $SECTION_HEADER =~ /Memory/ )
    {
      $STYLE='STYLE="fork"';
      print "$pad6</node>\n";
      print "$pad4</node>\n";
      print "$pad4<node CREATED=\"" . int (gettimeofday * 1000) . "\" FOLDED=\"true\" ID=\"ID_" . int(rand(10000000000)) . "\" $STYLE TEXT=\"$SECTION_HEADER\">\n";
      print "$pad6<node CREATED=\"" . int (gettimeofday * 1000) . "\" FOLDED=\"true\" ID=\"ID_" . int(rand(10000000000)) . "\" TEXT=\"$SECTION_HEADER Summary\">\n";
      print "$pad8<node CREATED=\"" . int (gettimeofday * 1000) . "\" FOLDED=\"true\" ID=\"ID_" . int(rand(10000000000)) . "\" TEXT=\"$SECTION_HEADER Summary\">\n";
      print "$pad10<attribute_layout NAME_WIDTH=\"79\" VALUE_WIDTH=\"450\"/>\n";
    }
    elsif ( $SECTION_HEADER =~ /Mounted Filesystems/ )
    {
      print "$pad8</node>\n";
      print "$pad6</node>\n";
      print "$pad4</node>\n";
      print "$pad4<node CREATED=\"" . int (gettimeofday * 1000)  . "\" FOLDED=\"true\" ID=\"ID_" . int(rand(10000000000)) . "\" TEXT=\"$SECTION_HEADER\">\n";
      print "$pad6<node CREATED=\"" . int (gettimeofday * 1000) . "\" FOLDED=\"true\" ID=\"ID_" . int(rand(10000000000)) . "\" TEXT=\"$SECTION_HEADER\">\n";
      print "$pad8<attribute_layout NAME_WIDTH=\"79\" VALUE_WIDTH=\"450\"/>\n";
    }

    $tmpsection = $SECTION_HEADER;
    $node_started = 1;

  }
  elsif ( $line !~ /^# .* #+/ && $begin_skip != 1) 
  {
    if ( $line =~ /(.*) \| (.*)/ && $node_started == 1 )
    {
      my $key = $1;
      my $value = $2;
      if ( $tmpsection !~ /Memory/ )
      {
        print "$pad8<attribute NAME=\"$key\" VALUE=\"$value\"/>\n";
      } else {
        print "$pad10<attribute NAME=\"$key\" VALUE=\"$value\"/>\n";
      }
    }
    elsif ( $line =~ /(Locator)(.*)/ )
    {
      print "$pad8</node>\n";
      print "$pad6</node>\n";
      print "$pad6<node CREATED=\"" . int (gettimeofday * 1000) . "\" FOLDED=\"true\" ID=\"ID_" . int(rand(10000000000)) . "\" TEXT=\"Memory Banks\">\n";
      print "$pad8<node CREATED=\"" . int (gettimeofday * 1000) . "\" FOLDED=\"true\" ID=\"ID_" . int(rand(10000000000)) . "\" TEXT=\"Memory Banks\">\n";
      print "$pad10<attribute_layout NAME_WIDTH=\"79\" VALUE_WIDTH=\"450\"/>\n";
      print "$pad10<attribute NAME=\"$1\" VALUE=\"$2\"/>\n";
    }
    elsif ( $line =~ /(DIMM[0-9])(.*)/ )
    {
      print "$pad10<attribute NAME=\"$1\" VALUE=\"$2\"/>\n";
    }
    elsif ( $line =~ /[0-9]\%|Opts.*Mountpoint/ )
    {
      my ( undef, $fs, $sz, $used, $type, $opts, $mnt ) = split (/\s+/, $line);
      print "$pad8<attribute NAME=\"$mnt\" VALUE=\"$fs, $sz, $used, $type, $opts\"/>\n";
    }
  }
}
print "$pad6</node>\n";
print "$pad4</node>\n";
print "$pad4<node CREATED=\"" . int (gettimeofday * 1000)  . "\" FOLDED=\"true\" ID=\"ID_" . int(rand(10000000000)) . "\" STYLE=\"fork\" TEXT=\"Network\">\n";
my $int_details_started = 0;
for my $int (@network)
{
  $int =~ s/.*DEVICE=/DEVICE=/;
  $int =~ s/.*HWADDR=/HWADDR=/;
  $int =~ s/.*IPADDR=/IPADDR=/;
  $int =~ s/.*MASTER=/MASTER=/;
  $int =~ s/.*ONBOOT=/ONBOOT=/;
  if ( $int =~ /DEVICE=(.*)/ )
  {
    my $DEVICE=$1;
    if ( $int_details_started == 1 )
    {
      print "$pad8</node>\n";
      print "$pad6</node>\n";
    }
    my $miitool = 'interface disabled';
    my $miitooloutput = `sudo /sbin/mii-tool $1 2>/dev/null`;
    $miitool = $miitooloutput if ( $miitooloutput ne '' );
    $miitool =~ s/^(eth|bond)[0-9]: //g;
    chomp($miitool);
    print "$pad6<node CREATED=\"" . int (gettimeofday * 1000) . "\" FOLDED=\"true\" ID=\"ID_" . int(rand(10000000000)) . "\" TEXT=\"$DEVICE\">\n";
    print "$pad8<node CREATED=\"" . int (gettimeofday * 1000) . "\" FOLDED=\"true\" ID=\"ID_" . int(rand(10000000000)) . "\" TEXT=\"$DEVICE\">\n";
    print "$pad10<attribute_layout NAME_WIDTH=\"79\" VALUE_WIDTH=\"450\"/>\n";
    print "$pad10<attribute NAME=\"MIITOOL\" VALUE=\"$miitool\"/>\n";
    $int_details_started = 1;
  }
  elsif ( $int =~ /(HWADDR)=(.*)/ )
  {
    print "$pad10<attribute NAME=\"$1\" VALUE=\"$2\"/>\n";
  }
  elsif ( $int =~ /(IPADDR)=(.*)/ )
  {
    print "$pad10<attribute NAME=\"$1\" VALUE=\"$2\"/>\n";
  }
  elsif ( $int =~ /(MASTER)=(.*)/ )
  {
    print "$pad10<attribute NAME=\"$1\" VALUE=\"$2\"/>\n";
  }
  elsif ( $int =~ /(ONBOOT)=(.*)/ )
  {
    print "$pad10<attribute NAME=\"$1\" VALUE=\"$2\"/>\n";
  }
}
print "$pad8</node>\n";
print "$pad6</node>\n";
print "$pad4</node>\n";
# Get list of Important Applications
print "$pad4<node CREATED=\"" . int (gettimeofday * 1000) . "\" FOLDED=\"true\" ID=\"ID_" . int(rand(10000000000)) . "\" TEXT=\"Applications\">\n";
print "$pad6<node CREATED=\"" . int (gettimeofday * 1000) . "\" FOLDED=\"true\" ID=\"ID_" . int(rand(10000000000)) . "\" TEXT=\"Applications\">\n";
print "$pad8<attribute_layout NAME_WIDTH=\"125\" VALUE_WIDTH=\"450\"/>\n";
for my $app (sort { lc($a) cmp lc($b) } @applist)
{
  for my $rpm (sort { lc($a) cmp lc($b) } @allrpm)
  {
    chomp ( $rpm );
    my ( $appname, $rpmdetail ) = split(/\|/, $rpm);
    print "$pad8<attribute NAME=\"$app\" VALUE=\"$rpmdetail\"/>\n" if ( $app eq $appname );
  }
}
print "$pad6</node>\n";
print "$pad4</node>\n";
print "$pad2</node>\n";

October 13, 2011

Perl to Microsoft SQL Server 2008 Standard via ODBC using FreeTDS Drivers

Filed under: microsoft, ODBC, perl, sql — lancevermilion @ 2:30 pm

Perl to Microsoft SQL Server 2008 Standard via ODBC using FreeTDS

Today I had a need to have a perl script (running on CentOS 5.4) connect to a MSSQL Server 2008 Std server so i had to do a little research and playing around. The solution to getting everything working with “Free/OpenSource” stuff was not difficult if you don’t follow everything that everyone publishes.

I have provided the steps I used below to get everything working.

Updated step 5 on Sep 11, 2012 with a note that reflects the findings of  user xtruthx.

Pre-requisites

Before you start you need to ensure you know how to satisfy all the RPMs in the pre-requisites.
Location from where you can get the RPMs is listed in parentheses ().

  1. RPMs Needed/used:
    perl - 5.8.8-10 (Yum)
    perl-DBI - 1.52-1 (Yum)
    freetds - 0.64-11 (Yum)
    unixODBC-devel - 2.2.11.7.1 (Yum)
    unixODBC - 2.2.11.7.1 (Yum)
    perl-DBD-ODBC - 1.23-1 (http://pkgs.repoforge.org/perl-DBD-ODBC/perl-DBD-ODBC-1.23-1.el5.rf.i386.rpm)
    

Steps to get MSSQL,ODBC and Perl working as one happy product.

  1. How did I get ODBC setup?
    Install freetds, unixODBC, unixODBC-devel via Yum

    sudo yum install freetds unixODBC unixODBC-devel
    

    Then install the perl, perl-DBI if you don’t have them installed already.

    sudo yum install perl perl-DBI
    

    If you haven’t downloaded the perl-DBD-ODBC module you can do so using wget/curl/etc. I prefer wget.
    Then install the perl, perl-DBI if you don’t have them installed already.

    wget http://pkgs.repoforge.org/perl-DBD-ODBC/perl-DBD-ODBC-1.23-1.el5.rf.i386.rpm
    

    Then install the perl-DBD-ODBC module. I am assuming the perl-DBD-ODBC is in the same directory you are working in. If not you will need to provide a complete path to the rpm.

    sudo rpm -ivh perl-DBD-ODBC
    
  2. DBD::ODBC We used DBD::ODBC. You can use similar methods as above to determine if DBD::ODBC is installed and to see what version you have:To check you have the DBD::ODBC module installed:
    perl -e 'use DBD::ODBC;'
    

    If you have not got DBD::ODBC installed you can build it via CPAN or download the RPM and go from there. I just downloaded it, see the RPM list at the top of the article.To show the DBD::ODBC version:

    perl -MDBD::ODBC -e 'print $DBD::ODBC::VERSION;'
    

    e.g.

    perl -MDBD::ODBC -e 'print $DBD::ODBC::VERSION;'
    1.23
    

    To show all drivers DBI knows about and their versions:

    perl -MDBI -e 'DBI->installed_versions;'
    

    e.g.

    perl -MDBI -e 'DBI->installed_versions;'
      Perl            : 5.008008    (i386-linux-thread-multi)
      OS              : linux       (2.6.9-42.0.3.elsmp)
      DBI             : 1.52
      DBD::Sponge     : 11.10
      DBD::Proxy      : install_driver(Proxy) failed: Can't locate RPC/PlClient.pm in @INC
      DBD::ODBC       : 1.23
      DBD::File       : 0.35
      DBD::ExampleP   : 11.12
      DBD::DBM        : 0.03
    
  3. What ODBC drivers have I got?
    You can find out what ODBC drivers are installed under unixODBC with:

    odbcinst -q -d
    

    e.g.

    odbcinst -q -d
    [PostgreSQL]
    

    For unixODBC, drivers are installed in the odbcinst.ini file. You can find out which odbcinst.ini file unixODBC is using with:

    odbcinst -j
    

    e.g.

    odbcinst -j
    unixODBC 2.2.11
    DRIVERS............: /etc/odbcinst.ini
    SYSTEM DATA SOURCES: /etc/odbc.ini
    
  4. Here, /etc/odbcinst.ini defines the ODBC drivers. Configure unixODBC to use FreeTDS as the Driver for the MSSQL connection.
    First we need to locate the driver file.

    sudo updatedb
    locate libtdso
    

    e.g.

    sudo updatedb
    locate libtdso
    /usr/lib/libtdsodbc.so.0
    /usr/lib/libtdsodbc.so.0.0.0
    

    Now we know where the source files are so lets create a driver template to use for driver installation.
    You can use vi if you want, but to make sure people can copy/paste as much as possible I have provided an echo command below.
    Adjust the path and possibly filename for “/usr/lib/libtdsodbc.so.0”.
    Note: Only use the driver that has a single 0 if there is more than one returned like my example.

    echo "[FreeTDS]
    Driver = /usr/lib/libtdsodbc.so.0" >> tds.driver.template
    

    Now lets install the driver from the template. This will install the driver so anyone can use this driver.

    sudo odbcinst -i -d -f tds.driver.template
    

    e.g.

    sudo odbcinst -i -d -f tds.driver.template
    odbcinst: Driver installed. Usage count increased to 1.
        Target directory is /etc
    

    If you check to see what drivers are available to ODBC you will now see “FreeTDS” has been added.

    odbcinst -q -d
    

    e.g.

    odbcinst -q -d
    [PostgreSQL]
    [FreeTDS]
    
  5. Configure ODBC with MSSQL parameters.
    To do this we need to modify the /etc/odbc.ini file.
    Your odbc.ini file could be in a different location. use odbcinst -j to location your ini file.
    You need to adjust the values accordingly for the echo command to work for your install.
    You will want to be root to do the file modification. I jump to root by way of sudo su -.
    Note:You will need a SQL user account setup in MSSQL to allow you to connect remotely with the way I am showing you.Note: A user (xtruthx) has reported that “TDS Version” needed an underscore in order to work for his setup. The user used “TDS_Version = 8.0” instead of what is in the example below.

    sudo su -
    echo "[MSSQL]
    Driver = FreeTDS
    Address = IPADDRESSOFMSSQL
    Port = 1433
    TDS Version = 8.0
    Database = MYDATABASENAME" >> /etc/odbc.ini
    

    e.g.

    sudo su -
    echo "[MSSQL]
    Driver = FreeTDS
    Address = 192.168.10.1
    Port = 1433
    TDS Version = 8.0
    Database = Northwind" >> /etc/odbc.ini
    
  6. Test the ODBCconnect to your MSSQL database.
    isql -v -s MSSQL SQLUSERNAME SQLUSERPASSWORD
    +---------------------------------------+
    | Connected!                            |
    |                                       |
    | sql-statement                         |
    | help [tablename]                      |
    | quit                                  |
    |                                       |
    +---------------------------------------+
    SQL>
    
  7. If the step above was successful then lets try using a Perl script.
    Note: Below I specify DSN=MSSQL, that should match the name in [ ] in your odbc.ini file.
    You will also want to change SQLUSERNAME and SQLUSERNAMEPASSWORD to your username/password needed to connect to MSSQL using SQL username/password.
    This is NOT windows authentication.

    #!/usr/bin/perl
    use DBI;
    use strict;
    use DBI;
    my @dsns = DBI->data_sources('ODBC');
    foreach my $d (@dsns)
    {
      print "$d\n";
    }
      my $dbh = DBI-> connect('dbi:ODBC:DSN=MSSQL;UID=SQLUSERNAME;PWD=SQLUSERPASSWORD') or die "CONNECT ERROR! :: $DBI::err $DBI::errstr $DBI::state $!\n";
    if ($dbh)
    {
      print "There is a connection\n";
      my $sql = q/SELECT * FROM dbo.users/;
      my $sth = $dbh->prepare($sql);
      $sth->execute();
      my @row;
      while (@row = $sth->fetchrow_array) {  # retrieve one row at a time
        print join(", ", @row), "\n";
      }
      $dbh->disconnect;
    }
    

External Reference links used to get everything working.

http://www.unixodbc.org/doc/FreeTDS.html
http://www.martin-evans.me.uk/node/20
http://www.easysoft.com/developer/languages/perl/dbi_dbd_odbc.html
http://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_1.html
http://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_2.html
http://www.easysoft.com/developer/languages/perl/sql_server_unix_tutorial.html
http://www.easysoft.com/developer/languages/perl/tutorial_data_web.html
http://www.easysoft.com/developer/languages/perl/dbi-debugging.html

October 3, 2011

REDCOM DCT Translation Tester using net.db (100% offline)

Filed under: perl, REDCOM — lancevermilion @ 2:23 pm

The other day I shared my script on how to parse Dial Code Tables (DCT) if took the time to convert them to CSV. Well after thinking about it this morning I made a quick change to my code to read in the net.db file (ascii copy of the database) and process routing that way.

Update: I found some coding errors, removed the utilization of external shell commands, and added strict usage.

Redcom version 4.0 R1P1

The Code:

#!/usr/bin/perl
# Author:  Lance Vermilion
# Purpose: Walk through Redcom Dial Code Tables and determine
#          what entry the digits would hit if it was ran
#          through a Redcom.
# Script:  dct.pl
# Date:    10/4/2011
# Rev:     0.4
# Syntax:  dct.pl
# Example: dct.pl dct0 4352348763
#################################################################

#################################################################
# Instructions:
# Downlaod the binary configuration to a ascii format on the
# Redcom.
# rsh /tmp> xld downl;over=yes;downl;exit;log
# Cat the net.db and copy/paste this in a local net.db file in the
# same folder as dct.pl
# cat /sys/net.db
# dct.pl will parse net.db and preform all the work needed just
# as if it was being processed through the Redcom.
#################################################################

#################################################################
# Not supported yet
# TYPE (other than dct, rte, int, stn, sup)
# SC
# PRE  (does support any variation of ac- ac+)
# POS  (does support any variation of ac- ac+)
# MARK (does support any variation of ac- ac+)
# Next (Only jumps to that dct)
# SB
# SST
# TID
#
# Pattern that uses a # in the pattern
#################################################################

use strict;
use Carp;
use Data::Dumper;

my $dctnumber = "$ARGV[0]";
my $dialeddigits = "$ARGV[1]";
my $file = "net.db";

# DCT to be used that is passed by commandline
my $hash_ref = {};
print "Reading \"$file\" file: ";
if ( -f "$file" )
{
  print "[OK]\n";
}
else
{
  print "[FAILED]\n";
  croak "File: \"$file\" does not exist. :: $!\n";
}

# File net.db exists slurp it to an array
open(FH, "$file" ) or die "Could not open $file\n";
my @netdbarr = ;

sub help()
{
  print "Syntax:  dct.pl  \n";
  print "Example: dct.pl dct0 4352348763\n";
  exit;
}

if ( scalar(@ARGV) ne 2 )
{
  print "You didn't provide enough variables\n"
  &help();
}

&buildDCThash();
&processDCThash($dctnumber,$dialeddigits,0,0,0);

# Sub-routine to print DCT
sub printdct ($$$$$$$$)
{
  my $printent  = shift;
  my $printpatt = shift;
  my $printsc   = shift;
  my $printtype = shift;
  my $printval  = shift;
  my $printpre  = shift;
  my $printpos  = shift;
  my $printmark = shift;
  my $printnext = shift;
  my $printsb = shift;
  my $printsst = shift;
  my $printtid = shift;

  print "ENTRY PATTERN         SC TYPE VAL PRE POS MARK NEXT SB/SNU  SST/NST  TID    \n";
format DCTREPORT =
@<<<< @<<<<<<<<<<<<<< @< @<<< @<< @<< @<< @<<< @<<< @<<<<<  @<<<<<<  @<< $printent $printpatt $printsc $printtype $printval $printpre $printpos $printmark $printnext $printsb $printsst $printtid .   $~ = 'DCTREPORT';   write; } # Sub-routine to build our hash sub buildDCThash() {   my $curdct = '';   my $curgrp = '';   my $currte = '';   for my $netdbline (@netdbarr)   {     if ( $netdbline =~ /^_dcta\[ (\d+) \] = (.*)/ )     {       $curdct = "dct$1";       my ($dctname, $dctqty, undef, undef, undef, undef, $dcttoneable, undef, $dctdialtimer) = split(/,/, $2);       $hash_ref->{'dct'}->{$curdct}->{'name'} = $dctname;
      $hash_ref->{'dct'}->{$curdct}->{'qty'} = $dctqty;
      $hash_ref->{'dct'}->{$curdct}->{'tonable'} = $dcttoneable;
      $hash_ref->{'dct'}->{$curdct}->{'dtimer'} = $dctdialtimer;
    }

    $netdbline =~ s/,252,/,,/g if $netdbline =~ /^_dctm/; #252 equals blank
    $netdbline =~ s/220/ac/g if $netdbline =~ /^_dctm/;   #220 equals ac
    if ( $netdbline =~ /^_dctm\[ (\d+) \] = (.*)/ )
    {
      my $ent=$1;
      my ($patt, $type, $val, $next, $sc, $pre, $pos, $mark, $sb, undef, $sst, undef, $tid) = split(/,/, $2);
      $patt = '-default-' if $ent eq 0;  # net.db stored a blank entry for entry 0 aka -default- so we hard set the value.
      $hash_ref->{'dct'}->{$curdct}->{'entries'}->{$ent}->{'origpatt'} = $patt;
      $patt = 'NULL_PATTERN' if $patt eq '';
      $patt =~ s/n/[2-9]/g;
      $patt =~ s/x/[0-9]/g;
      $patt =~ s/q/[0-4]/g;
      $patt =~ s/Q/[5-9]/g;
      $patt =~ s/X/[2-8]/g;
      $patt =~ s/\~/\\d+/g;
      $patt =~ s/\*/\\*/g;
      $patt =~ s/\#/\\#/g;
      $patt =~ s/\?/[0-9]/g;
      $patt =~ s/w//g;
      $hash_ref->{'dct'}->{$curdct}->{'entries'}->{$ent}->{'patt'} = $patt;
      $hash_ref->{'dct'}->{$curdct}->{'entries'}->{$ent}->{'sc'}   = $sc;
      $hash_ref->{'dct'}->{$curdct}->{'entries'}->{$ent}->{'type'} = $type;
      $hash_ref->{'dct'}->{$curdct}->{'entries'}->{$ent}->{'val'}  = $val;
      $hash_ref->{'dct'}->{$curdct}->{'entries'}->{$ent}->{'pre'}  = $pre;
      $hash_ref->{'dct'}->{$curdct}->{'entries'}->{$ent}->{'pos'}  = $pos;
      $hash_ref->{'dct'}->{$curdct}->{'entries'}->{$ent}->{'mark'} = $mark;
      $hash_ref->{'dct'}->{$curdct}->{'entries'}->{$ent}->{'next'} = $next;
      $hash_ref->{'dct'}->{$curdct}->{'entries'}->{$ent}->{'sb'} = $sb;
      $hash_ref->{'dct'}->{$curdct}->{'entries'}->{$ent}->{'sst'} = $sst;
      $tid =~ s/65535//;   # similar to VAL, except that the final value 65535 would be reserved for the "blank" case)
      $hash_ref->{'dct'}->{$curdct}->{'entries'}->{$ent}->{'tid'} = $tid;
    }

    #
    # INCLUDE ROUTE AND GROUP INFORMATION
    #
    if ( $netdbline =~ /^_grpatt\[ (\d+), 1 \] = (.*)/ )
    {
      $curgrp = $1;
      my ( $type1,undef,undef,$grpname,undef,undef,undef ) = split(/,/, $2);
      #print "$group $type1 $grpname\n";
      $hash_ref->{'grp'}->{$curgrp}->{'group'} = $curgrp;
      $hash_ref->{'grp'}->{$curgrp}->{'type1'} = $type1;
      $hash_ref->{'grp'}->{$curgrp}->{'grpname'} = $grpname;
    }

    if ( $netdbline =~ /^_grpatt\[ (\d+), 3 \] = (.*)/ )
    {
      if ( $curgrp eq $1 )
     {
        my ( $type3,$grptype,undef,undef,undef,undef,undef,undef,undef) = split(/,/, $2);
        #print "$group,$type3,$grptype\n";
        $hash_ref->{'grp'}->{$curgrp}->{'type3'} = $type3;
        $hash_ref->{'grp'}->{$curgrp}->{'grptype'} = $grptype;
      }
    }

    my $currte = '';
    if ( $netdbline =~ /^route\[ (\d+), 1 \] = (.*)/ )
    {
      $currte = "rte$1";
      my ( $prefix_digits,undef,undef,undef,$rtename,$alt1,$alt2,$alt3,undef,undef,undef,undef,$catid,undef,undef,undef,undef,undef,undef,undef,$comp,undef,undef,undef,undef,$groupnum,undef,undef,undef,undef,undef,undef,undef ) = split(/,/, $2);
      $comp =~ s/0x0/off/g;
      $comp =~ s/0x40/on/g;
      #print "$route,$prefix_digits,$rtename,$alt1,$alt2,$alt3,$comp,$groupnum\n";
      $hash_ref->{'rte'}->{$currte}->{'prefix_digits'} = $prefix_digits;
      $hash_ref->{'rte'}->{$currte}->{'rtename'} = $rtename;
      $hash_ref->{'rte'}->{$currte}->{'alt1'} = $alt1;
      $hash_ref->{'rte'}->{$currte}->{'alt2'} = $alt2;
      $hash_ref->{'rte'}->{$currte}->{'alt3'} = $alt3;
      $hash_ref->{'rte'}->{$currte}->{'comp'} = $comp;
      $hash_ref->{'rte'}->{$currte}->{'groupnum'} = $groupnum;
    }
  }
}

# Sub-routine to process the hash
sub processDCThash($$$$$)
{
# TYPE (DCT type and val (ie. dct6)
# Digits (digits to be processed)
# Pre (digits to delete from begining of the digit string)
# Pos (digits to delete from end of the digit string)
# Mark (pass all digits, but read from n place in the digit string)

  # TYPE from DCT
  my $TYPE = shift;

  # Since we wrap back on our self to walk DCTs we need to make sure we exit on types of int,etc
  if ( $TYPE =~ /^(int|int\d+)/ )
  {
    print "User will be sent to an intercept message\n";
    print "#" x 73 . "\n";
    exit;
  }
  elsif ( $TYPE =~ /^(rte|rte\d+)/ )
  {
    my $tempTYPE = $TYPE;
    $tempTYPE =~ s/rte//g;
    print "Digits will be sent to:\n";
    print "  Primary Route: Route $tempTYPE $hash_ref->{'rte'}->{$TYPE}->{'rtename'}\n";
    print "    Group $hash_ref->{'rte'}->{$TYPE}->{'groupnum'} $hash_ref->{'grp'}->{$hash_ref->{'rte'}->{$TYPE}->{'groupnum'}}->{'grpname'} Type: $hash_ref->{'grp'}->{$hash_ref->{'rte'}->{$TYPE}->{'groupnum'}}->{'type1'},$hash_ref->{'grp'}->{$hash_ref->{'rte'}->{$TYPE}->{'groupnum'}}->{'type3'}\n";
    print "    Inserted Prefix Digits: $hash_ref->{'rte'}->{$TYPE}->{'prefix_digits'}\n" if $hash_ref->{'rte'}->{$TYPE}->{'prefix_digits'} ne '';
    if ( $hash_ref->{'rte'}->{$TYPE}->{'alt1'} ne 0 )
    {
      my $tmpalt1rte = "rte" . $hash_ref->{'rte'}->{$TYPE}->{'alt1'};
      my $tmpalt1grp = $hash_ref->{'rte'}->{$tmpalt1rte}->{'groupnum'};
      print "  Alternate Route #1: Route $hash_ref->{'rte'}->{$TYPE}->{'alt1'} $hash_ref->{'rte'}->{$tmpalt1rte}->{'rtename'}\n";
      print "    Group $tmpalt1grp $hash_ref->{'grp'}->{$tmpalt1grp}->{'grpname'} Type: $hash_ref->{'grp'}->{$tmpalt1grp}->{'type1'},$hash_ref->{'grp'}->{$tmpalt1grp}->{'type3'}\n";
      print "    Inserted Prefix Digits: $hash_ref->{'rte'}->{$tmpalt1rte}->{'prefix_digits'}\n" if $hash_ref->{'rte'}->{$tmpalt1rte}->{'prefix_digits'} ne '';
    }
    if ( $hash_ref->{'rte'}->{$TYPE}->{'alt2'} ne 0 )
    {
      my $tmpalt2rte = "rte" . $hash_ref->{'rte'}->{$TYPE}->{'alt2'};
      my $tmpalt2grp = $hash_ref->{'rte'}->{$tmpalt2rte}->{'groupnum'};
      print "  Alternate Route #2: Route $hash_ref->{'rte'}->{$TYPE}->{'alt2'} $hash_ref->{'rte'}->{$tmpalt2rte}->{'rtename'}\n";
      print "    Group $tmpalt2grp $hash_ref->{'grp'}->{$tmpalt2grp}->{'grpname'} Type: $hash_ref->{'grp'}->{$tmpalt2grp}->{'type1'},$hash_ref->{'grp'}->{$tmpalt2grp}->{'type3'}\n";
      print "    Inserted Prefix Digits: $hash_ref->{'rte'}->{$tmpalt2rte}->{'prefix_digits'}\n" if $hash_ref->{'rte'}->{$tmpalt2rte}->{'prefix_digits'} ne '';
    }
    if ( $hash_ref->{'rte'}->{$TYPE}->{'alt3'} ne 0 )
    {
      my $tmpalt3rte = "rte" . $hash_ref->{'rte'}->{$TYPE}->{'alt3'};
      my $tmpalt3grp = $hash_ref->{'rte'}->{$tmpalt3rte}->{'groupnum'};
      print "  Alternate Route #3: Route $hash_ref->{'rte'}->{$TYPE}->{'alt3'} $hash_ref->{'rte'}->{$tmpalt3rte}->{'rtename'}\n";
      print "    Group $tmpalt3grp $hash_ref->{'grp'}->{$tmpalt3grp}->{'grpname'} Type: $hash_ref->{'grp'}->{$tmpalt3grp}->{'type1'},$hash_ref->{'grp'}->{$tmpalt3grp}->{'type3'}\n";
      print "    Inserted Prefix Digits: $hash_ref->{'rte'}->{$tmpalt3rte}->{'prefix_digits'}\n" if $hash_ref->{'rte'}->{$tmpalt3rte}->{'prefix_digits'} ne '';
    }
    print "#" x 73 . "\n";
    exit;
  }
  elsif ( $TYPE =~ /^(stn|stn\d+)/ )
  {
    print "Digits will be sent to a local station.\n";
    print "#" x 73 . "\n";
    exit;
  }

  # Account for the fact the user may not provide dct6 and may provide something else.
  if ( $TYPE !~ /^dct\d+$/ )
  {
    print "Error: The Dial Code Table (DCT) you want to start at must be in this format.\n";
    print "       dct\n";
    print "       Example: dct0\n";
    my $loop = 0;
    until ( $TYPE =~ /^dct\d+$/ )
    {
      print "Re-Enter your DCT: ";
      chomp($TYPE = );
      $loop++;
      if ($loop >= 3)
      {
        print "Exiting!!! You failed to enter the DCT correctly 3 times.\n";
        exit;
      }
    }
  }

  # numbers dialed
  my $digits = shift;
  chomp($digits);

  # Adjust digits based on pre, pos, and mark
  my $pre = shift;
  my $pos = shift;
  my $mark = shift;
  $digits = substr($digits, $pre)  if ( $pre > 0 );
  $digits = substr($digits, -$pos) if ( $pos > 0 );
  $digits = substr($digits, $mark)  if ( $mark > 0 );

  my $curdct = '';

  # Create a pattern hash keyed by pattern with entry and value
  my $patt_ref = {};
  for my $key ( sort keys %{$hash_ref->{'dct'}->{$TYPE}->{'entries'}} )
  {
    $patt_ref->{$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$key}->{'origpatt'}} = $key;
  }

  print "\n" . "#" x 73 . "\n";
  print "Processing " . length($digits) . " Digit Dialed Number \"$digits\"\n";

  my $charcnt = 0;

  # Print DCT in really short form
  my $tmpTYPE = $TYPE;
  $tmpTYPE =~ s/dct//g;
  print "DCT Number = $tmpTYPE\n";
  print "DCT Name = $hash_ref->{'dct'}->{$TYPE}->{'name'}\n";
  print "DCT Entry Quantity = $hash_ref->{'dct'}->{$TYPE}->{'qty'}\n";
  print "DCT Toneable = $hash_ref->{'dct'}->{$TYPE}->{'tonable'}\n";
  print "DCT Dial Timer = " . $hash_ref->{'dct'}->{$TYPE}->{'dtimer'}/100 . " seconds\n";
  print "=" x 73 . "\n";
  print "ENTRY PATTERN        PATTERN (as seen by Perl)\n";
  for my $key ( sort { $a  $b } keys %{$hash_ref->{'dct'}->{$TYPE}->{'entries'}} )
  {
    printf ("%-5s %-14s %s\n", $key,$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$key}->{'origpatt'},($hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$key}->{'patt'}));
  }
  print "=" x 73 . "\n";

  # Check for an exact match first
  for my $patt_orig (sort keys %$patt_ref)
  {
    my $dctentry = $patt_ref->{$patt_orig};
    my $patt = $hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'patt'};
    if ( $dctentry eq 0 )
    {
      next;
    }
    if ( $digits =~ /^$patt$/ )
    {
      print " * Found exact match!\n";
      &printdct($dctentry,$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'origpatt'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'sc'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'type'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'val'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'pre'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'pos'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'mark'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'next'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'sb'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'sst'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'tid'});
      print "#" x 73 . "\n";
      if ( $hash_ref->{'dct'}->{$dctentry}->{'type'} eq 'sup' )
      {
        print "User will be sent to a DCT equal to the Next field with Supervision\n";
        print "#" x 73 . "\n";
        $hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'type'} = 'dct';
        $hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'val'} = $hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'next'}
      }
      &processDCThash("$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'type'}$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'val'}",$digits,$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'pre'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'pos'},length($patt));
    }
    else
    {
      if ( substr($digits, 0, length($hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'origpatt'})) =~ /^$patt$/ )
      {
        print " * Found DbyD exact match!\n";
        &printdct($dctentry,$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'origpatt'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'sc'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'type'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'val'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'pre'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'pos'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'mark'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'next'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'sb'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'sst'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'tid'});
        print "#" x 73 . "\n";
        if ( $hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'type'} eq 'sup' )
        {
          print "User will be sent to a DCT equal to the Next field with Supervision\n";
          print "#" x 73 . "\n";
          $hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'type'} = 'dct';
          $hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'val'} = $hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'next'}
        }
        &processDCThash("$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'type'}$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'val'}",$digits,$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'pre'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'pos'},length($patt));
      }
    }
  }

  print " * No match using Default.\n";
  &printdct(0,$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'origpatt'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'sc'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'type'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'val'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'pre'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'pos'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'mark'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'next'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'sb'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'sst'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'tid'});
  print "#" x 73 . "\n";
  if ( $hash_ref->{'dct'}->{'0'}->{'type'} eq 'sup' )
  {
    print "User will be sent to a DCT equal to the Next field with Supervision\n";
    print "#" x 73 . "\n";
    $hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'type'} = 'dct';
    $hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'val'} = $hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'next'}
  }
  &processDCThash("$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'type'}$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'val'}",$digits,$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'pre'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'pos'},0);
}

# make sure we exit incase we don't make it into the subroutines
exit;

Sample output: Traffic routes to a local station.

Reading "net.db" file: [OK]

#########################################################################
Processing 10 Digit Dialed Number "6235951206"
DCT Number = 0
DCT Name = "Loop Originate"
DCT Entry Quantity = 10
DCT Toneable = dial
DCT Dial Timer = 7 seconds
=========================================================================
ENTRY PATTERN        PATTERN (as seen by Perl)
0     -default-      -default-
1     *              \*
2     #              \#
3     911            911
4     zxxxx          z[0-9][0-9][0-9][0-9]
5     1nxxnxxxxxx    1[2-9][0-9][0-9][2-9][0-9][0-9][0-9][0-9][0-9][0-9]
6     011~w          011\d+
7     011~#          011\d+\#
8     1800nxxxxxx    1800[2-9][0-9][0-9][0-9][0-9][0-9][0-9]
9                    NULL_PATTERN
=========================================================================
 * No match using Default.
ENTRY PATTERN         SC TYPE VAL PRE POS MARK NEXT SB/SNU  SST/NST  TID
0     -default-       0  dct  6   0   0   0         0       0
#########################################################################

#########################################################################
Processing 10 Digit Dialed Number "6235951206"
DCT Number = 6
DCT Name = "NPA Check"
DCT Entry Quantity = 10
DCT Toneable = sil
DCT Dial Timer = 7 seconds
=========================================================================
ENTRY PATTERN        PATTERN (as seen by Perl)
0     -default-      -default-
1     623            623
2     480            480
3     xxx            [0-9][0-9][0-9]
4                    NULL_PATTERN
5                    NULL_PATTERN
6                    NULL_PATTERN
7                    NULL_PATTERN
=========================================================================
 * Found DbyD exact match!
ENTRY PATTERN         SC TYPE VAL PRE POS MARK NEXT SB/SNU  SST/NST  TID
1     623             0  dct  7   0   0   ac        0       0
#########################################################################

#########################################################################
Processing 7 Digit Dialed Number "5951206"
DCT Number = 7
DCT Name = "Switch Code Check"
DCT Entry Quantity = 7
DCT Toneable = sil
DCT Dial Timer = 7 seconds
=========================================================================
ENTRY PATTERN        PATTERN (as seen by Perl)
0     -default-      -default-
1     959            959
2     595            432
3     738            738
4     525            525
5     nxxxxxx        [2-9][0-9][0-9][0-9][0-9][0-9][0-9]
=========================================================================
 * Found DbyD exact match!
ENTRY PATTERN         SC TYPE VAL PRE POS MARK NEXT SB/SNU  SST/NST  TID
2     595             0  dct  8   0   0   ac        0       0
#########################################################################

#########################################################################
Processing 4 Digit Dialed Number "1206"
DCT Number = 8
DCT Name = "Local Numbers"
DCT Entry Quantity = 20
DCT Toneable = sil
DCT Dial Timer = 7 seconds
=========================================================================
ENTRY PATTERN        PATTERN (as seen by Perl)
0     -default-      -default-
1     1155           1155
2     11xx           11[0-9][0-9]
3     12xx           12[0-9][0-9]
4     25xx           25[0-9][0-9]
5     26xx           26[0-9][0-9]
6     93xx           93[0-9][0-9]
7     94xx           94[0-9][0-9]
8     95xx           95[0-9][0-9]
9     xxxx           [0-9][0-9][0-9][0-9]
=========================================================================
 * Found exact match!
ENTRY PATTERN         SC TYPE VAL PRE POS MARK NEXT SB/SNU  SST/NST  TID
3     12xx            0  stn  0   0   0   ac        0       0
#########################################################################
Digits will be sent to a local station.
#########################################################################

Sample Output: Traffic goes out a Trunk to PSTN

Reading "net.db" file: [OK]

#########################################################################
Processing 11 Digit Dialed Number "16134321209"
DCT Number = 0
DCT Name = "Loop Originate"
DCT Entry Quantity = 15
DCT Toneable = dial
DCT Dial Timer = 7 seconds
=========================================================================
ENTRY PATTERN        PATTERN (as seen by Perl)
0     -default-      -default-
1     *              \*
2     #              \#
3     911            911
4     zxxxx          z[0-9][0-9][0-9][0-9]
5     1480nxxxxxx    1480[2-9][0-9][0-9][0-9][0-9][0-9][0-9]
6     1602nxxxxxx    1602[2-9][0-9][0-9][0-9][0-9][0-9][0-9]
7     1623nxxxxxx    1623[2-9][0-9][0-9][0-9][0-9][0-9][0-9]
8     1nxxnxxxxxx    1[2-9][0-9][0-9][2-9][0-9][0-9][0-9][0-9][0-9][0-9]
9     011~w          011\d+
10    011~#          011\d+\#
11    1800nxxxxxx    1800[2-9][0-9][0-9][0-9][0-9][0-9][0-9]
12                   NULL_PATTERN
=========================================================================
 * Found exact match!
ENTRY PATTERN         SC TYPE VAL PRE POS MARK NEXT SB/SNU  SST/NST  TID
8     1nxxnxxxxxx     0  rte  1   0   0   0         0       0
#########################################################################
Digits will be sent to:
  Primary Route: Route 1 "Primary PSTN"
    Group 8 "PRI Span 1/0" Type: trk,trk
  Alternate Route #1: Route 7 "Secondary PSTN"
    Group 6 "PRI Span 1/1" Type: trk,trk
#########################################################################

September 30, 2011

REDCOM DCT Translation Tester (100% offline)

Filed under: perl, REDCOM — lancevermilion @ 5:45 pm

I took some time today to write a fairly simple Perl script to walking through REDCOM Dial Code Tables(DCT) and preform the translations in a 100% offline mode. You will need to get a copy of each of your DCTs and make them a CSV format.

The script works for basic stuff, but it does not evaluate the SC, NEXT (except when used with TYPE=sup), SB/SNU, SST/NST, TID fields because I don’t see a need for them in my testing. The script does not handle using ac-x, ac+x, etc in the PRE, POS, or MARK fields. There is no support for patterns that contain # symbols (again no need for me to figure out why my script doesn’t like them).

I need to do some cleanup, more inline comments, add strict usage, and some other things. This is a functional script. If you do use it then please let me know what you think of it and also if you make changes to it that are for the positive please let me know of those since I shared this with you.

The Code:

#!/usr/bin/perl
# Author:  Lance Vermilion
# Purpose: Walk through Redcom Dial Code Tables and determine
#          what entry the digits would hit if it was ran
#          through a Redcom.
# Script:  dct.pl
# Date:    9/30/2011
# Rev:     0.1
# Syntax:  dct.pl <starting dct> <digits dialed>
# Example: dct.pl dct0 4352348763
#################################################################

#################################################################
# Instructions:
# Copy the all each entry (row) in the DCT to a CSV file named dct[n].csv.
# Do not copy the headers, footers, other data, etc.
# All possible dial code tables should be in one directory.
# Sample Output
# [root@localhost dct]$ ls -als
# total 60
# 8 drwxrwxr-x  2 root root 4096 Sep 30 16:48 .
# 8 drwx------ 33 root root 4096 Sep 30 16:48 ..
# 8 -rw-rw-r--  1 root root  260 Sep 30 15:38 dct0.csv
# 8 -rw-rw-r--  1 root root   92 Sep 30 15:11 dct6.csv
# 8 -rw-rw-r--  1 root root  202 Sep 30 15:16 dct7.csv
# 8 -rw-rw-r--  1 root root  285 Sep 30 15:14 dct8.csv
#12 -rw-rw-r--  1 root root 6410 Sep 30 16:44 dct.pl
#################################################################

#################################################################
# Not supported yet
# TYPE (other than dct, rte, int, stn, sup)
# SC
# PRE  (does support any variation of ac- ac+)
# POS  (does support any variation of ac- ac+)
# MARK (does support any variation of ac- ac+)
# Next (Only jumps to that dct)
# SB
# SST
# TID
#
# Pattern that uses a # in the pattern
#################################################################

my $inputfile = "$ARGV[0]";
my $dialeddigits = $ARGV[1];

sub help()
{
  print "Syntax:  dct.pl <starting dct> <digits dialed>\n";
  print "Example: dct.pl dct0 4352348763\n";
  exit;
}

if ( scalar(@ARGV) ne 2 )
{
  print "You didn't provide enough variables\n"
  &help();
}

&work($inputfile,$dialeddigits,0,0,0);

sub work($$$$$)
{

  # DCT file in csv format or type
  my $type = shift;

  # Since we wrap back on our self to walk DCTs we need to make sure we exit on types of int,etc
  if ( $type =~ /^(int|int\d+)/ )
  {
    print "User will be sent to an intercept message\n";
    exit;
  }
  elsif ( $type =~ /^(rte|rte?\d+)/ )
  {
    print "Digits will be sent to Route: $type\n";
    exit;
  }
  elsif ( $type =~ /^(stn|stn?\d+)/ )
  {
    print "Digits will be sent to a local station.\n";
    exit;
  }

  # numbers dialed
  my $digits = shift;
  chomp($digits);

  # Adjust digits based on pre, pos, and mark
  my $pre = shift;
  my $pos = shift;
  my $mark = shift;
  $digits = substr($digits, $pre)  if ( $pre > 0 );
  $digits = substr($digits, -$pos) if ( $pos > 0 );
  $digits = substr($digits, $mark)  if ( $mark > 0 );

  # DCT to be used that is passed by commandline
  my $dct = {};
  if ( -f "$type.csv" )
  {
    print "Reading DCT file: \"$type.csv\"\n";
  }
  else
  {
    print "File: \"$type.csv\" does not exist. :: $!\n";
    exit;
  }
  my @dctarr = `cat "$type.csv"`;

  for my $dctentry (@dctarr)
  {
    my ($ent, $patt, $sc, $type, $val, $pre, $pos, $mark, $next, $sb, $sst, $tid);
    ($ent, $patt, $sc, $type, $val, $pre, $pos, $mark, $next, $sb, $sst, $tid) = split(/,/, $dctentry);
    $dct->{$ent}->{'origpatt'} = $patt;
    $patt =~ s/n/[2-9]/g;
    $patt =~ s/x/[0-9]/g;
    $patt =~ s/q/[0-4]/g;
    $patt =~ s/Q/[5-9]/g;
    $patt =~ s/X/[2-8]/g;
    $patt =~ s/\~/\\d+/g;
    $patt =~ s/\*/\\*/g;
    $patt =~ s/\#/\\#/g;
    $patt =~ s/\?/[0-9]/g;
    $patt =~ s/w//g;
    $dct->{$ent}->{'patt'} = $patt;
    $dct->{$ent}->{'sc'}   = $sc;
    $dct->{$ent}->{'type'} = $type;
    $dct->{$ent}->{'val'}  = $val;
    $dct->{$ent}->{'pre'}  = $pre;
    $dct->{$ent}->{'pos'}  = $pos;
    $dct->{$ent}->{'mark'} = $mark;
    $dct->{$ent}->{'next'} = $next;
    $dct->{$ent}->{'sb'} = $sb;
    $dct->{$ent}->{'sst'} = $sst;
    $dct->{$ent}->{'tid'} = $tid;
  }

  # Create a pattern hash keyed by pattern with entry and value
  my $patt_ref = {};
  for my $key ( sort keys %$dct )
  {
    $patt_ref->{$dct->{$key}->{'origpatt'}} = $key;
  }

  print "Proccessing " . length($digits) . " Digit Dialed Number \"$digits\"\n";

  my $charcnt = 0;

  # Print DCT in really short form
  print "DCT = $type\n";
  print "ENTRY PATTERN         PATTERN (as seen by Perl)\n";
  for my $key ( sort { $a <=> $b } keys %$dct )
  {
    printf ("%-5s %-14s %s\n", $key,$dct->{$key}->{'origpatt'},($dct->{$key}->{'patt'}));
  }

  # Sub-routine to print DCT
  sub printdct ($$$$$$$$)
  {
    my $printent  = shift;
    my $printpatt = shift;
    my $printsc   = shift;
    my $printtype = shift;
    my $printval  = shift;
    my $printpre  = shift;
    my $printpos  = shift;
    my $printmark = shift;
    my $printnext = shift;
    my $printsb = shift;
    my $printsst = shift;
    my $printtid = shift;

    print "ENTRY PATTERN         SC TYPE VAL PRE POS MARK NEXT SB/SNU  SST/NST  TID    \n";
format DCTREPORT =
@<<<< @<<<<<<<<<<<<<< @< @<<< @<< @<< @<< @<<< @<<< @<<<<<  @<<<<<<  @<<
$printent $printpatt $printsc $printtype $printval $printpre $printpos $printmark $printnext $printsb $printsst $printtid
.
    $~ = 'DCTREPORT';
    write;
  }

  # Check for an exact match first
  for my $patt_orig (sort keys %$patt_ref)
  {
    my $dctentry = $patt_ref->{$patt_orig};
    my $patt = $dct->{$dctentry}->{'patt'};
    if ( $dctentry eq 0 )
    {
      next;
    }
    if ( $digits =~ /^$patt$/ )
    {
      print "\nFound exact match!\n";
      &printdct($dctentry,$dct->{$dctentry}->{'origpatt'},$dct->{$dctentry}->{'sc'},$dct->{$dctentry}->{'type'},$dct->{$dctentry}->{'val'},$dct->{$dctentry}->{'pre'},$dct->{$dctentry}->{'pos'},$dct->{$dctentry}->{'mark'},$dct->{$dctentry}->{'next'},$dct->{$dctentry}->{'sb'},$dct->{$dctentry}->{'sst'},$dct->{$dctentry}->{'tid'});
      if ( $dct->{$dctentry}->{'type'} eq 'sup' )
      {
        print "User will be sent to a DCT equal to the Next field with Supervision\n";
        $dct->{$dctentry}->{'type'} = 'dct';
        $dct->{$dctentry}->{'val'} = $dct->{$dctentry}->{'next'}
      }
      &work("$dct->{$dctentry}->{'type'}$dct->{$dctentry}->{'val'}",$digits,$dct->{$dctentry}->{'pre'},$dct->{$dctentry}->{'pos'},length($patt));
    }
    else
    {
      if ( substr($digits, 0, length($dct->{$dctentry}->{'origpatt'})) =~ /^$patt$/ )
      {
        print "\nFound DbyD exact match!\n";
        &printdct($dctentry,$dct->{$dctentry}->{'origpatt'},$dct->{$dctentry}->{'sc'},$dct->{$dctentry}->{'type'},$dct->{$dctentry}->{'val'},$dct->{$dctentry}->{'pre'},$dct->{$dctentry}->{'pos'},$dct->{$dctentry}->{'mark'},$dct->{$dctentry}->{'next'},$dct->{$dctentry}->{'sb'},$dct->{$dctentry}->{'sst'},$dct->{$dctentry}->{'tid'});
        if ( $dct->{$dctentry}->{'type'} eq 'sup' )
        {
          print "User will be sent to a DCT equal to the Next field with Supervision\n";
          $dct->{$dctentry}->{'type'} = 'dct';
          $dct->{$dctentry}->{'val'} = $dct->{$dctentry}->{'next'}
        }
        &work("$dct->{$dctentry}->{'type'}$dct->{$dctentry}->{'val'}",$digits,$dct->{$dctentry}->{'pre'},$dct->{$dctentry}->{'pos'},length($patt));
      }
    }
  }

  print "\nNo match using Default.\n";
  &printdct(0,$dct->{'0'}->{'origpatt'},$dct->{'0'}->{'sc'},$dct->{'0'}->{'type'},$dct->{'0'}->{'val'},$dct->{'0'}->{'pre'},$dct->{'0'}->{'pos'},$dct->{'0'}->{'mark'},$dct->{'0'}->{'next'},$dct->{'0'}->{'sb'},$dct->{'0'}->{'sst'},$dct->{'0'}->{'tid'});
  if ( $dct->{'0'}->{'type'} eq 'sup' )
  {
    print "User will be sent to a DCT equal to the Next field with Supervision\n";
    $dct->{'0'}->{'type'} = 'dct';
    $dct->{'0'}->{'val'} = $dct->{$dctentry}->{'next'}
  }
  &work("$dct->{'0'}->{'type'}$dct->{'0'}->{'val'}",$digits,$dct->{'0'}->{'pre'},$dct->{'0'}->{'pos'},0);
}

# make sure we exit incase we don't make it into the subroutines
exit;

Here are my sample Dial Code Table files.

::::::::::::::
dct0.csv
::::::::::::::

0,-default-,0,dct,6,0,0,0,
1,*,0,dct,1,0,0,ac,
2,#,0,dct,2,0,0,ac,
3,?111,0,rte,1,0,0,0,
4,zxxxx,0,rte,3,0,0,0,
5,1nxxnxxxxxx,0,rte,1,0,0,0,
6,011~w,0,rte,1,0,0,0,
7,011~#,0,rte,1,0,0,0,
8,1800nxxxxxx,0,rte,1,0,0,0,
9,911,0,rte,1,0,0,0,
10,011~,0,rte,1,0,0,0,

::::::::::::::
dct6.csv
::::::::::::::

0,-default-,0,dct,7,0,0,0,
1,623,0,dct,7,0,0,ac,
2,480,0,dct,7,0,0,ac,
3,xxx,0,rte,1,0,0,0,

::::::::::::::
dct7.csv
::::::::::::::

0,-default-,0,int,0,0,ac,,,0,,
1,959,0,dct,8,0,0,ac,,,0,,
2,4x21207,0,dct,8,0,0,ac,,,0,,
3,738,0,dct,8,0,0,ac,,,0,,
4,525,0,dct,8,0,0,ac,,,0,,
5,4x21206,0,dct,8,0,0,ac,,,0,,
6,4x21,0,dct,8,0,0,ac,,,0,,

::::::::::::::
dct8.csv
::::::::::::::

0,-default-,0,int,,0,0,ac,,,0,,
1,1155,0,sup,,ac,0,ac,17,,0,,
2,11xx,0,stn,0,0,0,ac,,,0,,
3,12xx,0,stn,0,0,0,ac,,,0,,
4,25xx,0,stn,0,0,0,ac,,,0,,
5,26xx,0,stn,0,0,0,ac,,,0,,
6,93xx,0,stn,0,0,0,ac,,,0,,
7,94xx,0,stn,0,0,0,ac,,,0,,
8,95xx,0,stn,0,0,0,ac,,,0,,
9,xxxx,0,rte,1,0,0,0,,,0,,

::::::::::::::
dct17.csv
::::::::::::::

0,-default-,0,int,,0,0,ac,,,0,,
1,1155,0,sup,,ac,0,ac,17,,0,,
2,11xx,0,stn,0,0,0,ac,,,0,,
3,12xx,0,stn,0,0,0,ac,,,0,,
4,25xx,0,stn,0,0,0,ac,,,0,,
5,26xx,0,stn,0,0,0,ac,,,0,,
6,93xx,0,stn,0,0,0,ac,,,0,,
7,94xx,0,stn,0,0,0,ac,,,0,,
8,95xx,0,stn,0,0,0,ac,,,0,,
9,xxxx,0,rte,1,0,0,0,,,0,,

August 11, 2011

Poor man’s query logging (from http://www.mysqlperformanceblog.com)

Filed under: database, linux, mysql, perl — lancevermilion @ 5:51 pm

Occasionally there is a need to see what queries reach MySQL. The database provides several ways to share that information with you. One is called general log activated with

--log

(or

--general-log

in MySQL 5.1+) start-up parameter. The log writes any query being executed by MySQL to a file with limited amount of additional information. The other is slow log enabled by

--log-slow-queries

parameter (MySQL 5.1 requires also

--slow-query-log

), which was designed to store poorly performing queries that run at least 2 seconds. Percona actually extended the slow log to, among others, include any query regardless of the execution time.

The problem is that for both you need to prepare earlier either by enabling the logging before starting the database instance or, even more work, by applying the patch and rebuilding the entire database from sources.

I know that many databases out there run with none of these and it would require a restart to get the logging in place and possibly another restart to disable it when no longer necessary (though actually slow log can be disabled by simply setting

long_query_time

MySQL variable vale high enough).

So what can be done when you really need to see the queries, but can’t afford any downtime?

If you are a privileged user (i.e. root), you can use tcpdump on a database server to take a peek into a network stream and filter for packets that go to MySQL. Those packets contain queries. Here’s my quick one-liner which I will write in multiple lines:

garfield ~ # sudo /usr/sbin/tcpdump -i eth0 -s 0 -l -w - dst port 3306 | strings | perl -e '
while(<>) { chomp; next if /^[^ ]+[ ]*$/;
  if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER)/i) {
    if (defined $q) { print "$q\n"; }
    $q=$_;
  } else {
    $_ =~ s/^[ \t]+//; $q.=" $_";
  }
}'

The output may contain little garbage, but it can be easily filtered out.

Obviously this method works only when applications communicate with MySQL through TCP sockets. When localhost (not to be confused with 127.0.0.1) is used as a MySQL host, this will not work since all traffic goes through a unix socket file.

It’s most definitely not a MySQL log replacement, but can be very useful if you need just a few minute dump.

Maciek

Here is sample output

tcpdump: listening on bond0, link-type EN10MB (Ethernet), capture size 65535 bytes
110811 17:47:32     325 Connect     bill@10.168.192.90 on
                    325 Query       select @@version_comment limit 1
110811 17:47:38     325 Query       SELECT DATABASE()
                    325 Init DB     accounting
                    325 Query       show databases
                    325 Query       show tables
                    325 Field List  records
110811 17:48:15     325 Quit
Older Posts »

Blog at WordPress.com.