Query to show asset hardware details with Bios date (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306)
PGSQL:
SELECT MAX(aaov.NAME) AS "Site",
MAX(workstation.WORKSTATIONNAME) AS "Machine Name",
MAX(workstation.MANUFACTURER) AS "Manufacturer",
MAX(workstation.MODEL) AS "Model",
MAX(memInfo.TOTALMEMORY) AS "Total Memory",
MAX(osInfo.OSNAME) AS "OS",
MAX(osInfo.SYSTEMTYPE) AS "System Type",
MAX(workstation.LOGICALCPUCOUNT) AS "Logical Processors",
MAX(procInfo.SPEED) AS "Processor Speed (in MHz)",
MAX(workstation.LOGGEDUSER) AS "Last Logged In User",
sum(driveinfo.CAPACITY)/(1024*1024*1024) TotalCapacity,
sum(driveinfo.FREESPACE)/(1024*1024*1024) Freespace,(substring((workstation.biosdate),7,2) || '-' || substring((workstation.biosdate),5,2) || '-'|| substring((workstation.biosdate),1,4)) "Bios Date" FROM SystemInfo workstation
LEFT JOIN Resources resource ON workstation.WORKSTATIONID=resource.RESOURCEID
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 driveinfo on workstation.WORKSTATIONID=driveinfo.WORKSTATIONID
LEFT JOIN MemoryInfo memInfo ON workstation.WORKSTATIONID=memInfo.WORKSTATIONID
LEFT JOIN OsInfo osInfo ON workstation.WORKSTATIONID=osInfo.WORKSTATIONID
LEFT JOIN ProcessorInfo procInfo ON workstation.WORKSTATIONID=procInfo.WORKSTATIONID
GROUP BY workstation.WORKSTATIONID ORDER BY 1 NULLS FIRST , 2 NULLS FIRST
Without bios date:
SELECT Max(resource.resourcename) AS "Asset Name",
Max(ninfo.ipaddress) AS "IP Address",
MAX(citype.TYPENAME) AS "CI Type",
Max(sysinfo.model) AS "Model",
Max(osinfo.osname) AS "OS",
Max(sysinfo.servicetag) AS "Service Tag",
Max(aaov.NAME) AS "Site", MAX(ad.ORG_NAME) AS "Account" FROM resources resource
LEFT JOIN componentdefinition product ON resource.componentid = product.componentid
LEFT JOIN systeminfo sysinfo ON sysinfo.workstationid = resource.resourceid
LEFT JOIN memoryinfo memInfo ON sysinfo.workstationid = memInfo.workstationid
LEFT JOIN memorymoduleinfo memminfo ON sysinfo.workstationid = memminfo.workstationid
LEFT JOIN monitorinfo monInfo ON sysinfo.workstationid = monInfo.workstationid
LEFT JOIN harddiskinfo hdinfo ON hdinfo.workstationid = sysinfo.workstationid
LEFT JOIN componenttype productType ON product.componenttypeid = productType.componenttypeid
LEFT JOIN vendordefinition resourceVendor ON resource.vendorid = resourceVendor.vendorid
LEFT JOIN sdorganization aao ON resourceVendor.vendorid = aao.org_id
LEFT JOIN resourcestate state ON resource.resourcestateid = state.resourcestateid
LEFT JOIN barcodes barcodes ON resource.barcodeid = barcodes.barcodeid
LEFT JOIN networkinfo ninfo ON resource.resourceid = ninfo.workstationid
LEFT JOIN staticgroup sgroup ON sgroup.resourceid = resource.resourceid
LEFT JOIN resourcegroup rgroup ON sgroup.groupid = rgroup.groupid
LEFT JOIN osinfo ON osinfo.workstationid = sysinfo.workstationid
LEFT JOIN processorinfo procInfo ON sysinfo.workstationid = procInfo.workstationid
LEFT JOIN lastauditinfo LastAuditInfo ON resource.resourceid = lastauditinfo.workstationid
LEFT JOIN audithistory LASTSUCCESSAUDIT ON lastauditinfo.last_success_auditid = LASTSUCCESSAUDIT.auditid
LEFT JOIN audithistory AuditHistory ON lastauditinfo.last_auditid = audithistory.auditid
LEFT JOIN discoverytypeconstants SDCON ON SDCON.typeid = LASTSUCCESSAUDIT.scantype
LEFT JOIN discoverytypeconstants DCON ON DCON.typeid = audithistory.scantype
LEFT JOIN resourceowner resOwn ON resource.resourceid = resOwn.resourceid
LEFT JOIN departmentdefinition resDept ON resOwn.deptid = resDept.deptid
LEFT JOIN aaauser resUser ON resOwn.userid = resUser.user_id
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 CI ci on resource.ciid = ci.ciid LEFT JOIN CIType citype ON ci.CITYPEID = citype.TYPEID
INNER JOIN AccountSiteMapping asm ON resource.SITEID = asm.SITEID
INNER JOIN AccountDefinition ad ON asm.ACCOUNTID = ad.ORG_ID
GROUP BY sysinfo.workstationid
MSSQL:
Shows assets only with success scan status
SELECT Max(resource.resourcename) AS "Asset Name", MAX(citype.TYPENAME) AS "CI Type", Max(sysinfo.model) AS "Model", Max(osinfo.osname) AS "OS", Max(sysinfo.servicetag) AS "Service Tag", Max(aaov.NAME) AS "Site", MAX("ad"."ORG_NAME") AS "Account", Max(ninfo.ipaddress) AS "IP Address", Max(sysinfo.loggeduser) AS "Last Logged In User", CASE WHEN Len(sysinfo.biosdate) <= 1 THEN '-' WHEN Len(sysinfo.biosdate) > 10 THEN substring(sysinfo.biosdate, 7, 2) +'-' +substring(sysinfo.biosdate, 5, 2)+'-'+substring(sysinfo.biosdate, 1, 4) ELSE Replace((sysinfo.biosdate), '/', '-') END AS "Bios Date", (dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (((Max(audithistory.audittime)))/1000),'1970-01-01 00:00:00')) "Last Scan Date" FROM resources resource LEFT JOIN componentdefinition product ON resource.componentid = product.componentid LEFT JOIN systeminfo sysinfo ON sysinfo.workstationid = resource.resourceid LEFT JOIN memoryinfo memInfo ON sysinfo.workstationid = memInfo.workstationid LEFT JOIN memorymoduleinfo memminfo ON sysinfo.workstationid = memminfo.workstationid LEFT JOIN monitorinfo monInfo ON sysinfo.workstationid = monInfo.workstationid LEFT JOIN harddiskinfo hdinfo ON hdinfo.workstationid = sysinfo.workstationid LEFT JOIN componenttype productType ON product.componenttypeid = productType.componenttypeid LEFT JOIN vendordefinition resourceVendor ON resource.vendorid = resourceVendor.vendorid LEFT JOIN sdorganization aao ON resourceVendor.vendorid = aao.org_id LEFT JOIN resourcestate state ON resource.resourcestateid = state.resourcestateid LEFT JOIN barcodes barcodes ON resource.barcodeid = barcodes.barcodeid LEFT JOIN networkinfo ninfo ON resource.resourceid = ninfo.workstationid LEFT JOIN staticgroup sgroup ON sgroup.resourceid = resource.resourceid LEFT JOIN resourcegroup rgroup ON sgroup.groupid = rgroup.groupid LEFT JOIN osinfo ON osinfo.workstationid = sysinfo.workstationid LEFT JOIN processorinfo procInfo ON sysinfo.workstationid = procInfo.workstationid LEFT JOIN lastauditinfo LastAuditInfo ON resource.resourceid = lastauditinfo.workstationid LEFT JOIN audithistory LASTSUCCESSAUDIT ON lastauditinfo.last_success_auditid = LASTSUCCESSAUDIT.auditid LEFT JOIN audithistory AuditHistory ON lastauditinfo.last_auditid = audithistory.auditid LEFT JOIN discoverytypeconstants SDCON ON SDCON.typeid = LASTSUCCESSAUDIT.scantype LEFT JOIN discoverytypeconstants DCON ON DCON.typeid = audithistory.scantype LEFT JOIN resourceowner resOwn ON resource.resourceid = resOwn.resourceid LEFT JOIN departmentdefinition resDept ON resOwn.deptid = resDept.deptid LEFT JOIN aaauser resUser ON resOwn.userid = resUser.user_id 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 CI ci on resource.ciid = ci.ciid LEFT JOIN CIType citype ON ci.CITYPEID = citype.TYPEID
INNER JOIN "AccountSiteMapping" "asm" ON "resource"."SITEID" = "asm"."SITEID" INNER JOIN "AccountDefinition" "ad" ON "asm"."ACCOUNTID" = "ad"."ORG_ID" where audithistory.auditstatus='SUCCESS' and audithistory.auditid in (Select max(audithistory.auditid) from audithistory group by workstationid) GROUP BY sysinfo.workstationid , sysinfo.biosdate
New to ADSelfService Plus?
Related Articles
Query to show Asset details with Warranty Expiry Date (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) SELECT productType.COMPONENTTYPENAME AS "Product Type", resource.RESOURCENAME AS "Asset Name", product.COMPONENTNAME AS "Product", productType.COMPONENTTYPENAME AS "Product Type", state.DISPLAYSTATE AS ...Query to get Asset contract and its details (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) SELECT mcdt.Contractid "URL Contract ID", mcdt.customcontractid "Custom contractid in details page", mcdt.CONTRACTNAME "Contract Name", aao.NAME "Maintenance Vendor Name", LONGTODATE(mcdt.FROMDATE) ...Query to show workstation's hard disk details (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT Max(workstation.workstationname) "Workstation", Max(workstation.servicetag) "Service Tag", Max(workstation.model) "Model", Max(osinfo.osname) "OS", MAX(memInfo.TOTALMEMORY/(1024*1024*1024)) AS ...Query to show Average response time for Category (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg Response Time" FROM ...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()) + ...