Summary of assets under each Site

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

      • 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", ...
      • 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 ...
      • 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 ...