Open-AudIT
https://www.open-audit.org/phpBB3/

[bug] [solved] deleting systems from the database
https://www.open-audit.org/phpBB3/viewtopic.php?f=20&t=6120
Page 1 of 1

Author:  cmcd2820 [ Fri Jun 14, 2013 12:12 am ]
Post subject:  [bug] [solved] deleting systems from the database

Had an old, corrupted database on my old OA server (v10.something) so I converted the server to use the XAMPPlite version of OA v1.0 and decided to not try to import my old data (5000+ computers).

After running a scan of my AD environment, I noticed that there were some old computer accounts that needed to be cleaned out. I deleted them out of AD, but I can't seem to find a way to delete the computers from OA. In the previous version, I would just select the node and then delete it, or run the "Delete all systems not audited in X days" script to clean it out. Am I just not seeing it or has this feature been removed?

Author:  jpa [ Fri Jun 14, 2013 1:30 am ]
Post subject:  Re: v1.0

Feature removed. You now set a systems Status to one of "Production, Retired, Maintenance or Deleted."

Author:  cmcd2820 [ Fri Jun 14, 2013 1:55 am ]
Post subject:  Re: v1.0

[quote="jpa"]Feature removed. You now set a systems Status to one of "Production, Retired, Maintenance or Deleted."

So once a system is in, it's in forever? Machines that get booted off the domain and then have their name changed and rejoined can not be physically removed? Bad adds with inapproprate or incorrect names will always be in there? :shock:

There is no way to prune the database? I used Delete all systems not audited in X days on a regular basis to remove old data to help with system performance. With 5000+ computers on the domain all auditing at login, the server tends to get bogged down with database inserts, and then reports get slowed way down.

Also, the search button is somewhat useless. In the old version when a person called the helpdesk I could quickly type in their username and see what systems they had recently logged into, then use the offer remote assistance link. Now when I type something in the search box and hit search, I can not get it to give me a result. Also, no wildcard search (ie *mcdonald or cmcdon* gives an error.)

Author:  jpa [ Fri Jun 14, 2013 6:30 am ]
Post subject:  Re: v1.0

Yeah, I used to do that as well. You can delete the systems from the System database table if you have access to the DB.

[url=http://www.open-audit.org/phpBB3/viewtopic.php?f=20&t=6105]Search is broken but fixed[/url] in the next version. I don't know when that's coming.

Author:  Mark [ Fri Jun 14, 2013 9:12 am ]
Post subject:  Re: v1.0

There are two functions for this that are not made available in the menus.
SERVER/index.php/admin_db/get_non_production_systems
SERVER/index.php/admin_db/delete_non_production_systems

These will report on and delete from the database systems with a status of "deleted".
NOTE - the "count" column on the report shows the number of systems matching the hostname. It is correct, but maybe not something we are used to seeing. I had several systems that were set to "deleted", but had been put back into service, hence I had multiple systems with the same hostname, but only one with a status of deleted per hostname (if that makes sense).

These functions are not exposed because they are very powerful and there is no warnings or reversing this action. I also had users specifically request that rows NOT be allowed to be deleted because of regulatory requirements. Having the functions but not exposing them in the web interface is my compromise :-) I should probably make it an option to enable them...

I plan on introducing some general database maintenance routines soon (as has been raised here on the forums). I recently upgraded a previous employers version (they were on beta 7 from memory!), and they had 50 million rows in the alerts table. Good times!

Author:  cmcd2820 [ Tue Jun 18, 2013 2:47 am ]
Post subject:  Re: [help] [solved] deleting systems from the database

Awesome. Thanks Mark. That was exactly what I was looking for.

Any suggestions on the search for username in post #3? When we get a helpdesk call it was a tremendous help to search for a username in the search box and see all the systems the user had logged into. I can't seem to make this work even after I patched the m_system.php and main.php. I can search for computers now and it comes back quick, but searching for a username comes back empty every time.

Author:  jpa [ Tue Jun 18, 2013 4:35 am ]
Post subject:  Re: [help] [solved] deleting systems from the database

Make sure your audits are getting this information. Check a system's "Summary->Windows Details->Last Logged on User Field" for the information.

Author:  cmcd2820 [ Tue Jun 18, 2013 6:19 am ]
Post subject:  Re: [help] [solved] deleting systems from the database

Last Logged on User: cmcdonald@risd (ADMIN)

It's there, you just cant run a search for 'cmcdonald' and have it return anything.

Author:  jpa [ Tue Jun 18, 2013 6:28 am ]
Post subject:  Re: [help] [solved] deleting systems from the database

It's working for me but I don't know why yours is not working. A search for my username hits on the Windows User Name attribute in the Windows table.

The username search already has a front and back wildcard applied as well. So "ministra" should get "Administrator".

Author:  back_packn [ Wed Jun 19, 2013 12:36 am ]
Post subject:  Re: [help] [solved] deleting systems from the database

Glad I found this post, as I was hoping to remove some systems from my DB. I marked theses systems as "deleted". I tried to use the index.php/admin_db/delete_non_production_systems function, however I'm receiving an error:

Fatal error: Call to a member function result() on a non-object in /usr/local/open-audit/code_igniter/application/models/m_system.php on line 561

The report function does not give an error, however it returns 0 results.

Any thoughts?

Author:  jpa [ Wed Jun 19, 2013 1:25 am ]
Post subject:  Re: [help] [solved] deleting systems from the database

The systems with a man_status of deleted should be gone but the function is not quite correct and it errors.

Basically it's expecting a result from the Delete query which does not return results. Both the controller and model need to be fixed so until Mark does that you'll need to live with the error.

Edited thread title from [help] to [bug]

Author:  Mark [ Sat Jun 29, 2013 12:36 pm ]
Post subject:  Re: [bug] deleting systems from the database

I cannot replicate the search issue.
It works for me.

I have fixed the error when running the delete_non_production_systems function.

Page 1 of 1 All times are UTC + 10 hours
Powered by phpBB® Forum Software © phpBB Group
https://www.phpbb.com/