Asset Module

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


      New to M365 Manager Plus?
      New to M365 Manager Plus?
        New to RecoveryManager Plus?
        New to RecoveryManager Plus?
          New to Exchange Reporter Plus?
          New to Exchange Reporter Plus?
            New to SharePoint Manager Plus?
            New to SharePoint Manager Plus?
                See all integrations
                New to ADManager Plus?
                See all integrations
                New to ADManager Plus?

                  New to ADSelfService Plus?

                  Start your free trial
                  Start your free trial

                    • Related Articles

                    • Change module data : Import into Analytics Plus

                      Change module data : Import into Analytics Plus Note: When Analytics Plus officially supports Change module integration Out of the box, then these imported tables and reports might get overwritten. Please ensure to name the table as 'Change_localDB'. ...

                    • Problem Module Data import

                      Importing Problem data into Analytics Plus:  You could log into the Analytics Plus 'ME ServiceDesk Plus Analytics' database and start creating these two tables using the below queries. Check this link for steps to import data from Local DB. Problem ...

                    • How to import description field details from ServiceDesk Plus to Analytics Plus?

                      Description details from the Requests' module are not available out-of-the box in Analytics Plus as it might contain HTML contents and cannot be displayed properly for reporting. However, if you wish to import description details into Analytics Plus, ...

                    • Importing Projects Module data into Analytics Plus

                      We are yet to sync 'Projects' module's data into Analytics Plus. Meanwhile, you can use custom queries to import those data from ServcieDesk Plus. Step 1: Login into Analytics Plus and open ServiceDesk Plus database. Step 2: Click 'Import Data' and ...

                    • How to construct a query to pull additional fields - Asset

                      To find Alais name of an asset additional field name select COLUMNNAME,ALIASNAME from columnaliases where tablename='Resource_fields' Sample Additional fields Environment Hosting Platform Patching Time-Frame MFA Function Datacenter Backups Scheduled ...

                      Related Products