Audit SERIOUS PROBLEM

[spaww]

Hi all,

I have problems with zabbix in my enviroment.

I use ZABBIX to save all information about my servers (hostname, hardware description, serial number, tag, etc).

BUT the audit NOT RECORD CHANGES in PROFILE / PROFILE EXT.

What is the problem ? If user A add information, and user B AFTER edit this record the changes will not be recorded and I NEVER will know if USER A add this information or not...

Log of example.
I Change HOSTNAME AND profile information and zabbix AUDIT AND debug information on SCREEN only show the changes on hosts, not show the changes on profile.

==========
******************* Estatísticas de script *************************
Tempo total: 0.273027
Limite de memória : 128M
Uso de memória : 1.25M - 11.75M (10.5M)
Memory peak : 11.75M
Contador de seleções SQL: 38
Contador de execuções SQL: 13
Contador de requisições SQL: 51
Data:7.2E-5 SQL: SET NAMES utf8
Data:3.5E-5 SQL: SET CHARACTER SET utf8
Data:7.0E-5 SQL: SELECT * FROM nodes WHERE nodetype=1 ORDER BY nodeid
Data:0.000539 SQL: SELECT u.*,s.* FROM sessions s,users u WHERE s.sessionid='2ac967a048b58d1ca68793243602f6d9' AND s.status=0 AND s.userid=u.userid AND ((s.lastaccess+u.autologout>1312902627) OR (u.autologout=0)) AND ((u.userid BETWEEN 000000000000000 AND 099999999999999))
Data:4.0E-5 SQL: SELECT MAX(g.gui_access) as gui_access FROM usrgrp g, users_groups ug WHERE ug.userid=3 AND g.usrgrpid=ug.usrgrpid
Data:4.1E-5 SQL: SELECT g.usrgrpid FROM usrgrp g, users_groups ug WHERE ug.userid = 3 AND g.usrgrpid = ug.usrgrpid AND g.users_status = 1 LIMIT 1 OFFSET 0
Data:0.003498 SQL: UPDATE sessions SET lastaccess=1312902627 WHERE sessionid='2ac967a048b58d1ca68793243602f6d9'
Data:4.5E-5 SQL: SELECT g.usrgrpid FROM usrgrp g, users_groups ug WHERE ug.userid = 3 AND g.usrgrpid = ug.usrgrpid AND g.debug_mode = 1 LIMIT 1 OFFSET 0
Data:0.004343 SQL: SELECT * FROM profiles WHERE userid=3 AND ((profileid BETWEEN 000000000000000 AND 099999999999999)) ORDER BY userid ASC, profileid ASC
Data:5.8E-5 SQL: SELECT * FROM config WHERE ((configid BETWEEN 000000000000000 AND 099999999999999))
Data:0.000284 SQL: SELECT title1, url1, title2, url2, title3, url3, title4, url4, title5, url5 FROM user_history WHERE userid=3
Data:4.3E-5 SQL: SELECT g.groupid FROM groups g WHERE ((g.groupid BETWEEN 000000000000000 AND 099999999999999)) AND (g.groupid IN (8))
Data:0.000153 SQL: SELECT h.hostid FROM hosts h WHERE (h.hostid IN (326)) AND ((h.hostid BETWEEN 000000000000000 AND 099999999999999)) AND h.status IN (0,1,3)
Data:3.8E-5 SQL: SELECT nodeid FROM nodes
Data:3.2E-5 SQL: begin
Data:0.000173 SQL: SELECT h.* FROM hosts h WHERE (h.hostid IN (326)) AND ((h.hostid BETWEEN 000000000000000 AND 099999999999999)) AND h.status IN (0,1)
Data:0.00011 SQL: SELECT h.hostid FROM hosts h WHERE (h.hostid IN (326)) AND ((h.hostid BETWEEN 000000000000000 AND 099999999999999)) AND h.status IN (0,1)
Data:6.3E-5 SQL: SELECT h.* FROM hosts h WHERE (h.hostid IN (326)) AND ((h.hostid BETWEEN 000000000000000 AND 099999999999999)) AND h.status IN (0,1)
Data:0.000247 SQL: SELECT h.hostid FROM hosts h WHERE ((h.hostid BETWEEN 000000000000000 AND 099999999999999)) AND h.status IN (0,1) AND ( (h.host IN ('1.5 - TEMPORARIO MIGRACAO PENTAHO - e do adai2')) )
Data:0.00018 SQL: SELECT h.hostid FROM hosts h WHERE ((h.hostid BETWEEN 000000000000000 AND 099999999999999)) AND h.status=3 AND ((h.hostid BETWEEN 000000000000000 AND 099999999999999)) AND ( (h.host IN ('1.5 - TEMPORARIO MIGRACAO PENTAHO - e do adai2')) ) LIMIT 1 OFFSET 0
Data:0.000281 SQL: UPDATE hosts SET proxy_hostid=0, host='1.5 - TEMPORARIO MIGRACAO PENTAHO - e do adai2', port=10000, status=0, useip=1, dns='', ip='10.12.1.5', useipmi=0, ipmi_port=623, ipmi_authtype=-1, ipmi_privilege=2, ipmi_username='', ipmi_password='', ipmi_ip='' WHERE (hostid IN (326))
Data:0.000153 SQL: SELECT * FROM hosts WHERE (hostid IN (326)) AND status IN (0,1)
Data:4.3E-5 SQL: SELECT DISTINCT g.groupid,hg.hostid FROM groups g,hosts_groups hg WHERE ((g.groupid BETWEEN 000000000000000 AND 099999999999999)) AND (hg.hostid IN (326)) AND hg.groupid=g.groupid
Data:0.000295 SQL: SELECT DISTINCT h.hostid FROM hosts h,hosts_templates ht WHERE ((h.hostid BETWEEN 000000000000000 AND 099999999999999)) AND h.status=3 AND (ht.hostid IN (326)) AND h.hostid=ht.templateid AND ((ht.hostid BETWEEN 000000000000000 AND 099999999999999))
Data:9.9E-5 SQL: SELECT h.hostid FROM hosts h WHERE (h.hostid IN (326)) AND ((h.hostid BETWEEN 000000000000000 AND 099999999999999)) AND h.status IN (0,1)
Data:4.5E-5 SQL: SELECT hm.* FROM hostmacro hm WHERE ((hm.hostmacroid BETWEEN 000000000000000 AND 099999999999999)) AND (hm.hostid IN (326))
Data:0.000324 SQL: DELETE FROM hosts_profiles_ext WHERE (hostid IN (326))
Data:0.000157 SQL: SELECT h.* FROM hosts h WHERE (h.hostid IN (326)) AND ((h.hostid BETWEEN 000000000000000 AND 099999999999999)) AND h.status IN (0,1)
Data:0.00026 SQL: SELECT nextid FROM ids WHERE nodeid=0 AND table_name='auditlog' AND field_name='auditid'
Data:0.000108 SQL: UPDATE ids SET nextid=nextid+1 WHERE nodeid=0 AND table_name='auditlog' AND field_name='auditid'
Data:9.2E-5 SQL: SELECT nextid FROM ids WHERE nodeid=0 AND table_name='auditlog' AND field_name='auditid'
Data:0.000758 SQL: INSERT INTO auditlog (auditid,userid,clock,ip,action,resourcetype,resou rceid,resourcename) values (46481,3,1312902627,'10.12.120.57',1,4,326,'1.5 - TEMPORARIO MIGRACAO PENTAHO - e do adai2')
Data:0.000117 SQL: SELECT nextid FROM ids WHERE nodeid=0 AND table_name='auditlog_details' AND field_name='auditdetailid'
Data:9.6E-5 SQL: UPDATE ids SET nextid=nextid+1 WHERE nodeid=0 AND table_name='auditlog_details' AND field_name='auditdetailid'
Data:9.3E-5 SQL: SELECT nextid FROM ids WHERE nodeid=0 AND table_name='auditlog_details' AND field_name='auditdetailid'
Data:0.000636 SQL: insert into auditlog_details (auditdetailid,auditid,table_name,field_name,oldva lue,newvalue) values (11167,46481,'hosts','host','1.5 - TEMPORARIO MIGRACAO PENTAHO - e do adai','1.5 - TEMPORARIO MIGRACAO PENTAHO - e do adai2')
Data:0.000235 SQL: DELETE FROM hosts_profiles WHERE (hostid IN (326))
Data:9.2E-5 SQL: SELECT * FROM hosts_profiles WHERE hostid=326
Data:9.9E-5 SQL: INSERT INTO hosts_profiles (hostid,devicetype,name,os,serialno,tag,macaddress ,hardware,software,contact,location,notes) VALUES (326,'','aqui7','','','','','','','','','')
Data:0.00018 SQL: commit
Data:0.003497 SQL: SELECT DISTINCT g.* FROM groups g,hosts_groups hg,hosts h WHERE ((g.groupid BETWEEN 000000000000000 AND 099999999999999)) AND hg.groupid=g.groupid AND h.hostid=hg.hostid AND h.status IN(0,1)
Data:0.000499 SQL: SELECT DISTINCT h.hostid,hg.groupid,h.host FROM hosts h,hosts_groups hg WHERE (hg.groupid IN (8)) AND hg.hostid=h.hostid AND ((hg.groupid BETWEEN 000000000000000 AND 099999999999999)) AND h.status IN (0,1) ORDER BY h.host ASC LIMIT 1001 OFFSET 0
Data:0.000759 SQL: SELECT h.* FROM hosts h WHERE (h.hostid IN (326,152,69,70,71,72,78,85,86,87,88,89,103,90,91,8 4,93,94,182,190,191,294,296,337,297,338,339,127,12 8,129,130,131,132,133,134,158)) AND ((h.hostid BETWEEN 000000000000000 AND 099999999999999)) AND h.status IN (0,1)
Data:0.000941 SQL: SELECT DISTINCT h.hostid, h.hostid,h.host,ht.hostid as linked_hostid FROM hosts h,hosts_templates ht WHERE ((h.hostid BETWEEN 000000000000000 AND 099999999999999)) AND h.status=3 AND (ht.hostid IN (69,70,71,72,78,84,85,86,87,88,89,90,91,93,94,103, 127,128,129,130,131,132,133,134,152,158,182,190,19 1,294,296,297,326,337,338,339)) AND h.hostid=ht.templateid AND ((ht.hostid BETWEEN 000000000000000 AND 099999999999999))
Data:0.019715 LONG SQL: SELECT count(DISTINCT i.itemid) as rowscount,i.hostid FROM items i WHERE ((i.itemid BETWEEN 000000000000000 AND 099999999999999)) AND i.type<>9 AND (i.hostid IN (69,70,71,72,78,84,85,86,87,88,89,90,91,93,94,103, 127,128,129,130,131,132,133,134,152,158,182,190,19 1,294,296,297,326,337,338,339)) GROUP BY i.hostid
Data:0.007435 SQL: SELECT DISTINCT COUNT(DISTINCT t.triggerid) as rowscount,i.hostid FROM triggers t,functions f,items i WHERE ((t.triggerid BETWEEN 000000000000000 AND 099999999999999)) AND (i.hostid IN (69,70,71,72,78,84,85,86,87,88,89,90,91,93,94,103, 127,128,129,130,131,132,133,134,152,158,182,190,19 1,294,296,297,326,337,338,339)) AND f.triggerid=t.triggerid AND f.itemid=i.itemid GROUP BY i.hostid
Data:0.00421 SQL: SELECT DISTINCT count(DISTINCT g.graphid) as rowscount,i.hostid FROM graphs g,graphs_items gi,items i WHERE ((g.graphid BETWEEN 000000000000000 AND 099999999999999)) AND (i.hostid IN (69,70,71,72,78,84,85,86,87,88,89,90,91,93,94,103, 127,128,129,130,131,132,133,134,152,158,182,190,19 1,294,296,297,326,337,338,339)) AND gi.graphid=g.graphid AND i.itemid=gi.itemid GROUP BY i.hostid
Data:5.2E-5 SQL: SELECT count(a.applicationid) as rowscount,a.hostid FROM applications a WHERE ((a.applicationid BETWEEN 000000000000000 AND 099999999999999)) AND (a.hostid IN (69,70,71,72,78,84,85,86,87,88,89,90,91,93,94,103, 127,128,129,130,131,132,133,134,152,158,182,190,19 1,294,296,297,326,337,338,339)) GROUP BY a.hostid
Data:0.000228 SQL: SELECT h.hostid FROM hosts h WHERE ((h.hostid BETWEEN 000000000000000 AND 099999999999999)) AND h.status=3 AND (h.hostid IN (5,6,8,7,16,370,15,3)) AND ((h.hostid BETWEEN 000000000000000 AND 099999999999999))
Data:0.000339 SQL: SELECT DISTINCT h.hostid, h.hostid,h.host,ht.hostid as linked_hostid FROM hosts h,hosts_templates ht WHERE ((h.hostid BETWEEN 000000000000000 AND 099999999999999)) AND h.status=3 AND (ht.hostid IN (3,5,6,7,8,15,16,370)) AND h.hostid=ht.templateid AND ((ht.hostid BETWEEN 000000000000000 AND 099999999999999))
Data:0.000128 SQL: SELECT title5, url5 FROM user_history WHERE userid=3
Tempo total gasto na SQL: 0.05203
******************** Fim de script ***************************
==========

