cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
allenh318ebc
Engaged Sweeper
I am trying to get a report that pulls PCs without a localuser account. I can get this one to find devices that have the account, but using 'Not Like' just lists all the other accounts on the devices.



Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblUsers.Name,
tblUsers.Fullname,
tblUsers.Accounttype,
tblUsers.Disabled,
tblUsers.Lockout,
tblUsers.PasswordChangeable,
tblUsers.PasswordExpires,
tblUsers.PasswordRequired,
tblUsers.SID,
tblUsers.Status,
tblUsers.BuildInAdmin,
tblUsers.Lastchanged,
tblAssetCustom.Model
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblUsers On tblAssets.AssetID = tblUsers.AssetID
Where tblUsers.Name = 'localuser' And tblAssetCustom.Model Like 'Think%' And
tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName,
tblUsers.Name



6 REPLIES 6
allenh318ebc
Engaged Sweeper
We are trying to find out what devices do not have a localuser account, so we can get that installed on them. If I have a report that shows devices that do not have that account, I can run a deployment against that report. The report will pull multiple lines of the same device with each local account. It is basically pullihg every local account that is not a localuser. If LaptopA does not have a localuser account, it still shows on this report with every other local account. I need it to tell me each device that does not have that local account, but only once for each device.
RCorbeil
Honored Sweeper II
If you're looking to filter the list for multiple user accounts, you'll need to include them all in the exception list.
Select Top 1000000
tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssetCustom.Model
From
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where
tblAssetCustom.Model Like 'Think%'
And tblAssetCustom.State = 1
AND tblAssets.AssetID NOT IN (SELECT
tblUsers.AssetID
FROM
tblUsers
WHERE
tblUsers.Name IN ('localuser1', 'localuser2', 'localuser3') )
Order By
tblAssets.Domain,
tblAssets.AssetName

If that's not what you're after, can you try to provide a little more detail, because as the query sits I don't think it should be producing duplicates.
allenh318ebc
Engaged Sweeper
I did that, and it runs, but will still list the same device 4-5 times with all the other local accounts. I would need to have eech device listed only one time.
RCorbeil
Honored Sweeper II
It looks like I copy/pasted a bit I'd deleted elsewhere. Drop the reference to tblUsers.Name from the Order By. Sorry about that.
allenh318ebc
Engaged Sweeper
When I try to save, I am getting the error 'The multi-part identifier "tblUsers.Name" could not be bound'
RCorbeil
Honored Sweeper II
OK, computer, first, pull me a list of assets where "localuser" is found. Got that? Good. Now pull me a list of assets that aren't in that first list.
Select Top 1000000
tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssetCustom.Model
From
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where
tblAssetCustom.Model Like 'Think%'
And tblAssetCustom.State = 1
AND tblAssets.AssetID NOT IN (SELECT
tblUsers.AssetID
FROM
tblUsers
WHERE
tblUsers.Name = 'localuser')
Order By
tblAssets.Domain,
tblAssets.AssetName,
tblUsers.Name