Query to show all asset details ( MSSQL )
Tested in build MSSQL (14306)
SELECT MAX("resource"."RESOURCENAME") AS "Asset Name",
MAX("resource"."ASSETTAG") AS "Asset Tag",MAX(resource.SERIALNO) AS "Org Serial Number",
MAX("product"."COMPONENTNAME") AS "Product",
MAX("productType"."COMPONENTTYPENAME") AS "Product Type",
MAX("state"."DISPLAYSTATE") AS "Asset State", MAX("rtype"."TYPE") AS "Asset Type",
MAX("aaaUser"."FIRST_NAME") AS "User",Max(sduser.employeeid) "Employee ID",
MAX("deptDef"."DEPTNAME") AS "Department", MAX("aaov"."NAME") AS "Site",
MAX("resToCost"."TOTALCOST") AS "Total Cost", dateadd(ss,convert(int,left(min(rsh.starttime),10)) ,'1970-01-01') "Asset added date",MAX("ad"."ORG_NAME") AS "Account" FROM "Resources" "resource"
LEFT JOIN "ComponentDefinition" "product" ON "resource"."COMPONENTID"="product"."COMPONENTID"
LEFT JOIN "ComponentType" "productType" ON "product"."COMPONENTTYPEID"="productType"."COMPONENTTYPEID"
LEFT JOIN "ResourceType" "rtype" ON "productType"."RESOURCETYPEID"="rtype"."RESOURCETYPEID"
LEFT JOIN "ResourceState" "state" ON "resource"."RESOURCESTATEID"="state"."RESOURCESTATEID"
LEFT JOIN "ResourceOwner" "rOwner" ON "resource"."RESOURCEID"="rOwner"."RESOURCEID"
LEFT JOIN "ResourceAssociation" "rToAsset" ON "rOwner"."RESOURCEOWNERID"="rToAsset"."RESOURCEOWNERID"
LEFT JOIN "SDUser" "sdUser" ON "rOwner"."USERID"="sdUser"."USERID"
LEFT JOIN "AaaUser" "aaaUser" ON "sdUser"."USERID"="aaaUser"."USER_ID"
LEFT JOIN "DepartmentDefinition" "deptDef" ON "rOwner"."DEPTID"="deptDef"."DEPTID"
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"
LEFT JOIN "ResourceToCost" "resToCost" ON "resource"."RESOURCEID"="resToCost"."RESOURCEID"
INNER JOIN "AccountSiteMapping" "asm" ON "resource"."SITEID"="asm"."SITEID"
INNER JOIN "AccountDefinition" "ad" ON "asm"."ACCOUNTID"="ad"."ORG_ID"
LEFT JOIN resourcestatehistory rsh on rsh.resourceid=resource.resourceid GROUP BY "resource"."RESOURCEID"
With more columns:
SELECT MAX("workstation"."WORKSTATIONNAME") AS "Machine Name",
resource.resourcename "Asset Name", citype.TYPENAME AS "CI Type",
MAX(net.ipaddress) "IP Address",
MAX("workstation"."MANUFACTURER") AS "Manufacturer",
Max(aaauser.first_name) "User",
Longtodate(Max(LASTSUCCESSAUDIT.audittime)) "Last success Scan Date",
Longtodate(Max(audithistory.audittime)) "Last Scan Date", Max(audithistory.auditstatus) "Audit Status",
MAX("workstation"."MODEL") AS "Model",
MAX("workstation"."SERVICETAG") AS "Service Tag",
MAX("workstation"."LOGGEDUSER") AS "Last Logged In User",
MAX("state"."DISPLAYSTATE") AS "Asset State",
MAX("resource"."WARRANTYEXPIRY") AS "Warranty Expiry",
MAX("osInfo"."OSNAME") AS "OS", MAX("aaov"."NAME") AS "Site",
MAX("resLocation"."LOCATION") AS "Location",
MAX("resFields"."UDF_CHAR1") AS "Asset Support",
MAX("ad"."ORG_NAME") AS "Account",
MAX("resFields"."UDF_CHAR2") AS "BU Number & Name",
MAX("deptDef"."DEPTNAME") AS "Department" FROM "SystemInfo" "workstation"
LEFT JOIN "Resource_Fields" "resFields" ON "workstation"."WORKSTATIONID"="resFields"."RESOURCEID"
LEFT JOIN "Resources" "resource" ON "workstation"."WORKSTATIONID"="resource"."RESOURCEID"
LEFT JOIN "ResourceState" "state" ON "resource"."RESOURCESTATEID"="state"."RESOURCESTATEID"
LEFT JOIN "ResourceOwner" "rOwner" ON "resource"."RESOURCEID"="rOwner"."RESOURCEID"
LEFT JOIN lastauditinfo ON resource.resourceid = lastauditinfo.workstationid
LEFT JOIN audithistory LASTSUCCESSAUDIT ON lastauditinfo.last_success_auditid=LASTSUCCESSAUDIT.auditid
LEFT JOIN networkinfo net ON resource.resourceid = net.workstationid
LEFT JOIN audithistory ON lastauditinfo.last_auditid = audithistory.auditid
LEFT JOIN "ResourceAssociation" "rToAsset" ON "rOwner"."RESOURCEOWNERID"="rToAsset"."RESOURCEOWNERID"
LEFT JOIN CI ci on resource.ciid=ci.ciid LEFT JOIN CIType citype ON ci.CITYPEID=citype.TYPEID
LEFT JOIN sduser ON rOwner.userid = sduser.userid LEFT JOIN aaauser aaaUser ON sduser.userid = aaauser.user_id
LEFT JOIN departmentdefinition deptDef ON rOwner.deptid = deptDef.deptid
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"
INNER JOIN "AccountSiteMapping" "asm" ON "resource"."SITEID"="asm"."SITEID"
INNER JOIN "AccountDefinition" "ad" ON "asm"."ACCOUNTID"="ad"."ORG_ID"
LEFT JOIN "OsInfo" "osInfo" ON "workstation"."WORKSTATIONID"="osInfo"."WORKSTATIONID"
GROUP BY "workstation"."WORKSTATIONID", CIType.TYPENAME, resource.resourcename, net.ipaddress ORDER BY 2
New to ADSelfService Plus?
Related Articles
Query to show Asset state history ( MSSQL)
Tested in MSSQL build (14306) SELECT res.resourcename 'Asset Name', res.assettag 'Asset Tag',res.serialno 'Asset Serial No.', rs2.statedesc 'Previous State',rs1.statedesc 'Current State',DATEADD(s,DATEDIFF(s,GETUTCDATE() ,getdate()) + ...Query to show number of requests raised per Asset (MSSQL)
Tested in Build MSSQL (14306) SELECT ci.ciname 'Asset Name', cd.COMPONENTNAME 'Product', cty.COMPONENTTYPENAME 'Product Type',COUNT(wo.ciid) 'Total Requests raised for this asset' FROM workorder wo left join CI ON ci.ciid=wo.ciid LEFT JOIN RESOURCES ...Query to show Contract details with assets associated (PGSQL)
Tested in PGSQL build (14300) SELECT mcdt.customcontractid "Contract ID", mcdt.CONTRACTNAME AS "Contract Name", mcdt.customcontractid "Contract ID", contractcategory.Categoryname "Contract Type", LONGTODATE(mcdt.CREATEDDATE) AS "Created Time", ...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", ...Query to show workstation details.(MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Below query will show Only workstations and its details. SELECT Max(workstation.workstationname) "Workstation", Max(workstation.servicetag) "Service Tag", Max(workstation.model) "Model", ...