[richlv]

unfortunately, there are a bit too many problems with audit :

https://support.zabbix.com/browse/ZBX-2815

https://support.zabbix.com/browse/ZBX-1204

https://support.zabbix.com/browse/ZBX-1277

https://support.zabbix.com/browse/ZBX-2212

and the one you are facing right now :

https://support.zabbix.com/browse/ZBX-1484

[spaww]

unfortunately, there are a bit too many problems with audit :

https://support.zabbix.com/browse/ZBX-2815

https://support.zabbix.com/browse/ZBX-1204

https://support.zabbix.com/browse/ZBX-1277

https://support.zabbix.com/browse/ZBX-2212

and the one you are facing right now :

https://support.zabbix.com/browse/ZBX-1484

I dont know WY this BUGs is not solved, but, I make a FIX for hosts edit (today... 6 hours of work (great part to understand zabbix update works).....).

Is not a very clean or beatiful code... but works for me (in initial tests):

I change on hosts.php:

on line 557 i add:

				if(get_request('useprofile', 'no') == 'yes'){
					$host_profile_fields = array('devicetype', 'name', 'os', 'serialno', 'tag','macaddress', 'hardware', 'software', 'contact', 'location', 'notes');
					$select_fields 	= "";
					$field_desc 	= array("");
					for ($i = 0; $i < count($host_profile_fields); $i++) {
						$select_fields .= ($i == 0 ? "" : ", ") . $host_profile_fields[$i];
						$field_desc[$i] = $host_profile_fields[$i];
					}
					//var_dump($host_old);
					$sqlProfile = 'SELECT ' . $select_fields . $where_profile;
					$res2 = DBselect($sqlProfile);
					$profile_old = array(); $profile_old["in.use"] = "true";
					$profile_new = array(); $profile_new["in.use"] = "true";
					// recupera o perfil atual
					while($row = DBfetch($res2)){
						for ($i = 0; $i < count($host_profile_fields); $i++) {
							$profile_old[$field_desc[$i]] = $row[$host_profile_fields[$i]];
						}						
					}
					// recupera o novo perfil
					for ($i = 0; $i < count($host_profile_fields); $i++) {
						$profile_new[$field_desc[$i]] = get_request($host_profile_fields[$i],"");
						if ($usa_perfil == false) { $profile_old[$field_desc[$i]] = ""; $profile_old["in.use"] = "false"; }
					}						
					add_audit_ext(AUDIT_ACTION_UPDATE, AUDIT_RESOURCE_HOST,
					$host['hostid'],
					$host['host'],
					'hosts.profile',
					$profile_old,
					$profile_new); 
				} else { // registra que o host não esta configurado para utilizar perfil
					$profile_old = array(); $profile_old["in.use"] = ($usa_perfil == true ? "true" : "false");
					$profile_new = array(); $profile_new["in.use"] = "false";
					var_dump($usa_perfil);
					var_dump($profile_old);
					var_dump($profile_new);

					add_audit_ext(AUDIT_ACTION_UPDATE, AUDIT_RESOURCE_HOST,
					$host['hostid'],
					$host['host'],
					'hosts.profile',
					$profile_old,
					$profile_new); 
				}

