Unifi Überwachung ohne Adapter und viel Ressourcen

sissiwup

Hallo,

die Überwachungen für die Unifi-Geräte waren mir bisher immer zu Ressourcenhungrig, da viele Datenpunkte in kurzer Zeit abgefragt wurden.
Was mich eigentlich interessiert gibt es Probleme mit einen Device, springt es häufig zwischen APs oder ist ein AP ausgefallen, so dass ich ihn neu booten muss (per Skript).

Da Unifi eine PHP Schnittstelle hat, habe ich mir diese installiert.
Ein Beispielskript angepaßt und das alle 15 Minuten (reicht mir) laufen lassen.
Darauf ein wenig grafana und alles da was ich brauche.

Angefangen wo speichere ich meine Daten. Ich mache das oldschool in einer MySQL-Datenbank: (ggf. eure Datenbank anpassen)

--
-- Tabellenstruktur für Tabelle `unifi`
--

CREATE TABLE `unifi` (
  `id` bigint(20) NOT NULL,
  `ts` timestamp NOT NULL DEFAULT current_timestamp(),
  `status_date` datetime DEFAULT NULL,
  `site_id` text COLLATE utf8mb3_bin DEFAULT NULL,
  `assoc_time` datetime DEFAULT NULL,
  `latest_assoc_time` datetime DEFAULT NULL,
  `oui` text COLLATE utf8mb3_bin DEFAULT NULL,
  `user_id` text COLLATE utf8mb3_bin NOT NULL,
  `mac` text COLLATE utf8mb3_bin NOT NULL,
  `is_guest` tinyint(1) NOT NULL DEFAULT 0,
  `first_seen` datetime DEFAULT NULL,
  `last_seen` datetime DEFAULT NULL,
  `name` text COLLATE utf8mb3_bin DEFAULT NULL,
  `_uptime_by_uap` int(11) DEFAULT NULL,
  `ap_mac` text COLLATE utf8mb3_bin DEFAULT NULL,
  `ap_name` text COLLATE utf8mb3_bin DEFAULT NULL,
  `channel` int(11) DEFAULT NULL,
  `radio` text COLLATE utf8mb3_bin DEFAULT NULL,
  `radio_name` text COLLATE utf8mb3_bin DEFAULT NULL,
  `essid` text COLLATE utf8mb3_bin DEFAULT NULL,
  `bssid` text COLLATE utf8mb3_bin DEFAULT NULL,
  `powersave_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `is_11r` tinyint(1) NOT NULL DEFAULT 0,
  `ccq` int(11) DEFAULT NULL,
  `rssi` int(11) DEFAULT NULL,
  `noise` int(11) DEFAULT NULL,
  `signale` int(11) DEFAULT NULL,
  `tx_rate` int(11) DEFAULT NULL,
  `rx_rate` int(11) DEFAULT NULL,
  `tx_power` int(11) DEFAULT NULL,
  `idletime` int(11) DEFAULT NULL,
  `ip` text COLLATE utf8mb3_bin NOT NULL,
  `satisfaction` int(11) DEFAULT NULL,
  `anomalies` int(11) DEFAULT NULL,
  `uptime` int(11) DEFAULT NULL,
  `tx_bytes` bigint(20) DEFAULT NULL,
  `rx_bytes` bigint(20) DEFAULT NULL,
  `wifi_tx_attempts` int(11) DEFAULT NULL,
  `qos_policy_applied` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=Aria DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin;

--
-- Indizes der exportierten Tabellen
--

--
-- Indizes für die Tabelle `unifi`
--
ALTER TABLE `unifi`
  ADD PRIMARY KEY (`id`),
  ADD KEY `mac_i` (`mac`(17),`name`(32),`ap_name`(32),`ts`),
  ADD KEY `name_i` (`ts`,`name`(32),`signale`,`satisfaction`,`essid`(32)) USING BTREE,
  ADD KEY `ts_i` (`ts`,`mac`(17),`ap_name`(32),`name`(32)) USING BTREE,
  ADD KEY `ts_id` (`ts`,`id`);

--
-- AUTO_INCREMENT für exportierte Tabellen
--

--
-- AUTO_INCREMENT für Tabelle `unifi`
--
ALTER TABLE `unifi`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;
COMMIT;

Zwei Skripte um die Daten abzufragen: list_clients.php
Hier auf jeden Fall die MAC-Adressen eurer APs eintragen und die Namen.

<?php
/**
 * PHP API usage example
 *
 * contributed by: Art of WiFi
 * description:    example to pull site health metrics from the UniFi controller and output the results
 *                 in json format
 */

/**
 * using the composer autoloader
 */
require_once 'vendor/autoload.php';

/**
 * include the config file (place your credentials etc. there if not already present)
 * see the config.template.php file for an example
 */
require_once 'config.php';

/**
 * the short name of the site you wish to query
 */

/**
 * initialize the UniFi API connection class and log in to the controller and pull the requested data
 */

$unifi_connection = new UniFi_API\Client($controlleruser, $controllerpassword, $controllerurl, $site_id );
$set_debug_mode   = $unifi_connection->set_debug($debug);
$loginresults     = $unifi_connection->login();
$result           = $unifi_connection->list_clients();

/**
 * output the results in correct json formatting
 */
//header('Content-Type: application/json');
// echo (json_encode($result, JSON_PRETTY_PRINT));
// $data = json_decode($result, true);
//
$conn = new mysqli("SQL-SERVER-NAME-ODER-IP-HIER","USER-HIER","PASSWORT-HIER","NAME-DER-DATENBANK-HIER");
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
$trans = array('18:e8:29:c6:39:85' => 'AP1 DG Wintergarten',
	       'b4:fb:e4:f3:69:fb' => 'AP2 2OG Buero',
	       'fc:ec:da:a6:44:a8' => 'AP3 Dachboden',
	       '18:e8:29:c9:1e:6c' => 'AP4 DG Atrium',
	       '78:8a:20:4b:04:6a' => 'AP5 Abstellraum',
	       '78:8a:20:53:8f:ac' => 'AP6 Katzen',
	       '80:2a:a8:99:6d:1d' => 'AP7 Garage');
$importDate = date('Y-m-d H:i:s');

foreach ($result as $key => $value) {
//	$data=get_object_vars($value);
//	ksort($data);
	//	var_dump($data);
	if (!isset($value->name)) {
	//	var_dump($value);
		if (isset($value->hostname)) {
			$value->name=$value->hostname;
		} else {
			$value->name="unknown";
		}
	}
	if (!isset($value->ip)) {
		$value->ip="0.0.0.0";
	}
	$value->is_guest=intval($value->is_guest);
	$value->powersave_enabled=intval($value->powersave_enabled);
	$value->is_11r=intval($value->is_11r);
	$value->qos_policy_applied=intval($value->qos_policy_applied);
	$value->ap_name=$trans[$value->ap_mac];
	$value->assoc_time=date('Y-m-d H:i:s',$value->assoc_time);
	$value->latest_assoc_time=date('Y-m-d H:i:s',$value->latest_assoc_time);
	$value->first_seen=date('Y-m-d H:i:s',$value->first_seen);
	$value->last_seen=date('Y-m-d H:i:s',$value->last_seen);
	$sql = "INSERT INTO unifi(status_date, site_id, assoc_time, latest_assoc_time, oui, user_id, mac, is_guest, first_seen, last_seen, name,
		_uptime_by_uap, ap_mac, ap_name, channel, radio, radio_name, essid, bssid, powersave_enabled, is_11r, ccq, rssi, noise, signale,
		tx_rate, rx_rate, tx_power, idletime, ip, satisfaction, anomalies, uptime, tx_bytes, rx_bytes, wifi_tx_attempts, qos_policy_applied)
		VALUES (
			'$importDate', '$value->site_id', '$value->assoc_time', '$value->latest_assoc_time', '$value->oui', '$value->user_id',
		       	'$value->mac', '$value->is_guest', '$value->first_seen', '$value->last_seen', '$value->name',
			'$value->_uptime_by_uap', '$value->ap_mac', '$value->ap_name', '$value->channel', '$value->radio', '$value->radio_name', '$value->essid', '$value->bssid',
			'$value->powersave_enabled', '$value->is_11r', '$value->ccq', '$value->rssi', '$value->noise', '$value->signal',
			'$value->tx_rate', '$value->rx_rate', '$value->tx_power', '$value->idletime', '$value->ip', '$value->satisfaction', '$value->anomalies',
			'$value->uptime', '$value->tx_bytes', '$value->rx_bytes', '$value->wifi_tx_attempts', '$value->qos_policy_applied'
		)";
	// echo($sql);
	if ($conn->query($sql) === TRUE) {
		  echo $importDate, " ", $value->mac," ",$value->name," New record created successfully\n";
	} else {
  		echo "Error: " . $sql . "\n" . $conn->error;
	}
}

$conn->close();

Die verwendete config.php:

<?php
/**
 * the short name of the site you wish to query
 */
$site_id = 'default';

/**
 * initialize the UniFi API connection class and log in to the controller and pull the requested data
 */

$controlleruser = 'user-unifi-controller';
$controllerpassword = 'password-unifi-controller';
$controllerurl = 'https://rechner-oder-ip-unifi-controller:8443';
$controllerversion = '6';
$debug = false;

jetzt noch das Skript was alle 15 Minuten läuft:

#!/bin/bash

logfile=/var/skripte/unifi_stats.log
echo "$(date) Starte unifi_stats" >> $logfile
cd /var/skripte/unifi
php list_clients.php >> $logfile
echo "$(date) Ende unifi_stats" >> $logfile

In grafana kann man dann z.B. mit

with anz as 
  (select count(name) as anz2 ,name as aname
   from zotac.unifi where $__timeFilter(ts) and name<>"unknown" and length(name)>0
   group by name order by anz2 desc fetch first row only), 
   list as 
  (select count(name) as anz3,name as lname
   from zotac.unifi where $__timeFilter(ts) and name<>"unknown" and length(name)>0
   group by name) 

SELECT
  CONVERT_TZ(ts,"MET","UTC") as time,
  signale as value,
  concat(u.name,"-",l.anz3,"/",a.anz2) as metric
FROM unifi u,list l,anz a
WHERE
  $__timeFilter(ts) 
  and length(name)>0 and name=lname
  and name<>"unknown" and name not like "%iPhone%" and name not like "%Pad%"
  and name not like "%mobil%"
  and name in (select distinct lname from list l,anz a where l.anz3<a.anz2)
  order by ts

Hier sieht man dann z.B. dass der weatherman 46/47 möglichen Kontakten hatte. Der iMac hat Nachts geschlafen und war nicht immer im WLAN.
Bildschirmfoto 2022-01-30 um 11.42.57 AM.png

Bei Interesse kann ich auch noch die Skripte für die Überwachung der APs posten.

NEWS

Unifi Überwachung ohne Adapter und viel Ressourcen

Support us

939
Online

31.9k
Users

80.2k
Topics

1.3m
Posts

NEWS

Unifi Überwachung ohne Adapter und viel Ressourcen

Support us

939Online

31.9kUsers

80.2kTopics

1.3mPosts

939
Online

31.9k
Users

80.2k
Topics

1.3m
Posts