Endpoint Protection

 View Only
Expand all | Collapse all

How to enumerate deleted groups in the SQL database?

Migration User

Migration UserFeb 05, 2010 03:04 PM

  • 1.  How to enumerate deleted groups in the SQL database?

    Posted Feb 02, 2010 10:57 AM
    We're running SEP 11 RU5 with a SQL database. Based on the Database Schema reference PDF, we're creating some custom reports. However we can't figure out how to detect if a group has been deleted. Obviously it's being flagged in some way, because if you delete a group it doesn't show up anymore in the SEPM Console. However we can't figure out what that flag is so we can also detect that a group has been deleted, and thus exclude it from our custom reports.

    Anyone know how deleted groups are flagged in the database?


  • 2.  RE: How to enumerate deleted groups in the SQL database?

    Posted Feb 02, 2010 11:07 AM
    are you able to get report with the group which is deleted?


  • 3.  RE: How to enumerate deleted groups in the SQL database?

    Posted Feb 02, 2010 01:49 PM
    Dear bonner

    Did you install sepm+sql server 2005

    if please how did u manage it, we r struggling here to complete the installation

    please reply

    Thank you


  • 4.  RE: How to enumerate deleted groups in the SQL database?

    Posted Feb 02, 2010 01:50 PM
    I'm not sure I understand what you're trying to ask Rafeeq...


  • 5.  RE: How to enumerate deleted groups in the SQL database?

    Posted Feb 02, 2010 01:51 PM
    Yes. SQL is on a completely separate server. Just followed the installation instructions. Nothing out of the ordinary was required.


  • 6.  RE: How to enumerate deleted groups in the SQL database?

    Posted Feb 02, 2010 02:19 PM

     Sql service account is  domain account ot local service account


  • 7.  RE: How to enumerate deleted groups in the SQL database?

    Posted Feb 02, 2010 02:24 PM

    Please  describe  the sqlserver installation  configuration , i just want  to make sure that i am in right path


    Thank you

     



  • 8.  RE: How to enumerate deleted groups in the SQL database?

    Posted Feb 02, 2010 02:50 PM
     Hi Supportsib,

    Please create a new thread for you issue. I understand that you need to get your issue resolved, but I also need to make sure that Jon is getting the answers he needs without the thread getting to far away from his subject. I hope you understand and thanks.

    Grant-



  • 9.  RE: How to enumerate deleted groups in the SQL database?

    Posted Feb 03, 2010 01:09 PM
    AFAIK, the groups which are deleted doesn't go away from the database... I am not sure if they have made it intentionally.... Anyway.. You can find them in the table IDENTITY_MAP...

    Table name : IDENTITY_MAP

    Field name: SemClientGroup

    A simple select statement would give you the list of groups and you should be able to find the deleted ones too...

    Hope that helps :)


  • 10.  RE: How to enumerate deleted groups in the SQL database?

    Posted Feb 04, 2010 03:57 PM
    Visu310,

    Yes, I know that deleted groups are not deleted from the database.

    Under the IDENTITY_MAP table, it lists all of the groups that ever existed, deleted or not.

    So it comes back to my original question... how to differenciate between active groups and deleted groups?


  • 11.  RE: How to enumerate deleted groups in the SQL database?
    Best Answer

    Posted Feb 04, 2010 06:58 PM
    For small group counts, this should work on MS-SQL 2005
    DECLARE @xmlDoc varchar(max) 
    DECLARE @handle INT; 
    SET @xmlDoc = ( select convert(varchar(max), convert(varbinary(max), content)) from basic_metadata where type='SemClientGroupTree' ); 
    
    EXEC sp_xml_preparedocument @handle OUTPUT, @xmlDoc 
    select ID,Name from identity_map where type='SemClientGroup' and Id not in( 
    SELECT * FROM OPENXML (@handle, '//*', 1) WITH("Id" VARCHAR(64)) where Name != 'NULL' and Id != 'NULL' 
    ) 
    EXEC sp_xml_removedocument @handle;
    


  • 12.  RE: How to enumerate deleted groups in the SQL database?

    Posted Feb 05, 2010 10:33 AM
    Landon,

    What do you mean for small group counts? We do have legitimate folders (not deleted) that do not have clients in them at the moment, but will eventually.

    Thanks!


  • 13.  RE: How to enumerate deleted groups in the SQL database?

    Posted Feb 05, 2010 02:29 PM
    By 'group counts' I really mean the number of folders (groups) you have in your SEPM. By "small", I mean I haven't tested the upper limit (of the number of folders/groups) that this query will work for. I have successfully used it on a SEPM that had 1000 folder/groups in it (query took ~10 s) and a SEPM that had 10,000 folder/groups in it (query took ~2 mins).  

    The number of clients in the folder/group doesn't affect this query.


  • 14.  RE: How to enumerate deleted groups in the SQL database?

    Posted Feb 05, 2010 03:04 PM
    Got it. Makes sense now. Thanks!