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 Fri Mar 29, 2024 5:29 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: Fri May 28, 2010 3:14 am 
Offline
Moderator

Joined: Fri Jul 20, 2007 8:27 am
Posts: 1259
The new printer stats view from SVN 1237 is very nice but I'm getting incorrect stats. Linked PCs are handled okay but network printers get duplicates if the other_timestamp is the same as the system_timestamp from system. If I have three machines in system with a system_timestamp
of 20100527000001 and one network printer in other with the same timestamp then the statistics report will show the printer three times.

I think it may need two queries (attached and network printers) union-ed together to get proper stats.


Top
 Profile  
Reply with quote  
PostPosted: Fri May 28, 2010 5:36 pm 
Offline
Open-AudIT Fellow

Joined: Thu May 17, 2007 5:47 pm
Posts: 568
Location: Italy
I don't have this bug: if you have time to test a better query, please post it and it will be updated on the SVN.

_________________
Edoardo


Top
 Profile  
Reply with quote  
PostPosted: Sat May 29, 2010 6:21 am 
Offline
Moderator

Joined: Fri Jul 20, 2007 8:27 am
Posts: 1259
I'm not having much luck with the SQL. I'm trying an INNER JOIN on the system table but the results aren't working. My SQL is not good.

But if I might make another suggestion. Populate the other_model field with the printer driver name.

audit.vbs changes:
[code]
Index: audit.vbs
===================================================================
--- audit.vbs (revision 1239)
+++ audit.vbs (working copy)
@@ -1972,7 +1972,8 @@
& printer_shared & "^^^" _
& printer_share_name & "^^^" _
& printer_system_name & "^^^" _
- & printer_location & "^^^"
+ & printer_location & "^^^" _
+ & printer_driver_name & "^^^"
entry form_input,comment,objTextFile,oAdd,oComment
form_input = ""
Next
[/code]

admin_pc_add_2.php changes:
[code]
Index: admin_pc_add_2.php
===================================================================
--- admin_pc_add_2.php (revision 1239)
+++ admin_pc_add_2.php (working copy)
@@ -1404,6 +1404,7 @@
$printer_share_name = trim($extended[5]);
$printer_system_name = strtoupper(str_replace('\\','',trim($extended[6])));
$printer_location = trim($extended[7]);
+ $printer_driver_name = trim($extended[8]);
$printer_name = NULL;
//if (strpos($printer_system_name,'\\\\') !== false ) { $printer_system_name = substr($printer_system_name, 2); }

