Database View Help

[michael_gaston]

Hello,

I have been wrestling with this for about a day and can't seem to figure out what I'm doing wrong. I need to be able to create a report from the Assessment Instance Question Table, that is able to bring in details from the change request table. These details include Risk, Configuration Item, ect. The asmt table contains a field named "Source" that shows what table the assessment is from and the change number associated with it. 

I created a new field in the asmt table that parses the change number from the table prefix, which i thought would allow me to easily create a database view that linked the number from this new field, to the change number on the change request table. I've tried many different variations in the where clause of the database view to try and get this to work. The expected behavior is to be able to link the change number, to the number in the asmt table, and be able to pull in details from the change request table via the link. I either get way too many results or not the correct ones, it looks like it pulls in duplicate change id's, or pulls in records with an empty value for the metric field even if I specify to only pull where metric = "Critical Business Application?". I will attach screenshots of the config and please let me know if you need anything else to assist. 

[Arpan Baishya · Accepted solution]

Hi @michael_gaston,

I might have a solution to your problem. I tried implementing something similar to what you have done and after some trial and error, I reached a point from where I could see the fields I needed in the database view.

Here are the screenshots for your reference.

View Table 1

View Table 2

Final result

I opted to keep the variable prefixes to two characters because I came across this link.

Let me know if this was helpful.

[michael_gaston]

Thank you for your reply Arpan,

Could you share what data the source_id for the AIQ table contains? I do not have a field named source_id in my AIQ table, but i'm wondering if it's just named differently in my instance. 

[michael_gaston]

Disregard, this worked! Thank you.

[Arpan Baishya]

You're welcome. 😃