Community and Support

            Query to Pull Asset Module data from ServiceDesk Plus

            Please use the following query for Asset Module(General Details)

            PGSQL:
            select res.RESOURCEID AS "ASSET ID", 
            res.RESOURCENAME AS "NAME",
            to_timestamp(res.ACQUISITIONDATE/1000) AS "ACQUISITION DATE",
            to_timestamp(res.WARRANTYEXPIRY/1000) AS "WARRANTY EXPIRY",
            to_timestamp(res.EXPIRYDATE/1000) AS "EXPIRY DATE",
            res.ASSETTAG AS "ASSET TAG",
            res.LOCATION AS "LOCATION",
            res.SERIALNO AS "SERIAL NUMBER",
            bar.BARCODE AS "BARCODE",
            sys.MODEL AS "MODEL",
            sys.SERVICETAG AS "SERVICE TAG",
            sys.LOGGEDUSER AS "LAST LOGGEDIN USER",
            recost.PURCHASECOST AS "PURCHASE COST",
            recost.OPERATIONALCOST AS "OPERATIONAL COST",
            recost.CURRENTCOST AS "CURRENT COST",
            recost.TOTALCOST AS "TOTAL COST",
            org.NAME AS "SITE",
            reg.REGIONNAME AS "REGION",
            dept.DEPTNAME AS "DEPARTMENT",
            aaa.FIRST_NAME AS "USER",
            os.OSNAME AS "OS",
            dom.DOMAINNAME AS "DOMAIN NAME"
            from RESOURCES res left join RESOURCETOCOST recost on res.RESOURCEID = recost.RESOURCEID
            left join RESOURCEOWNER reowner on res.RESOURCEID = reowner.RESOURCEID
            left join DEPARTMENTDEFINITION dept on reowner.DEPTID = dept.DEPTID
            left join SYSTEMINFO sys on res.RESOURCEID = sys.WORKSTATIONID
            left join SITEDEFINITION site on res.SITEID = site.SITEID
            left join SDORGANIZATION org on site.SITEID = org.ORG_ID
            left join REGIONDEFINITION reg on site.REGIONID = reg.REGIONID
            left join BARCODES bar on res.BARCODEID = bar.BARCODEID
            left join RESOURCESTATE rest on res.RESOURCESTATEID = rest.RESOURCESTATEID
            left join SDUSER sdu on reowner.USERID =  sdu.USERID
            left join AAAUSER aaa on sdu.USERID = aaa.USER_ID
            left join OSINFO os on sys.WORKSTATIONID = os.WORKSTATIONID
            left join SYSTEMINFODOMAIN sysin on sys.WORKSTATIONID = sysin.WORKSTATIONID
            left join DOMAININFO dom on sysin.DOMAINID = dom.DOMAINID
            MSSQL:
            select res.RESOURCEID AS "ASSET ID", 
            res.RESOURCENAME AS "NAME",
            DATEADD(MILLISECOND, res.ACQUISITIONDATE % 1000, DATEADD(SECOND, res.ACQUISITIONDATE / 1000, '19700101')) AS "ACQUISITION DATE",
            DATEADD(MILLISECOND, res.WARRANTYEXPIRY % 1000, DATEADD(SECOND, res.WARRANTYEXPIRY / 1000, '19700101')) AS "WARRANTY EXPIRY",
            DATEADD(MILLISECOND, res.EXPIRYDATE % 1000, DATEADD(SECOND, res.EXPIRYDATE / 1000, '19700101')) AS "EXPIRY DATE",
            res.ASSETTAG AS "ASSET TAG",
            res.LOCATION AS "LOCATION",
            res.SERIALNO AS "SERIAL NUMBER",
            bar.BARCODE AS "BARCODE",
            sys.MODEL AS "MODEL",
            sys.SERVICETAG AS "SERVICE TAG",
            sys.LOGGEDUSER AS "LAST LOGGEDIN USER",
            recost.PURCHASECOST AS "PURCHASE COST",
            recost.OPERATIONALCOST AS "OPERATIONAL COST",
            recost.CURRENTCOST AS "CURRENT COST",
            recost.TOTALCOST AS "TOTAL COST",
            org.NAME AS "SITE",
            reg.REGIONNAME AS "REGION",
            dept.DEPTNAME AS "DEPARTMENT",
            aaa.FIRST_NAME AS "USER",
            os.OSNAME AS "OS",
            dom.DOMAINNAME AS "DOMAIN NAME"
            from RESOURCES res left join RESOURCETOCOST recost on res.RESOURCEID = recost.RESOURCEID
            left join RESOURCEOWNER reowner on res.RESOURCEID = reowner.RESOURCEID
            left join DEPARTMENTDEFINITION dept on reowner.DEPTID = dept.DEPTID
            left join SYSTEMINFO sys on res.RESOURCEID = sys.WORKSTATIONID
            left join SITEDEFINITION site on res.SITEID = site.SITEID
            left join SDORGANIZATION org on site.SITEID = org.ORG_ID
            left join REGIONDEFINITION reg on site.REGIONID = reg.REGIONID
            left join BARCODES bar on res.BARCODEID = bar.BARCODEID
            left join RESOURCESTATE rest on res.RESOURCESTATEID = rest.RESOURCESTATEID
            left join SDUSER sdu on reowner.USERID =  sdu.USERID
            left join AAAUSER aaa on sdu.USERID = aaa.USER_ID
            left join OSINFO os on sys.WORKSTATIONID = os.WORKSTATIONID
            left join SYSTEMINFODOMAIN sysin on sys.WORKSTATIONID = sysin.WORKSTATIONID
            left join DOMAININFO dom on sysin.DOMAINID = dom.DOMAINID

            Helpful?  
            Help us to make this article better
            0 0