Query to show all asset details_ MSSQL

Query to show all asset details_ MSSQL

MSSQL:

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‚Äč
          • Related Articles

          • Query to show Contract details with assets associated

            PGSQL: SELECT mcdt.customcontractid "Contract ID", mcdt.CONTRACTNAME AS "Contract Name", mcdt.customcontractid "Contract ID", contractcategory.Categoryname "Contract Type", LONGTODATE(mcdt.CREATEDDATE) AS "Created Time", ad.ORG_NAME AS "Account",  ...
          • Query to show workstation details. PGSQL & MSSQL

            Below query will show Only workstations and its details.  SELECT Max(workstation.workstationname) "Workstation",         Max(workstation.servicetag)      "Service Tag",        Max(workstation.model)           "Model",         Max(osinfo.osname)      ...
          • Query to show number of requests raised per Asset

            MSSQL: 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 res ON ci.ciid=res.ciid ...
          • Query to show asset monitor details

            SELECT si.workstationname "Asset Name", si.workstationname  "Asset Name", mi.monitortype "Monitor Type", mi.serialnumber "Monitor Serial Number" from systeminfo si LEFT JOIN monitorinfo mi ON si.workstationid=mi.workstationid ORDER BY 1
          • Query to show Asset state history

            MSSQL: 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()) + (rsh.STARTTIME/1000),'1970-01-01 ...