Last Windows Update

[paultech]

There is a chart report Last Windows Update Chart Query

And the SQL query is:

Select Top 1000000 Case
    When LastPatchDate.lastPatchDate >= DateAdd(day, -7, GetDate()) Then
      '1. less than a week'
    When LastPatchDate.lastPatchDate < DateAdd(day, -7, GetDate()) And LastPatchDate.lastPatchDate >= DateAdd(day, -30, GetDate()) Then
      '2. less than a month'
    When LastPatchDate.lastPatchDate < DateAdd(day, -31, GetDate()) And LastPatchDate.lastPatchDate >= DateAdd(day, -90, GetDate()) Then
      '3. less than 3 months'
    Else '4. more than 3 months'
  End As DateRange,
  count(*) As NumberOfAssets
From (Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.OSname,
  tblAssets.Username,
  tblAssets.IPAddress,
  tblAssets.Description,
  Max(Convert(date,tblQuickFixEngineering.InstalledOn)) As lastPatchDate,
  tblAssets.Lastseen
From tblQuickFixEngineering
  Join tblAssets On tblAssets.AssetID = tblQuickFixEngineering.AssetID
  Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssetCustom.State = 1
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.OSname,
  tblAssets.Username,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssets.Lastseen
Order By lastPatchDate) As LastPatchDate
Group By Case
    When LastPatchDate.lastPatchDate >= DateAdd(day, -7, GetDate()) Then
      '1. less than a week'
    When LastPatchDate.lastPatchDate < DateAdd(day, -7, GetDate()) And LastPatchDate.lastPatchDate >= DateAdd(day, -30, GetDate()) Then
      '2. less than a month'
    When LastPatchDate.lastPatchDate < DateAdd(day, -31, GetDate()) And LastPatchDate.lastPatchDate >= DateAdd(day, -90, GetDate()) Then
      '3. less than 3 months'
    Else '4. more than 3 months'
  End

I would like to amend the above query so that it will filter an active directory OU.
So somewhere in the above i'm looking to add

Where tblComputers.OU Like '%OU=Finance%' 

but i'm not really sure how to go about doing it.

Can anyone help?

Many thanks

[RCorbeil]

Other than your copying in the code that you linked to, I don't see a difference in what you're asking. I'm sorry if I'm not understanding what you think you're asking, but as best I can see my suggestion answers what I think you're asking.

Select Top 1000000
  Case
    When LastPatchDate.lastPatchDate >= DateAdd(day, -7, GetDate()) Then '1. less than a week'
    When LastPatchDate.lastPatchDate < DateAdd(day, -7, GetDate()) And LastPatchDate.lastPatchDate >= DateAdd(day, -30, GetDate()) Then '2. less than a month'
    When LastPatchDate.lastPatchDate < DateAdd(day, -31, GetDate()) And LastPatchDate.lastPatchDate >= DateAdd(day, -90, GetDate()) Then '3. less than 3 months'
    Else '4. more than 3 months'
  End As DateRange,
  count(*) As NumberOfAssets
From
  ( Select Top 1000000 tblAssets.AssetID,
      tblAssets.AssetName,
      tsysOS.OSname,
      tblAssets.Username,
      tblAssets.IPAddress,
      tblAssets.Description,
      Max(Convert(date,tblQuickFixEngineering.InstalledOn)) As lastPatchDate,
      tblAssets.Lastseen

    From
      tblQuickFixEngineering
      Join tblAssets On tblAssets.AssetID = tblQuickFixEngineering.AssetID
      Join tsysOS On tblAssets.OScode = tsysOS.OScode
      Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
      -- link in the computer AD info
      Inner Join tblADComputers ON tblADComputers.AssetID = tblAssets.AssetID
    Where
      tblAssetCustom.State = 1
      -- filter on the computer AD info
      AND tblADComputers.OU LIKE '%OU=Finance%'

    Group By
      tblAssets.AssetID,
      tblAssets.AssetName,
      tsysOS.OSname,
      tblAssets.Username,
      tblAssets.IPAddress,
      tblAssets.Description,
      tblAssets.Lastseen
    Order By
      lastPatchDate) As LastPatchDate
Group By
  Case
    When LastPatchDate.lastPatchDate >= DateAdd(day, -7, GetDate()) Then '1. less than a week'
    When LastPatchDate.lastPatchDate < DateAdd(day, -7, GetDate()) And LastPatchDate.lastPatchDate >= DateAdd(day, -30, GetDate()) Then '2. less than a month'
    When LastPatchDate.lastPatchDate < DateAdd(day, -31, GetDate()) And LastPatchDate.lastPatchDate >= DateAdd(day, -90, GetDate()) Then '3. less than 3 months'
    Else '4. more than 3 months'
  End

[paultech]

Thank you for your reply RC62N and my apologies as I didn't explain it very well in my original post so I have just edited it to be more specific.

[RCorbeil]

Modify the inner SELECT to JOIN against tblADComputers and add your filter to the WHERE clause.

...
From
  tblQuickFixEngineering
  Join tblAssets On tblAssets.AssetID = tblQuickFixEngineering.AssetID
  Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblADComputers ON tblADComputers.AssetID = tblAssets.AssetID
Where
  tblAssetCustom.State = 1
  AND tblADComputers.OU LIKE '%OU=Finance%'
...