4 Replies Latest reply on Apr 17, 2017 10:35 AM by Motaz

    Viewing results in a correct order

    Motaz ITSMMVPGroup

      Dear Xtraction people,


      I am playing around with the data model and came across an issue (at least for me as I am not a SQL expert). I have a table where it contains data for LDMS (Custom data) however, all of the data are being filled in one column. I managed to get the results in two different columns in Xtraction but I cannot get the results in one row as shown in the attached screenshot. I would like to have both the results for (Application Catalogue test and Severity) in one row per device. Can you please let me know how can I achieve this?

        • 1. Re: Viewing results in a correct order
          GreggSmith SupportEmployee

          I need to have a better understanding of how the data is stored in the database before I tell you how to map it in the data model.  Can you please give some additional information to the tables/fields involved?

          • 2. Re: Viewing results in a correct order
            Motaz ITSMMVPGroup

            The table that I am trying to pull the data from is called UNMODELEDDATA and the column name is DATASTRING. All of the values are stored in the same column. The same Computer can have multiple values in that column.


            What I need is to have these values next to each other as columns in Xtraction (All values related to one computer to be in one row instead of multiple ones)

            • 3. Re: Viewing results in a correct order
              GreggSmith SupportEmployee

              1. Create a new table, presumably in the Computer view, to return one of the specific values.  In this example, I am assuming a data point called Severity (based on your original post).


              2. For the table name, use a nested query that returns the computer ID and the value for the Severity (wrapping the query in parenthesis)

              Here is that specific query as text that you can copy to the clipboard, in case you want to use it:


              (select umd.computer_idn, ma.ENU attr, umd.datastring value

              from unmodeleddata umd

                   join metaobjattrrelations mar


                   join METAATTRIBUTES ma

                     on mar.METAATTRIBUTES_IDN = ma.METAATTRIBUTES_IDN

              where ma.ENU = 'Severity')


              3. Map the Computer FK field for the join from the Computer table to this Severity table


              4. Map the Severity field that will display the value from the table that indicates the Severity.


              5. Finally, don't forget the join from Computer to Severity


              If you have multiple different fields that are stored in UNMODELEDDATA that you want to each appear as their own "field" in the recordset (which it sounds like you do), then you need to repeat the above steps for each of those name/value pairs stored in that table.


              Also note that you may need to manipulate the data, depending on its use.  The data points in this table are all stored as strings.  So, for example, if the data point represents a date and you want Xtraction to use that value as a date, then your expression for that data field will have to convert the string value to a date value.  Likewise if the data point is supposed to be a numeric value (some sort of count, cost, rating, etc.).


              I hope this helps.



              • 4. Re: Viewing results in a correct order
                Motaz ITSMMVPGroup

                You got it! Thanks a lot Gregg


                One last question, it is not related to the post but I hope you can help. I need to be able to get the installed/not installed patches for each device to be shown in Xtraction. Is there a way to achieve this?