Thought I might share my rough host uptime calculator (availability report)

(Chris Wall) #1

Hey all,
I had a requirement to be able to provide host availability data for our monitored hosts.
I couldn’t really find anything to do this I hacked up the following (rough) script to do so.
I thought I might share it with you all in case it is useful for anyone else.

(n.b. I’m not really a software developer by trade, and I needed something working fast, so please excuse if this is a bit ugly.)

<h1>Availability Report</h1>

 purpose: calculate host availability for a given time period, inferring off state changes recorded into icinga2 IDO database
 author: chris.wall
 date: 2018-08-02

  - 2018-08-02: initial 'functional' code

  - extract calculation code from display code so we can do better things with it
  - add output format options (currently html table, do CSV output etc)
  - better selection criteria
  - make this whole thing into an icingaweb module??
  - figure out a better thing to do when inferring initial host state
  - calculate intervals into a percentage of total time period

 - utilising Icinga2 IDO
 - using Postgresql as database provider
 - that $$AvailabilityViewName is created as a view in icinga2 database
 - $dbuser has select access to $$AvailabilityViewName

$$AvailabilityViewName definition:
 SELECT icinga_hostgroups.alias AS groupname,
    icinga_hosts.alias AS hostname,
   FROM icinga_hostgroup_members
     JOIN icinga_hostgroups ON icinga_hostgroup_members.hostgroup_id = icinga_hostgroups.hostgroup_id
     JOIN icinga_hosts ON icinga_hostgroup_members.host_object_id = icinga_hosts.host_object_id
     JOIN icinga_objects ON icinga_objects.object_id = icinga_hosts.host_object_id
     JOIN icinga_statehistory ON icinga_statehistory.object_id = icinga_hosts.host_object_id
  WHERE icinga_objects.is_active = 1
  ORDER BY icinga_hosts.host_object_id, icinga_statehistory.state_time;


$dbname = "icinga2";
$dbhost = "x.x.x.x";
$dbuser = "yourusernamehere";
$dbpass = "yourpasswordhere";
$AvailabilityViewName = "view-host-state-history";

$conditions = "";
$Cond_Start_Date = '';
$Cond_End_Date  = '';
$Cond_groupname = '';

$states = [0=>'up',1=>'down',2=>'unreachable'];

$DisplayHistData = TRUE;
}else{ $DisplayHistData = FALSE; }

if(array_key_exists('Start_Date',$_POST) AND $_POST['Start_Date'] <> ''){
  $dt_Start_Date = date_create( $_POST['Start_Date']);
  $dt_Start_Date = date_create('now')->sub(new DateInterval('P1M'));
$Cond_Start_Date = $dt_Start_Date->format('Y-m-d h:m');
$conditions .= "state_time > '".$Cond_Start_Date."' AND ";

if(array_key_exists('End_Date',$_POST) AND $_POST['End_Date'] <> ''){
  $dt_End_Date = date_create( $_POST['End_Date'] );
 $dt_End_Date = date_create('now');
$Cond_End_Date = $dt_End_Date->format('Y-m-d h:m');
$conditions .="state_time < '".$Cond_End_Date."' AND ";

if(array_key_exists('groupname',$_POST) AND $_POST['groupname'] <> ''){
  $Cond_groupname = $_POST['groupname'];
  $conditions .="groupname = '".$Cond_groupname."' AND ";

if($conditions <> ""){
        $conditions = "WHERE $conditions";
        $conditions = substr($conditions,0,-5);

$dbcon = pg_connect("dbname=$dbname host=$dbhost user=$dbuser password=$dbpass");

$query = 'SELECT groupname,hostname,address,state_time,state from public."'.$AvailabilityViewName.'"'.$conditions;
$result = pg_query($query);

$data = null;
while($row = pg_fetch_assoc($result)){
        $data[$row['hostname']][] = ['state' => $row['state'], 'time' => date_create($row['state_time']) ];

$groupnameqry = 'SELECT DISTINCT groupname FROM public."'.$AvailabilityViewName.'" Order by groupname ASC';
$groupnamerecords = pg_query($groupnameqry);
$groupnames = null;
while($row = pg_fetch_assoc($groupnamerecords)){ $groupnames[] = $row['groupname'];}

function statedatecomp($a, $b){
        $t1 = $a['time']->getTimestamp();
        $t2 = $b['time']->getTimestamp();
        return $t1 - $t2;

function addintervals($a,$b){
        $t = new DateTime('00:00');
        $x = clone $t;

        $a->invert = 0; //make absolute because interval seems unreliable? confirm this? maybe we did something else wrong
        $b->invert = 0; //


        return $t->diff($x);

<form method="post" action="">

  <label for="groupname">Host Group</label>
  <select id="groupname" name="groupname">
    <option value="">-All-</option>
    foreach($groupnames as $gname){
      $sel= ($gname == $Cond_groupname ? 'selected="selected"' : '');
      ?> <option value="<?=$gname?>" <?=$sel?>><?=$gname?></option> <?

  <label for="Start_Date">Start Date</label>
  <input id="Start_Date" name="Start_Date" type="text" maxlength="255" value="<?=$Cond_Start_Date?>"/>

  <label for="End_Date">End Date</label>
  <input id="End_Date" name="End_Date" type="text" maxlength="255" value="<?=$Cond_End_Date?>"/>

  <input type="submit" name="submit" value="Submit" />
  <label for="Show_History" >Show History </label>
  <input id="Show_History" type="checkbox" name="Show_History"<?=($DisplayHistData?"checked":"") ?> />

    <?if($DisplayHistData){ ?> <th>history</th> <?}?>
    <?foreach($states as $state) echo "<th>$state</th>" ?>
foreach( $data as $key => $value){ //loop through each CI
        echo "<tr><td>$key</td>";
        $prevtime = $dt_Start_Date;//= null; //initialise start time to the beginning start period
        $timediff = null;
        $prevstate = $states[0];//= null; //assume initial state  TODO: dont do this? maybe allow a user choice?
        foreach( $states as $state){
          ${$state} = new dateinterval('P1Y');
        if($DisplayHistData) echo "<td><ol>";
        foreach( $value as $state ){ //loop through each state record
                $s = $state['state'];
                $sname = $states[$s];
                //if($prevtime !=null){
                $timediff = $state['time']->diff($prevtime);
                ${$prevstate} = addintervals(${$prevstate},$timediff);
                if($DisplayHistData) echo "<li>".$state['time']->format('Y-m-d H:i:s')." - $prevstate for ".$timediff->format('%Mm %Dd %Hh %im %ss')."</li>";
        //last state until end period (yes repeated code, yes we can do better.. later)
        $lastbit = end($value);
        $timediff = $lastbit['time']->diff($dt_End_Date);
        ${$prevstate} = addintervals(${$prevstate},$timediff );
        if($DisplayHistData) echo "<li>".$dt_End_Date->format('Y-m-d H:i:s')." - $prevstate for ".$timediff->format('%Mm %Dd %Hh %im %ss')."</li></td></ol>";

        foreach($states as $stkey => $stvalue)
                echo "<td>".${$stvalue}->format('%Mm %Dd %Hh %im %ss')."</td> ";
        echo "</td></tr>";
(Michael Friedrich) #2

Thanks, I’ve moved it to #community:howto where it fits better and others may see it.

(Chris Wall) #3

oh yep, Thanks, that makes more sense.