Database Structure

[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]

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:

Field	Type	Description
item_id	integer	DCI id
idata_timestamp	integer	Timestamp in UNIX format (seconds since epoch)
idata_value	varchar(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:

Field	Type	Description
id	integer	Node object id
primary_ip	varchar(15)	Node's primary IP address
uname	varchar(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:

Field	Type	Description
id	integer	Object id
name	varchar(63)	Object name
status	integer	Object status
comments	text	Object 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]

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'