Reply
Member
Posts: 207
Registered: ‎07-21-2010
Kudos: 46
Solutions: 5

Export IP, MAC, SSID, Product, Lat/Long and hostname from AC2.1b6

We need to feed inventory into the Sonar Billing / OSS platform.  https://sonar.software/

 

So, I dug around in postgres..

 

This basic parsing method will fall apart if the SSID or Hostname have
commas in them.  I don't have experience with json parsing.  Rather than take time to learn, I took the risk.  

 

I had two devices with ", Inc." in the hostname.   They showed up in the top two lines of the AC2Devices.csv file.  I fixed the AC2Devices.json file manually and re-ran the awk/sed command.

 

Status seems to be :  1 (un-monitored), 2 (active), or 3 (offline)

 

We had no status 4 devices.  The status 5 devices do not show in the AC2 GUI.

 

Results look like this: 

2,"1.1.1.62","68:72:51:00:00:00","REDWOOD FOREST","AirRouter HP",LAT,-LONG,"Doe Husband & Wife"
2,"10.10.10.179","24:A4:3C:00:00:00","SSID","PowerBeam M5 300",LAT,-LONG,"Last First"

I will have to further manipulate that data to get it into Sonar, but thought others might have a need to gather such information from their AC2 installation.   So here it is.

 

As postgres user (sudo -u postgres -s) :

cd /opt/Ubiquiti/AirControl2/pgsql/data

../bin/psql -d ac2 > AC2Devices.json <<EOF
select data from ac2.nodes where data::text LIKE '{"type": "com.ubnt.ac.model.nodes.Device"%';
EOF

awk 'BEGIN { FS = "," }; {print $9, $3, $4, $6, $11, $14, $15, $17}' AC2Devices.json \
 | sed -e 's/"earthPos": {//' -e 's/"status": //' -e 's/ "ip": /,/' -e 's/ "mac": /,/' -e 's/ "essid": /,/' -e 's/ "product": /,/' -e 's/ "lat": /,/' -e 's/ "lon": /,/' -e 's/ "hostname": /,/' | sort -n | uniq | grep '^[123]' > AC2Devices.csv

 

Veteran Member
Posts: 4,823
Registered: ‎03-02-2015
Kudos: 934
Solutions: 217

Re: Export IP, MAC, SSID, Product, Lat/Long and hostname from AC2.1b6

@lambert

1. direct postgreSQL access is neither supported nor recommended.
2. I would create a postgreSQL user only with read rights to keep the risk at minimum.

3. related to airOS >= 6.0.7 / 8.3.x:
UNMS will or maybe already has basic support to export (IP, name, SSID etc.) as simple csv. There is already a thread about...

anyway good luck with your project
===================================================
We all work for KUDOs here.
Thx
Member
Posts: 207
Registered: ‎07-21-2010
Kudos: 46
Solutions: 5

Re: Export IP, MAC, SSID, Product, Lat/Long and hostname from AC2.1b6

1.  Understood before we started.  Please, show me another supported way to get my information out of AC2.

 

2.  Too much effort for a one off grab of data.

 

3.  UNMS is not AC2.   We are using AC2 and may or may not ever go to UNMS.

 

My task is complete.  I didn't really have any questions.   Just figured I'd post what I came up with for anyone else who might run into a similar need.

Veteran Member
Posts: 4,823
Registered: ‎03-02-2015
Kudos: 934
Solutions: 217

Re: Export IP, MAC, SSID, Product, Lat/Long and hostname from AC2.1b6

[ Edited ]

Was not a indoctrination for U:
so at least you wrote regarding AC2 version... maybe this works with AC2.0.x.

1. Was just info for new guys reading this. AC2.1 has REST API whose documentation will be released soon. 
2. effort? would take 15 secs. but was more a advice/ warning for inexperienced users.
3. UNMS isn't a replacement for AC2. Just info for anyone who's already using UNMS + AC2.

===================================================
We all work for KUDOs here.
Thx
Highlighted
New Member
Posts: 5
Registered: ‎02-26-2010

