Query to show to Assets with their IP and MAC Address

Query to show to Assets with their IP and MAC Address

PGSQL & MSSQL:

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
          • Related Articles

          • 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",   ...
          • Query to show workstations that has a scan status

            PGSQ & MSSQL: Below query that will show the Assets that has a scan status. select systeminfo.workstationname "Workstation Name", LONGTODATE(audithistory.audittime) "Last Scanned on", audithistory.auditstatus "Scan status" from audithistory left join ...
          • When will the Assets get duplicated ?

            Please find the possible scenarios when the machines are renamed with _old Scenario 1:    When the workstations are swapped in the network.  Consider two workstations, MACHINE-A with service tag ST-1 and MACHINE-B with ST-2. Both the workstations are ...
          • Query to show count of IT assets or count of assets that consumed license

            SELECT count(Resources.RESOURCEID) "Count Of Used Assets" FROM Resources LEFT JOIN ComponentDefinition ON Resources.COMPONENTID=ComponentDefinition.COMPONENTID LEFT JOIN ComponentType ON ...
          • 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",  ...