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", ...Set Task Owner depending on the request site
Requirement: Set Task Owner depending on the request site. Usecase: When a task is triggered from a request, it should be assigned to a technician depending on the request site. Steps to follow: Goto Admin > Task Custom Functions > Global function > ...Update Change Impact based on Selected Assets Business impact value
This requirement includes 3 global functions, 1 change custom action and 1 change custom trigger. Step 1: getConfiguration Global Function 1) Go to Admin -> change custom function -> global function-> Add new --> Mention function name and Return ...How to auto-create change request(s) for unplanned changes on Assets
This post describes the use of a python script to auto-create change requests for unplanned modifications on Assets using Custom Schedules. This script is applicable only for builds prior to 11138. UseCase: Multiple assets are inventoried in ...