Script for adding or removing hosts in a hostgroup

[mp4783]

When it's complete, I'll ask if I can share it with you.

So far, the only function that has been a challenge is deleting hosts/services because it has to go through the nagiosql_delete_service.php and/or nagiosql_delete_host.php utilities. This isn't an issue if you have a relatively small number of hosts or services to delete, but for large numbers, as stated previously, it is problematic and does not behave as it should. My near term solution will be to "batch" the deletes, reconfiguring in between each batch.

Given that changes like this are reflected, ultimately, in the MySQL database and the object definition files, very quick, mass changes should be possible, but tracing through all of the dependencies is tedious.

[lmiltchev]

Given that changes like this are reflected, ultimately, in the MySQL database and the object definition files, very quick, mass changes should be possible, but tracing through all of the dependencies is tedious.

I totally agree. Modifying MySQL directly could be quite dangerous, too. Anyway, please share your script with us when complete. Thanks!

[mp4783]

Well, it's dangerous only because the details and relationships are obscured or just plain obscure. I've spent a day trying to trace through a delete operation that was driven through the nagiosql_delete_service.php utility. I captured all of the statements run, but there's no way of knowing if these are all the statements for all circumstances. Unfortunately, nagiosql.inc.php is one of the file where the PHP is obscured.

With the caveat that I don't know the circumstances under which it was created, the MySQL database design seems very "crude". There's no use of foreign key relationships, no triggers, and no stored procedures that I could find. This means that the database can't "take care of itself" nor does it offer easy and optimized procedures for certain tasks. This means we're forced to either endure the very slow performance (on operations with a large number of objects) of the utilities provided or figure it out ourselves and risk fatally corrupting the database.

I know I'm going on and on about this, but in another test today where I attempted to do a mass "deactivation" of a large number of services, the reconfiguration process failed, destroying the contacts.cfg file as it has done to other configuration files in previous tests. I was able to roll everything back as in the past. For those of us with very large installed bases, this is going to be a serious problem down the line.

What I would like to know is what SQL statements need to be run to delete a service, taking into account whatever dependencies it might have? Where's the entity relationship diagram (I created a crude one of my own)? My experience, limited though it admittedly may be, tells me that properly optimized stored procedures would do this operations in a far shorted amount of time, even for large object sets. If I knew more about what exactly has to happen, I'll write the procedure myself (I've already started).

Here are the "modification" SQL statements that I have found in the order executed (data is example only):

INSERT INTO `tbl_logbook` SET `user`='foobar',`time`=NOW(), `ipadress`='199.99.99.12', `domain`='localhost', `entry`='Service file deleted: myhost.mydomain.com.cfg'
DELETE FROM `tbl_lnkServicedependencyToService_DS` WHERE `idSlave`=99
DELETE FROM `tbl_lnkServicedependencyToService_S` WHERE `idSlave`=99
DELETE FROM `tbl_lnkServiceescalationToService` WHERE `idSlave`=99
DELETE FROM `tbl_lnkServicegroupToService` WHERE `idSlaveS`=99
DELETE FROM `tbl_lnkServiceToContact` WHERE `idMaster`=99
DELETE FROM `tbl_lnkServiceToContactgroup` WHERE `idMaster`=99
DELETE FROM `tbl_lnkServiceToHost` WHERE `idMaster`=99
DELETE FROM `tbl_lnkServiceToHostgroup` WHERE `idMaster`=99
DELETE FROM `tbl_lnkServiceToServicegroup` WHERE `idMaster`=99
DELETE FROM `tbl_lnkServiceToServicetemplate` WHERE `idMaster`=99
DELETE FROM tbl_variabledefinition WHERE `id`=207
DELETE FROM `tbl_lnkServiceToVariabledefinition` WHERE `idMaster`=99
DELETE FROM `tbl_serviceextinfo` WHERE `service_description`=99
DELETE FROM `tbl_service` WHERE `id`='99' LIMIT 1
UPDATE `tbl_host` SET `last_modified`=NOW() WHERE `id` = '99'

This statement is executed in the midst of the statements above and I can only assume the results returned are used by the PHP code to perform the DELETE on the tbl_variabledefinition table.

SELECT * FROM `tbl_lnkServiceToVariabledefinition` WHERE `idMaster`=99

Thanks as always for the support and patience.

[tmcdonald]

At this point in the thread I feel I need to point out a few things.

First, while it's always cool seeing people create scripts like this we can't guarantee the functionality of the server when those scripts start playing with the DB. We're well-aware that the DB is not well-documented, and that's for two reasons. One is specifically what I just mentioned, that we can't guarantee modified installs. Even with a fully-documented DB schema, people making changes to the DB directly can wreak havoc on a system if not done correctly. Having the DB documented in detail would encourage modification, which is not something that everyone is qualified to do safely. "A little knowledge is a dangerous thing" sort of situation. The second reason is that the underlying CCM DB is based off of the NagiosQL DB structure and is not something we made from scratch. I'm certain it would have been done differently if it were 100% home-grown. Doing a rewrite would be a monumental task. Not that we don't like the challenge, but it's a lot to consider.

Second, regarding the large installations, strides have been made in improving the efficiency of the DB in recent months. Notably, our own abrist made changes to the CCM so that when changes are applied, only the files that strictly need to be modified will be. The old system essentially dumped out the changes to disk which had a high I/O cost and wait time ( O(1) essentially, based on the total number of config objects), whereas now the changes are more linear ( O(n) where n is the number of changed config objects).

Third, while we are happy to provide knowledge in addition to solutions ("how" and "what" without "why" are rarely sufficient) we do at some point need to draw a line. We offer custom development, training, and consultation for various reasons, and this is starting to stray somewhat into a gray area between all three. Our SLA covers problems in our software, and issues at least in the Customer forum are covered by that SLA, but custom mods and user-supplied scripts aren't something we exactly cover. Again we're happy to help, but from a contract point of view it gets to be a bit much.

That being said, if it's okay with Willem (OP of the thread) we'd like to either close this since I believe the initial question has been answered, or else move it to General so the conversation can be continued outside of the Customer forum. Moving it to General is mostly a clerical decision that makes our day a bit easier, since we need to maintain our SLA in the Customer forum.

[WillemDH]

Hey Trevor, this thread can be closed. Thanks for the input all.

[cmerchant]

We'll go ahead and close the thread. Thanks.