Software Not installed machines
SELECT Max(workstation.workstationname)"Machine Name",
Max(aaauser.first_name) "User" FROM systeminfo workstation
LEFT JOIN resources rs
ON workstation.workstationid = rs.resourceid
LEFT JOIN softwareinfo si
ON workstation.workstationid = si.workstationid
LEFT JOIN softwarelist sl
ON si.softwareid = sl.softwareid
LEFT JOIN resourceowner rOwner
ON rs.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
WHERE workstation.workstationid NOT IN (SELECT workstationid
FROM softwareinfo wi
LEFT JOIN softwarelist wl
ON
wl.softwareid = wi.softwareid
WHERE
wl.softwarename LIKE '%Microsoft%')
GROUP BY workstation.workstationname
New to ADSelfService Plus?
Related Articles
Query to show software and its version along with workstations/Servers (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) SELECT SystemInfo.WORKSTATIONNAME "Workstation",MAX(aaaUser.FIRST_NAME) AS "User", MAX(Systeminfo.MODEL) AS "Model", (SoftwareList.SOFTWARENAME) "Software_Name", MAX(swmfg.NAME) "Manufacturer", ...
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", ...
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", ...
Script to send multiple license expiry notification in Software License/Agreement
We can set only 1 notification for the license expiry. Using custom schedule multiple expiry notifications like 90 days before, 60 days before and 30 days before can be achieved using script. Prerequisites: 1. Python is a third party software that ...