Report help requested: to find out which Assets are Used by Coworkers (matching h_cmdb_assets.h_used_by with h_sys_accounts.h_user_id)

[Berto2002]

I have some bad h_used_by data in my h_cmdb_assets table. I am mostly interested in fixing those entries where the h_used_by matches a valid and current coworker (h_sys_accounts.h_account_status = 0).

The trouble is h_cmdb_assets has the h_used_by in the format "urn:sys:0:Post Room:emailhere@domain.gov.uk" but h_sys_accounts has h_user_id as just "emailhere@domain.gov.uk"

My question is what type of join (join, left or right) and what table join options do I need for the tables to match and me to get a report which lists all assets with a certain defect in the h_used_by and where the h_user_id is active. Note that the match I seek is the underlined portion above (without everything before the last colon from the left). Then the report filter I want on that is where the first part of h_used_by is "urn:sys::" (bad data, should have a zero between the "::" or it will find cases of "urn:::" which are doubly messed up)

Thanks in advance.

[Ricky]

Hi Berto,

You can use the REGEXP_REPLACE function to remove the prefix:

REGEXP_REPLACE(h_used_by,'^urn:sys:[0|1]:.*:', '')

The above statement can be used in a JOIN as follows:

 

 

Regards,

Ricky

 

[Berto2002]

@Ricky that's fantastic, thank you. In fact, to get the bad data I altered the join configuration to find the ones with the two colons and then applied the filter in the report to find those for h_account_status = 0:

REGEXP_REPLACE(h_used_by,'^urn:sys::.*:', '')