APC UPS Smart-UPS 750 XL battery constants

, posted: 23-Feb-2020 17:55

I purchased a second hand APC UPS (Smart-UPS 750 XL) off Trademe a while ago and purchased a Network Management Card for it to remotely control it.

The issue I had was the runtime remaining was always below 5 mins even though it has a brand new APC legit battery.

Finally figured out somehow the Battery Constant for the UPS was broken so I needed to re-program it so that the runtime showed correctly.

For this you need to connect to the serial port (not USB) and need a correctly wired serial cable.

As per this blog entry: https://alioth-lists.debian.net/pipermail/nut-upsuser/2005-August/000118.html

DB9M(UPS)             DB9F(Computer)
CHASSIS   9 <---------- < 5 SG (Ground/Signal Ground)
TXD          2 <---------- < 2 RXD
RXD          1 <---------- < 3 TXD

Then you should be able to use Putty or similar terminal tool with the setting of 2400 8/N/1 and make sure XON/XOFF Flow control is enabled (as I had it off and it doesn't work without it)

Next steps, as per these two blogs: https://conetrix.com/blog/manually-esetting-an-apc-smart-ups-battery-constant-after-new-battery-replacement or https://alioth-lists.debian.net/pipermail/nut-upsdev/2006-June/000938.html 

Make sure you have removed NMC cards, USB cables and everything that is connected to the UPS.

apcupsd documents a number of different constants, use at your own risk: http://www.apcupsd.com/manual/manual.html#resetting-the-ups-battery-constant

Check if there is a connection (Type Shift + Y, should return **SM** ). Do not enter any other characters via Hyperterminal other than that described in these instructions because this can cause irreparable damage to the UPS

Type **1**, wait 2-3 secs and type **1** again (Should return **Prog**)

Enter a **0** and the UPS reports the present value of the battery constant.  If this value does not correspond to the default value that was given to you by RM Support or APC , it must be changed.

If this value is not correct, press **+** or **-** until the correct value is returned.

The Smart-UPS 750 XL value I have on another 750 XL that is working fine and the value it has is 9E the UPS that wasn't working correctly had 17.

I updated the broken UPS by increasing the value from 17 to 9E.. and Hey presto at 30% load and would last for about an hour.

Press **R** to close the session. (Should return **Bye**)

Enter **<Shift> Y**, the UPS reports again with **SM**.

Enter **0** once again and check if the UPS reports back the standard setting that has been set.

Active Directory Kerberos authentication with Apache on Linux

, posted: 15-Feb-2020 08:56

Kerberos single sign-on with Apache and AD and LDAP authorization

I always forget how to do this as I only ever need to set it up so infrequently I thought I would document it here to remind myself of the steps. I am looking to create an AD Service Account (standard user) rather than having Linux fully registered with AD (machine account). Below detail the components and steps:

Components involved:

  • AD Service Account - Created on the AD Domain Controller and then using ktpass to create a Keytab file to copy to the Linux host
  • Linux Apache instance - Running Apache 2.4 without full AD / Kerberos federation and will need a working keytab file
  • Windows desktop - A member of the AD Domain that should be able to use the Kerberos ticket using IE, Chrome or Firefox. For IE&Chrome it is via Registry or Group Policy to add the domain/site you want to into the "Local Intranet" and for Firefox it is an advanced setting in firefox.
  • Adding LDAP Group based authorization - So you need to be part of a AD Group to gain access.

1) Active Directory Service Account.

Assuming you are running Windows 2008 or newer (including Forrest and Domain functional level upgraded to 2008 or higher!) then you should be able to use AES256-SHA1 rather than AES128/RC4 or DES crypto, since seriously you should be upgrading to strong crytpo. As per this MS article on Kerberos.

Create service account:

Creating the service account using Powershell to also enable Kerberos 256 on the account.

New-ADUser -Name "ServiceAccountCN" -Path "OU=ServiceAccounts,DC=AD,DC=DOMAIN,DC=LOCAL" -ServicePrincipalNames "HTTP/apachehost.dnsdomain" -SamAccountName "ServiceAccountSAM" -UserPrincipalName "ServiceAccountUPN@AD.DOMAIN.LOCAL" -AccountPassword (ConvertTo-SecureString "**PASSWORD**" -AsPlainText -Force) -passwordNeverExpires $true -Enabled $true -OtherAttributes @{'msDS-SupportedEncryptionTypes'="26"}

The following values should be changed in bold:

Name = This is the CN or how the record will be displayed in AD, it serves no functional use other than how it is displayed in AD Users and Computers

Path = The container to also create the service account, again probably not useful since otherwise users get created in cn=Users including the domain name.

ServicePrincipalNames (SPN) = This should be the Service Principal Name, starting with "HTTP/" in upper case no matter if you have the site http or https, and then the full DNS domain name of the Apache host.

SamAccountName (SAN) = The Sam Account Name, typically the same as the Name / CN but not always and needs to be unique in AD.

UserPrincipalName (UPN) = This is the object UPN, this will be overwritten by ktpass but it will be reference and needs to include the AD domain name in upper case.

AccountPassword = Shown here setting the password in clear text.

passwordNeverExpires, Enabled = You don't want the Service Account password to expire, and want the account enabled when you create it.

msDS-SupportedEncryptionTypes = 26 = This enables Kerberos AES128 and AES256 on the account. If you just want AES256 set it to 18 but I prefer 26.


Create keytab:

Now you have a Service Account created you need to create a keytab to import into Apache. To create the keytab file use ktpass. Either you could create a single keytab only supporting AES256, or a keytab with both AES128 and AES256. You shouldn't never add "-setupn" to the command line since then the UPN isn't updated, and the UPN MUST be the Service Principal Name ie "HTTP/servername.domain" otherwise AD doesn't know how to resolve the account name. It also means if you have multiple hostnames you need multiple service accounts and multiple keytabs.

Keytab only supporting AES256:

ktpass -princ HTTP/apachehost.dnsdomain@AD.DOMAIN.LOCAL -mapuser ServiceAccountUPN@AD.DOMAIN.LOCAL -pass **PASSWORD** -crypto AES256-SHA1 -ptype KRB5_NT_PRINCIPAL -out apachehost.keytab

Keytab supporting both AES128 and AES256:

ktpass -princ HTTP/apachehost.dnsdomain@AD.DOMAIN.LOCAL -mapuser ServiceAccountUPN@AD.DOMAIN.LOCAL -pass **PASSWORD** -crypto AES128-SHA1 -ptype KRB5_NT_PRINCIPAL -out apachehost-temp.keytab
ktpass -princ HTTP/apachehost.dnsdomain@AD.DOMAIN.LOCAL -mapuser ServiceAccountUPN@AD.DOMAIN.LOCAL -pass **PASSWORD** -crypto AES256-SHA1 -ptype KRB5_NT_PRINCIPAL -in apachehost-temp.keytab -out apachehost.keytab

Similar values should be change in bold:

princ = The principal of the domain, always prefixed with HTTP/ in upper case no matter if you are connecting over http or https and the ADDOMAIN should always be in upper case.

mapuser = The same value as UPN from above with the ADDOMAIN in upper case.

pass = The password you want to set, typically the same as the random password you used above to create the account, but it can be different.

crypto = AES256-SHA1 or AES128-SHA1

in/out = The name of the file to create, and if you specify an "in" you are loading the first keytab in to create a keytab with multiple records.


You should now see the "User logon name" aka UPN in AD Users and Computers "Account" tab has updated to be the same as the SPN. 

Now you should have a working keytab to load on Apache.

2) Linux Apache instance


yum install httpd mod_auth_kerb


apt-get install apache2 libapache2-mod-auth-kerb

Kerberos local configuration

There are numerous posts saying you need to update the /etc/krb5.conf file to resolve your domain. This is required if you want the host to prompt for a password and still be able to resolve the host. It's not strictly a mandatory requirement but helps.


 default_realm = AD.DOMAIN.LOCAL
 kdc_timesync = 1
 ccache_type = 4
 forwardable = true
 proxiable = true
 dns_lookup_realm = true
 kdc = addc.dnsdomain
 admin_server = addc.dnsdomain
apachehost.dnsdomain = AD.DOMAIN.LOCAL

Similar values should be change in bold:

default_realm = This should be set to the full DNS AD Domain name in upper case ie "COMPANY.ORG.NZ" or whatever the full domain is not just the local domain.

realms = Same value as the default realm to specify where the kdc and admin server are. The AD Domain value again needs to be the full domain and in upper case.

kdc and admin server = A domain controllers DNS name to resolve the DC.

domain_realm = This is the local hostname how you plan to access it exactly like the Service Principal Name but without HTTP/ prefixing it, and that should then map through to the ADDOMAIN full domain name again in upper case.

Apache Configuration:

Copy the keytab from the Windows Domain Controller to /etc/httpd (Centos) or /etc/apache2 (Debian/Ubuntu) or somewhere that Apache can access and has ownership of the file. The keytab should be considered sensitive so you should chmod it to 600 and chown to apache.


<Directory "/var/www/html/ad">
  AuthType Kerberos
  AuthName "Active Directory"
  KrbServiceName HTTP
  KrbMethodNegotiate on
  KrbMethodK5Passwd off
  Krb5Keytab /etc/httpd/apachehost.keytab
  require valid-user

Similar values should be change in bold:

KrbAuthRealms = This again should point to the AD Domain in upper case, and match the values in the krb5.conf

KrbMethodK5Passwd = If you want to prompt for password, which defeats the whole purpose of Kerberos auth.

Krb5Keytab = The location of the keytab created and copied as part of the ktpass process on the DC.


3) Windows Desktop

Natively IE, Edge and Chrome support Kerberos. Few Microsoft references



Local settings for IE, Edge and Chrome:

Control Panel -> Internet Settings -> Security -> Local Intranet -> Sites -> Advanced -> 

Add the host including if it is https or http into the URL ie "https://apachehost.dnsdomain" and / or "http://apachehost.dnsdomain" if the checkbox "Require server verification (https:) for all sites in this zone" then Apache will need to be setup with a a SSL/TLS certificate.

This will add registry keys under: HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap\Domains\ for the dns domain.

Sometimes it is easier (but somewhat less secure) if you want to add the whole local domain as wildcard so you can add "https://*.dnsdomain"


Group Policy settings for IE, Edge and Chrome:

These settings can be set via GPO in: https://blogs.manageengine.com/active-directory/2018/08/02/securing-zone-levels-internet-explorer.html or https://blog.thesysadmins.co.uk/group-policy-internet-explorer-security-zones.html

Computer Configuration -> Administrative Templates -> Windows Components -> Internet Explorer -> Internet Control Panel -> Security Page -> Site to Zone Assignment List

Set it to enabled, then add entries with the "Value name" is the site ie "http://apachehost.dnsdomain" and the "Value" to indicate it is for the Local Intranet is set to "1".

Once the GPO has been applied there will be registry keys under:

HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\CurrentVersion\Internet Settings

HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap\Domains

HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMapKey


Settings for Firefox:

As Firefox doesn't leverage built in Windows settings you need to set it yourself: https://developer.mozilla.org/en-US/docs/Mozilla/Integrated_authentication

Go to "about:config" and set the value:

network.negotiate-auth.trusted-uris = apachehost.dnsdomain

Or if you want to trust the whole corporate domain add the domain with a preceding dot:

network.negotiate-auth.trusted-uris = .dnsdomain


3) AD LDAP Based authorization.

Once you have logged in via Kerberos you need to modify the Directory setting in Apache to include the LDAP lookup.