Re: Export IP, MAC, SSID, Product, Lat/Long and hostname from AC2.1b6

[ Edited ]

Just had to fight this dog today.. 

 

I wrote a small php script ( I run it from the command line ) to do this with the new API in 2.1.1

 

You could easily add more fields to export if need be.

Writes two files , one with the json from aircontrol, and the second with the CSV that I needed.

Don't blast on error checking.. it works for me.

 

needs php with curl built-in

 

Richard

 

<?php

// set these to your aircontrol server 
// tested on AC2.1.1 Beta
//
// Richard Strittmatter 010419
//
// NO WARRANTY implied or otherwise.
//

$serverip = "10.0.0.1";
$user = "admin";
$pass = "password";

// dump of devices from AirControl
$jsonfile = dirname(__FILE__).'/ac2dump.txt';

// file for CSV output
$csvfile  = dirname(__FILE__).'/ac2decode.csv';

// curl cookie file
$cookie = dirname(__FILE__).'/cookie.txt';
$header = array('accept: application/json', 'Content-Type: application/json');
$curlObj = curl_init();

$options = [
        CURLOPT_URL => "https://".$serverip."/api/v1/login",
        CURLOPT_RETURNTRANSFER => 1,
        CURLOPT_FOLLOWLOCATION => 1,
        CURLOPT_AUTOREFERER => 1,
        CURLOPT_TIMEOUT => 60,
        CURLOPT_USERAGENT => 'Mozilla/5.0 (compatible; MSIE 5.01; Windows NT 5.0)',
        CURLOPT_HTTPHEADER => $header,
	CURLOPT_IPRESOLVE => CURL_IPRESOLVE_V4,
	CURLOPT_SSL_VERIFYHOST => false,
	CURLOPT_SSL_VERIFYPEER => false,
	CURLOPT_COOKIEJAR => $cookie,
	CURLOPT_COOKIEFILE => $cookie,
        CURLOPT_POST => true,
        CURLOPT_POSTFIELDS => "{\"username\":\"".$user."\",\"password\":\"".$pass."\",\"eulaAccepted\":true,\"verifyCsrfToken\":true}"
];

curl_setopt_array($curlObj, $options);
$returnData = curl_exec($curlObj);

if (curl_errno($curlObj)) {
	echo curl_error($curlObj);
	exit;
}

// output devices to file

$fp = fopen($jsonfile, "w");
curl_setopt($curlObj, CURLOPT_FILE, $fp);
curl_setopt($curlObj, CURLOPT_POST, false);
curl_setopt($curlObj, CURLOPT_URL, "https://".$serverip."/api/v1/devices");

$returnData = curl_exec($curlObj);

if (curl_errno($curlObj)) {
	echo curl_error($curlObj);
	exit;
}
curl_close($curlObj);
unlink($cookie);
fclose($fp);

//read back data from ac2.1.1 api / devices
$jsondata = file_get_contents($jsonfile);
$ac2 = json_decode($jsondata,true);

//open file to write csv
$fp =  fopen($csvfile,"w");

$item = array(); 
// 0 - status
// 1 - ip
// 2 - mac
// 3 - product
// 4 - lat
// 5 - lon
// 6 - hostname 

foreach($ac2['results'] as $device) {

	$item[0] = $device['properties']['status'];
	$item[1] = long2ip($device['properties']['ip']);
	$item[2] = $device['properties']['mac'];
	$item[3] = $device['properties']['product'];

	if (isset($device['location']['gps']['lat'])) {

		if ($device['location']['gps']['lat'] != 0) {
			$item[4] = $device['location']['gps']['lat'];
			$item[5] = $device['location']['gps']['lon'];

		} else if ( $device['location']['custom']['lat'] != 0) {
			$item[4] = $device['location']['custom']['lat'];
			$item[5] = $device['location']['custom']['lon'];
		}

	} else {
		$item[4] = 0;
		$item[5] = 0;
	}

	$item[6] = $device['properties']['hostname'];
	fputcsv($fp,$item);
	unset($item);
}
fclose($fp);

?>

 

Reply