on line 546 i add:

				$where_profile = ' from hosts_profiles hp where hp.hostid = '.get_request('hostid',0);
				$res = DBselect('SELECT count(hp.hostid) as id '.$where_profile);
				while($row = DBfetch($res)) {
					$usa_perfil = $row['id'] > 0;
				}

[nelsonab]

I would suggest you make a patch file for the above fixes and attach them to this forum thread and the appropriate ticket. When creating your patch file be sure it shows which version of Zabbix the patch is for. This will make it easier for the appropriate developer to make use of your hard work should it be appropriate for the fix they had in mind.

[spaww]

I would suggest you make a patch file for the above fixes and attach them to this forum thread and the appropriate ticket. When creating your patch file be sure it shows which version of Zabbix the patch is for. This will make it easier for the appropriate developer to make use of your hard work should it be appropriate for the fix they had in mind.

Hi Nelson,

How to create a "patch" ?
Send the changed file only ? Or Have a specific way ?

PS. I found a currently limitation (i will solve sun...) is not working for mass update (aditional code is required in another area of this file).

Best Regards,
Adail

[nelsonab]

Here's one result from Google:

http://jungels.net/articles/diff-patch-ten-minutes.html

The basic gist is to have a directory with the original Zabbix source, I'll call it ~/zabbix.1.8.5 (Assume this directory is a tar -zxvf of the original Zabbix tarball) and your directory with the changes (I'm going to assume it's in your web directory...

