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?
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)
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
on umd.METAOBJATTRRELATIONS_IDN = mar.METAOBJATTRRELATIONS_IDN
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.
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?