Specific User PC Department Report

[Drexilla]

Hi there,

I'm quite new to writing up SQL queries and am having some difficulty producing particular reports. Hoping someone can help!

In this report, I'm using the last seen user and their respective department listed in AD.

I'd like to expand on the following report to only display PC's within a specific department such as Customer Service:

Select Top 1000000 tblAssetCustom.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Username,
  tblADusers.Department,
  tblAssets.Lastseen As [Last Seen],
  tblAssets.Lasttried,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.PurchaseDate As [Purchase Date],
  tblAssetCustom.Warrantydate As [Warranty Expiration],
  tsysOS.Image As icon,
  tblAssets.IPAddress,
  tblADComputers.OU,
  tblAssets.Processor,
  tblAssets.Memory,
  tblAssetCustom.Serialnumber
From tblAssetCustom
  Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Inner Join tblADusers On tblAssets.Username = tblADusers.Username
Where (tblAssets.Lastseen > GetDate() - 60 And tblAssetCustom.PurchaseDate Is
  Not Null And tblAssetCustom.State = 1 And tblAssetCustom.State = 1) Or
  (tblAssetCustom.Warrantydate Is Not Null And tblAssetCustom.State = 1 And
  tblAssetCustom.State = 1)
Order By [Last Seen] Desc,
  [Warranty Expiration] Desc

[Doratheexplorer · Accepted solution]

Google for "sql filter", second hit: http://stackoverflow.com/questions/16062960/sql-like-expression-to-match-all-varchar-values-i-e-no-actual-filtering-done

[...]
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblADusers On tblAssets.Username = tblADusers.Username
Where tblADusers.Department Like 'Customer Service'
Order By [Last Seen] Desc,
[Warranty Expiration] Desc
[...]

I might understand your question wrong, but this does show only Computers for "Customer Service" now. This is working just like your other Where-Statement.