Using SQL Queries to Access the Intermapper Database

 

When configured to do so, Intermapper can save a great deal of information about devices, interfaces, events, and performance data in the Intermapper Database. The best way to access information in this database is to use Intermapper Reports, but sometimes you may want to retrieve information not covered by one of our reports. Intermapper Database uses the PostgreSQL database engine and thus can be queried using SQL.

This document will demonstrate some of the techniques you'll need to know to query Intermapper Database effectively. It assumes you've looked over the schema, or at least that you have it open and available to look at. You can find the schema at https://your.ip.address.here:8182/~imdatabase/schemaddl.html.

* * * * *

The basis of many of the sample queries is the IMID, or Intermapper ID, a unique ID for a server, map, or device. Thus, it is important to know how to find this, and how to use it in a query, in order to make sure that you are getting information about the right server, map, or device.

To find the IMID for a given device, right-click on the device, choose Info Window, and look for IMID in the list. You should see an entry like device.1.28.217. The parts mean:

You can also get a list of IMIDs associated with a given IP address with the following query:

select d.server_id as "Server ID", d.map_id as "Map ID", d.device_id as "Device ID"
 from device d
 where d.ip = '192.168.1.217'
 order by d.server_id, d.map_id, d.device_id

The combination of server, map, and device IDs indicates the IMID. If, for instance, one of the items in the list gave a server ID of 1, a map ID of 28, and a device ID of 217, the IMID of the device would be 'device.1.28.217'. (Extracting more fields, such as name and probe, from the would help you identify which device it is in the GUI, or you could use the Find Devices… menu item in the GUI to plug in the IMID and find the device.)

So, how would you use this IMID? Let's consider a simple query: listing a complete ("infinite") event log for a single device, the device with the IMID above. For the moment, we won't worry about a time range. It would look like this:

select e.status, e.reason, e.begin_time as order_time, e.end_time
 from event e
 where e.server_id = 1
 and e.map_id = 28
 and e.device_id = 217
order by order_time

And, in fact, that's pretty good as a demonstration of the IMID. As an "infinite" event log, however, it's lacking acknowledgements and notifications. How would we add those? By augmenting our statement to look like this:

select e.status, e.reason, e.begin_time as order_time, e.end_time
 from event e
 where e.server_id = 1
 and e.map_id = 28
 and e.device_id = 217
union
select a.ack_kind, a.ack_message, a.ack_time as order_time, a.ack_time
 from event e, acknowledgment a
 where e.server_id = 1
 and e.map_id = 28
 and e.device_id = 217
 and e.event_id = a.event_id
union
select n.notify_method, n.notify_result, n.notify_time as order_time, n.notify_time
 from event e, notification n
 where e.server_id = 1
 and e.map_id = 28
 and e.device_id = 217
 and e.event_id = n.event_id
order by order_time

That's pretty useful, but one more tweak would be helpful. Let's specify a time range to look at, rather than getting an infinite log:

select e.status, e.reason, e.begin_time as order_time, e.end_time
 from event e
 where e.server_id = 1
 and e.map_id = 28
 and e.device_id = 217
 and e.begin_time BETWEEN '2012-02-24 00:00:00-05' AND '2012-02-25 00:00:00-05'
union
select a.ack_kind, a.ack_message, a.ack_time as order_time, a.ack_time
 from event e, acknowledgment a
 where e.server_id = 1
 and e.map_id = 28
 and e.device_id = 217
 and e.event_id = a.event_id
 and a.ack_time BETWEEN '2012-02-24 00:00:00-05' AND '2012-02-25 00:00:00-05'
union
 select n.notify_method, n.notify_result, n.notify_time as order_time, n.notify_time
 from event e, notification n
 where e.server_id = 1
 and e.map_id = 28
 and e.device_id = 217
 and e.event_id = n.event_id
 and n.notify_time BETWEEN '2012-02-24 00:00:00-05' AND '2012-02-25 00:00:00-05'
order by order_time

Here are a couple more handy event-related queries. You might be interested in calculating and displaying the duration of the items in the events table. Here's a simple version, based on our original query (as durations don't make much sense for acknowledgements and notifications).

 select e.status, e.reason, e.begin_time as order_time, e.end_time, e.end_time - e.begin_time as duration
 from event e
 where e.server_id = 1
 and e.map_id = 28
 and e.device_id = 217
 and e.begin_time BETWEEN '2012-02-24 00:00:00-05' AND '2012-02-25 00:00:00-05'
order by order_time

However, if the last event in the list hasn't ended yet, you'll get an error with that version. To fix it, change it to this:

select e.status, e.reason, e.begin_time as order_time, e.end_time, CASE e.end_time WHEN 'INFINITY' THEN current_timestamp - e.begin_time ELSE e.end_time - e.begin_time END as duration
 from event e
 where e.server_id = 1
 and e.map_id = 28
 and e.device_id = 217
 and e.begin_time BETWEEN '2012-02-24 00:00:00-05' AND '2012-02-25 00:00:00-05'
