cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Notonyourradar
Engaged Sweeper III
Looking to create a report of Machines that do not Skype for Business Basic 2016, Microsoft Office Professional 2016, or Microsoft Office 365 ProPlus installed (and others). I am able to find reports on single pieces of software but can't seem to modify correctly to accommodate for multiple products. Below is what I was trying but having no luck.


Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tsysOS.OSname,
tblOperatingsystem.Caption,
tsysIPLocations.IPLocation,
tblAssets.Lastseen,
tblADComputers.OU,
tblComputersystem.SystemType
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where (tblSoftwareUni.softwareName Like 'Microsoft Office 365%' And
tblSoftware.softwareVersion Like '16.%') Or
(tblSoftwareUni.softwareName Like 'Microsoft Office Professional 2016%') Or
(tblSoftwareUni.softwareName Like 'Skype For Business Basic 2016%') Or
(tblSoftwareUni.softwareName Like 'Microsoft Office 365 ProPlus%' And
tblSoftware.softwareVersion Like '16.%') Or
(tblSoftwareUni.softwareName Like 'Skype voor Bedrijven Basic 2016%') Or
(tblSoftwareUni.softwareName Like 'Office 365 16.0%') Or
(tblSoftwareUni.softwareName Like 'Microsoft Office Professionnel Plus%' And
tblSoftware.softwareVersion Like '16.%') Or
(tblSoftwareUni.softwareName Like
'Microsoft Office Professional Plus 2016%') Or
(tblSoftwareUni.softwareName Like 'Microsoft Office 2016%' And
tblAssetCustom.State = 1))
Order By tsysIPLocations.IPLocation,
tblAssets.IPAddress,
tblAssets.AssetName


Any ideas?
3 REPLIES 3
Esben_D
Lansweeper Employee
Lansweeper Employee
If you want asset which do not have any of the products mentioned you will have to use And in your where clause to get the logic that the assets do not have software X and software Y and software Z.

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblSoftwareUni.softwareName Not Like 'Microsoft Office 365%' And
tblSoftwareUni.softwareName Not Like 'Microsoft Office Professional 2016%' And
tblSoftwareUni.softwareName Not Like 'Skype For Business Basic 2016%' And
tblSoftwareUni.softwareName Not Like 'Microsoft Office 365 ProPlus%'
And tblSoftwareUni.softwareName Not Like 'Skype voor Bedrijven Basic 2016%'
And tblSoftwareUni.softwareName Not Like 'Office 365 16.0%' And
tblSoftwareUni.softwareName Not Like 'Microsoft Office Professionnal Plus%'
And
tblSoftwareUni.softwareName Not Like
'Microsoft Office Professional Plus 2016%' And tblSoftwareUni.softwareName Not
Like 'Microsoft Office 2016%' And tblSoftware.softwareVersion Not Like '16.%'
And tblState.Statename = 'Active'
Order By tblAssets.AssetName
Notonyourradar
Engaged Sweeper III
Doesn't seem to give me what I want and lists every windows asset we have - we are looking for it to report back on machines that don't have ANY of the products in the report. So if it has Skype for Business Basic, but not Office 365 it would not show on the report.

I am looking to show any machine that doesn't have ANY of these products on it.
Esben_D
Lansweeper Employee
Lansweeper Employee
Personally I think it is a lot easier to just use "Not like" instead of "Not in". I created a report using your where clause which should give you what you described.

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where (tblSoftwareUni.softwareName Not Like 'Microsoft Office 365%' And
tblSoftware.softwareVersion Not Like '16.%') Or
(tblSoftwareUni.softwareName Not Like 'Microsoft Office Professional 2016%')
Or
(tblSoftwareUni.softwareName Not Like 'Skype For Business Basic 2016%') Or
(tblSoftwareUni.softwareName Not Like 'Microsoft Office 365 ProPlus%' And
tblSoftware.softwareVersion Not Like '16.%') Or
(tblSoftwareUni.softwareName Not Like 'Skype voor Bedrijven Basic 2016%') Or
(tblSoftwareUni.softwareName Not Like 'Office 365 16.0%') Or
(tblSoftwareUni.softwareName Not Like 'Microsoft Office Professionnal Plus%'
And tblSoftware.softwareVersion Not Like '16.%') Or
(tblSoftwareUni.softwareName Not Like
'Microsoft Office Professional Plus 2016%') Or
(tblSoftwareUni.softwareName Not Like 'Microsoft Office 2016%' And
tblState.Statename = 'Active')
Order By tblAssets.AssetName