How To: Find Relationships in ISM using SQL Management Studio

Version 1

    Environment:

    This Solution applies to all versions of Ivanti Service Manager (current version, on the date when this article is created is 2018.1)

     

    Problem:

    How to Find Relationships in ISM using SQL Management Studio. It can be helpful if we are not able to find relationship in Admin UI.

     

    Script:

    First we need script which searched for specific value across all tables of a database.

    Source: https://social.msdn.microsoft.com website.

    Link: Search a string accross multiple tables

     

    DECLARE

    @search_string  VARCHAR(100),

    @table_name SYSNAME,

    @table_id INT,

    @column_name SYSNAME,

    @sql_string VARCHAR(2000)

     

    SET @search_string = '6822C3E155CE4BD880BADCA37535530E' -- type here value you are looking for

     

    DECLARE tables_cur CURSOR FOR SELECT ss.name +'.'+ so.name [name], object_id FROM sys.objects so INNER JOIN sys.schemas ss ON so.schema_id = ss.schema_id WHERE  type = 'U'

     

    OPEN tables_cur

     

    FETCH NEXT FROM tables_cur INTO @table_name, @table_id

     

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

        DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id

            AND system_type_id IN (167, 175, 231, 239)

     

        OPEN columns_cur

     

        FETCH NEXT FROM columns_cur INTO @column_name

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

    SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ' WHERE [' + @column_name + ']

                LIKE ''%' + @search_string + '%'') PRINT ''' + @table_name + ', ' + @column_name + ''''

     

    EXECUTE(@sql_string)

     

    FETCH NEXT FROM columns_cur INTO @column_name

    END

     

        CLOSE columns_cur

     

    DEALLOCATE columns_cur

     

    FETCH NEXT FROM tables_cur INTO @table_name, @table_id

    END

     

    CLOSE tables_cur

    DEALLOCATE tables_cur

     

    Example of usage:

    For instance, Incident record has Source column and we would like to add new Source (eg. Pigeon Post), but we do not know where.

     

    In Incident table we see Source ( eg. Phone ) and its RecId in Source_Valid table.

    If we type RecId into our script, it will return all locations having this value. ( It can take some time before it is executed ).

     

    In this example results are:

     

    According to those results we can find that Source comes from IncidentSource table from RecId column.