# require valid-user

 AuthLDAPURL "ldaps://AD.DOMAIN.LOCAL/OU=Users,DC=AD,DC=DOMAIN,DC=LOCAL?userPrincipalName?sub?(objectClass=*)"
 AuthLDAPBindDN "CN=ServiceAccountCN,OU=ServiceAccounts,DC=AD,DC=DOMAIN,DC=LOCAL"
 AuthLDAPBindPassword "**Password**"
 require ldap-group CN=ApacheAccess,OU=Unix Access,DC=AD,DC=DOMAIN,DC=LOCAL

The LDAP URL should be LDAPs (as Microsoft are turning of cleartext LDAP), then specify the AD DNS Domain name as that should be able to resolve any DC, or point it to the IP address of your preferred DC. Then the search base should be the OU that contains all your users. The search value is userPrincipalName. That is also why I didn't enable "KrbLocalUserMapping On" since then the domain gets stripped and you are doing a lookup on samAccountName instead of UPN. And stick with the sub and objectclass=* or filter that down if desired.

Then specify the Service Account username & password.

And lastly comment out the "require value-user" and replace it with "require ldap-group CN=..." to the full CN of the AD Group that you want to use and you are done.



There is numerous blogs around enabling Apache to use Kerberos authentication for single signon to a website. Some of the better ones include:



But none of them cover all aspects to the solution to make sure it is working and how to debug it if it isn't or perhaps I just have never read them properly and they tend to include lot of it is also outdated talking about NT4 or AES128 ciphers and the correct way to create the keytab file you require.

Decrypting Sailpoint IdentityIQ (IIQ) "encrypted" passwords

, posted: 21-Feb-2019 14:27

Sailpoint IdentityIQ IIQ allows you create "encrypted" passwords so then you store those encrypted passwords in config files such as the database credentials that are stored within the iiq.properties file.




There are two versions of the symmetrical encryption either "AES" or "AES/CBC/PKCS5Padding". 

If it's a legacy AES one then the format is as above, "key number : encrypted password"

If it's a new "AES/CBC/PKCS5Padding" format then they insert "ACP" as an additional field with the same colon separator so it becomes: "1:ACP:JaGpXuLFE2btMQjnrggkdju449U/qfp1HLQA1rgSGno="


Sailpoint cleverly supports decrypting the "encrypted" passwords if you pass a "special" system property via the command line when calling the identityiq.jar which is called by the iiq command line tool. I found this decompiling the identityiq.jar under sailpoint.server.KeyStoreConsole where they had byte encoded the string they required (facepalm).




So you call Sailpoint command line using:

java -cp WEB-INF/lib/identityiq.jar -Dsailpoint.keyStore.consoleContext=magellan sailpoint.launch.Launcher keystore


You will see there are two new commands at then end "encrypt" and "decrypt"

> ?

Console Commands

? display command help
help display command help
echo display a line of text
quit quit the shell (same as exit)
exit exit the shell (same as quit)
source execute a file of commands
properties display system properties
time show how much time a command takes to run.
xtimes Run a command x times.
addKey Generate a new encryption key, the key will be securly generated and random.
list List the contents of the keystore.
master Change the master password and re-encrypt the keystore using the new master.
use Specify the keystore and master file to use when interacting with an alternate keystore.

> decrypt
decrypt <string>

If you have created a custom key rather than the standard supplied one it will default to use the latest key.

> encrypt hello

But you can encrypt using key 1

> encrypt hello 1

Or specify key 2

> encrypt hello 2

Then decrypt by just pasting the whole string in.

> decrypt 1:ACP:JaGpXuLFE2btMQjnrggkdju449U/qfp1HLQA1rgSGno=

Or the standard password stored within the iiq.properties for the database password:

> decrypt 1:iCAlakm5CVUe7+Q6hVJIBA==

You'll also notice that the list option displays generated AES keys in base64 format which is kinda hand.

> addKey
Generate a new encryption key (y/n)?
Generating a new encryption key for keystore [/data/sailpoint/WEB-INF/classes/iiq.dat].
New encrpytion key successfully saved to keystore.
All application servers must be restarted for changes to take effect.
> list
Listing contents for keystore [/data/sailpoint/WEB-INF/classes/iiq.dat].
KeyAlias Algorithm Format Object