@@ -1431,7 +1432,7 @@
$sql = "INSERT INTO other (other_ip_address, other_description, other_location, other_type, other_model, ";
$sql .= "other_network_name, other_p_port_name, other_p_shared, other_p_share_name, ";
$sql .= "other_timestamp, other_first_timestamp) VALUES (";
- $sql .= "'" . ip_trans_to($printer_ip) . "', '$printer_caption', '$printer_location', 'printer', '$printer_description', ";
+ $sql .= "'" . ip_trans_to($printer_ip) . "', '$printer_caption', '$printer_location', 'printer', '$printer_driver_name', ";
$sql .= "'$printer_network_name', '$printer_port_name', '$printer_shared', '$printer_share_name', ";
$sql .= "'$timestamp', '$timestamp')";
if ($verbose == "y"){echo $sql . "<br />\n\n";}
@@ -1440,7 +1441,7 @@
// Update
$sql = "UPDATE other SET other_timestamp = '$timestamp', other_p_port_name = '$printer_network_name', ";
$sql .= " other_location = '$printer_location', other_description = '$printer_caption', ";
- $sql .= " other_p_shared = '$printer_shared', other_p_share_name = '$printer_share_name' ";
+ $sql .= " other_p_shared = '$printer_shared', other_p_share_name = '$printer_share_name', other_model = '$printer_driver_name' ";
$sql .= "WHERE other_ip_address = '" . ip_trans_to($printer_ip) . "'";
if ($verbose == "y"){echo $sql . "<br />\n\n";}
$result = mysql_query($sql) or die ('Insert Failed: ' . mysql_error() . '<br />' . $sql);
@@ -1465,12 +1466,12 @@
if ($verbose == "y"){echo "Count: " . $myrow['count'] . "<br />\n\n";}
if ($myrow['count'] == "0"){
// Insert into database
- $sql = "INSERT INTO other (other_linked_pc, other_description, other_type, ";
+ $sql = "INSERT INTO other (other_linked_pc, other_description, other_model, other_type, ";
$sql .= "other_p_port_name, ";
$sql .= "other_p_shared, other_p_share_name, ";
$sql .= "other_network_name, other_location,";
$sql .= "other_timestamp, other_first_timestamp ) VALUES (";
- $sql .= "'$uuid', '$printer_caption', 'printer', ";
+ $sql .= "'$uuid', '$printer_caption', $printer_driver_name, 'printer', ";
$sql .= "'$printer_port_name',";
$sql .= "'$printer_shared', '$printer_share_name', ";
$sql .= "'$printer_system_name', '$printer_location', ";
@@ -1479,7 +1480,7 @@
$result = mysql_query($sql) or die ('Insert Failed: ' . mysql_error() . '<br />' . $sql);
} else {
// Already present in database - update timestamp and dynamic values
- $sql = "UPDATE other SET other_timestamp = '$timestamp', other_location = '$printer_location', ";
+ $sql = "UPDATE other SET other_timestamp = '$timestamp', other_model = '$printer_driver_name', other_location = '$printer_location', ";
$sql .= " other_p_shared = '$printer_shared', other_p_share_name = '$printer_share_name' ";
$sql .= "WHERE other_linked_pc = '$uuid' AND other_description = '$printer_caption' AND other_p_port_name = '$printer_port_name' ";
$sql .= " AND other_timestamp = '$printer_timestamp'";

[/code]


Top
 Profile  
Reply with quote  
PostPosted: Mon May 31, 2010 8:35 pm 
Offline
Open-AudIT Fellow

Joined: Thu May 17, 2007 5:47 pm
Posts: 568
Location: Italy
[quote="jpa"]But if I might make another suggestion. Populate the other_model field with the printer driver name.

I wasn't aware that the other_model field was completely unused.
Added to SVN rev. 1240. Thanks

_________________
Edoardo


Top
 Profile  
Reply with quote  
PostPosted: Wed Jun 02, 2010 9:17 am 
Offline
Moderator

Joined: Fri Jul 20, 2007 8:27 am
Posts: 1259
Updated query which handles network printers better. Let me know if this doesn't work as you'd expect or you need more explanation.
[code]
SELECT DISTINCT other_description,
COUNT( * ) AS count_item,
round( 100 / (SELECT count(other_id)
FROM other LEFT JOIN system ON (other.other_timestamp = system.system_timestamp) AND (other.other_linked_pc = system.system_uuid)
WHERE (other.other_type="printer")
) * COUNT( * ),$round_to_decimal_places) AS percentage
FROM other LEFT JOIN system ON (other.other_timestamp = system.system_timestamp) AND (other.other_linked_pc = system.system_uuid)
WHERE (other.other_type="printer")
GROUP BY other_description
[/code]


Top
 Profile  
Reply with quote  
PostPosted: Thu Jun 03, 2010 5:55 pm 
Offline
Open-AudIT Fellow

Joined: Thu May 17, 2007 5:47 pm
Posts: 568
Location: Italy
It seems to me that, although network printers stats are OK (I had to replace "printer" with 'printer'), your query shows also old locally attached printers (i.e. no more installed on the system). Try this (for me it's working fine)
[code]
"sql"=>"SELECT DISTINCT other_description,
COUNT( * ) AS count_item,
round( 100 / (SELECT count(other_id)
FROM other LEFT JOIN system ON (other.other_timestamp = system.system_timestamp)
AND (other.other_linked_pc = system.system_uuid)
WHERE (other.other_type='printer' AND other.other_timestamp = system.system_timestamp)
OR (other.other_type='printer' AND other.other_linked_pc = '')
) * COUNT( * ),$round_to_decimal_places) AS percentage
FROM other LEFT JOIN system ON (other.other_timestamp = system.system_timestamp)
AND (other.other_linked_pc = system.system_uuid)
WHERE (other.other_type='printer' AND other.other_timestamp = system.system_timestamp)
OR (other.other_type='printer' AND other.other_linked_pc = '')
GROUP BY other_description",
"sort"=>"count_item",

[/code]

_________________
Edoardo


Top
 Profile  
Reply with quote  
PostPosted: Sat Jun 05, 2010 10:39 am 
Offline
Moderator

Joined: Fri Jul 20, 2007 8:27 am
Posts: 1259
[quote="ef"]your query shows also old locally attached printers
Woops. Thank you for taking the time to fix this up for me. This final query does it all I'd say.

Top
 Profile  
Reply with quote  
PostPosted: Mon Jun 07, 2010 8:39 pm 
Offline
Open-AudIT Fellow

Joined: Thu May 17, 2007 5:47 pm
Posts: 568
Location: Italy
Updated at SVN rev. 1241.
Thank you.

_________________
Edoardo


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:  
cron
Powered by phpBB® Forum Software © phpBB Group