then:

diff -Naur ~/zabbix.1.8.5/frontends/php/ /var/www/html/zabbix/ > ~/mypatch.patch

You should then have a file which describes your changes which you can then upload for everyone else to use.

[richlv]

the most important thing is to create "unified" diffs (-u option for diff) so that context information is preserved. that's also the default for "svn diff"

[spaww]

Here's one result from Google:

http://jungels.net/articles/diff-patch-ten-minutes.html

The basic gist is to have a directory with the original Zabbix source, I'll call it ~/zabbix.1.8.5 (Assume this directory is a tar -zxvf of the original Zabbix tarball) and your directory with the changes (I'm going to assume it's in your web directory...

then:

diff -Naur ~/zabbix.1.8.5/frontends/php/ /var/www/html/zabbix/ > ~/mypatch.patch

You should then have a file which describes your changes which you can then upload for everyone else to use.

Thanks for code.
I will try to solve the massupdate issue and will send to this topic the patch !

[richlv]

note that a proper fix would be to implement audit recording capabilities in the api layer, other than that it's just a quick bandaid that most likely would not be merged upstream.

[spaww]

note that a proper fix would be to implement audit recording capabilities in the api layer, other than that it's just a quick bandaid that most likely would not be merged upstream.

Well..

API Layer = api directory ?

If yes, all "original audit records" is created on hosts.php (base frontend folder).

So, i extend the original situations of records adding more data recorded.

I will continue changing hosts.php because i think is correctly place to make this change, but, if someone recomends another file to change I can check without problems.

[richlv]

"original" entries are not correct. audit must be recorded at the api level (otherwise somebody could modify things over api and completely escape any auditing)

[spaww]

"original" entries are not correct. audit must be recorded at the api level (otherwise somebody could modify things over api and completely escape any auditing)

My God

Ok... I will analise the custs to implement on API level and return to forum.