2 AES RAW lcECExlG4AF/ehwvZ9SIKw==


Thanks sailpoint for making decrypting passwords so easy.

Useful SNMP OIDs for Home Automation

, posted: 14-Jan-2019 17:37

I've been playing with Home Assistant and using a few devices around my home network so thought it would be useful to document some of the useful SNMP OIDs I have found:


Device SNMP OID On Value Off Value Notes
APC 7920 PDU . 1 2 Increment the last value of the OID for the port number.
Raritan PX2-2190R . 1 0 Increment the last value of the OID for the port number.
Cisco 3750 PoE Switch inline power . 1 4 Increment the last value of the OID for the port number. Integer 4 is "disabled" and 1 is Auto




Device SNMP OID Notes
Raritan DPX-T2H2 sensor for PX2-2190R Temp . Need to divide number by 10 to get decimal place as it is an integer. The DPX-T2H2 has two sensors and the second sensor has the OID value of .3
Raritan DPX-T2H2 sensor for PX2-2190R Humidity . Humidity and the second sensor is .4
Raritan PX2-2190R . Rms Current
Raritan PX2-2190R . Rms Voltage
Raritan PX2-2190R . Active Power
Raritan PX2-2190R . Apparent Power
Raritan PX2-2190R . Power Factor
Raritan PX2-2190R . Active Energy
Raritan PX2-2190R . Active Energy
APC NMC AP9335T . Need to divide number by 10 to get decimal place as it is an integer


New Zealand Dialing validation regex

, posted: 10-Sep-2018 16:10

Was doing some work with the NZ Dialing plan and needed to write some regex to validate NZ numbers. Using the information from the NAD this is what I ended up with

Local area codes 03,04,06,07,09 are all 8 digits long including the 0
Mobiles 0201 to 0206 are either 10 or 11
Mobiles 0211 and 0212 are 10
Mobiles 0213 to 0219 are 9 
Mobiles 022, 027 and 029 are 10
Mobiles 028 are 9 to 11 digits
I could be wrong here, but that was my understanding. Feel free to comment on the above if any of it is incorrect.

Akamai IP Reputation Filter

, posted: 31-May-2018 11:53

So today I had an interesting issue where I was unable to access a web site from a customers internet connection.

It seems that Akamai IP Reputation Filter has for some reason decided that the reputation of this IP address has some random issue that will prevent me from accessing their web site.

The sites I can't access include:



When I attempt to access the sites I get:


Access Denied

You don't have permission to access "http://www.jbhifi.co.nz/" on this server.

Reference #18.67ff6dcb.1527719015.a6cebb8


At no point in the above do you have any idea this is related to Akamai Web Application Firewall blocking you. There are a number of not particularly helpful posts on the Akamai community talking about the issue.




And none of them provide any real resolution apart from contacting the site you are trying to access and then they can contact Akamai and figure out why.

So after a lengthy conversations it turns out the only way to resolve the issue is to send an email to "support at akamai.com" and then hopefully get to the bottom of why you are blocked.

My first email consisted of:


Akamai does not block users from accessing our customers’ websites. However, our customers can use tools and policies which may in turn block you (the end user). Our customers use these rules to protect them and you from malicious actors on the internet.

We have checked the logs you provided and have found you are being blocked because of a Web Application Firewall rule being triggered by your IP address. The most likely reason that a company or an end user may be blocked from several sites is due to Reputation-based blocking.  Billions of IP addresses interact with the Akamai Intelligent Platform every month, and the Client Reputation module provides information regarding the reputation of each of them. Customers with this module enabled can block IP addresses whose reputation exceeds a certain configurable threshold.

Some of other reasons that a block may be happening are:

Explicit IP blocking / blacklisting

Location-based blacklisting

Rule-based blocking (i.e. web application firewall protections)

HTTP request rate controls (e.g. DoS protections)

We cannot unblock your IP address as it is the Akamai clients who make up the rules to block certain customers. You can check directly with the website owners why your IP is being blocked if you feel you have not done any harm or any illegal activities on the site you are being blocked. 

Having said this, I have checked x.x.x.x and its not being blocked.


So that's no use at all, as we are still blocked. When I finally manage to figure out how to unblock our IP address I will update this blog.

Bogon Filtering using Regex

, posted: 24-Jan-2018 13:16

I had to filter the Bogon (RFC1918 + CGNAT (RFC6598) + Loopback (RFC990) + Link Local (RFC3927)) from within a proxy server I was setting up. RFC5735 covers a lot of the non-routeable addresses all in a single RFC with the exception of CGNAT. I used this page as a start and tweaked it to my own requirements.

My regex testing site of preference is: https://regex101.com/ as it has an excellent UI to test with and breaks out what the regex is doing.

This is the regex I ended up with:


Reading it the regex blocks the following ranges: &

Regex: ((?:10|127)(?:\.(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)){3})

Explanation - A non capture group ?: match 10 or 127 then capture ". + 1-255" {3} times as a non capture group. &

Regex: ((?:192\.168|169\.254)(?:\.(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)){2})

Explanation: A non capture group ?: match 192.168 or 169.254 then capture ". + 1-255" {2} times as a non capture group.

Regex: ((172\.(?:1[6-9]|2[0-9]|3[0-1])(?:\.(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)){2})

Explanation: "172." then non capture group 16-19 or 20-29 or 30-31 then capture ". + 1-255" {2} times as a non capture group.

Regex: ((100\.(?:6[4-9]|[7-9][0-9]|1[0-1][0-9]|12[0-7])(?:\.(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)){2})

Explanation: "100." then non capture group 64-69 or 70-99 or 100-119 or 120 to 127 then capture ". + 1-255" {2} times as a non capture group.


Hope someone finds this useful.

Extracting encrypted passwords from Sun Identity Manager

, posted: 2-Nov-2011 20:25

For my sins I am using Sun IDM.  And I was somewhat disappointed at how easy it is to extract any password from Sun IDM without needing any authentication.  As long as you have comand line access to call the LH you can extract the configurator password.  Here are a few links I found on this topic.


The second link includes the javascript code needed, but a session needs to be established first.  So this is the modified code I used.

/* Decrypt Sun IDM Passwords */
var _lhSession = new InternalSession();
print("Encrypted = " + arguments[0]);
var pwd = new EncryptedData();
print("Decrypted = " + pwd.decryptToString());
/* End */

Then have the above script called pwd.js.  Calling it as shown below:

./lh js pwd.js "3A961D0D453E218C:6BCF235D:122ED7C4BB6:-7FA4|GsggQAq2YSSbb1sZE9Xaxw=="
Encrypted = 3A961D0D453E218C:6BCF235D:122ED7C4BB6:-7FA4|GsggQAq2YSSbb1sZE9Xaxw==
Decrypted = configurator

So... to get the encrypted password you want, a modified version of the JS to take an input file and return the passwords:

/* Decrypt Sun IDM Passwords */
var _lhSession = new InternalSession();
var file = new FileReader(arguments[0]);
var br = new BufferedReader(file);
var line;
while ((line = br.readLine()) != null) {
var obj = _lhSession.getObject(Type.USER, line);
var pwd = new EncryptedData();
print(obj.getName() + "," + pwd.decryptToString());
/* End */

Plus i've now got the offline decryption working too by extracting out the encryption key:

$WSHOME/bin/lh console -c "getObject EncryptionKey *"

And the Misc data:

/* Extract Misc Data */
var _lhSession = new InternalSession();
var obj = _lhSession.getObject(Type.MISCELLANEOUS, "miscData");
print("Result" + obj.toXml());
/* End */

Then call the javascript:

$WSHOME/bin/lh js misc.js

Using the code shown above from Dark Edges.

Sad.. But true.

NZ Cell Site location information - Now with Google Fusion Tables!

, posted: 26-Aug-2011 13:00

Many of you may know that I have an existing blog entry with KML files you can download or load up in Google Earth here: http://www.geekzone.co.nz/BarTender/7403

So... Now thanks to Mark Hansen and is great work he did drawing all the links nationwide http://markhansen.co.nz/nz-wireless-map/

I've come to learn of Google Fusion Tables.  Now the super cool thing about Fusion Tables is you can map data from a table in fusion tables directly into Google Maps and not have the annoying 1000 Placemark limit you have with KMLs plus sluggish response when loading a KML file into Google Maps.

With Google Fusion you can either map directly from with Fusion from your dataset, or write up a small web page and do some pretty stuff.  I've got the web page so then I can add a Fusion Tables style in so all the place marks have a "T"/"V" etc depending on if it's a Telecom or Vodafone site.

Try V2 of The NZ Cell Information: http://dl.dropbox.com/u/22487235/fusion.html

If you look in the html it lists the Fusion Table data that I extracted out of RSM which can be found here: 1355049

NZ Cell Site Information

, posted: 26-Sep-2010 14:52

I have extracted the data out of the Radio Spectrum Management Database Spectrum Search Lite which is the Government agency who controlls all radio frequencies in NZ. They kindly put up their whole database as a Access 97 DB free for download (Yay for Open Govt!).  So this is the frequencies that the companies are allowed to transmit from, not necessarily an actual Cell Site.  But more often that not it IS a cell site.
First I transferred the Access Database into a SQLite Database using mdb-sqlite. Then using a SQL Query and Saxon I manipulated the files into KML Files.


I've been playing around with Google Fusion thanks to Mark Hanson's great entry showing how he used the RSM data to get all the radio links: http://wirelessmap.markhansen.co.nz/

Now I have a Google Fusion site showing all sites.

Update: I've just added Carrier specific kml's with icons at each site saying what frequencies are transmitted from that site.

Update, this is a work in progress with updated data and displaying the data slightly differently.

RSM Database dump 20 June 2011

If you want a copy of the whole archive to create the KMLs from the RSM Database download just PM me and I will send you the link.

All Sites

All Cell Sites by Carrier:
This lists all Cell Sites broken down by carrier.  The Google Maps 1000 Feature Limit means that it won't load properly in anything other than Google Earth.
KML and Google Maps Link

This has just Telecom, Voda, 2Deg and Woosh, and all the frequencies on a per-site basis.  It still doesn't open very well in Google Maps due to the large number of Placemarks.
And a GPS with the Site Name - Provider and Location all in the Name string:
http://sites.google.com/site/nzcellinfo/forgps.kml or Google Maps Link


All Telecom Sites: CDMA, XT850 and XT2100
KML or Google Maps Link

Telecom CDMA:
KML or Google Maps Link
Telecom XT 850 Mhz:
KML or Google Maps Link
Telecom XT 2100 Mhz:
KML or Google Maps Link


All Vodafone Sites: 900, 1800 and 2100
KML or Google Maps Link

Vodafone GSM 900:
KML or Google Maps Link
Vodafone GSM 1800:
KML or Google Maps Link
Vodafone WCDMA 2100:
KML or Google Maps Link

Note: I'm not sure which 900Mhz sites are 2G and which are 3G Extended.  This isn't registered in RSM since Vodafone own the frequency and can do what they like with it.

Two Degrees

All Two Degrees Sites: 900, 1800 and 2100
KML or Google Maps Link

Two Degrees GSM 900:
KML or Google Maps Link
Two Degrees GSM 1800:
KML or Google Maps Link
Two Degrees WCDMA 2100:
KML or Google Maps Link

Custom Two Degrees Maps

And New Cell Site Locations (that only have microwave backhaul so far).

KML or Google Maps Link

And All 2D Locations across the country.

KML or Google Maps Link


Woosh TD-CDMA 2067.5:
KML or Google Maps Link

Any other mobile networks I should add, just PM me.

Feel free to redistribute in any format you want, as this is public information from RSM anyway.

BarTender's profile

Bar Tender
New Zealand