Problem with queries return the same username over and over again

[jsmisek]

I found a query online and it works without issues.

Select Top 1000000 tblADusers.Userdomain,
tblADusers.Name,
tblADusers.Displayname,
tblADusers.OU
From tblADusers
Inner Join tblUsersInGroup On tblUsersInGroup.Username = tblADusers.Username
And tblUsersInGroup.Domainname = tblADusers.Userdomain
Where tblUsersInGroup.Admingroup = 1

It winds up looking like this for many users.


Mary A*** Mary A*** Mary A***
Mary A*** Mary A*** Mary A***
Mary A*** Mary A*** Mary A***
Mary A*** Mary A*** Mary A***
Mary A*** Mary A*** Mary A***
Mary A*** Mary A*** Mary A***
Mary A*** Mary A*** Mary A***
Mary A*** Mary A*** Mary A***
Mary A*** Mary A*** Mary A***
Mary A*** Mary A*** Mary A***
Mary A*** Mary A*** Mary A***
Mary A*** Mary A*** Mary A***
Mary A*** Mary A*** Mary A***

Any ideas?

[jsmisek]

What specifically does "Last Change" encompass in regards to what changes are made to an account?

PS. Want to thank you for the assistance, since i've been remiss in doing so.

[Hemoco]

jsmisek wrote:
What specifically does "Last Change" encompass in regards to what changes are made to an account?

Many tables have a Lastchanged field. This field reflects when a change last occurred within a table record. If a user's Displayname is changed for instance, the Lastchanged field will be updated as well.

[jsmisek]

I used the query that was posted above. Which did show me why i was getting redundant returns. It was returning every machine the user currently was logged into. Which resolves that question. Though i still can't get account disabled value returned without causing issues.

[RCorbeil]

I'm not sufficiently familiar with AD to be able to help, alas.

I've been poking through my tblADUsers and tblUsersInGroup tables to see if there's anything obvious to filter on, but the former doesn't offer the attributes you're looking for and the latter is definitely associated with assets, so any one user can have multiple, different results based on their status with respect to any given computer/asset.

Hopefully someone who knows AD can shed some light.

[jsmisek]

I"m simply attempting to produce a list of each account username, first and last if applicable. Then of course display the OU so that it gives us an idea of whether it's Service account or User account. I've also been attempting to add in the "Disalbled" queury but it tends to blow up returning an IIS web.conf error.

[Hemoco]

Lansweeper only scans the enabled/disabled status of local computer accounts, not of AD accounts.

jsmisek wrote:
I"m simply attempting to produce a list of each account username, first and last if applicable. Then of course display the OU so that it gives us an idea of whether it's Service account or User account.

Use the report below for this.

Select Top 1000000 tblADusers.Username,
tblADusers.Userdomain,
tblADusers.Displayname,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.OU,
tblADusers.Lastchanged
From tblADusers
Order By tblADusers.Userdomain,
tblADusers.Username

[RCorbeil]

Mary A probably has an account on numerous assets.

Quick & dirty check:

SELECT TOP 1000000
  tblADusers.Username AS AD_Username,
  tblADUsers.Userdomain,
  tblUsersInGroup.Username AS InGroup_Username,
  tblUsersInGroup.Groupname,
  tblUsersInGroup.Domainname,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tblAssets.Username AS Asset_Username,
  tblAssets.Description
FROM
  tblADusers
  INNER JOIN tblUsersInGroup ON (tblUsersInGroup.Username = tblADusers.Username)
                            And (tblUsersInGroup.Domainname = tblADusers.Userdomain)
  INNER JOIN tblAssets ON tblAssets.AssetID = tblUsersInGroup.AssetID
WHERE
  tblUsersInGroup.Admingroup = 1
ORDER BY
  tblADUsers.Userdomain,
  tblADusers.Username,
  tblAssets.AssetUnique

The tblUsersInGroup table contains account references for each asset.

What are you trying to produce? A description of the goal can only help.