2 Replies Latest reply on Sep 8, 2016 9:29 AM by XPA

    Where are the custom data stored after archieved a device ?

    XPA Rookie

      Hi All,

       

      I've create a custom table in Landesk database to store informations.

       

      I use these informations with an excel report acceding database via ODBC.

       

      Once the devices have been archived, i can see the information in the inventory from Asset control, but i can't find in wich table they're stored.

       

      Can someone help ?

       

      Regards

        • 1. Re: Where are the custom data stored after archieved a device ?
          phoffmann SupportEmployee

          So - an "archived" device is (for all intents and purposes" deleted from the "regular" LANDesk tables.

           

          It instead has been copied/moved into a bunch of MP_xxx tables (which is why it doesn't show in the "regular" list of devices anymore). So that's one key thing to be aware of!

           

          First of all, your computer entries are in the following two tables (rather than the "regular" COMPUTER table):

          • MPACComputer
          • MP_AC_Devices

           

          ... as for your custom data - rather than being in the "UNMODELLEDDATA" table (unless you've built a specific table to hold data for you?), that's now held based on the name of your data.

          So for instance, I've created some custom data under:

          ""

          COMPUTER - CUSTOM DATA - MY DATA - VERSION =1.2.3.4.5

          ""

           

          The act of Archiving, takes the "My Data" (the data object) and turns that into a table - MPACMYDATA (notice the lack of spaces). It sort of HAS to do that, since it can't piggy-back off the METAOBJECTS / METAATTRIBUTES logical links in the "regular" database. So the answer is "it depends on where you placed your custom data".

           

          As a tip / to help you along - if you need to "go locate stuff", I'd recommend having a (small) test server set up (with 1-2 devices to control the amount of data you're looking at) and making use of this wonderful SQL script here to "SearchAllTables" ...

          CREATE PROC SearchAllTables
          (
          @SearchStr nvarchar(100)
          )
          AS
          BEGIN
          
          -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
          -- Purpose: To search all columns of all tables for a given search string
          -- Written by: Narayana Vyas Kondreddi
          -- Site: http://vyaskn.tripod.com
          -- Tested on: SQL Server 7.0 and SQL Server 2000
          -- Date modified: 28th July 2002 22:50 GMT
          
          
          CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
          
          SET NOCOUNT ON
          
          DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
          SET @TableName = ''
          SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
          
          WHILE @TableName IS NOT NULL
          BEGIN
          SET @ColumnName = ''
          SET @TableName =
          (
          SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
          FROM INFORMATION_SCHEMA.TABLES
          WHERE TABLE_TYPE = 'BASE TABLE'
          AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
          AND OBJECTPROPERTY(
          OBJECT_ID(
          QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
          ), 'IsMSShipped'
          ) = 0
          )
          
          WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
          BEGIN
          SET @ColumnName =
          (
          SELECT MIN(QUOTENAME(COLUMN_NAME))
          FROM INFORMATION_SCHEMA.COLUMNS
          WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
          AND TABLE_NAME = PARSENAME(@TableName, 1)
          AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
          AND QUOTENAME(COLUMN_NAME) > @ColumnName
          )
          
          IF @ColumnName IS NOT NULL
          BEGIN
          INSERT INTO #Results
          EXEC
          (
          'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
          FROM ' + @TableName + ' (NOLOCK) ' +
          ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
          )
          END
          END
          END
          
          SELECT ColumnName, ColumnValue FROM #Results
          END
          
          -- How to run the stored procedure.
          -- Execute the following in SQL Query Analyzer:
          -- EXEC SearchAllTables 'String'
          
          

           

          That can help you chase down all sorts of stuff if needed.

           

          HOPE THIS HELPS .

          • 2. Re: Where are the custom data stored after archieved a device ?
            XPA Rookie

            Many thanks.

             

            You're quicker than the support team !!!!

             

            I finally find my tables with the script.

             

            The table name is MPACMPACxxxxxxx

             

            I've had a look on all tables, but never find these

             

            Regards