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 ...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", ...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", ...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 ...Technician notification based on the site
This KB will help you to get the notification about based on the site to a technician. Please follow the below instructions. Please navigate to Admin >> Request timer action >> Add new timer action. Please configure the below in the application. ...