Add a report filter condition that compares a string field to another string field in the same record?

[melosborne]

Background information: I have created a view using a couple of tables (cmdb_ci_computer and sys_user). I did this in order to generate a discrepancy report on the following:

1. CMDB_CI_COMPUTER.ASSIGNED_TO does not equal to CMDB_CI_COMPUTER.LAST_LOGGED_IN_USER (custom string field)
   • I had to link cmdb_ci_computer to the sys_user table twice in order to compare these fields (this portion works fine)
2. CMDB_CI_COMPUTER.ASSET_TAG is empty
   • Also very easy to configure
3. CMDB_CI_COMPUTER.NAME does not contain the SYS_USER.LAST_NAME of the user in the CMDB_CI_COMPUTER.LAST_LOGGED_IN_USER
   • To get the correct last name, last_logged_in_user was compared to the user_name field in the sys_user table and then the last_name field was pulled for that record

The issue that I am running into is trying to compare the last_name of the last_logged_in_user to the computer name of the same record in my view table.

I have tried to use a before query business rule and calling script include through the report filter condition.

My question is, is there a way for me to configure a report filter condition that would compare the cmdb_ci_computer.name string field to the sys_user.last_name string field?

Something like this:

Any help or nudge in the right direction would be GREATLY appreciated.

Thanks,

Mel

[Brad Tilton · Accepted solution]

Hi Melinda,

Unfortunately when comparing two fields through the filter you're limited to equals or doesn't equal. Typically if you need to report on something like this that isn't available through the filter, you would add a true/false field, and then populate it with a before update business rule that does the comparison for you. Then you could run your report off of that field instead. In order to get all of the data current you'd have to run a background or fix script against all the other records to populate your new field.

[melosborne]

Hmmmm, thanks Brad. I can see this actually working out pretty well. Luckily, we are doing a complete wipe of the cmdb_ci_computer data in preparation for a modified integration... soooooo, adding this field and making the business rule could work out pretty well.

Thanks for the idea!

- Mel