I decided to install postgresql in a zone on my home server this weekend to play around with PostgreSQL some. So I decided to export my syswatch db from MySQL and import it into PostgreSQL. So I immediatly did a mysqldump -uroot -p syswatch > syswatch.dump. This gave me a file (i actually did each table to its own file but the concept is the same), that I then tried to import in to PostgreSQL without modifying it. (I knew this would not work but wanted to see exactly what wouldn’t work). So I ended up editing one of the smaller tables to create it. Here are some of my notes:

  1. Even though in mysqlshow a column may show varchar, it is outputed as char. When defining a table in PostgreSQL this will be the full size of what ever you defined it as. For example 255 would be the size of the value plus white space till it filled the 255. So I changed the char(255) to varchar(255), much better
  2. The datetime value needs changed to timestamp
  3. int and bigint in mysqldump will have a integer after it for the size, this is not valid in PostgreSQL
  4. auto_increment is not valid, either user serial or a sequence. I chose to use sequences to replace my auto_increments.
  5. If you are using a recent version of mysql, make sure to use the –skip-opts option on the mysqldump. If you don’t you will get the extended-insert type insert statements which PostgreSQL does NOT like, and running a 3,000,000 line file through sed to change the ),( to ); insert into blah values ( takes for ever and a day.
  6. The back ticks around the table names on the inserts need removed

That is all for now, doing a reexport of all the tables with –skip-opts so each insert statement is on it’s own line. Will see if this makes it easier to get it up and running.

The whole purpose for this, other than trying to learn PostgreSQL is to see if PostgreSQL is any faster than MySQL.

Posted by unixwiz, filed under MySQL, PostgreSQL, Random Stuff, Syswatch. Date: November 15, 2006, 9:46 pm | No Comments »

Well I started testing the hardware inventory script and found a couple of problems. Noteably that the “-p” option to psrinfo is not available in all versions of solaris 9, it was released in a later update so it broke some of my solaris 9 installs.

So here is my complete script as it stands right now.

< ?
//SYSVERSION:0.0.4
//
//This Function does change management on the processor and memory subsystem,
//What it will do is tell us when we are adding or changing processors and memory
//Get the information.
//Created on 2006-05-18

function SYS_cmproc($DEBUG,$hostname,$OS,$OSRevision,$Arch) {

        if ($OS == "SunOS") {
                //We are going to look at the output of several commands:
                //psrinfo -p to get number of physcial processors
                //psrinfo to get the total number of cores
                //psrinfo -v to get the processor speed
                //prtconf to get the memory in the machine
                $NumberOfPhysical=trim(shell_exec("/usr/sbin/psrinfo -p"));
                if (!is_numeric($NumberOfPhysical)) {
                        //Must be the latest patches are not on the machine to show the -p, it probably also does not have virutalprocessors
                        //Therefore need to do NumberOfPhyiscal will be like NumberOfVirutal
                        $NumberOfPhysical=trim(shell_exec("/usr/sbin/psrinfo | /bin/wc -l | /bin/sed ’s/ //g’"));
                        //Also need to drop the OSRevision back to 5.8 so it can grab the info from prtconf as opposed to psrinfo
                        $OSRevision="5.8";
                }
                $NumberOfVirtual=trim(shell_exec("/usr/sbin/psrinfo | /bin/wc -l | /bin/sed ’s/ //g’"));
                $FirstProcessor=trim(shell_exec("/usr/sbin/psrinfo | /bin/head -1 | /bin/awk ‘{print $1}’"));
                $ProcessorSpeed=trim(shell_exec("/usr/sbin/psrinfo -v $FirstProcessor | /bin/grep operates | /bin/awk ‘{print $6}’"));
                $MemorySize=trim(shell_exec("/usr/sbin/prtconf | /bin/grep Memory | /bin/head -1 | /bin/awk ‘{print $3}’"));
                //Processor type, different ways for 5.8,5.9 and 5.10
                if ($OSRevision=="5.8") {
                        //psrinfo command does not work for this OS as it is missing the -p option, so get the info out
                        //of prtconf -v grep for Ultra (we don’t really have any machines running 8 that are not Ultra’s anymore
                        //This will also assume that the machine is running the same type of processor for all processors
                        if ($Arch == "sparc" ) {
                                $ProcessorType=shell_exec("/usr/sbin/prtconf | /bin/grep Ultra | /bin/awk -F’,’ ‘{print $2}’ | /bin/awk ‘{print $1}’ | /bin/head -1");
                        }
                        if ($Arch == "i86pc" ) {
                                $ProcessorType="Generic Intel";
                        }
                }
                if ($OSRevision=="5.9") {
                        //Need to find the first processor, do NOT assume that 0 will be it. that does not work on the E25K!
                        //Use the $FirstProcessor from above   
                        if ($Arch == "sparc") {
                                $ProcessorType=shell_exec("/usr/sbin/psrinfo -pv $FirstProcessor | /bin/awk ‘{print $2}’");
                        }
                        if ($Arch == "i86pc" ) {
                                $ProcessorType="Intel";
                        }
                }
                if ($OSRevision=="5.10") {
                        //Need to find first processor same as for 5.9, but the output of psrinfo has changed
                        if ($Arch == "sparc" ) {
                                $ProcessorType=shell_exec("/usr/sbin/psrinfo -pv $FirstProcessor |/bin/tail -1 | /bin/awk ‘{print $1}’");
                        }
                        if ($Arch == "i86pc" ) {
                                $ProcessorTypeT=shell_exec("/usr/sbin/psrinfo -pv $FirstProcessor | /bin/tail -1");
                                $ProcessorType=trim($ProcessorTypeT);
                        }
                }
                $ProcessorType=trim($ProcessorType);
        }       

        if ($OS == "AIX" ) {
                //Going to use the following commands to get the number of processors and memory
                //lsdev -C | grep -c proc
                //lsattr -El proc# to get the speed and processor type
                $NumberOfPhysical=trim(shell_exec("/usr/sbin/lsdev -C | /usr/bin/grep -c proc"));
                $FirstProcessor=trim(shell_exec("/usr/sbin/lsdev -C | /usr/bin/grep proc | /usr/bin/head -1 | /usr/bin/awk ‘{print $1}’"));
                $ProcessorType=trim(shell_exec("/usr/sbin/lsattr -El $FirstProcessor | /usr/bin/grep type | /usr/bin/awk ‘{print $2}’"));
                $ProcessorSpeed=trim(shell_exec("/usr/sbin/lsattr -El $FirstProcessor | /usr/bin/grep Speed | /usr/bin/awk ‘{print $2}’"));
                $ProcessorSpeed=sprintf("%d",$ProcessorSpeed/1024/1024); //Speed in MHZ
                $MemorySize=trim(shell_exec("/usr/sbin/lsattr -El mem0 | /usr/bin/grep Total | /usr/bin/awk ‘{print $2}’"));
                $NumberOfVirtual=$NumberOfPhysical; //Not sure how to find them at the moment as we have no machines that have them            
        }

        if ($OS == "Linux" ) {
                //cat /proc/cpuinfo | grep -c processor
                //cat /proc/meminfo
                $NumberOfPhysical=trim(shell_exec("/bin/cat /proc/cpuinfo | /bin/grep -c processor"));
                $ProcessorType=trim(shell_exec("/bin/cat /proc/cpuinfo | /bin/grep \"model name\" | /bin/awk -F: ‘{print $2}’ |/usr/bin/uniq"));
                $ProcessorSpeed=trim(shell_exec("/bin/cat /proc/cpuinfo | /bin/grep \"cpu MHz\" | /bin/awk -F: ‘{print $2}’ | /bin/awk -F. ‘{print $1}’ | /usr/bin/uniq"));
                $MemorySizeT=trim(shell_exec("/bin/cat /proc/meminfo | /bin/grep MemTotal | /bin/awk ‘{print $2}’"));
                $MemorySize=sprintf("%d",$MemorySizeT/1024);
                $NumberOfVirtualT=trim(shell_exec("/bin/cat /proc/cpuinfo | /bin/grep -ci siblings"));
                if ($NumberOfVirtualT !=0 ) {
                        //We have more than 1 core per processor, linux being stupid presents each core as a seperate processor and then
                        //just puts a siblings line in the /proc/cpuinfo
                        $NumberOfVirtual=$NumberOfVirtualT;     
                        $NumberOfPhysical=$NumberOfPhysical/$NumberOfVirtual;
                } else {
                        $NumberOfVirtual=$NumberOfPhysical;
                }

        }

        if ($OS == "Darwin") {
                //Use the system_profiler command
                //system_profiler SPHardwareDataType
               
                $NumberOfPhysical=trim(shell_exec("/usr/sbin/system_profiler SPHardwareDataType | /usr/bin/grep \"Number Of CPUs\" | /usr/bin/awk -F’:’ ‘{print $2}’"));
                //$NumberOfVirtual=trim(shell_exec("/usr/sbin/system_profiler SPHardwareDataType | /usr/bin/grep \"Number Of CPUs\" | /usr/bin/awk -F’:’ ‘{print $2}’"));
                $NumberOfVirtual=0;
                //$NumberOfVirtual=trim(shell_exec("/usr/sbin/psrinfo | /bin/wc -l | /bin/sed ’s/ //g’");
                $ProcessorSpeedTEMP=trim(shell_exec("/usr/sbin/system_profiler SPHardwareDataType | /usr/bin/grep \"CPU Speed\" | /usr/bin/awk -F’:’ ‘{print $2}’"));
                if (ereg("GHz",$ProcessorSpeedTEMP)){
                        $ProcessorSpeed=ereg_replace(" GHz","",$ProcessorSpeedTEMP);
                        $ProcessorSpeed=$ProcessorSpeed*1024;
                }
                if (ereg("MHz", $ProcessorSpeedTEMP)){
                        $ProcessorSpeed=ereg_replace(" MHz","",$ProcessorSpeedTEMP);
                }

                $ProcessorType=trim(shell_exec("/usr/sbin/system_profiler SPHardwareDataType | /usr/bin/grep \"CPU Type\" | /usr/bin/awk -F’:’ ‘{print $2}’"));
                $MemorySizeTemp=trim(shell_exec("/usr/sbin/system_profiler SPHardwareDataType | /usr/bin/grep \"Memory\" | /usr/bin/awk -F’:’ ‘{print $2}’"));
                if (ereg("GB",$MemorySizeTemp)) {
                        $MemorySize=ereg_replace(" GB","",$MemorySizeTemp);
                        $MemorySize=$MemorySize*1024;
                }
                if (ereg("MB",$MemorySizeTemp)) {
                        $MemorySize=ereg_replace(" MB","",$MemorySizeTemp);
                };
        }

        if ($DEBUG) echo "Machine has $NumberOfPhysical processors of $ProcessorType with $NumberOfVirtual cores running at $ProcessorSpeed MHZ with $MemorySize MB of Memory\n";
       
       
        //Check to see if there is an entry in the cm_proc_mem table if it is, compare with current to see if any changes, if not
        //insert the record
        $CMResult=mysql_query("select * from cm_proc_mem where hostname=’$hostname’ order by date desc") or die(mysql_error());
        $CMN=mysql_num_rows($CMResult);
        if ($CMN==0) {
                //Insert new record
                mysql_query("insert into cm_proc_mem values (’$hostname’,'$ProcessorType’,'$NumberOfPhysical’,'$ProcessorSpeed’,'$NumberOfVirtual’,'$MemorySize’,NOW(),”)") or die(mysql_error());
        } else {
                //Must be a previous entry If the info is different then add new record otherwise just bail
                //Only care about the most recent which should be on top
                $SProcessor_Type=mysql_result($CMResult,0,"processor_type");
                $SProcessor_Count=mysql_result($CMResult,0,"processor_count");
                $SProcessor_Speed=mysql_result($CMResult,0,"processor_speed");
                $SProcessor_Cores=mysql_result($CMResult,0,"processor_cores");
                $SMemory=mysql_result($CMResult,0,"memory");
                $Changed="NO";
                if ($SProcessor_Type != $ProcessorType) {
                        $Changed="YES";
                }
                if ($SProcessor_Count != $NumberOfPhysical) {
                        $Changed="YES";
                }
                if ($SProcessor_Speed != $ProcessorSpeed) {
                        $Changed="YES";
                }
                if ($SProcessor_Cores != $NumberOfVirtual) {
                        $Changed="YES";
                }
                if ($SMemory != $MemorySize ) {
                        $Changed="YES";
                }
                if ($Changed=="YES" ) {
                        mysql_query("insert into cm_proc_mem values (’$hostname’,'$ProcessorType’,'$NumberOfPhysical’,'$ProcessorSpeed’,'$NumberOfVirtual’,'$MemorySize’,NOW(),”)") or die(mysql_error());
                }
        }
       
}

Posted by unixwiz, filed under Syswatch. Date: May 19, 2006, 8:21 pm | No Comments »

I decided to add a modules to my Syswatch system that would do basic hardware inventory (Processor[speed,number,type, etc] and Memory). Basicly this was so that DBA’s could look at a web page to see what OS and hardware a machine was running. So far each OS has it’s own way of doing this and this is how I am getting the information.

Solaris:

if ($OS == "SunOS") {
                //We are going to look at the output of several commands:
                //psrinfo -p to get number of physcial processors
                //psrinfo to get the total number of cores
                //psrinfo -v to get the processor speed
                //prtconf to get the memory in the machine
                $NumberOfPhysical=shell_exec("/usr/sbin/psrinfo -p");
                $NumberOfVirtual=shell_exec("/usr/sbin/psrinfo | /bin/wc -l | /bin/sed ’s/ //g’");
                $FirstProcessor=shell_exec("/usr/sbin/psrinfo | /bin/head -1 | /bin/awk ‘{print $1}’");
                $ProcessorSpeed=shell_exec("/usr/sbin/psrinfo $FirstProcessor | /bin/grep operates | /bin/awk ‘{print $6}’");
                $MemorySize=shell_exec("/usr/sbin/prtconf | /bin/grep Memory |/bin/head -1 | /bin/awk ‘{print $3}’");
                //Processor type, different ways for 5.8,5.9 and 5.10
                if ($OSRevision=="5.8") {
                        //psrinfo command does not work for this OS as it is missing the -p option, so get the info out
                        //of prtconf -v grep for Ultra (we don’t really have any machines running 8 that are not Ultra’s anymore
                        //This will also assume that the machine is running the same type of processor for all processors
                        $ProcessorType=shell_exec("/usr/sbin/prtconf | /bin/grep Ultra | /bin/awk -F’,’ ‘{print $2}’ | /bin/awk ‘{print $1}’ | /bin/head -1");
                }
                if ($OSRevision=="5.9") {
                        //Need to find the first processor, do NOT assume that 0 will be it. that does not work on the E25K!
                        //Use the $FirstProcessor from above    
                        $ProcessorType=shell_exec("/usr/sbin/psrinfo -pv $FirstProcessor | /bin/awk ‘{print $2}’");
                }
                if ($OSRevision=="5.10") {
                        //Need to find first processor same as for 5.9, but the output of psrinfo has changed and the
                        //Processor type is on the second line.
                        $ProcessorType=shell_exec("/usr/sbin/psrinfo -pv $FirstProcessor |/bin/tail -1 | /bin/awk ‘{print $1}’");
                }
        }

What the above code does is populate the following Variables:

$NumberOfPhysical : Number of Physical processors in the machine
$NumberOfVirutal: Number of Cores/threads that are in the machine
$FirstProcessor: The Numeric ID of the first processor in the machine. For example in an E25K your first processor could be a non 0 number
$MemorySize: Size of memory in MB
$ProcessorType: The Processor type, i.e. UltraSPARC-III+
$ProcessorSpeed: Speed of the Processor in MHz

For AIX the commands are done a little differently:

if ($OS == "AIX" ) {
                //Going to use the following commands to get the number of processors and memory
                //lsdev -C | grep -c proc
                //lsattr -El proc# to get the speed and processor type
                $NumberOfPhysical=shell_exec("/usr/sbin/lsdev -C | /usr/bin/grep -c proc");
                $ProcessorType=shell_exec("/usr/sbin/lsattr -El proc0 | /usr/bin/grep type | /usr/bin/awk ‘{print $2}’");
                $ProcessorSpeed=shell_exec("/usr/sbin/lsattr -El proc0 | /usr/bin/grep Speed | /usr/bin/awk ‘{print $2}’");
                $ProcessorSpeed=$ProcessorSpeed/1024/1024; //Speed in MHZ
                $MemorySize=shell_exec("/usr/sbin/lsattr -El mem0 | /usr/bin/grep Total | /usr/bin/awk ‘{print $2}’");
                $NumberOfVirtual=0; //Not sure how to find them at the moment as we have no machines that have them            
        }

Same variables are availabe as in Solaris.

For Apple MacOSX you do it like this:

if ($OS == "Darwin") {
                //Use the system_profiler command
                //system_profiler SPHardwareDataType
               
                $NumberOfPhysical=shell_exec("/usr/sbin/system_profiler SPHardwareDataType | /usr/bin/grep \"Number Of CPUs\" | /usr/bin/awk -F’:’ ‘{print $2}’");
                $NumberOfVirtual=shell_exec("/usr/sbin/system_profiler SPHardwareDataType | /usr/bin/grep \"Number Of CPUs\" | /usr/bin/awk -F’:’ ‘{print $2}’");
                //$NumberOfVirtual=shell_exec("/usr/sbin/psrinfo | /bin/wc -l | /bin/sed ’s/ //g’");
                $ProcessorSpeedTEMP=shell_exec("/usr/sbin/system_profiler SPHardwareDataType | /usr/sbin/grep \"CPU Speed\" | /usr/bin/awk -F’:’ ‘{print $2}’");
                if (ereg("GHz",$ProcessorSpeedTEMP)){
                        $ProcessorSpeed=ereg_replace(" GHz","",$ProcessorSpeedTEMP);
                        $ProcessorSpeed=$ProcessorSpeed*1024;

                $ProcessorType=shell_exec("/usr/sbin/system_profiler SPHardwareDataType | /usr/bin/grep \"CPU Type\" | /usr/bin/awk -F’:’ ‘{print $2}’");
                $MemorySizeTemp=shell_exec("/usr/sbin/system_profiler SPHardwareDataType | /usr/sbin/grep \"Memory\" | /usr/bin/awk -F’:’ ‘{print $2}’");
                if (ereg("GB",$MemorySizeTemp)) {
                        $MemorySize=ereg_replace(" GB","",$MemorySizeTemp);
                        $MemorySize=$MemorySize*1024;
                }
                if (ereg("MB",$MemorySizeTemp) {
                        $MemorySize=ereg_replace(" MB","",$MemorySizeTemp);
                };
        }

Mac was the most intersting to do as I looked around for a while to try and find a command that would output what I wanted. I figured out how to do it while watching the Activity monitor and ran the gui system _profiler command. I decided to run “man system_profiler” and found that you could run it via the command line. Made it really easy to get info out. What I use above is just 1 of many data types that system_profiler will return.

I still have to write the Linux part to this module, and it will use the “/proc” file system with the cpuinfo and meminfo files to get info. Once I get that done next week I will post it as well.

Posted by unixwiz, filed under Shell Scripts, Syswatch. Date: May 13, 2006, 11:17 pm | No Comments »

So i previously mentioned that all the current version information is stored in a MySQL database on the main syswatch server. So how does the client code get updated? Pretty easy, each time before the program runs it runs this script to check the version info between the local files and what is on the server.

< ?
mysql_connect("syswatch-server","syswatch") or die(mysql_error());
mysql_select_db("syswatch");
//Get list of files to check to see if they exist
//Get OS so only ones required by this os are downloaded
$OSTYPE=@system("/bin/uname");

$result=mysql_query("select * from versions where os =’ALL’ or os=’$OSTYPE’");
$num=mysql_num_rows($result);
$i=0;
echo "There are $num function files\n";
while ($i < $num ) {
        $File[$i]=mysql_result($result,$i,"function");
        $Version[$i]=mysql_result($result,$i,"version");
        $MD5Sum[$i]=mysql_result($result,$i,"md5file");
       
        $fp = @fopen($File[$i],r);
        if ($fp) {
                //File Exists
                while (!feof($fp)) {
                        $buffer=fgets($fp);
                        if (ereg("//SYSVERSION:",$buffer)) {
                                //Should be a version line explode on the :
                                list($PreHead,$VERSION)=explode(":",$buffer);
                                $VERSION=ereg_replace("\n","",$VERSION);
                                if ("$VERSION" != "$Version[$i]" ) {
                                        echo "$File[$i] is downrev, it is $VERSION, current is $Version[$i]\n";
                                        echo "Downloading new version\n";
                                        //File is downrev, need to get new file
                                        $newfp=fopen("http://syswatch-server:8080/syswatch/$File[$i].txt",r);
                                        fclose($fp);
                                        unlink($File[$i]);
                                        $fp=fopen($File[$i],w);
                                        while (! feof($newfp)) {
                                                $buffernew=fgets($newfp);
                                                fputs($fp,$buffernew);
                                        }
                                        //Check the md5 sum of the file to make sure it has not been changed;
                                        $NewFile=md5_file($File[$i]);
                                        if ($NewFile!=$MD5Sum[$i]) {
                                                //If MD5’s don’t match issue warning and stop.
                                                echo "Local File does not match MD5 of stored function, stopping now\n";
                                                echo "File name: $File[$i]\n";
                                                echo "Local MD5: $NewFile\n";
                                                echo "Store MD5: $MD5Sum[$i]\n";
                                                exit (1);
                                        }
                                }
                                else {
                                        $NewFile=md5_file($File[$i]);
                                        if ($NewFile!=$MD5Sum[$i]) {
                                                //If MD5’s don’t match issue warning and stop.
                                                echo "Local File does not match MD5 of stored function, stopping now\n";
                                                echo "File name: $File[$i]\n";
                                                echo "Local MD5: $NewFile\n";
                                                echo "Store MD5: $MD5Sum[$i]\n";
                                                exit (1);
                                        }
                                        echo "$File[$i] is at current version\n";
                                        fclose($fp);
                                        break;
                                }
                        }
                }
        }
        else {
                //File does not exist, need to download it
                echo "$File[$i] does not exist, downloading now\n";
                $newfp=fopen("http://syswatch-server:8080/syswatch/$File[$i].txt","rb");
                while (!feof($newfp))
                {
                        $contents.=fgets($newfp,1024);
                }
                fclose($newfp);
                $fp2=@fopen($File[$i],"w");
                fwrite($fp2,$contents);
                fclose($fp2);
                $NewFile=md5_file($File[$i]);
                if ($NewFile!=$MD5Sum[$i]) {
                        //If MD5’s don’t match issue warning and stop.
                        echo "Local File does not match MD5 of stored function, stopping now\n";
                        echo "File name: $File[$i]\n";
                        echo "Local MD5: $NewFile\n";
                        echo "Store MD5: $MD5Sum[$i]\n";
                        exit (1);
                }
        }
        $contents="";
        $i++;
}

Posted by unixwiz, filed under PHP, Syswatch. Date: January 23, 2006, 8:47 pm | No Comments »

The second table used, is the uptimes table. This table keeps a list of all the machines with their OS, hardware type and last time they updated.

mysql&>describe uptimes;

+------------+------------+------+-----+---------+-------+
| Field      | Type       | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| hostname   | char(255)  |      | PRI |         |       |
| date       | datetime   | YES  |     | NULL    |       |
| timeinsec  | bigint(20) | YES  |     | NULL    |       |
| arch       | char(25)   | YES  |     | NULL    |       |
| osrevision | char(10)   | YES  |     | NULL    |       |
| os         | char(25)   | YES  |     | NULL    |       |
+------------+------------+------+-----+---------+-------+

Basicly the fields are describe what they are used for. I store the machine type in the arch field, I.E. sparc,x86_64,powerpc, x86, etc. Bascily what ever is returned by:

uname -p

The OS Version is what is returned by the :

uname -r

However AIX is weird and will not tell you the real version so on AIX I do the following:

if ($OS=="AIX") {      
     $OSRM=system("/bin/uname -v");      
     $OSRminor=system("/bin/uname -r");    
     $OSRevision="$OSRM.$OSRminor";    
}

The timeinsec field holds the time that the machie updated in seconds. This is used by the web interface to report any machines that have not updated in the last xxx seconds. This table is updated twice a run, once at the begining and once at the end. This table also helps with Sun Patch management which I will describe later.

Posted by unixwiz, filed under PHP, Syswatch. Date: January 10, 2006, 7:13 pm | No Comments »

« Previous Entries