‎03-11-2015 11:04 PM
Select Top 1000000 tblAssets.AssetID,
tblAssets.IPAddress,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.Username,
tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version,
tblSoftwareUni.SoftwarePublisher As publisher,
tsysOS.Image As icon,
tblADComputers.OU
From tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblAssets.IPAddress Like '10.162.%' And tblSoftwareUni.softwareName Like
'%McAfee Virus and Spyware Protection Service%' And tblAssetCustom.State = 1
Order By tblAssets.AssetName,
software,
version
Solved! Go to Solution.
‎03-13-2015 03:24 PM
Select Top 1000000 tblAssets.AssetID,
tblAssets.IPAddress,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.Username,
tsysOS.Image As icon,
tblADComputers.OU
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblAssets.AssetID In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftware.softID =
tblSoftwareUni.SoftID
Where tblSoftwareUni.softwareName Like '%Product A%') And
tblAssets.AssetID In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftware.softID =
tblSoftwareUni.SoftID
Where tblSoftwareUni.softwareName Like '%Product B%') And
tblAssets.IPAddress Like '10.162.%' And tblAssetCustom.State = 1
Order By tblAssets.AssetName
‎03-15-2015 03:24 AM
‎03-13-2015 03:24 PM
Select Top 1000000 tblAssets.AssetID,
tblAssets.IPAddress,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.Username,
tsysOS.Image As icon,
tblADComputers.OU
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblAssets.AssetID In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftware.softID =
tblSoftwareUni.SoftID
Where tblSoftwareUni.softwareName Like '%Product A%') And
tblAssets.AssetID In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftware.softID =
tblSoftwareUni.SoftID
Where tblSoftwareUni.softwareName Like '%Product B%') And
tblAssets.IPAddress Like '10.162.%' And tblAssetCustom.State = 1
Order By tblAssets.AssetName
‎03-12-2015 05:36 PM
‎03-12-2015 02:02 PM
Select Top 1000000 tblAssets.AssetID,
tblAssets.IPAddress,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.Username,
tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version,
tblSoftwareUni.SoftwarePublisher As publisher,
tsysOS.Image As icon,
tblADComputers.OU
From tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblAssets.AssetID In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftware.softID =
tblSoftwareUni.SoftID
Where tblSoftwareUni.softwareName Like '%Product A% ') And
tblAssets.AssetID In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftware.softID =
tblSoftwareUni.SoftID
Where tblSoftwareUni.softwareName Like '%Product B% ') And
tblAssets.IPAddress Like '10.162.%' And (tblSoftwareUni.softwareName Like
'%Product A% ' Or tblSoftwareUni.softwareName Like '%Product B% ') And
tblAssetCustom.State = 1
Order By tblAssets.AssetName,
software,
version
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now