Smart Reporting Duplicating Users

Version 2
    Share This:

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    Remedy IT Service Management Suite


    COMPONENT:

    ITSM In-App Reporting


    APPLIES TO:

    BMC Remedy Smart Reporting 9.0.01



    PROBLEM:

    Smart Reporting is duplicating user entries in the IpContact table. Example attached was 6 yesterday and is now 8. We are unable to modify the user's settings in Smart Reporting plus the number of entries for users is increasing daily.


    SOLUTION:

    1. Have ran the following query and confirmed that customer did the On-boarding with same company 7 times

    ----------------------------------------------------------------------------------

    Query to get the details of the deleted Org from Smart reporting Database : select * from Organisation where IpOrg in (Select IpChild from iprltshp where endReasonCode = 'DELETED')

    ----------------------------------------------------------------------------------

    2. We have ran the below quires to clean up the unwanted records from the Smart Reporting Database, which is associated with the off-boarded clients

    ----------------------------------------------------------------------------------
    delete from Person where IpPerson in (select ipid from ipclass where enddate <> '9999-12-31 00:00:00.000')
    delete from iprltshp where ipchild in (select ipid from ipclass where enddate <> '9999-12-31 00:00:00.000')
    delete from staffmemberrole where ipemployee in (select ipid from ipclass where enddate <> '9999-12-31 00:00:00.000')
    delete from ipcontact where ipid in (select ipid from ipclass where enddate <> '9999-12-31 00:00:00.000')
    delete from AccessGroup where IpManager in (select ipid from ipclass where enddate <> '9999-12-31 00:00:00.000')
    delete from ipclass where enddate <> '9999-12-31 00:00:00.000'
    delete from from Organisation where IpOrg in (Select IpChild from iprltshp where endReasonCode = 'DELETED')
    ----------------------------------------------------------------------------------

    3. Have confirmed that unwanted records are deleted from the Database as running the above quires has affected more than 30000 + records in db.

    4. Then again we ran the Query of getting the details of the deleted Org  and found that all 7 entries are gone (0 rows returned ) Also we have again ran the all quires of clean up the unwanted records just to verify that now it not affecting any of the rows as we have already deleted the unwanted records and we got zero rows returned for all queries.

    5. Summary of above 4 points is that we have confirmed that now we don't have any unwanted records in the DB.

     


    Article Number:

    000105292


    Article Type:

    Solutions to a Product Problem



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles