Aggregate ports together on a switch

[evgeny elkin]

Situation: for connections broken diagnostic I want to detect a moment, when many ports on a switch (i.e 5 and more) shanged it's status. How to realize it with zabbix?

p.s. Nelsonlab asked it before, and even solve this by hack, but I can't found solution.

2 nelsonlab: If it's not difficult can you describes a solution? Thanks anyway.

[nelsonab]

I'll try and dig up the solution I had and post it for you.

[nelsonab]

Ok here ya go. :-)

Here is the bash script. Place in a location appropriate. As you can see I have mine in the /etc/zabbix directory. (copy/paste after first line) This must be installed on the server unless you have opened up your mysql database to other hosts.

zabbix@zabbix:/etc/zabbix> cat sql_aggregate 
#!/bin/bash

QUERY="select sum(t1.value) from (select h.itemid,h.clock,h.value from history h where h.itemid in ($1) and h.clock >= (select UNIX_TIMESTAMP(NOW())-(select min(delay) from items where itemid in ($1)))) as t1"

RESULTS=`echo $QUERY|mysql -s -u root zabbix`

echo $RESULTS

In the zabbix_agent.conf file I added the following line:

UserParameter=sql.aggregate[*],/etc/zabbix/sql_aggregate $1

The major caveat to this is that it must be installed on the server. If root requires a password for mysql or you use a different user you will need to modify the script as appropriate. When you add the "Aggregate" item you will need to add it to the host associated with the location of the agent (server). When you look at the values they will be associated with the server host, not the host you are aggregating. This is due to a limitation of Zabbix. When you are adding the item you will need to tell it which items to aggregate. The easiest way to get this is to go to "Latest Data" and pull up a simple graph for the item you want. In the address bar you will see itemid, the number after this is the one you want. Lather rinse repeat as needed to get the other itemids. When you add the key you will need to enclose the items in quotes inside the brackets for example sql.aggregate["20071,20072"]. If you don't include the quotes you will only be aggregating the first item, not all of them. The "store as" selection is based on the type of data returned. Look at the items you are aggregating to determine this value.

Ok so what does the script do? There are three queries all rolled up into one. The inner most query looks up in the items table how often we check the values. It will then query the history table for the items in question, but will only go back as far as we are checking, no further. Thus if an item is checked every 60 seconds and there is no data for 2 minutes we won't see anything. (well that's the theory, I didn't test that) Also if you have two items, A and B, and B is checked every 30 seconds and A is every 60 seconds it will only look back 30 seconds. Thus depending on your update frequency you might "flap" on your aggregation. Hopefully I've managed to explain this.

If you have any other questions just ask!

[evgeny elkin]

This is a very elegance solution! It works fine. Thank you!

I can imagine only one limitation for it - a limitation of mysql data type for field 'key' in 'hosts' table: VARCHAR(255). i.e if itemid value 10100000020103, maximum 16 items is available for aggregate.


p.s please, excuse me for incorect spelling of your name in previous post

[nelsonab]

This is a very elegance solution! It works fine. Thank you!

I can imagine only one limitation for it - a limitation of mysql data type for field 'key' in 'hosts' table: VARCHAR(255). i.e if itemid value 10100000020103, maximum 16 items is available for aggregate.

You're welcome. :-)

I don't think mysql will be the limitation in this case, but rather the length of the request string from the Zabbix agent/server and I don't know what that is, but I wouldn't be too surprised if it is around 14 keys in a DM setup.