/build/static/layout/Breadcrumb_cap_w.png

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

0 Comments   [ + ] Show comments

Answers (2)

Posted by: craig.thatcher 13 years ago
Orange Belt
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.
Posted by: ms01ak 13 years ago
10th Degree Black Belt
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
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Don't be a Stranger!

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

Sign up! or login

View more:

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