
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-01-2021 04:57 PM
I have a report unauthorized local admins on pc's. I'm looking to also filter out ((User X on PC Y) and (user W on PC Q)) from the report. I can't seem to make it work though.
Current report:
Select Top 1000000 tblAssets.AssetName,
tblUsersInGroup.AssetID,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Username As LastUser,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblUsersInGroup.Domainname,
tblUsersInGroup.Username,
tblUsersInGroup.Lastchanged,
tsysOS.OSname As OS,
tsysOS.Image As icon,
tblAssets.Lastseen
From tblUsersInGroup
Inner Join tblAssets On tblUsersInGroup.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssetCustom.Manufacturer Not Like 'VMware%' And
tblUsersInGroup.Username Not Like 'Group1' And
tblUsersInGroup.Username Not Like 'Group2' And
tblUsersInGroup.Username Not Like 'Group3' And
tsysOS.OSname Like 'Win 10' And tblUsersInGroup.Admingroup = 1 And
tblAssetCustom.State = 1
Order By tblAssets.AssetName
Current report:
Select Top 1000000 tblAssets.AssetName,
tblUsersInGroup.AssetID,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Username As LastUser,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblUsersInGroup.Domainname,
tblUsersInGroup.Username,
tblUsersInGroup.Lastchanged,
tsysOS.OSname As OS,
tsysOS.Image As icon,
tblAssets.Lastseen
From tblUsersInGroup
Inner Join tblAssets On tblUsersInGroup.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssetCustom.Manufacturer Not Like 'VMware%' And
tblUsersInGroup.Username Not Like 'Group1' And
tblUsersInGroup.Username Not Like 'Group2' And
tblUsersInGroup.Username Not Like 'Group3' And
tsysOS.OSname Like 'Win 10' And tblUsersInGroup.Admingroup = 1 And
tblAssetCustom.State = 1
Order By tblAssets.AssetName
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-01-2021 05:10 PM
...
WHERE
...
AND NOT (tblAssets.Username = 'User X' AND tblAssets.AssetName = 'PC Y')
AND NOT (tblAssets.Username = 'User W' AND tblAssets.AssetName = 'PC Q')
3 REPLIES 3
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-01-2021 05:59 PM
The built-in editor sometimes rewrites manually-entered code when it parses it. I prefer the legibility of "NOT (X AND Y)", but the result is the same.
Cheers!
Cheers!

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-01-2021 05:33 PM
It changed to the following when I saved.. but seems to work.
Thanks!!
And (Not tblUsersInGroup.Username = 'User X' Or Not tblAssets.AssetName = 'PC y')
Thanks!!
And (Not tblUsersInGroup.Username = 'User X' Or Not tblAssets.AssetName = 'PC y')
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-01-2021 05:10 PM
...
WHERE
...
AND NOT (tblAssets.Username = 'User X' AND tblAssets.AssetName = 'PC Y')
AND NOT (tblAssets.Username = 'User W' AND tblAssets.AssetName = 'PC Q')
