Query to show to Assets with their IP and MAC Address (MSSQL & PGSQL)

Query to show to Assets with their IP and MAC Address (MSSQL & PGSQL)

Tested in Build PGSQL (14300) or MSSQL (14306)

SELECT productType.COMPONENTTYPENAME AS "Product Type", resource.RESOURCENAME AS "Asset Name", networkinfo.IPADDRESS "IP Address", networkinfo.macaddress "MAC Address", product.COMPONENTNAME AS "Product", productType.COMPONENTTYPENAME AS "Product Type", aao.NAME AS "Vendor Name", state.DISPLAYSTATE AS "Asset State", rCategory.CATEGORY AS "Asset Category", rtype.TYPE AS "Asset Type" 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 ResourceCategory rCategory ON productType.RESOURCECATEGORYID=rCategory.RESOURCECATEGORYID 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 ResourceLocation resLocation ON resource.RESOURCEID=resLocation.RESOURCEID LEFT JOIN networkinfo on resource.resourceid=networkinfo.WORKSTATIONID ORDER BY 1

                  New to ADSelfService Plus?