Registrations to Open-AudIT forums are now closed. To ask any new questions please visit Opmantek Community Questions.

Open-AudIT

What's on your network?
It is currently Thu Mar 28, 2024 9:21 pm

All times are UTC + 10 hours




Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 8 posts ] 
Author Message
PostPosted: Thu May 09, 2013 12:14 am 
Offline
Contributor

Joined: Wed Apr 07, 2010 8:04 am
Posts: 105
Location: Boston, MA
Hi folks,
I'm trying to update a Group, but everytime I make and submit a change, I get this error, and the report ends up breaking (no longer selects records). Any help would be appreciated,
Alberto

[code]A Database Error Occurred

Error Number: 1136

Column count doesn't match value count at row 1

INSERT INTO oa_group_sys (system_id, group_id, group_sys_type) SELECT distinct(system.system_id) FROM system WHERE system.system_id = system.system_id AND manufacturer LIKE '%Dell%' AND system.man_status = 'production' AND system.timestamp = system.timestamp

Filename: C:\wamp\www\code_igniter\system\database\DB_driver.php

Line Number: 330[/code]

edit: Changed subject to [Bug]

_________________
Old OA Setup: 500 Windows 7 workstations & 200 Apple OSX with OA v1.5.2 on Windows Server 2003 and WAMP 2
New OA Setup: 100 Windows servers with OA 2.2 on Windows Server 2016 and WAMP 3


Last edited by el_geto on Thu Jun 27, 2013 3:58 am, edited 2 times in total.

Top
 Profile  
Reply with quote  
PostPosted: Fri May 10, 2013 2:25 pm 
Offline
Site Admin
User avatar

Joined: Mon Jun 07, 2004 11:48 am
Posts: 1964
Location: Brisbane, Australia
[code]INSERT INTO oa_group_sys (system_id, group_id, group_sys_type) SELECT distinct(system.system_id) FROM system WHERE system.system_id = system.system_id AND manufacturer LIKE '%Dell%' AND system.man_status = 'production' AND system.timestamp = system.timestamp[/code]

That SQL is not valid. You try to insert values for system_id, group_id, group_sys_type, bt only actually send it a system_id (the SELECT part of the query).

