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 resources.COMPONENTID=product.COMPONENTID
left join componentdefinitionlaptop on componentdefinitionlaptop.componentid=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Workstation' and componentdefinitionlaptop.islaptop is not null) as "Workstation-Laptop Count",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left join componentdefinitionlaptop on componentdefinitionlaptop.componentid=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Workstation' and componentdefinitionlaptop.islaptop is null) as "Workstation-Desktop Count",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Server') as "Server",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Access Point') as "Access Point",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Firewall') as "Firewall",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Printer') as "Printer",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Projector') as "Projector",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Rack') as "Rack",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Router') as "Router",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Scanner') as "Scanner",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Storage Device') as "Storage Device",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Switch') as "Switch" from SiteDefinition siteDef
left JOIN SDOrganization aaov ON siteDef.SITEID=aaov.ORG_ID left join regiondefinition on regiondefinition.regionid=siteDef.regionid group by regiondefinition.regionname,aaov.NAME,siteDef.SITEID
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left join componentdefinitionlaptop on componentdefinitionlaptop.componentid=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Workstation' and componentdefinitionlaptop.islaptop is not null) as "Workstation-Laptop Count",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left join componentdefinitionlaptop on componentdefinitionlaptop.componentid=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Workstation' and componentdefinitionlaptop.islaptop is null) as "Workstation-Desktop Count",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Server') as "Server",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Access Point') as "Access Point",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Firewall') as "Firewall",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Printer') as "Printer",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Projector') as "Projector",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Rack') as "Rack",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Router') as "Router",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Scanner') as "Scanner",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Storage Device') as "Storage Device",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Switch') as "Switch" from SiteDefinition siteDef
left JOIN SDOrganization aaov ON siteDef.SITEID=aaov.ORG_ID left join regiondefinition on regiondefinition.regionid=siteDef.regionid group by regiondefinition.regionname,aaov.NAME,siteDef.SITEID
New to ADSelfService Plus?
Related Articles
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 ...Site details
This report is used to get the complete list of Site detail configured in the application. SELECT sd.name"SiteName", sd.Description "Description", regionDef.REGIONNAME "Region", timezonedefinition.displayname "Timezone", apa.DOOR_NO"Door Number", ...Query to show Non IT assets or the assets that does not consume license (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) SELECT MAX(resource.RESOURCENAME) AS "Asset Name", MAX(resource.ASSETTAG) AS "Asset Tag", MAX(product.COMPONENTNAME) AS "Product", MAX(productType.COMPONENTTYPENAME) AS "Product Type", ...When will the Assets get duplicated ?
Please find the possible scenarios when the machines are renamed with _old Scenario 1: When the workstations are swapped in the network. Consider two workstations, MACHINE-A with service tag ST-1 and MACHINE-B with ST-2. Both the workstations are ...How to assign a department to site and requester
1. How to assign department to new sites - > When a new site is created, it would have a 'General' department which would be available by default. In order to add additional departments, go to Admin - > Account details - > Departments - > select the ...