Network Switch Report
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
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
Answers (2)
Please log in to answer
Posted by:
craig.thatcher
12 years ago
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.
Posted by:
ms01ak
12 years ago
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
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
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.