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 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 ...
          • Query to show Asset details with Warranty Expiry Date

            SELECT productType.COMPONENTTYPENAME AS "Product Type", resource.RESOURCENAME AS "Asset Name", product.COMPONENTNAME AS "Product", productType.COMPONENTTYPENAME AS "Product Type", state.DISPLAYSTATE AS "Asset State", rtype.TYPE AS "Asset Type", ...
          • Query to get the asset details , mac address , last scanned, status alone with user details

            Query to get the asset details , mac address , last scanned, status alone with user details SELECT Max(workstation.workstationname) "Asset Name",        Max(workstation.servicetag)      "Service Tag",        Max(resource.assettag)      "Asset Tag",   ...