Hi Guys,

I'm trying to generate a report that lists server's attached to specific switches. I want to be able to hand to my boss a report saying this switch has these servers (name, serial #,etc) on it.

I've created a switch asset type and linked it to a computer but I can't seem to get the report right:

Here's my sql code:

SELECT MACHINE.NAME AS SYSTEM_NAME, BIOS_MANUFACTURER, BIOS_SERIAL_NUMBER, MID(GROUP_CONCAT(DISTINCT A48.NAME SEPARATOR '\n'), 1, LENGTH(GROUP_CONCAT(DISTINCT A48.NAME SEPARATOR '\n'))) AS FIELD_48 FROM MACHINE LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5 LEFT JOIN ASSET_ASSOCIATION J48 ON J48.ASSOCIATED_ASSET_ID = ASSET.ID AND J48.ASSET_FIELD_ID=48 LEFT JOIN ASSET A48 ON A48.ID = J48.ASSET_ID WHERE (1 in (select 1 from ASSET, ASSET_ASSOCIATION J48, ASSET A48 where ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5 AND J48.ASSOCIATED_ASSET_ID = ASSET.ID AND J48.ASSET_FIELD_ID=48 AND A48.ID = J48.ASSET_ID and A48.NAME LIKE '%server%')) GROUP BY MACHINE.ID ORDER BY MACHINE.NAME asc,BIOS_MANUFACTURER asc,BIOS_SERIAL_NUMBER asc,A48.NAME asc
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
please post a sample of the output. from looking at this initially you dont need all the stuff in the Where clause since you are doing it in the join. changing to inner join will help to limit data being returned. please post a sample.
Answered 07/31/2011 by: craig.thatcher
Orange Belt

Please log in to comment
0
Hi,

I'll use a more basic example. I have a department which has a number of assets. I want to generate a report to show all the assets that belong to that department. I know it can be done because when I click on a department it lists all the assets under there, I just need to know how.

Thanks,

Mike
Answered 08/02/2011 by: ms01ak
Tenth Degree Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity