Create report with attachment type field
Hey, guys!
In the inventory, I have an "attachment" type field of files and I wanted to create the report to pull the assets that don't have that file attached in this field.In the inventory, I have a field of type "attachment" of files and I wanted to create the report to pull the assets that don't have that file attached in this field.
I tried to do it through the wizard by bringing the field as "null" but it doesn't bring up the report as expected.
How do I get the report like this bringing the assets without the file attached in "attachment" field?
- ATTACHMENT IS EMPTY:
- ATTACHMENT IS OK:
Answers (1)
Top Answer
First, create an SQL report. (Not Wizard report) To retrieve the ASSET_DATA_5 .FIELD_????? name using the following SQL
select CONCAT('FIELD_', (select ID from ASSET_FIELD_DEFINITION where FIELD_TYPE = 'file' and ASSET_TYPE_ID = 5))
Now, create a new SQL report using the following SQL after replacing "FIELD_10002" in the three places below with the field name returned from the previous SQL report.
select
MACHINE.NAME,
ASSET.NAME,
ASSET_DATA_5.FIELD_10002
from MACHINE
join ASSET on ASSET.MAPPED_ID = MACHINE.ID
join ASSET_DATA_5 on ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID
where ASSET_DATA_5.FIELD_10002 IS NULL or ASSET_DATA_5.FIELD_10002 = ''
