→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
paultech
Engaged Sweeper
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
3 REPLIES 3
RCorbeil
Honored Sweeper II
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
Engaged Sweeper
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
Honored Sweeper II
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%'
...