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 Count" FROM Resources resource
left JOIN ComponentDefinition product ON resource.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 resource.RESOURCEID=resLocation.RESOURCEID
left JOIN SiteDefinition siteDef ON resLocation.SITEID=siteDef.SITEID
left JOIN SDOrganization aaov ON siteDef.SITEID=aaov.ORG_ID where productType.COMPONENTTYPENAME='workstation' group by aaov.NAME,componentdefinitionlaptop.islaptop
left JOIN ComponentDefinition product ON resource.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 resource.RESOURCEID=resLocation.RESOURCEID
left JOIN SiteDefinition siteDef ON resLocation.SITEID=siteDef.SITEID
left JOIN SDOrganization aaov ON siteDef.SITEID=aaov.ORG_ID where productType.COMPONENTTYPENAME='workstation' group by aaov.NAME,componentdefinitionlaptop.islaptop
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 report to extract Active Technician Login and IP Address (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Go to Reports- New Query Report and execute this query. select DISTINCT ac.ACCOUNT_ID as "Login Account ID", au.FIRST_NAME as "Technician Name ( First Name )", count(acs.SESSION_ID) as "Number of ...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", ...Software Compliance Report
Number of products out of compliance select swct.compliancetype "Compliance Type",count(swcd.softwareid) "Number of Products" from SWComplianceDetails swcd left join swcompliancetype swct on swcd.swcompliancetypeid=swct.swcompliancetypeid where ...