SQL Query

[b_pc80]

Hello,

we have an syslog server with mysql backend. Every router in our company send his log to syslog server.

Also every router is monitoring via snmp with zabbix. Now i like to create a template for sql query. The problem is, how i can get the snmp ipadress of the router dynamicly to the template.

I need something like this for the sql query template (Database Monitor)

select Message from SystemEvents where Message like '%Login%' and ReceivedAt > timestampadd(HOUR,-1,current_timestamp()) and FromHost = '{[COLOR="red"]SNMPIPADDRESS[/COLOR]}' order by ReceivedAt desc Limit 1

Is there any variable i can use for {SNMPIPADDRESS}?

I don´t want create 300 items manually

[ingus.vilnis]

Hi,

This is not a clean solution but you can try to use a sub-query to get the default SNMP interface of the router specified in Zabbix.

 select Message from SystemEvents 
	where Message like '%Login%' 
	and ReceivedAt > timestampadd(HOUR,-1,current_timestamp()) 
	and FromHost = 
		(select i.ip from interface i, hosts h 
			where i.hostid = h.hostid 
			and h.host='{HOST.HOST}') 
	order by ReceivedAt desc Limit 1

Hope this helps!

Best Regards,
Ingus

[b_pc80]

Hi,

your solution don´t work, because our syslog server and the zabbix server are different machine with different mysql databases. Your subquery have to ask the zabbix database, the rest of the statement have to connect to the syslog database.

[ingus.vilnis]

Right, got it....

Well maybe you can figure out a query which combines them both.

Sorry that my answer was not that helpful!

Best Regards,
Ingus

[b_pc80]

your answer was helpfull :-)

our trainee replace now every hostname to hostname,IPADDRESS

my query looks now like this and works fine

 select Message from SystemEvents 
	where Message like '%Login%' 
	and ReceivedAt > timestampadd(HOUR,-1,current_timestamp()) 
	and FromHost = SUBSTRING_INDEX('{HOST.HOST}',',',-1); 
	order by ReceivedAt desc Limit 1