_________________
Support and Development hours available from [url=https://opmantek.com]Opmantek[/url].
Please consider a purchase to help make Open-AudIT better for everyone.


Top
 Profile  
Reply with quote  
PostPosted: Sat May 11, 2013 1:57 am 
Offline
Moderator

Joined: Fri Jul 20, 2007 8:27 am
Posts: 1259
I think the problem is with 9.2. Unless it's a simple fix you'll probably need to wait for the next release.


Top
 Profile  
Reply with quote  
PostPosted: Sat May 11, 2013 3:29 am 
Offline
Contributor

Joined: Wed Apr 07, 2010 8:04 am
Posts: 105
Location: Boston, MA
I don't modify the SQL statement, if that's where the error is, then its 9.2. I use to modify reports all the time on my previous test server running Beta 7.x but this version I can't do it, I have to recreate them from scratch.

Also, I just noticed my groups are not updating even if new records fall within the Select criteria, and the alerts table is still growing by leaps with software/service installed/removed. This problem ate the hard drive of my previous test server. I'm running the audit v18.vbs at startup via GPO.

_________________
Old OA Setup: 500 Windows 7 workstations & 200 Apple OSX with OA v1.5.2 on Windows Server 2003 and WAMP 2
New OA Setup: 100 Windows servers with OA 2.2 on Windows Server 2016 and WAMP 3


Top
 Profile  
Reply with quote  
PostPosted: Thu May 16, 2013 1:59 am 
Offline
Contributor

Joined: Wed Apr 07, 2010 8:04 am
Posts: 105
Location: Boston, MA
I figured out how to update the SQL statement on the database, essentially (and please correct me if I'm wrong) the group "Select SQL" statement for the Group is located in group_dynamic_select on the oa_group table, this statement will INSERT the selected records plus the group id and group type into the oa_group_sys table, and I found that the UPDATE button in the "List Groups" page will do just that.

What I found in 9.2, is that when editing a particular group, OA will update the "Select SQL" statement on the oa_group table, and then run an INSERT query to update the oa_group_sys table, the problem is that for some reason, the insert query doesn't add the group id and group type, hence giving me the error above.

I've been able to update my groups Select SQL statements directly in the database, but the "Edit Group" page is not working.

I've changed the subject of the thread to "Bug"

_________________
Old OA Setup: 500 Windows 7 workstations & 200 Apple OSX with OA v1.5.2 on Windows Server 2003 and WAMP 2
New OA Setup: 100 Windows servers with OA 2.2 on Windows Server 2016 and WAMP 3


Top
 Profile  
Reply with quote  
PostPosted: Thu May 16, 2013 8:32 am 
Offline
Site Admin
User avatar

Joined: Mon Jun 07, 2004 11:48 am
Posts: 1964
Location: Brisbane, Australia
[quote="el_geto"]and please correct me if I'm wrong

No, you're spot on.

Interesting. The function that does this (m_oa_group->update_specific_group) hasn't changed between 9.2 and my current code. The function is below. I say interesting because your original SQL error as quoted would be produced by the second last SQL creation line - [code]$sql_insert = substr_replace($sql_select, "INSERT INTO oa_group_sys (system_id, group_id, group_sys_type) ", 0, 0);[/code] This should not be executed against the DB as the very next line further alters this SQL.

I cannot replicate the issue. If you export a Group you know is showing this error, post it here with exact step by step instructions on how you can reporduce it, we may be able to get some further info...

I have inserted a couple of echo's into my code and altered a Group. The function works and shows the following for the first and second SQL generations statements.

[code]$sql_insert = substr_replace($sql_select, "INSERT INTO oa_group_sys (system_id, group_id, group_sys_type) ", 0, 0);
produces
INSERT INTO oa_group_sys (system_id, group_id, group_sys_type) SELECT distinct(system.system_id) FROM system WHERE system.man_org_id = '1' AND system.man_status = 'production' [/code]
aThis output would seem to match the SQL you posted, but as stated it should not be run against the DB as the very next statement alters the SQL further, like so -
[code]$sql_insert = str_ireplace("SELECT DISTINCT(system.system_id)", "SELECT DISTINCT(system.system_id), '" . $group_id . "', 'system'", $sql_insert);
produces
INSERT INTO oa_group_sys (system_id, group_id, group_sys_type) SELECT DISTINCT(system.system_id), '33', 'system' FROM system WHERE system.man_org_id = '1' AND system.man_status = 'production' [/code]

[code] function update_specific_group($group_id) {
# get the group select
$sql = "SELECT group_dynamic_select FROM oa_group WHERE group_id = ? LIMIT 1";
$data = array($group_id);
$query = $this->db->query($sql, $data);
foreach ($query->result() as $myrow) {
$sql_select = $myrow->group_dynamic_select;
}
# remove the existing systems in this group
$sql_delete = "DELETE FROM oa_group_sys WHERE group_id = ?";
$data = array($group_id);
$delete = $this->db->query($sql_delete, $data) or die ("Error with delete from oa_group_sys");
# update the group with all systems that match
$sql_insert = substr_replace($sql_select, "INSERT INTO oa_group_sys (system_id, group_id, group_sys_type) ", 0, 0);
$sql_insert = str_ireplace("SELECT DISTINCT(system.system_id)", "SELECT DISTINCT(system.system_id), '" . $group_id . "', 'system'", $sql_insert);
$insert = $this->db->query($sql_insert);
}[/code]

_________________
Support and Development hours available from [url=https://opmantek.com]Opmantek[/url].
Please consider a purchase to help make Open-AudIT better for everyone.


Top
 Profile  
Reply with quote  
PostPosted: Wed Jun 26, 2013 4:41 am 
Offline
Moderator

Joined: Fri Jul 20, 2007 8:27 am
Posts: 1259
I think I found the problem and it's Windows related which is why Mark doesn't see it. I also found a related problem with commas in the sql.

process_edit_group in admin_group.php needs to handle all newlines. And v_edit_group.php should not put tabs and newlines after commas in the select statements.

Attached are updated admin_group.php and v_edit_group.php files.


Attachments:
v_edit_group.txt [4.72 KiB]
Downloaded 389 times
admin_group.txt [9.84 KiB]
Downloaded 367 times
Top
 Profile  
Reply with quote  
PostPosted: Thu Jun 27, 2013 3:57 am 
Offline
Contributor

Joined: Wed Apr 07, 2010 8:04 am
Posts: 105
Location: Boston, MA
YES! That did it!
Finally I can edit groups again without messing with the DB.

One more time, thanks JPA!

_________________
Old OA Setup: 500 Windows 7 workstations & 200 Apple OSX with OA v1.5.2 on Windows Server 2003 and WAMP 2
New OA Setup: 100 Windows servers with OA 2.2 on Windows Server 2016 and WAMP 3


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 8 posts ] 

All times are UTC + 10 hours


Who is online

Users browsing this forum: No registered users and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
Powered by phpBB® Forum Software © phpBB Group