Query to show workstation additional fields (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306)
SELECT workstation.WORKSTATIONNAME AS "Machine Name", workstation.MANUFACTURER AS "Manufacturer", workstation.SERVICETAG AS "Service Tag", workstation.MODEL AS "Model", aao.NAME AS "Vendor Name", wof.udf_char1 "Cubical Number", wof.udf_char2 "Purchase order", wof.udf_char3 "Invoice Number", wof.udf_char4 "Invoice Date", resToCost.TOTALCOST AS "Total Cost", LONGTODATE(resource.WARRANTYEXPIRY) AS "Warranty Expiry" FROM SystemInfo workstation LEFT JOIN Resources resource ON workstation.WORKSTATIONID=resource.RESOURCEID LEFT JOIN VendorDefinition resourceVendor ON resource.VENDORID=resourceVendor.VENDORID LEFT JOIN SDOrganization aao ON resourceVendor.VENDORID=aao.ORG_ID LEFT JOIN ResourceToCost resToCost ON resource.RESOURCEID=resToCost.RESOURCEID LEFT JOIN ResourceLocation resLocation ON workstation.WORKSTATIONID=resLocation.RESOURCEID LEFT JOIN workstation_fields wof ON workstation.workstationid=wof.workstationid WHERE (ISSERVER='0')
New to ADSelfService Plus?
Related Articles
Query to display additional field associations with templates (MSSQL)
Tested in build MSSQL (14306) Use case The reports shows in which templates the created additional fields are associated Query select sd.name "Service Catalog Name", rt.templatename "Template name", fc.field_name "Fields" from requesttemplate_list rt ...Query to extract the “Software-License Additional Fields” along with the existing additional fields (PGSQL)
(Applicable for builds 14300 & above) Below query has been modified to add a LEFT JOIN between the additional fields table and the table from which you extracted the softwares: QUERY: SELECT SUM(scd.ALLOCATED) AS "Allocated", ...Query to show support groups and its individual custom attributes (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT ci.CINAME AS "Support Group Name", su.first_name "Owned By", ci.DESCRIPTION AS "Description",ia.attributename "Additional Attributes Name",ia.attributevalue "Additional Attributes ...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", ...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 ...