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

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!

No comments: