By using this web site, you agree to all terms and conditions for acceptable use.

ServiceLedger KnowledgeBase Knowledgebase Home Page > ServiceLedger | Contact Us

Search the Knowledge Base Browse by Category

ServiceLedger provides this content as-is for self help support purposes. ServiceLedger assumes no liability for any steps you take based on the directions in our knowledge base, and assumes that you have the knowledge to determine whether a given step is appropriate for your situation. Certain solutions can affect third-party software that ServiceLedger relies on or integrates with; it is your responsibility to consult with the documentation or publishers of such third-party software to understand whether any changes suggested here will have negative effects on other applications.


(I1034) How to find out how many attachments are attached to which kinds of record

Would you like to...

Print this page
Email this to a friend

This KB article contains a collection of useful SQL for determining which attachments or kinds of attachments are taking up the most space. The first query will tell you which kinds of records are taking up a lot of space, and the following one(s) can be used to drill down on whatever record type(s) are taking up a lot to see which specific records are responsible.

The following will look for attachments by common record type and tell you how many are associated with each.

select count(*) total_count
, count(company_user.user_id) user_count
, count(crew.crew_id) crew_count
, count(client.client_id) client_count
, count(client_location.location_id) location_count
, count(agreement.agreement_id) agreement_count
, count(equipment.equipment_id) equipment_count
, count(ticket.ticket_id) ticket_count
, count(invoice.invoice_id) invoice_count
, count(vendor.vendor_id) vendor_count
, count(purchase_order.purchase_order_id) purchase_order_count
, count(bill.bill_id) bill_count
, count(replication_delete.delete_guid) deleted_count
, cast(sum(cast(coalesce(company_user.user_id, 0) / coalesce(company_user.user_id, 1) * datalength(attachment.data) as float)) / 1024.0 / 1024.0 as numeric(28, 2)) user_mb
, cast(sum(cast(coalesce(crew.crew_id, 0) / coalesce(crew.crew_id, 1) * datalength(attachment.data) as float)) / 1024.0 / 1024.0 as numeric(28, 2)) crew_mb
, cast(sum(cast(coalesce(client.client_id, 0) / coalesce(client.client_id, 1) * datalength(attachment.data) as float)) / 1024.0 / 1024.0 as numeric(28, 2)) client_mb
, cast(sum(cast(coalesce(client_location.location_id, 0) / coalesce(client_location.location_id, 1) * datalength(attachment.data) as float)) / 1024.0 / 1024.0 as numeric(28, 2)) location_mb
, cast(sum(cast(coalesce(agreement.agreement_id, 0) / coalesce(agreement.agreement_id, 1) * datalength(attachment.data) as float)) / 1024.0 / 1024.0 as numeric(28, 2)) agreement_mb
, cast(sum(cast(coalesce(equipment.equipment_id, 0) / coalesce(equipment.equipment_id, 1) * datalength(attachment.data) as float)) / 1024.0 / 1024.0 as numeric(28, 2)) equipment_mb
, cast(sum(cast(coalesce(ticket.ticket_id, 0) / coalesce(ticket.ticket_id, 1) * datalength(attachment.data) as float)) / 1024.0 / 1024.0 as numeric(28, 2)) ticket_mb
, cast(sum(cast(coalesce(invoice.invoice_id, 0) / coalesce(invoice.invoice_id, 1) * datalength(attachment.data) as float)) / 1024.0 / 1024.0 as numeric(28, 2)) invoice_mb
, cast(sum(cast(coalesce(vendor.vendor_id, 0) / coalesce(vendor.vendor_id, 1) * datalength(attachment.data) as float)) / 1024.0 / 1024.0 as numeric(28, 2)) vendor_mb
, cast(sum(cast(coalesce(purchase_order.purchase_order_id, 0) / coalesce(purchase_order.purchase_order_id, 1) * datalength(attachment.data) as float)) / 1024.0 / 1024.0 as numeric(28, 2)) purchase_order_mb
, cast(sum(cast(coalesce(bill.bill_id, 0) / coalesce(bill.bill_id, 1) * datalength(attachment.data) as float)) / 1024.0 / 1024.0 as numeric(28, 2)) bill_mb
, cast(sum(cast(coalesce(replication_delete.replication_delete_status_id, 0) / coalesce(replication_delete.replication_delete_status_id, 1) * datalength(attachment.data) as float)) / 1024.0 / 1024.0 as numeric(28, 2)) replication_delete_mb
from attachment
left join company_user on attachment.object_guid = company_user.company_user_guid
left join crew on attachment.object_guid = crew.crew_guid
left join client on attachment.object_guid = client.client_guid
left join client_location on attachment.object_guid = client_location.client_location_guid
left join equipment on attachment.object_guid = equipment.equipment_guid
left join agreement on attachment.object_guid = agreement.agreement_guid
left join ticket on attachment.object_guid = ticket.ticket_guid
left join invoice on attachment.object_guid = invoice.invoice_guid
left join vendor on attachment.object_guid = vendor.vendor_guid
left join purchase_order on attachment.object_guid = purchase_order.purchase_order_guid
left join bill on attachment.object_guid = bill.bill_guid
left join replication_delete on attachment.object_guid = replication_delete.delete_guid

The following will break down a given record type's attachments by record, ordered by how much space each is taking up:

select client.client_id, client.name
, count(client.client_id) client_count
, cast(sum(cast(coalesce(client.client_id, 0) / coalesce(client.client_id, 1) * datalength(attachment.data) as float)) / 1024.0 / 1024.0 as numeric(28, 2)) client_mb
from attachment
left join client on attachment.object_guid = client.client_guid
group by client.client_id, client.name
order by client_mb desc

Replace `client` in the text above throughout with whatever record type you're working with. You may also need to replace `name` with `whatever_number` (i.e. `ticket_number` for `ticket`, `agreement_number` for `agreement` and so on) depending on whether the record has a name column or not. As an example, to do it for equipment, the query would be:

select equipment.equipment_id, equipment.equipment_number
, count(equipment.equipment_id) equipment_count
, cast(sum(cast(coalesce(equipment.equipment_id, 0) / coalesce(equipment.equipment_id, 1) * datalength(attachment.data) as float)) / 1024.0 / 1024.0 as numeric(28, 2)) equipment_mb
from attachment
left join equipment on attachment.object_guid = equipment.equipment_guid
group by equipment.equipment_id, equipment.equipment_number
order by equipment_mb desc


Related Questions:

Attachments:

No attachments were found.


ServiceLedger KnowledgeBase Copyright © 2019 support.serviceledger.com
Powered by AcitveKB Knowledgebase Software