Adding a single free variable? In bulk?

[vAJ]

Oh sages of stack traces:

We're working on an integration with ServiceNOW where we need to pass some identifier from a service or host that indicated to ServiceNOW what queue an incident should be created in.

We originally assumed we could do this by passing CONTACTGROUPNAME where we already define who gets email alerts, but that macro is not available to Event Handlers, only notification handlers.

THe best we can do is to create a new free variable and just add the value of the ServiceNOW queue that we want to drop it in. But with 10k services, you can see how this would be cumbersome.

Bulk Mod wizard doesn't offer ability to add a free variable (feature request!!!) but does offer the ability to update host/service templates. Only drawback there is that it overwrites all existing template definitions. No bueno. Would need to keep existing and add an additional template that only sets the free variable I need.

Thoughts?

P.S. I know you're still on a cake/sugar high from Monday. Hopefully I can benefit from that energy still today?

[tmcdonald]

If I trust you with a SQL query do you promise not to use it lightly?

[vAJ]

Pinky swear.

[tmcdonald]

To add a free variable named foo with value bar to the host with ID 2 (www.nagios.com in my case):

Code: Select all

use nagiosql;
insert into tbl_variabledefinition (name,value,last_modified) values ('foo','bar',NOW());
insert into tbl_lnkHostToVariabledefinition (idMaster, idSlave) values (2,6);

Adjust to your needs. The ID of 6 is because that is the sixth free var I have after adding it in the first insert:

Code: Select all

mysql> select * from tbl_variabledefinition;
+----+-----------+---------+---------------------+
| id | name      | value   | last_modified       |
+----+-----------+---------+---------------------+
|  1 | _xiwizard | website | 2017-04-18 15:49:54 |
|  2 | _xiwizard | website | 2017-04-18 15:49:54 |
|  3 | _xiwizard | website | 2017-04-18 15:49:54 |
|  4 | _xiwizard | website | 2017-04-18 15:49:54 |
|  5 | _xiwizard | website | 2017-04-18 15:49:54 |
|  6 | foo       | bar     | 2017-05-10 12:43:47 |
+----+-----------+---------+---------------------+
6 rows in set (0.00 sec)

Code: Select all

mysql> select * from tbl_lnkHostToVariabledefinition;
+----------+---------+
| idMaster | idSlave |
+----------+---------+
|        2 |       1 |
|        2 |       6 |
+----------+---------+

You will want to dig into the following tables to get values:

Code: Select all

tbl_variabledefinition
tbl_lnkHostToVariabledefinition
tbl_lnkServiceToVariabledefinition

remembering that the Master and Slave IDs correspond to the table names, so in this example case:

Code: Select all

mysql> describe tbl_lnkHostToVariabledefinition;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| idMaster | int(11) | NO   | PRI | NULL    |       |
| idSlave  | int(11) | NO   | PRI | NULL    |       |
+----------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

idMaster refers to the Host ID since that appears first in the tbl_lnkHostToVariabledefinition, and idSlave refers to the variable ID since it appears second.

Use with caution, lemme know if you have questions (though strictly speaking direct DB manipulation is not covered).

[tmcdonald]

Also, this is just for a single addition/association. You will wanna script this out in a dev/test environment for changes to be made in bulk. I just didn't extrapolate out that far because I don't know your criteria for what vars to associate with what hosts/services.

[tmcdonald]

Also also, it is generally good practice to append any custom vars with an underscore to avoid collision with existing standard variables:

https://assets.nagios.com/downloads/nag ... tvars.html

[vAJ]

Yep. We're adding "_SNGROUP" which gets passed to the event handler.

I'll test this out in our dev instance and let you know. Greatly appreciated.

-AJ

[dwhitfield]

Let us know how it goes!

[SteveBeauchemin]

I'll be needing to integrate with ServiceNow in the next year. What you are going through sounds kinda painful though.

Couldn't you add the variable to Service Templates and Host Templates to get them in place? I make heavy use of templates. Is your setup not doing that? I have host and service templates for each contact group that get notified. Many host templates. Many service templates.

I'm gonna have to keep an eye on your posts in the future so I can learn from the problems you encounter while getting ServiceNow in place. By the time my turn comes to implement it, I hope you shared all the problems and solutions you found.

Thanks

Steve B

[dwhitfield]

Bulk Mod wizard doesn't offer ability to add a free variable (feature request!!!)

I have submitted this feature request. Perhaps it will be around by the time @SteveBeauchemin needs it.