[Reports] Delete user reports from deleted users

[Aezox]

Hi community !

I'm having a little problem that I cannot find a solution.

I'm receiving mail from my company postmaster telling me that Nagios Report cannot be delivered to some user.
It's normal because these users have left the company.
They have been deleted from my contact table and their access have been deleted but Nagios is still trying to the report to the user.

My question is :
How can I delete reports of these users as I cannot log into their account to delete the report ?
Or how can I delete users email from the report mail list in order the stop the email sending ?

Thank you in advance for your help !
Regards

[mcapra]

Which version of Nagios XI are you using?

How I attempted to replicate this on 5.4.11:

• Created user 'test'
• Logged in as user 'test'
• Scheduled an 'Availability' report for user 'test' (there is a corresponding entry in the xi_usermeta table)
• Deleted user 'test'

As one would expect, the entry in xi_usermeta that corresponds to a user's scheduled reports was obliterated as well.

They have been deleted from my contact table and their access have been deleted but Nagios is still trying to the report to the user.

I would double check to make sure their system user was deleted. You can do this in the GUI via Admin->Manage Users:

2018_03_20_20_54_22_Administration_Nagios_XI.png

Once you've verified that the system user in question was absolutely 100% deleted, can you share the output of the following command executed from the CLI of your Nagios XI machine:

Code: Select all

echo "SELECT u.username, um.user_id, um.keyvalue FROM xi_usermeta um, xi_users u WHERE um.user_id NOT IN (SELECT user_id FROM xi_users) AND keyname='scheduled_reports';" | mysql -uroot -pnagiosxi -Dnagiosxi

That command will help identify orphaned xi_usermeta records in the incredibly unlikely event that some exist.

You may need to alter the mysql command if you've changed the default credentials for security reasons.

[Aezox]

Which version of Nagios XI are you using?

We are using NagiosXI 5.4.9 but we will push to 5.4.13 in few days

I would double check to make sure their system user was deleted. You can do this in the GUI via Admin->Manage Users:

100% sure user is deleted from Nagios

Code: Select all

echo "SELECT u.username, um.user_id, um.keyvalue FROM xi_usermeta um, xi_users u WHERE um.user_id IN (SELECT user_id FROM xi_users) AND keyname='scheduled_reports';" | mysql -uroot -pnagiosxi -Dnagiosxi

I will run this command and give you the output

[kyang]

Thanks for the help @mcapra!

Aezox, Let us know if you have any more questions.

[Aezox]

@mcapra,

Code is working pretty well ! THANKS a lot !

For security reasons I have to hide information :

existinguserid 78 a:5:{s:6:"dfajaa";a:10:{s:4:"name";s:20:"some text";s:4:"type";s:0:"";s:3:"url";s:327:"reports/availability.php?&host=&service=&reportperiod=last24hours&startdate=&enddate=&hostgroup=&servicegroup=SERVICEGROUP+-+REC&reporttimesubmitbutton=Update&advanced=0&assumeinitialstates=yes&assumestateretention=yes&assumestatesduringdowntime=yes&includesoftstates=no&assumedhoststate=3&assumedservicestate=6&timeperiod=&manual_run=1";s:11:"dontdisplay";i:0;s:10:"recipients";s:26:"[email protected]";s:9:"frequency";s:8:"Disabled";s:7:"subject";s:33:"Report text REC";s:4:"body";s:0:"";s:8:"schedule";a:5:{s:4:"hour";s:2:"08";s:6:"minute";s:2:"00";s:4:"ampm";s:2:"AM";s:9:"dayofweek";s:1:"1";s:10:"dayofmonth";s:1:"1";}s:11:"attachments";a:1:{s:3:"pdf";s:2:"on";}}s:6:"aop64o";a:10:{s:4:"name";s:22:"Report text";s:4:"type";s:0:"";s:3:"url";s:329:"reports/availability.php?&host=&service=&reportperiod=last24hours&startdate=&enddate=&hostgroup=&servicegroup=SERVICEGROUPNAME&reporttimesubmitbutton=Update&advanced=0&assumeinitialstates=yes&assumestateretention=yes&assumestatesduringdowntime=yes&includesoftstates=no&assumedhoststate=3&assumedservicestate=6&timeperiod=&manual_run=1";s:11:"dontdisplay";i:0;s:10:"recipients";s:84:"[email protected],[email protected],[email protected] ";s:9:"frequency";s:5:"Daily";s:7:"subject";s:28:"some text report";s:4:"body";s:251:"Some text


existinguserid refers to a user that still has access to NagiosXI and the account is working
[email protected] is the user I'm referencing in the beginning of my post

If I understand well the output of the command it gives me the userid who is sending availability reports to the users mail list, right ?
So if I want to edit the report and delete my deleted user from this report I have to log as existinguserid and remove user3 from the report mail list, right ?

Thank you for your support

[mcapra]

I actually totally gave the wrong SQL. That will pull all existing users when, in reality, we just want potentially orphaned ones.

Give this a shot:

Code: Select all

echo "SELECT u.username, um.user_id, um.keyvalue FROM xi_usermeta um, xi_users u WHERE um.user_id NOT IN (SELECT user_id FROM xi_users) AND keyname='scheduled_reports';" | mysql -uroot -pnagiosxi -Dnagiosxi

With the main change being we are now using NOT IN instead of IN. That command should, in a perfect world, return no entries thus showing us we don't have any orphaned records.

If I understand well the output of the command it gives me the userid who is sending availability reports to the users mail list, right ?

Sort of, except it should show *all scheduled reports* for a user instead of just availability reports.

[Aezox]

@mcapra,

Just tested the second code and in fact no orphaned users left.

So both codes show me that :
- Deleted users haven't left xi_usermeta records
- Existing users are sending reports to deleted users

A big thanks for you help ! I can now move forward to the resolution of this issue

[kyang]

Glad you were able to narrow it down.

Did you have any more questions or are we okay to close this thread?

[Aezox]

@kyang thread can be closed
Thanks

[kyang]

Sounds good!

Thanks for using the Nagios Support Forums!

Feel free to create a new thread if you have any more issues.

Have a great day!