Database Structure

Started by toad, October 13, 2008, 06:34:20 AM

Previous topic - Next topic

toad

I have Netxms installed using a MS SQL 2005 database. The plan is to have Netxms gather all the relevant WMI and SNMP statistics and server status then have an intranet webpage developed to extract various information from the Netxms database and show the status, ie are certain servers up, SQL and IIS server transaction statistics.

To achieve this a better understanding of the database fields is needed. Is there any documentation available to show the database field structure, in particular the latest values for node data collection.

many thanks

Victor Kirhenshtein

#1
Hello!

Short overview of database structure related to data collection:

All collected data stored in tables idata_xx, where xx is a node object id, so each node has it's own table for data. Structure of this table is quite simple:


FieldTypeDescription
item_idintegerDCI id
idata_timestampintegerTimestamp in UNIX format (seconds since epoch)
idata_valuevarchar(255)Value

You can get the list of nodes from nodes table, where you probably should be interested only in id and may be primary_ip fields.
Some fileds from "nodes" table:

FieldTypeDescription
idintegerNode object id
primary_ipvarchar(15)Node's primary IP address
unamevarchar(255)Node's uname (output of uname -a command)

Node's name, status, and comments can be obtained from "object_properties" table.
Some fileds from "object_properties" table:

FieldTypeDescription
idintegerObject id
namevarchar(63)Object name
statusintegerObject status
commentstextObject comments

Some information on database structure can also be obtained from this topic: https://www.netxms.org/forum/index.php/topic,198.0.html

Hope this helps!

Best regards,
Victor

toad

Many thanks Victor .... all the info I need much appreciated

regards

Geoff

toad

Victor - many thanks for the details on the database structure, however, I am in need to know how to structure an SQL command to fid the latest values. I see where ALL the collected data is but how do you display the latest values with differing timestamps for each item that exists in each nodes data?

Victor Kirhenshtein

Hello!

Server caches last values for each DCI, so when console displays them, data is coming directly from server's memory, not from SQL database.
However, you may use something like this:

If you wish to get last values for all DCIs on given node, you can use the following query:


select A.item_id,A.ts,D.idata_value,I.description from
(
select item_id,max(idata_timestamp) as ts from netxms.idata_14 group by item_id
) A
join netxms.idata_14 AS D on A.item_id=D.item_id and D.idata_timestamp = A.ts
join netxms.items AS I on I.item_id = A.item_id


For node with id 14.

You can also define the stored procedure for retrieving last values for given node:

CREATE PROC LastValues
@Id integer
AS
EXEC ('
select A.item_id,A.ts,D.idata_value,I.description from
(
select item_id,max(idata_timestamp) as ts from netxms.idata_' + @Id + ' group by item_id
) A
join netxms.idata_' + @Id + ' AS D on A.item_id=D.item_id and D.idata_timestamp = A.ts
join netxms.items AS I on I.item_id = A.item_id'
)
GO


I'm not good in SQL, so probably there are better way to do this :)

Best regards,
Victor

toad

Below are working SQL commands to extract the latest value for a DCI. Note - the DCI as named for the node is queried not its ID

The following SQL will list all of the idata_value's for the DCI named 'SQL Cache Memory KB' for node 201
SELECT  TOP 1   items.item_id, items.node_id, idata_201.idata_timestamp, idata_201.idata_value
FROM         items INNER JOIN
                      idata_201 ON items.item_id = idata_201.item_id
WHERE     (items.description = 'SQL Cache Memory KB') AND (items.node_id = 201)
ORDER BY idata_201.idata_value DESC, idata_201.idata_timestamp ASC

The following SQL code will list the latest idata_value for the DCI named 'SQL Cache Memory KB' for node 201
select A.item_id,A.ts,D.idata_value,I.description from
(
      select item_id,max(idata_timestamp) as ts from idata_201 group by item_id
) A
join idata_201 AS D on A.item_id=D.item_id and D.idata_timestamp = A.ts
join items AS I on I.item_id = A.item_id
where description = 'SQL Cache Memory KB'