Welcome to the extraxi blog...

If you found this page accidentally and don't know what extraxi is about... we specialise in reporting solutions for the Cisco Secure ACS and Funk SBR access control servers (aka AAA servers).

The servers are predominantly used to secure network services such as dial, wireless lan, vpn, firewall and network device management.

Typically these servers just chuck out MBs of raw CSV log data about network activity. What we do is to help collect this data then import and turn it into useable information.

Thursday, 29 January 2009

What the Government should be spending on in the credit crunch

A bit off the usual topic, but...

I was at my local gym this week (part of a larger council run leisure centre) and commented on how hot the poolside and changing rooms felt - not what you need after a 5km run!! It struck me how incredibly high their energy usage must be - 3 pools, sports hall, gym, various studios etc etc with what looks like quite a thin uninsulated roof.

It struck me that instead of pumping millions into banks, the car industry, more roads, 3rd runway at Heathrow etc.. that perhaps the government should embark on a project to retrofit all public owned buildings with solar thermal and electric (where possible). This would cost many millions of pounds but provide much needed jobs by creating a whole new industry and above all would SAVE MONEY and CO2 for years to come.

My local gym has a massive flat roof structure that would be ideal for mounting the panels and tubes etc. Right now the staff are being told to switch off lights to save money... given the lighting is all low energy anyway its hard to see them saving much.

Come on Mr Brown - spend money.. lots of money, but on the right stuff.

Calculated Fields in the Query Builder

This week I had to help a customer with the aaa-reports! query builder - amazingly flexible but equally a little hard to master! So I thought I'd post here by way of a primer.

This particular customer had imported their ACS user database and wanted to list inactive users. Although there is a canned report to do this, they wanted to export the data in XLS - which meant doing it in the query builder. Because  they are using ACS password ageing, the last authentication date is actually stored inside the ACS database, and is imported into aaa-reports!

So in this case we can use the Last Authenticated field as it holds... guess what? The last authenication date. We have to create a "Calculated Field" that is essentially the result of our test condition - say users who havent authenticated for 30 days. We can then set a criteria to test the calculated column.

So to get a .XLS of inactive users (via password ageing):
  1. Goto to the Query page and set the Query Type radio button to Filter/Sort
  2. Select ACS DB User Details from the Data Sets drop down
  3. On the Attributes tab select the user attributes you want to display
  4. On the Sorting tab pick the Last Authenticated attribute then click Add Ascending
  5. Click run
You'll now get users displayed with the oldest "last authenticated" date at the top. 

Ok, the above simple query will display all users and not just those that have been inactive for some period. To show only inactive users (say for 30 days or more) we need to modify the query slightly:
  1. Back on the Attributes tab, select the Calculated Fields radio button
  2. In the Name field enter IsInactive and in the Expression enter [Last Authenticated] < (Date() - 30)
  3. Click on Set to save the calculated column.
  4. Click Run again - you'll see a new IsInactive column with values 0 (false) and -1 (true)
  5. Finally on the Criteria tab select the new calculated col in the Attribute drop down, select <> from the Operator dropdown and enter 0  (zero) into the Value. Click Add
  6. Click Run again and now you will only see users whose last authentication date was more than 30 days ago.
This query can now be saved (for inclusion into a batch of reports) and exported to XLS, CSV etc.

This post shows how the use of a calculated field can help modify the ready made datasets in aaa-reports!