/build/static/layout/Breadcrumb_cap_w.png

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:

AX+4NCdaeippAAAAAElFTkSuQmCC

  • ATTACHMENT IS OK:

HyeSrnHIwEqPAAAAAElFTkSuQmCC


0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: KevinG 3 weeks ago
Red Belt
0

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 = ''

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