Report on Count of Assets by Site
DB Compatibility : PGSQL & MSSQL
Build Compatibility : Builds above 10000
SELECT aaov.NAME AS "Site", productType.COMPONENTTYPENAME AS "Product Type", count(resource.RESOURCENAME) AS "Asset Count" FROM Resources resource
left JOIN ComponentDefinition product ON resource.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID
left JOIN ResourceLocation resLocation ON resource.RESOURCEID=resLocation.RESOURCEID
left JOIN SiteDefinition siteDef ON resLocation.SITEID=siteDef.SITEID
left JOIN SDOrganization aaov ON siteDef.SITEID=aaov.ORG_ID group by aaov.NAME,productType.COMPONENTTYPENAME
left JOIN ComponentDefinition product ON resource.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID
left JOIN ResourceLocation resLocation ON resource.RESOURCEID=resLocation.RESOURCEID
left JOIN SiteDefinition siteDef ON resLocation.SITEID=siteDef.SITEID
left JOIN SDOrganization aaov ON siteDef.SITEID=aaov.ORG_ID group by aaov.NAME,productType.COMPONENTTYPENAME
New to ADSelfService Plus?
Related Articles
Summary of assets under each Site
DB Compatibility : PGSQL & MSSQL Build Compatibility : Builds above 10000 SELECT regiondefinition.regionname as "Region",aaov.NAME AS "Site", (select count(*) from resources left JOIN ComponentDefinition product ON ...Query report to get the successfully scanned assets (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Query report to get the successfully scanned assets with audit status, state , Serial no and site. SELECT max(resource.resourcename) "Asset Name", max(resource.SERIALNO) AS "Org Serial Number", ...Query to List down Account and corresponding sites and its requesters (MSSQL & PGSQL)
*Tested in builds from PGSQL (14300) or MSSQL (14306) *Applicable for 14500 builds For Accounts and Site: select adef.org_name "Account",sdo.name "Site Name" from accountdefinition adef left join accountsitemapping asm on adef.org_id=asm.accountid ...Best practise to configure Sites to follow same configurations using Site refer sites feature
Let me explain with some examples : In case , if the Accounts / Sites are geographically distributed and you want to configure dedicated technicians for them , we can make use of Site-Refer-Sites feature (similar to default settings but applicable ...Report on count of laptops and desktops in each site
DB Compatibility : PGSQL & MSSQL Build Compatibility : Builds above 10000 SELECT aaov.NAME AS "Site", case when componentdefinitionlaptop.islaptop is null then 'Desktop' else 'laptop' end as "Workstation type", count(resource.RESOURCENAME) AS "Asset ...