Get Device Registration Status

This script pulls devices and registration from CM and prints standard out to a CSV file. It was written to pull registration over time and print to a DB so that logic is still there and DB writes have been commented out.


<?php

$dbconn = pg_connect("host=127.0.0.1 port=15432 dbname=uc_db user=backend")
or die('Could not connect: ' . pg_last_error());


    //client configuration 10.1.1.10 is the server with username and password for AXL user
    $host         = "10.10.10.10";
    $username    = "admin";
    $password    = "password";

    $axlClient = new SoapClient("./bin/php/soap/AXLAPI.wsdl",
        array('trace'=>true,
        'exceptions'=>true,
        'location'=>"https://".$host.":8443/axl",
        'login'=>$username,
        'password'=>$password,
    ));

    $risportClient = new SoapClient("https://".$host.":8443/realtimeservice2/services/RISService70?wsdl",
        array('trace'=>true,
        'exceptions'=>true,
        'location'=>"https://".$host.":8443/realtimeservice2/services/RISService70?wsdl",
        'login'=>$username,
        'password'=>$password,
    ));


$timestamp = date("Y-m-d-H");


$sql="SELECT d.name AS Device,d.description,css.name AS css, devicepool.name AS dp,n.dnorpattern,partition.name AS pt, tm.name AS type,enduser.userid as owner
        FROM device AS d
                INNER JOIN callingsearchspace AS css ON css.pkid=d.fkcallingsearchspace
                INNER JOIN devicepool ON devicepool.pkid=d.fkdevicepool
                INNER JOIN devicenumplanmap AS dmap ON dmap.fkdevice=d.pkid
                INNER JOIN typemodel AS tm ON d.tkmodel = tm.enum
                   INNER JOIN numplan AS n ON dmap.fknumplan = n.pkid
                INNER JOIN routepartition AS partition ON partition.pkid=n.fkroutepartition
                INNER JOIN DevicePool AS dp ON d.fkDevicePool = dp.pkid
                LEFT  JOIN enduser ON enduser.pkid=d.fkenduser
    ";
$response = $axlClient->executeSQLQuery(array("sql"=>$sql));

class item
{
    public $Item;
}

$phone_count = 0;

foreach($response->return->row as $phone) {

    $phone_array[$phone_count]['name'] = $phone->device ;
    $phone_array[$phone_count]['description'] = $phone->description;
    $phone_array[$phone_count]['dp'] = $phone->dp ;
    $phone_array[$phone_count]['css'] = $phone->css ;
    $phone_array[$phone_count]['extension'] = $phone->dnorpattern;
    $phone_array[$phone_count]['partition'] = $phone->pt;
    $phone_array[$phone_count]['type'] = $phone->type;
    $phone_array[$phone_count]['ip'] = "unregistered";
    $phone_array[$phone_count]['owner'] = $phone->owner;

    $item_obj[$phone_count] = new item();
    $item_obj[$phone_count]->Item = $phone->device;

    $phone_count++;
}


$risport_loop = "0";
while ( $risport_loop <= $phone_count ) {

    $device_batch = array_slice($item_obj,$risport_loop,($risport_loop + 1000));

    $risport_query = array("StateInfo"=>"",
                        "CmSelectionCriteria"=>array(
                            "NodeName"=>"",
                            "Status"=>"Registered", 
                            "SelectBy"=>"Name",
                            "SelectItems"=>
                                $device_batch
                        ));

    $response = $risportClient->SelectCmDeviceExt($risport_query);
    $risport_loop = ( $risport_loop + 1000);

    foreach ( $response->selectCmDeviceReturn->SelectCmDeviceResult->CmNodes->item as $node) {
        if ( @is_array($node->CmDevices->item) ){
            foreach ( $node->CmDevices->item as $device ){
                 //echo "\t<tr>\n\t\t<td>".$device->Name.'<td>'.$device->Description.'<td>'.$device->IPAddress->item->IP."\n\t</tr>\n";
                 @$risport_array[$device->Name] = $device->IPAddress->item->IP ;
             }
        } else {
            @$risport_array[$node->CmDevices->item->Name] = $node->CmDevices->item->IPAddress->item->IP ;

        }

    }
}

$sql = "select count(*) from cm_device where timestamp like '".$timestamp."%';";

//$result = pg_fetch_row(pg_query($sql));
//$new_date = $result[0];
$new_date = 0;
echo "device,description,css,dp,pt,type,dn,ip,timestamp,owner\n";

$associate_loop = "0";
while ( $associate_loop <= $phone_count ) {

    if ( @$device_name = $phone_array[$associate_loop]['name']) {

        if ( @$risport_array[$device_name] ) {
            $phone_array[$associate_loop]['ip'] = $risport_array[$device_name];
        }
        if ( $new_date == 0 ) {
            $sql  = "insert into cm_device (device,description,css,dp,pt,type,dn,ip,timestamp,owner) values (";
            $value .=    "'".$phone_array[$associate_loop]['name']."',";
            $value .= "'".preg_replace("/'/","",$phone_array[$associate_loop]['description'])."',";
            $value .= "'".$phone_array[$associate_loop]['css']."',";
            $value .= "'".$phone_array[$associate_loop]['dp']."',";
            $value .= "'".$phone_array[$associate_loop]['partition']."',";
            $value .= "'".$phone_array[$associate_loop]['type']."',";
            $value .= "'".$phone_array[$associate_loop]['extension']."',";
            $value .= "'".$phone_array[$associate_loop]['ip']."',";
            $value .= "'".$timestamp."',";
            $value .= "'".$owner."'";
            $sql .= ");";

        } elseif ( $phone_array[$associate_loop]['ip'] != "unregistered" ) {

            $sql = "update cm_device set ip = '".$phone_array[$associate_loop]['ip']."' where device = '".$phone_array[$associate_loop]['name']."' and timestamp = '".$timestamp."';";
        }

        echo $value."\n";
        //$result = pg_query($sql) ;//or die('Query failed: ' . pg_last_error());
    }

    $associate_loop++;

}

pg_close($dbconn);

?>