SQL query to list the top 100 devices with biggest configs

Products

• VMware Smart Assurance

Issue/Introduction

Have found that there are some config files that seem to be quite big and consume lot of database space. When running SQL queries, we are not able to link the files to devices. 

 

What is the correct SQL query to list top 100 devices with biggest configs?

Environment

NCM 10.1.X

Resolution

To get a list of the top 100 devices with biggest configs, please run the follow query:

select dev.device_name, dev.device_idx, file.file_size from cm_device dev join cm_config_unit_revision rev on dev.device_id = rev.device_id join cm_config_file file on file.revision_id = rev.revision_id order by file_size desc limit 100;