order by order_time

You might also be interested in a list of devices which have been down for a certain number of hours. The following query gives the server name, map name, device name, and probe type of every device which has been down for at least two days (48 hours), starting with those which have been down the longest:

select s.name as "Server", m.name as "Map Name", d.name as "Device Name", d.probe as "Probe Type", 'now' - e.begin_time as Duration
 from server s, map m, device d, event e
 where e.end_time = 'INFINITY'
 and e.status = 'down'
 and 'now' - e.begin_time >= '48:00:00'
 and e.server_id = d.server_id
 and e.map_id = d.map_id
 and e.device_id = d.device_id
 and e.map_id = m.map_id
 and e.server_id = m.server_id
 and e.server_id = s.server_id
order by Duration desc

We've concentrated so far on events, but of at least as much interest, and perhaps more, are the performance datasets that Intermapper records, either automatically or because a user has started a chart for that variable. You might find it handy to find a list of such datasets for a given IMID:

select d.name as "Dataset", d.label as "Label", d.tags as "Tags", d.alarmpt as "Interface"
 from dataset d
 where d.server_id = 1
 and d.map_id = 28
 and d.device_id = 217 

In the output, "Label" is the display-friendly name of the dataset, while "Tags" is what you'll usually want to key on if you are trying to pick out a particular dataset. For values we automatically save, we've tried to regularize these tags so that it will be easy to find a similar value on different types of devices. "cpupercentavg" is a good example of this. For a complete list of tags automatically available for shipping probes, found in Automatically-Recorded Data Values. The column "Name" in this list corresponds to "Tags" in the database. There is also a set of data available for all probes; this list is available in The <datasets> section.)

We've already mentioned "cpupercentavg", so let's use that in our next example. Here's how you would find historical data for that value for a given IMID, the same one we've been using. In this case, we will return everything since the beginning of 2012:

select data_time as "Time", data_value as "CPU %"
from load_data((
    select dataset_id
    from dataset d
    where d.server_id = 1
      and d.map_id = 28
      and d.device_id = 217
      and d.tags = 'cpupercentavg'
    limit 1
), '2012-01-01 00:00:00-05', 'infinity')
order BY data_time

This same technique will work for most of the values mentioned above, as long as the tag/IMID combination identifies a unique dataset.

Some values, such as input byte rates and output byte rates, are available by interface. To select on a specific interface, we have to specify "@n" in the alarmpt field in the dataset table, where n is the number of the interface:

select data_time as "Time", data_value as "CPU %"
from load_data((
    select dataset_id
    from dataset d
    where d.server_id = 1
      and d.map_id = 28
      and d.device_id = 217
      and d.tags = "BytT"
and d.alarmpt = '@1'
    limit 1
), '2012-01-01 00:00:00-05', 'infinity')
order BY data_time

This gives us all the outgoing byte data in the time range for interface 1.

The preceding two examples work with the raw data values in the datapoint and datastore tables through the load_data stored procedure. This is useful as long as the retention policy for the raw data you are interested in dictates that it will still be stored for the time period in question.

If the data you are interested in is no longer retained in raw form, or if you do not need that level of granularity, you can get the averaged data from the datasample, datasamplehourly, and datasampledaily tables. These tables contain the data averaged every "x" number of minutes as defined by the retention policy, averaged every hour, and averaged every day.

The equivalent to our cpupercentavg query above, rewritten to address the datasample table, is:

select s.sample_time as "Time", s.sample_mean as "CPU % Avg", s.sample_min as "CPU % Min", s.sample_max as "CPU % Max"
    from dataset d, datasample s
    where d.server_id = 1
    and d.map_id = 28
   and d.device_id = 217
    and d.tags = 'cpupercentavg'
    and s.dataset_id = d.dataset_id
    and s.sample_time BETWEEN '2012-01-01 00:00:00-05' AND '2012-02-01 00:00:00-05'
order BY sample_time

The datasamplehourly and datasampledaily tables can be queried in the same way. Note that not only do we retrieve the average for the time in question, but the minimum and maximum values for that time. (Mean, standard deviation, and sample size are all also available.)

Unless you really need the raw data, we encourage you to query the datasample-related tables, as the performance will be much better.

We've dealt with devices only indirectly, as the device table is comparatively straightforward, but it's possible to get quite a bit of information about your devices' properties from the database, as well. We'll close with one common desire: finding the MAC address associated with a given IP address. You can use the following query to do this:

select name, ip, mac
from device
where ip = '192.168.1.217'

Note that you may get multiple results, one for each instance of that IP on your maps. In the current version of Intermapper, only instances of the IP being polled by an SNMP probe will have MAC addresses associated, so don't be surprised if some results are blank.