Latest Entries »

Further to a post over 3 years ago http://tickett.wordpress.com/2011/09/27/updateable-excel-frontend-for-sql-view/ – I have finally given up the search for a ready made solution and built something myself.

I work with a number of systems which have functionality gaps and/or need a simpler (quicker) interface for data entry. The solution is essentially a SQL view (or stored procedure) with a simple grid (excel like) front-end. If any data needs to be captured not currently handled by the system a custom table is created to hold the fields.

My previous Excel based solution works rather well, but is starting to show it’s age. I am now beta testing a web based solution i have built;

Screen Shot 2014-12-11 at 20.34.43

The application is extremely simple to configure- enter the SQL to retrieve & update the data;

Screen Shot 2014-12-11 at 20.43.43

And list the columns with some attributes (ReadOnly, Hidden, Title, Width etc);

Screen Shot 2014-12-11 at 20.42.10

And you’re all set!

Functionality currently includes;

  • Easy configuration
  • Simple/fast data entry (with validation)
  • Column resizing (non-persistent)
  • Sort on any column

Next steps;

  • Test (find and fix bugs)
  • Optimise code
  • Allow parameters in select SQL

That’s better!

After discovering last night that my newly acquired Broadcom 57810A Dual Port 10Gb PCIE Copper (RJ45) Ethernet NICs didn’t fit my Supermicro AS-2022TG-HIBQRF, I was pleased to find they do fit my Supermicro AS-1042G-LTF.

And best of all, absolutely no setup/configuration was necessary. I simply powered down the host, replaced the NIC and powered back up. The ports automatically assigned the same as the NIC I removed.

Screen Shot 2014-12-05 at 09.08.19

Doh! It doesn’t fit!

Looking for a cheaper alternative to the Intel X540-T2 (Dual Port 10Gb PCIE Copper (RJ45) Ethernet NIC) I purchased a Broadcom 57810A (Dual Port 10Gb PCIE Copper (RJ45) Ethernet NIC).

I eagerly opened up my server (Supermicro AS-2022TG-HIBQRF) but it doesn’t quite fit!

Screen Shot 2014-12-04 at 22.14.11

You can see the plastic RAM shroud just touching, and the CPU heatsink just touching but unfortunately the problem is the heatsink screw (just below the back end of the card);

Screen Shot 2014-12-04 at 22.14.36

Very frustrating! If the screw lay where the 20 pin connector / holes were i’d consider getting out the dremel. You can clearly see some chips where i’d need to cut though!

I have had a look and can’t find any alternative heatsinks that might free up the space either!

Guess i’ll have to stick with the Intel X540-T2 for now (admittedly I don’t even know if the Broadcom 57810A is compatible with the server & ESX yet… although I will be sure to try it in my Supermicro AS-1042G-LTF later (watch this space).

Here’s the X540-T2;

Screen Shot 2014-12-04 at 22.23.05

I have just setup an SSRS report for my helpdesk to show open calls, unread e-mails, some KPIs and graphs. I bought a cheap monitor, wall mounted and attached a raspberry pi.

I started with the latest Raspbian image and spent some time tweaking it to boot into x and launch chromium with the homepage set to the URL of my report. Unfortunately it was pretty flakey and always prompted for username/password so I had to VNC in every morning to authenticate.

Yesterday morning chromium decided it no longer wanted to load the report. I tried running;

sudo apt-get update
sudo apt-get upgrade
sudo raspi-update

And rebooted… Or at least I tried to but it would no longer boot- hanging on a message along the lines of;

mmcblk0: error -110

Rather than start again from scratch I decided someone else must have already tackled this “kiosk” style scenario and went on the hunt. Enter Raspberry Webkiosk; http://www.binaryemotions.com/raspberry-webkiosk/ (after a quick trial I paid the €9.90 “donation” to get the full version).

The image took care of the operating system, booting straight into full screen chromium with the home page set to the report URL but I was still stuck with the authentication prompt.

I hoped I could simply enable Anonymous Authentication in IIS, but the report server virtual directories don’t appear in IIS and the information available online all suggests this can’t be done.

The next idea was to try and configure chromium to remember the login credentials. Whilst I was able to save the username and password the prompt still persisted.

Final idea was to try and use an asp/html script of some sort. I then remembered a site I stumbled across recently with some sample/demo reports; http://www.reportsurfer.com/ which didn’t require authentication. I took a look at the html source and could see some simple javascript being used to pass the credentials.

So I built a simple “proxy” page in the default IIS site to redirect to the SSRS report and pass the username & password (the script must be on the same hostname/port to work).

<html>
  <head>
   <script>
var _0x1751=["\x61\x66","\x76\x76"];

function getHTTPObject() {
    if (typeof XMLHttpRequest != 'undefined') {
        return new XMLHttpRequest();
    }
    try {
        return new ActiveXObject("Msxml2.XMLHTTP");
    } catch (e) {
        try {
            return new ActiveXObject("Microsoft.XMLHTTP");
        } catch (e) {}
    }
    return false;
}	

function runReport() {
  var http = getHTTPObject();
  var url= "http://rs-01/ReportServer/Pages/ReportViewer.aspx?%2fTEL+Wall+Thing%2fWallDash&rs:Command=Render";
  http.onreadystatechange = function() {
    if (http.readyState == 4) {
      if (http.status == 401) {
        runReport(); 
      }
      if (http.status == 200) {
        document.location = url;
      }
    }
  };
  http.open("get", url, true, _0x1751[0x0], _0x1751[0x1]);
  http.send(null);
  return false;
}
    </script>
  </head>
  <body onload="runReport();">
  </body>
</html>

To use the script yourself, you essentially need to amend the top line;

var _0x1751=["\x61\x66","\x76\x76"];

The first value is the username and the second value is the password (hex encoded just to add some really basic obfuscation). Obviously this is easily reversible so you should create a new account with only permission for the specific report(s) they should be able to access. You can use http://string-functions.com/string-hex.aspx to convert your login details to hex.

Then simply update the report URL;

var url= "http://rs-01/ReportServer/Pages/ReportViewer.aspx?%2fTEL+Wall+Thing%2fWallDash&rs:Command=Render";

And you’re all set!

helpdesk

I provisioned a new Windows Server 2012 R2 VM to be used as a Domain Controller and another to be used for VMWare Update Manager and Veeam (Backup and Replication).

Assign a static IP address, and install all windows updates (this takes considerable time and numerous reboots).

Domain Controller

Follow the “wizard”. The main thing to note (as previously mentioned)- follow best practice when choosing a domain name; I’ve always gone with something.local or something.home in the past, but suffered as a result. I did a little research and found some articles suggesting best practice is to use a subdomain of an internet facing domain you own http://www.mdmarra.com/2012/11/why-you-shouldnt-use-local-in-your.html. So, say you own microsoft.com, your internal domain name may be ad.microsoft.com. You configure the NETBIOS name to be whatever you like, this will be used when you logon using NETBIOS\User rather than user@ad.microsoft.com.

Now you can join the other Windows Server to the domain and configure the identity source in vCenter. This took me a little longer than anticipated; You must login as administrator@vsphere.local (not root).

Update Manager

  • Install update manager (follow the “wizard”)
  • Login to vCenter (using vSphere)
  • Ensure all virtual machines off of host
  • Scan
  • Attach (patch and upgrade baselines)
  • Remediate (check both baselines and check all patches)
  • Repeat for each host

Veeam

  • Install Veeam
  • Connect to vCenter
  • Setup Backup Repository
  • Configure Backups (I stick roughly to the default… Weekly full backup with daily incrementals, retaining 14 restore points). *I added the entire datacenter to the job, so as I add new VMs they will automatically be included in the backup job. I can then create a new datacenter to store development machines and/or anything I don’t want included in the nightly backups*
  • Deploy vCenter Virtual Appliance
  • Configure static IP address, hostname etc
  • Check for & install updates (this took quite a while and the web interface appeared to hang, be patient)
  • Reboot
  • Launch & complete the setup wizard
  • Login to the web interface
  • Create a datacenter & cluster and add your hosts
  • Create vSphere Distributed Switch
  • *This is the step i’ve often missed which then causes loss of network connectivity. You then have to connect to the console (IPMI) and reset the host networking and start over!* : Edit the Distributed Port Group settings, under “Teaming and failover” move the uplink port(s) you intend to use to “Active uplinks”
  • Assign physical NICs to vDS
  • Migrate VMKernel  network adapters to vDS
  • *Assign SSL certificate (i’ve yet to do this, and imagine some detail will be required)

I have some additional hardware to setup. So will probably try and follow my own guide sometime in the coming weeks. I may add some screenshots and if it seems like any detail is missing.

Following on from: http://tickett.wordpress.com/2014/11/24/building-hosting-environment-part-1-hardware/

  • Configure IPMI (either use a static IP or setup a static DHCP lease)
  • Tweak the bios (ensure options are optimised for performance rather than to minimise noise etc)
  • Add DNS* entries for your IPMI and ESX Management Interfaces
  • Install ESXi (I did everything without the need to even plug a monitor/keyboard in, IPMI is a life saver)
  • Configure your management interfaces (use the IP addresses you previously configured in DNS, and the domain name you previously selected)

Now you can login with the vSphere client and configure a few more items;

  • NTP (on the Configuration tab under Software, Time Configuration)
  • Add your datastore (i’m using NFS, so I had to add a VMKernel interface first)

Until we have our vCenter server up and running we will stick to a single NIC.

*If you don’t yet have a device which provides DNS (router), you can add entries to your hosts file for now.

*Choosing a domain name; I’ve always gone with something.local or something.home in the past, but suffered as a result. I did a little research and found some articles suggesting best practice is to use a subdomain of an internet facing domain you own http://www.mdmarra.com/2012/11/why-you-shouldnt-use-local-in-your.html. So, say you own microsoft.com, your internal domain name may be ad.microsoft.com. You configure the NETBIOS name to be whatever you like, this will be used when you logon using NETBIOS\User rather than user@ad.microsoft.com.

Server/Network Monitoring

The problem is that we are spoilt for choice! My requirements are pretty basic, yet making a decision and wading through all the information/trialling etc was proving quite a challenge!

The feature set I’m looking for is pretty basic;

  • connectivity (probably through ping)
  • network throughput/bandwidth usage
  • website availability
  • sql server availability
  • disk space
  • cpu utilisation
  • disk queue length

The number of monitors/sensors/servers initially will be quite low, but i’m always nervous about purchasing something which is going to end up costing me substantial amounts if/when it needs expanding (always prefer unlimited licenses!).

And perhaps most importantly, I was really hoping to find something which sites on top of a Microsoft SQL Server backend database. This will make ad-hoc queries much simpler and allow me to pull data into existing SQL Server Reporting Services (SSRS) reports.

  • The top players in the industry appear to be Whatsup Gold & IPMonitor, both of them hold a pretty hefty price tag.
  • We then have a few big players with no price tag… Spiceworks & Nagios. I’ve used Spiceworks before, and find it too bloated (it does everything OK, but nothing very well… not to mention the adverts. Nagios sounds quite complex to get up and running, although a potential contender.
  • ServersAlive looked like an option with a very reasonable price tag. Although it doesn’t directly support a SQL Server backend, it does support the logging of “state changes” to SQL Server. Unfortunately the software looks incredibly dated- i’m not too sure it’s being actively maintained/developed.

And none of these use SQL Server as a backend database. Maybe I should just build something myself… my requirements are pretty basic… Luckily before making a purchase or starting to roll my own, i found this page on wikipedia;

http://en.wikipedia.org/wiki/Comparison_of_network_monitoring_systems

I took a quick look at the website for each tool listed as supporting MS SQL Server backend db and quickly found myself down to NetXMS (http://www.netxms.org/).

I installed the server along with a linux and windows agent (fairly effortless) and it’s all looking rather promising. I’ve started by setting up a few basic monitors and will hopefully find time over the coming weeks to add;

  • Additional monitors
  • Alerts
  • Poke around in the database (pull some data into SSRS)

I hope to be installing some equipment in a local datacenter to offer some hosting services. First item, the hardware;

  • Ubiquiti Edgerouter Lite
  • Dell 8024 (24x 10GbE Switch)
  • Synology RS3614RPXS NAS (6x WD RED 3TB + 2x Samsung EVO 840 1TB + Intel X540-T2 10GbE NIC)
  • 2x Supermicro AS-2022TG-HIBQRF (each w/ four nodes w/ 64GB RAM & 2x Opteron 6176 + Intel X540-T2 10GbE NIC)

Initially I went for a combination of the Netgear Prosafe XS708E (8x 10GbE Switch) paired with a Dell (24x 1GbE Switch) but quickly found myself running out of 10GbE ports and concerned about the lack of redundant power supplies.

Likewise, I had chosen the RS3614XS but felt the additional cost of the RP (model with redundant power supplies) was justified.

And finally the servers themselves, initially Supermicro AS1042G-LTF (single node with four sockets and single power supply) but then switching to the AS-2022TG-HIBQRF (four node, each with two sockets and shared redundant power supplies).

I’ve tried to avoid single points of failure at a component level (redundant power supplies etc) but without overkill couldn’t avoid it at device level (redundant switches, NAS etc).

Supplier wise… I got the switch from http://www.etb-tech.com/ and the NAS from http://elow.co.uk/ (both of which admittedly i had my doubts about when first placing the orders, as the prices seemed a little cheap, but the service was incredible, both dispatched same day using next day couriers). The rest from eBuyer and local suppliers.

Each device is connected to the switch using 2x10GbE LAG/LACP ports (I may go more into the configuration of this later).

VirtualBox High CPU Usage on OSX

I noticed the fan was constantly making a lot of racket in my MacBook Air lately (running Yosemite 10.10). VirtualBox appears to be the culprit, even when my Windows 7 guest is idling (showing as 100%+ in Activity Monitor).

After reducing the number of assigned processors to 2 (was previously 4) everything seems to be back to normal. I also set the max usage to 75% (although this had no impact when set to 4 processors, so i’m not sure if it actually helps).

Looking forward to the increased battery life more than anything.

Follow

Get every new post delivered to your Inbox.

Join 2,262 other followers

%d bloggers like this: