Specific Software

Specific Software

SELECT ( softl.softwarename )           "Software Name", 
       Max(workstation.workstationname) "Workstation", 
       Max(workstation.model)           "Model", 
       Max(aaauser.first_name)          "User", 
       Max(deptDef.deptname)            "Department", 
       Max(workstation.loggeduser)      "Last Logged In User", 
       Max(dominf.domainname)           "Domain Name", 
         WHEN Max(Cast(compDefLaptop.islaptop AS VARCHAR(5))) = 1 THEN 'Laptop' 
         ELSE 'Desktop' 
       END                              "Desktop /Laptopn" FROM   systeminfo workstation 
       LEFT JOIN resources resource 
              ON workstation.workstationid = resource.resourceid 
       LEFT JOIN componentdefinition product 
              ON resource.componentid = product.componentid 
       LEFT JOIN componentdefinitionlaptop compDefLaptop 
              ON product.componentid = compDefLaptop.componentid 
       LEFT JOIN resourceowner rOwner 
              ON resource.resourceid = rOwner.resourceid 
       LEFT JOIN resourceassociation rToAsset 
              ON rOwner.resourceownerid = rToAsset.resourceownerid 
       LEFT JOIN sduser sdUser 
              ON rOwner.userid = sduser.userid 
       LEFT JOIN aaauser aaaUser 
              ON sduser.userid = aaauser.user_id 
       LEFT JOIN departmentdefinition deptDef 
              ON rOwner.deptid = deptDef.deptid 
       LEFT JOIN systeminfodomain sysInfod 
              ON workstation.workstationid = sysInfod.workstationid 
       LEFT JOIN domaininfo dominf 
              ON sysInfod.domainid = dominf.domainid 
       LEFT JOIN softwareinfo swi 
              ON workstation.workstationid = swi.workstationid 
       LEFT JOIN softwarelist softl 
              ON swi.softwareid = softl.softwareid 
WHERE  softl.softwarename LIKE '%MICROSOFT%' 
GROUP  BY workstation.workstationid, 

                    New to ADSelfService Plus?

                      • Related Articles

                      • Software Licenses

                        SELECT resource.resourcename                 "License Name",         Max(softwarelist.softwarename)        "Software_Name",         Max(resource.acquisitiondate)         "Acquisition Date",  ...
                      • Software details

                        SELECT systeminfo.workstationname             "Workstation",         Max(aaauser.first_name)                "User",         ( softwarelist.softwarename )          "Software_Name",         Max(softwaretype.softwaretype)         "Software_Type",  ...
                      • How to export software data in CSV format?

                        Steps to export Software data in CSV format 1. Login to ServiceDesk Plus/AssetExplorer. 2. Click on the Reports tab and then click on New Query Report button. 3. Copy and paste the following query and click Run Report. select swm.name "Scanned ...
                      • 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 software license details (MSSQL & PGSQL)

                        Tested in Build PGSQL (14300) or MSSQL (14306) PGSQL & MSSQL: SELECT resources.resourcename "License Name", Max(softwarelist.softwarename) "Software_Name", LONGTODATE(Max(resources.acquisitiondate)) "Acquisition ...