1 2 Previous Next 18 Replies Latest reply on Aug 5, 2020 12:27 PM by Gustavo del Gerbo

    Setting MarkAsDeleted on relationships

    Jeff Sikorski
      Share This:

      Hey all (again I know, sorry!)

       

      Atrium version:  8.1.02

       

      I searched around and found this old post:

       

      Re: Setting MarkAsDelete flag

       

      I am already in the process of doing Carey's suggested method for ComputerSystems.  SQL query -> CSV -> import to dataset with ReconID and MaD = 1 -> Recon to BMC.ASSET.

      What is the preferred method for getting rid of relationships from the BaseRelationship table that I find out I want to delete?  I'm hoping the above method clears out most of the 90,000+ relationships, but I have a feeling I will have some leftover.

       

      I've written this SQL query to identify all the relationships I don't want (Don't ask me how I got here, lesson learned!)

       

       

                                                                                                                                                                                                                                                                                                                                                                                                                                                

      select
                 bes.Name as source_name
                ,bes.ClassId as source_classID
                ,bed.Name as destination_name
                ,bed.ClassId as destination_classID
                ,br.ClassId as Relationship_ClassID
                ,br.Name as Relationship_Name
                  ,dbo.JCP_REMEDY_TO_LOCALTIME(br.CreateDate ,-6,'Y') as   Relationship_Create_Date
                  ,dbo.JCP_REMEDY_TO_LOCALTIME(br.ModifiedDate ,-6,'Y') as   Relationship_Modfied_Date   
                ,br.InstanceId as   Relationship_InstanceID
                ,br.AttributeDataSourceList as   Relationship_DataSourceList
                
               
             from
             BMC_CORE_BMC_BaseRelationship br
             left join BMC_CORE_BMC_BaseElement bes   on br.Source_InstanceId = bes.InstanceId
             left join BMC_CORE_BMC_BaseElement bed   on br.Destination_InstanceId = bed.InstanceId
          
         where br.DatasetId = 'BMC.ASSET'
          and br.AttributeDataSourceList not like   '%ADDM3%'   
         
          and br.Name in
               (
             'FUNCTIONALCOMPONENTINAPPLICATION'
             ,'APPLICATIONSYSTEMHIERARCHY'
            --These 2 had some DALO examples in   them, but expanded the list from 15,000 to 96,500
      --       ,'Dependency'
      --       ,'APPLICATIONSYSTEMCOMPUTER'
            
            )
      select 
                 bes.Name as source_name 
                ,bes.ClassId as source_classID
                ,bed.Name as destination_name 
                ,bed.ClassId as destination_classID
                ,br.ClassId as Relationship_ClassID
                ,br.Name as Relationship_Name
                ,dbo.JCP_REMEDY_TO_LOCALTIME(br.CreateDate ,-6,'Y') as Relationship_Create_Date
                ,dbo.JCP_REMEDY_TO_LOCALTIME(br.ModifiedDate ,-6,'Y') as Relationship_Modfied_Date    
                ,br.InstanceId as Relationship_InstanceID
                ,br.AttributeDataSourceList as Relationship_DataSourceList
                 
                
      
      
      
           from 
             BMC_CORE_BMC_BaseRelationship br
             left join BMC_CORE_BMC_BaseElement bes on br.Source_InstanceId = bes.InstanceId
             left join BMC_CORE_BMC_BaseElement bed on br.Destination_InstanceId = bed.InstanceId
           
      
         where br.DatasetId = 'BMC.ASSET'
          and br.AttributeDataSourceList not like '%ADDM3%'    
          and br.AttributeDataSourceList like '%ADDM%'
          and br.Name in
             (
             'FUNCTIONALCOMPONENTINAPPLICATION'
             ,'APPLICATIONSYSTEMHIERARCHY'
            
      --       ,'Dependency'
      --       ,'APPLICATIONSYSTEMCOMPUTER'
             
          )
      

       

       

      Thanks!
      Jeff

        • 1. Re: Setting MarkAsDeleted on relationships
          Carey Walker

          Jeff

           

          If you delete a CI, relationships it has are also deleted. So, your RE Purge job, run after the MAD flag has been set for the CIs in question, will get rid of the relationships as well.

           

          If you are confident that you will have removed all the CIs you don't want via the method you are following, then your query above will ideally return no rows!

           

          I'm just trying to figure out what you are trying to achieve in checking the AttributeDataSourceList. Well, I can SEE what you are doing (including only ADDM type datasets except ofr any that have ADDM3 in their name), I guess that's part of your challenge here?

          • 2. Re: Setting MarkAsDeleted on relationships
            Jeff Sikorski

            So there have been "bugs" after upgrading ADDM over the years.  The shadow copy has been corrupted and the fix has been to just make a new ADDM dataset.

            So I initially started on BMC.ADDM , then went to BMC.ADDM2 , now I'm on ADDM3.  As you can probably tell from my 1000 other posts, my company is just now starting out and focusing on CMDB, and I stumbled across "stale data" from the old ADDM and ADDM2 datasets that need to go.

             

            I'm operating under the assumption:  If it was created in ADDM and ADDM2, but doesn't have an 'update' from ADDM3, it's old and bad (I dont have time to go through all 100,000+ CIs to check )

             

            I am hoping that if I purge all the stale ComputerSystem CIs, all those stale relationships will go with it.  But I'm also trying to plan for the worst and was wondering what the best practice is for deleting relationships?

             

            Similar situation -- I was playing around in my sandbox a while back .. had some data that was being promoted that I didn't want.  I ended up running a full "Delete" on my sandbox dataset ... but the bad relationship survived the Delete Recon job.

            • 3. Re: Setting MarkAsDeleted on relationships
              Jeff Sikorski

              So I finally found a workaround and purged all the ComputerSystems I didn't want.  However, there's still a boatload of CIs in the BMC_CORE_BMC_BaseRelationship table that I want gone.

               

              2,687 have a MarkAsDeleted = 1 , even after a purge of BMC.ASSET last night

              55,007 have a MarkAsDeleted = 0

               

              So the question is.. how does one delete entries from the BMC_CORE_BMC_BaseRelationship table that seem to be stuck and in limbo, and even running a Purge recon job on BMC.ASSET isn't removing them?

               

              Thanks!
              Jeff

              • 4. Re: Setting MarkAsDeleted on relationships
                Jeff Sikorski

                Well i MIGHT be taking this back ... I just realized the Purge job is still running from last night.  This is the first time BMC.ASSET has ever been "purged" .. so maybe it's just going to take a very long time to get it done.

                 

                I guess I still am pessimistic though , and still expect there to be some stuff in the:

                BMC_CORE_BMC_BaseRelationship


                form that i will want to delete.  Whether it's now or in the future.  So I'm still curious what the proper way to delete from BMC_CORE_BMC_BaseRelationship is!  Or is it the .. go into the form in BMC_CORE_BMC_BaseRelationship and delete ... which I know will make Remedy admins cringe.

                • 5. Re: Setting MarkAsDeleted on relationships
                  Carey Walker

                  I'd be waiting for the whole purge job to finish, make sure it didn't crap out anywhere, and then see what rels. you have left.

                  • 6. Re: Setting MarkAsDeleted on relationships
                    Jeff Sikorski

                    In my entire BMC.ASSET dataset for BMC_CORE_BMC_BASEELEMENT .. there is only 1 more thing that has a MarkAsDeleted = 1 .. it's a BMC_APPLICATION CI record that can be removed by whatever means necessary.

                     

                    But i'm getting this in the arerror.log (and the recon error log) when I try to purge it

                     

                    [2017/03/02 18:26:16.7400] [ ERROR ] [TID: 0000008820]  : ARERR[300] Insufficient server memory for running this process.

                    [2017/03/02 18:26:16.7400] [ ERROR ] [TID: 0000008820]  : ARERR[300] Insufficient server memory for running this process.

                    [2017/03/02 18:26:16.7400] [ ERROR ] [TID: 0000008820]  : Deletion of record failed :

                    [2017/03/02 18:26:16.7400] [ ERROR ] [TID: 0000008820]  : Class: BMC.CORE:BMC_Application

                    [2017/03/02 18:26:16.7400] [ ERROR ] [TID: 0000008820]  : Instance Id: OI-8C04446DD4E245D7A447EFF84AA07A21

                     

                    Thu Mar 02 18:26:16 2017  390698 : Insufficient server memory for running this process. (ARERR 300)

                    Thu Mar 02 18:26:16 2017  390698 : An application command failed. (ARERR 4554)

                    Thu Mar 02 18:26:16 2017     Application-Query-Delete-Entry "CHG:Associations"  '1000000204' = "OI-D9ABDE6D1B0F4B558E0A0648E201EB68"

                    Thu Mar 02 18:26:16 2017  390698 : Insufficient server memory for running this process. (ARERR 300)

                    Thu Mar 02 18:26:16 2017  390698 : An application command failed. (ARERR 4554)

                    Thu Mar 02 18:26:16 2017     Application-Query-Delete-Entry "AST:Attributes" '400129200' = "OI-D9ABDE6D1B0F4B558E0A0648E201EB68"

                     

                     

                    After that .. there are still 7,084 records in my BMC_CORE_BMC_BASERELATIONSHIP table that have MarkAsDeleted = 1 ... but the Purge job keeps failing on the above.  So I can't determine if the Purge job will continue and get rid of the BASERELATIONSHIPs that have a MaD = 1?

                     

                    Any thoughts on how to get rid of the 'corrupt' record?  Can I just manually delete it from the BaseElement table?

                    • 7. Re: Setting MarkAsDeleted on relationships
                      Carey Walker

                      Nasty.

                       

                      So it looks ike that application CI is related to one or more Change records. The form it mentions (CHG:Assocations) holds a link from a change to a CI. You can see if this is the case by looking at the CI in the Asset Mgt view, click on Relationships tab and then change the Show Related drop down to Related Changes.

                       

                      The server memory issue could be caused by many examples of the relationships from CIs to the other ITSM transactions (and when you try and purge the CIs maybe it's trying to clean up these associations) and there's just too many. Could be the total number, OR it could be just one pesky CI that happens to have been related to many many Changes.

                       

                      Can you check that out?

                      • 8. Re: Setting MarkAsDeleted on relationships
                        Jeff Sikorski

                        I think the reason it's breaking and running out of memory is because there IS no related record in the CHG:Associations form.


                        I was just looking at it.  There are only 5 entries in that entire form, and none of them have anything to do with the

                         

                        OI-D9ABDE6D1B0F4B558E0A0648E201EB68


                        So in my mind, it's trying to delete the corresponding record in CHG:Associations ... can't find it... and just breaks down.

                         

                        I'm thinking about tricking it and creating a garbage entry in CHG:Associations ?

                        • 9. Re: Setting MarkAsDeleted on relationships
                          Jeff Sikorski

                          OK i don't know what I was doing, but there were definitely more than 5 records in that form.  I must have swapped the search to "Deleted" before i searched, so I only saw 5.

                           

                          Once I re-gathered myself, I did a search on CHG:Associations for that Request ID = OI-D9ABDE6D1B0F4B558E0A0648E201EB68

                           

                          There were 7 records.  I looked through them .. one of them was in "Proposed" state.  I moved it to "Deleted".  The other 6 were Enabled.

                          I re-ran the purge job, and it's rolling now.  That sole survivor of the purge is now gone.  No more records in BMC_BaseElement with MarkAsDeleted = 1.

                           

                          However, still 7,084 records in my BMC_BaseRelationship table with MarkAsDeleted = 1 ... and the Purge job looks like it's done and not moving on to delete them.  So I'm back to my:
                          How do I get rid of all these relationships from CMDB if the Recon Purge wont do it?

                          • 10. Re: Setting MarkAsDeleted on relationships
                            Carey Walker

                            Possibly, but seems a long shot. I'd be more inclined to just get that one CI out of scope of what you are trying to do, and see if it still fails on the next CI it tries to deal with. That at least eliminates the CI as the cause. Can you set it as not MAD just temporarily and retry the job?

                            • 11. Re: Setting MarkAsDeleted on relationships
                              Jeff Sikorski

                              So it seems like no one has ever determined how to do either of these things:
                                 #1)   Purge relationships that are mark as deleted = 1
                                 #2)   Bulk update relationships that SHOULD be mark as deleted = 1

                               

                               

                              How to Purge Realtionship Records which are MarkedasDelete

                               

                               

                              How to Purge Relationships in Atrium

                               

                               

                              Not able to purge MarkAsDeleted relationships

                               

                              These relationships are most definitely bad / orphaned records from old ADDM datasets that no longer exist.  Right now, since we are just starting out with CMDB, 99% of the relationships are from ADDM.

                               

                              It's pretty weird to me that the Purge job won't remove relationships that are MarkAsDeleted = 1.  If the Purge Recon job won't purge it, then what exactly will ? 

                               

                              The only option I see is to use this CMDBDiag tool?  Which I've never used before .. I will read up on it to see if it really works.

                               

                              Thanks !
                              Jeff

                              1 of 1 people found this helpful
                              • 12. Re: Setting MarkAsDeleted on relationships
                                Carey Walker

                                Jeff

                                 

                                I just set up a quick test in my 9.1 play pen.

                                 

                                I had several Dependency rels. already defined in BMC.ASSET, I set two of them to MAD = Yes, built a simple job with just a purge activity on BMC.ASSET, and ran the job. It physically removed the two MAD Dependency rels. From the log file, you can clearly see it finding the two and removing them (in Dependency class), and you can also see it looking at BaseRelationship as well, although there were none there in my test case.

                                 

                                To be sure, I added a pure BaseRelationship entry, set the MAD and ran the Purge again. Again, it physically removed the relationship record.

                                 

                                So, despite some of the advice in the posts you have seen elsewhere, in 9.1 at least, things seem to work as expected for my test cases. We need to figure out what is different about yours.

                                 

                                First up, you say the rels. are in BaseRelationship - that's always the case, but what are the class IDs of the rels? Apologies if you already know all this, but every rel. has a record in BaseRelationship, but the ClassID attribute for the rel. shows you which specific type of rel. it is. In my example above of the two Dependency rels. I tested with, there were two records in BaseRelationship for these, but the ClassID said BMC_Dependency. If the remaining rels. you can't remove are all weak reference class rels. (example HostedSystemComponents) we may have an explanation.

                                 

                                SQL for an easy count of the rel. classes

                                 

                                select classid, count(*) from bmc_core_bmc_baserelationship where datasetid='BMC.ASSET' and markasdeleted = 1

                                group by classid

                                • 13. Re: Setting MarkAsDeleted on relationships
                                  Jeff Sikorski

                                  Here are the results from your query:

                                   

                                  classid(No column name)
                                  BMC.CORE:BMC_DEPENDENCY6
                                  BMC.CORE:BMC_HOSTEDACCESSPOINT364
                                  BMC.CORE:BMC_HOSTEDSYSTEMCOMPONENTS5845

                                   

                                   

                                  Here are the results from this query:

                                  select classid
                                  , count(*)
                                    from bmc_core_bmc_baserelationship
                                  
                                    where
                                    datasetid='BMC.ASSET'
                                    and markasdeleted = 0
                                      and AttributeDataSourceList not like '%ADDM3%'   
                                      and AttributeDataSourceList like '%ADDM%'
                                  group by classid
                                  

                                   

                                  These are relationships that I don't think should belong in my CMDB.  They were created/last modified by the BMC.ADDM and BMC.ADDM2 datasets, which are empty and no longer being utilized.

                                   

                                   

                                  classid(No column name)
                                  BMC.CORE:BMC_APPLICATIONSYSTEMSERVICES28757
                                  BMC.CORE:BMC_BASERELATIONSHIP24230
                                  BMC.CORE:BMC_COMPONENT44433
                                  BMC.CORE:BMC_DEPENDENCY343999
                                  BMC.CORE:BMC_HOSTEDACCESSPOINT12631
                                  BMC.CORE:BMC_HOSTEDSYSTEMCOMPONENTS42474
                                  BMC.CORE:BMC_INIPSUBNET29350

                                   

                                   

                                  BMC's solution was to use the CMDBDIAG tool.  I played around with it but it didn't have the desired results.

                                   

                                  Perhaps I'm being overly nervous about the 500,000+ relationships I think should be gone.  Perhaps they aren't being updated by the ADDM3 dataset because there is nothing to update?

                                   

                                   

                                  I'm considering using my nuclear bomb option though:
                                  IDentify all ComputerSystem CIs that were created from the ADDM3 dataset

                                  set MarkAsDeleted = 1
                                  Merge with BMC.ASSET
                                  Purge BMC.ASSET

                                  In theory -- this should definitely purge everything (relationships and computersystems) that were created by ADDM, ADDM2, or ADDM3 right?

                                   

                                  My fear is that I will do all this, and still have those 500,000 relationships stuck in limbo.  At that point, I think BMC support is the only group that could help me out.

                                  1 of 1 people found this helpful
                                  • 14. Re: Setting MarkAsDeleted on relationships
                                    Carey Walker

                                    OK, I have probably not been focusing on the actual issue - been more interested in trying to help you get rid of the existing MAD entries first. The query I gave you was just to count the remaining MAD entries, by class, to see if the workflow that stops weak reference relationships from being deleted in a Purge job, is the cause of your MAD issues. Seems that for some of them, it may be the case.

                                     

                                    In YOUR query, just to be 100% complete, be aware that MAD is sometimes set to NULL as well as 0 to indicate NOT soft deleted. So your query may produce slightly more records if you change it to

                                     

                                    .... (markasdeleted = 1 or markasdeleted is NULL) .....

                                     

                                    If it makes no difference then no harm done.

                                     

                                    I would definitely not do any more merging etc. to try and fix this. You have already figured out which rels. in BMC.ASSET need to go. You can run a SQL script that will set the MAD flag for all of these, and then run the Purge. However as I know you have already discovered, not all will be removed. Seems any of the candidate rels. that are defined as weak reference rels. in the model, are ignored in the Purge. So we need to figure out which of your rel. classes from the query result WILL disappear using the standard functionality and which won't. If you are brave enough, we can do a further SQL update to change the relationship class of these as well, to guarantee that the standard Purge job will deal with them. This is a more risky approach, would be unsupported if it breaks somewhere, and may be unacceptable to your organisation.

                                     

                                    If you think this is worthwhile, we can discuss further but you would need somewhere to test this thoroughly first. And you'd need to be able to have a backup and restore option as your final security blanket.

                                     

                                    Just as an aside, why didn't the cmdbdiag do what you needed?? I know it's not a fix-all for everything, but just curious.

                                    1 2 Previous Next