‎10-18-2013 05:19 PM
Solved! Go to Solution.
‎10-22-2013 03:08 PM
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Username,
tblAssets.Userdomain,
tblADusers.Firstname,
tblADusers.Lastname,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher,
tblAssets.Lastseen
From tblAssets
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssets.AssetID Not In (Select Top 1000000 tblAssetGroupLink.AssetID
From tblAssetGroups Inner Join tblAssetGroupLink
On tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
Where (tblAssetGroups.AssetGroup = 'DocTeam') Or
(tblAssetGroups.AssetGroup = 'UI Team')) And
tblSoftwareUni.SoftwarePublisher Like '%Adobe%' And tblAssets.Lastseen >
GetDate() - 30
Order By tblAssets.Domain,
tblAssets.AssetName,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
‎10-22-2013 05:14 PM
‎10-22-2013 03:08 PM
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Username,
tblAssets.Userdomain,
tblADusers.Firstname,
tblADusers.Lastname,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher,
tblAssets.Lastseen
From tblAssets
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssets.AssetID Not In (Select Top 1000000 tblAssetGroupLink.AssetID
From tblAssetGroups Inner Join tblAssetGroupLink
On tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
Where (tblAssetGroups.AssetGroup = 'DocTeam') Or
(tblAssetGroups.AssetGroup = 'UI Team')) And
tblSoftwareUni.SoftwarePublisher Like '%Adobe%' And tblAssets.Lastseen >
GetDate() - 30
Order By tblAssets.Domain,
tblAssets.AssetName,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
‎10-22-2013 03:06 PM
‎10-21-2013 08:07 PM
SELECT TOP 1000000where, of course, you replace 'X' and 'Y' with your asset groups.
...
WHERE
tblSoftwareUni.SoftwarePublisher LIKE '%Adobe%'
AND tblAssets.Lastseen > GetDate() - 30
-- [ BEGIN ]
AND tblAssets.AssetID NOT IN ( SELECT Top 1000000
tblAssetGroupLink.AssetID
FROM
tblAssetGroups
INNER JOIN tblAssetGroupLink ON tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
WHERE
(tblAssetGroups.AssetGroup = 'X')
OR (tblAssetGroups.AssetGroup = 'Y')
)
-- [ END ]
ORDER BY
...
‎10-21-2013 07:01 PM
Not In (Select Top 1000000 tblAssetGroupLink.AssetID
From tblAssetGroups Inner Join tblAssetGroupLink
On tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
Where (tblAssetGroups.AssetGroup = 'X') Or
(tblAssetGroups.AssetGroup = 'Y'))
‎10-21-2013 09:26 AM
‎10-18-2013 06:43 PM
Not In (Select Top 1000000 tblAssetGroupLink.AssetID
From tblAssetGroups Inner Join tblAssetGroupLink
On tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
Where (tblAssetGroups.AssetGroup = 'X') Or
(tblAssetGroups.AssetGroup = 'Y'))
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now