Open-AudIT

Hi,

I'm new to the forum, but have been using OAv2 for a while, gradually ramping up from testing the waters to production use.

I have about 2,800 or so items in my "Other" table. Of these, about 500 are identified as "Networked" items. The other 2,300 are things like locally attached printers, software printer drivers.

I really don't care much about the 2,300+ "Non-networked" devices, as much of it is just fake stuff like pdf writers that are identified as locally attached printers, etc. and some might be duplicates or old entries for things that have changed (there is no way we have that many actual non-networked devices; most of them have to be garbage data). However, the fact that there are so many bogus "non-networked" items doesn't bother me because they are easy to just ignore. They don't really get in the way of doing other things in OA.

The items I REALLY care about are the "Networked" items in the "other" table. Of the 500+ I have, a great majority of them came from nmap scans that so far I have only been doing infrequently, and represent network hardware, surveillance cameras, printers, managed UPS's, and sundry networked doo-dads scattered throughout the enterprise.

For some reason, periodically ALL of the "Networked" items in the "other" table suddenly disappear from the database. All of them at once: everything with an IP number just vanishes in a split-second. When this happens, all of the "non-networked" items in that table remain. I now keep a backup of that table that I restore using myPhpAdmin when I discover that this has happened. Its rather annoying and may hinder acceptance of this tool by my staff when I start demonstrating it to them.

I'm still trying to track down a pattern, but it seems to happen when I am doing a "sweep" of the network for auditing (I have a batch file that I run which cycles through every IP number in one of our class-B internal address ranges and runs "audit.vbs" against each address). I don't know yet if it is coincidence that this happens when running this or not.

Are there any known bugs in OA or in audit.vbs that anyone knows of that would cause something like this? Or some kind of automatic purge process? Its almost like there is a malformed SQL query or something.

I read a discussion elsewhere on this forum where something similar was happening, but in that case it was that "system" audits were stepping on items in the "other" table where they had an IP number in common. I don't think that is what is happening to me, because I now use the system's network name for a UUID, and not the IP number.

OAv1 is no longer developed and you'll mostly have to fix stuff like this yourself or with the help of the few of us that use it and are willing to help. OAv2 is in beta, not as feature-full but it's still got some attention from the developer. It might be better to start with OAv2.

That said I'd look at your Other table data that's disappearing. Since you're losing networked items does the data look correct: proper other_network_name, other_ip_address and other_mac_address?

You also don't mention what version of OA you're running. Make sure you're on the latest from SVN. Although even that is not problem free.

Sorry, my bad. It is not OAv2 I am using -- it is OAv1. Sorry for the mistake. It is whatever build of OAv1 is currently in the download section of the web site.

When you say to look for "proper" other_network_name, other_ip_address and other_mac_address, do you mean peruse the contents of the table to see if there is any corrupt information? On a cursory glance, everything looks fine, but I will have to look through all the networked items records to see for sure if anything is amiss. I'll do that as soon as I get a chance.

There are a number of items where I filled in "other_network_name" manually. Some of them have underscores (which isn't technically kosher by DNS specs). Would that cause a problem? Or names that are too long?

I figured out how to enable logging of all SQL queries in MySQL. Hopefully that will give me some insight as to what is going on.

I was just saying if you're just starting to use OpenAudit you'd be better off starting with Version 2 rather than Version 1.

There is an SQL statement in the add_pc code for version 1 which does a DELETE FROM Table other with a filter on MAC address. I could be this statement is somehow getting an empty MAC address which then does a delete of all records with an empty mac address. Not saying this is your problem but you never know. Probably reviewing the SQL statement logs will help track down the problem.

Hm, I don't think its the "DELETE FROM" code you're talking about, because most of the ones that get deleted do not have blank mac address fields (although a few do).

However, I did grep "DELETE FROM" in all the php files and am studying the output to see if something jumps out.

I ran the SQL statement logging for about 24 hours, and it generated about 3 million rows in the log table. That gets unwieldy to work with, but it happened again while I was logging, and I was able to pinpoint about a 10 minute interval in which it happened. I'm studying the sql statements, but so far not finding anything. (I turned the logging off now, so it won't fill up the disk space). I had to export the 10 minute interval of logs to an Excel sheet to work with it.

I used MyPhpAdmin to analyze the "other" table after I had restored it from backup, but it did not find any errors.

I suppose it could be the other way around though: maybe for some reason the "DELETE FROM" code is being triggered and applying to all records with a non-blank value, like if a wildcard character is somehow accidentally getting into the statement.

I would search the log files you have for some unique information in the other table that is getting removed. That way you can narrow it down a bit.

I figured it out!

The offending SQL statement is:



This is coming from delete_systems.php

Since networked "other" items are stand-alone and not linked to a PC, they will all have blank "other_linked_pc" fields. The non-networked "other" items have something in that field, so they are unaffected.

What I believe is happening is this: occasionally a failed attempt at an audit will produce some garbage data. I can only speculate how this happens, maybe audit.vbs is starting to run on a system, but the system being audited shuts down before it finishes. Or something like that. Then I end up occasionally with "system" records that have maybe an IP address and nothing else, and in particular, a blank "system_uuid". When I see these I say "oh, some garbage, lets delete it." So I go to delete_systems.php, select the garbage record and delete. The junk record vanishes and I'm happy (or so I think). Later I notice that all my networked "other" items are gone.

That's because deleting a system with a blank "system_uuid" ends up issuing the query listed above.

I don't think this had anything to do with my running of the script that scans the network. That was probably coincidence (other than the fact that it is when the script runs that once in a while I get the junk entries).

I think I'm going edit delete_systems.php so that the part that does all the delete queries is wrapped in an "if" statement that won't run the queries if "$id" is empty.

If you do please post it here. I'll use it in my own setup and you'll save me the trouble of doing it myself.

Ok, its a pretty simple fix. I just tested it and it seems to work.

Here it goes:

In delete_systems.php, find the two lines that read as follows:


This is the last of a series of SQL queries that delete records from various tables.

Immediately before these two lines, put a line that reads as follows:


After those two lines, put a closing curly brace ("}") to close the if statement.

That's pretty much it.

Similar code also appears in "delete_missed_audits.php" as well. I am not yet aware as to whether this fix is needed there as well. I'm not entirely clear yet where this file gets used.

Thanks. I've updated my copy.

Although the real problem is we've got "blank" systems in the first place.