Filter this query for Windows 7 only

[kim_kaberg]

Hi,

I've been trying to get this report to only show Windows 7 (any version/edition) but I'm not able to get it right. How should the report look to only include Windows 7 where the registry value is found?

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  TsysLastscan.Lasttime As LastRegistryScan,
  Case
    When TsysLastscan.Lasttime < GetDate() -
    1 Then
    'Last registry scan more than 24 hours ago! Scanned registry information may not be up-to-date. Try rescanning this machine.' End As Comment,
  Case
    When SubQuery1.Valuename Is Not Null And SubQuery1.Valuename <>
    '' Then 'Yes' Else 'No' End As ValuenameFound,
  SubQuery1.Regkey,
  SubQuery1.Valuename,
  SubQuery1.Value,
  SubQuery1.Lastchanged
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join TsysLastscan On tblAssets.AssetID = TsysLastscan.AssetID
  Inner Join TsysWaittime On TsysWaittime.CFGCode = TsysLastscan.CFGcode
  Left Join (Select Top 1000000 tblRegistry.AssetID,
    tblRegistry.Regkey,
    tblRegistry.Valuename,
    tblRegistry.Value,
    tblRegistry.Lastchanged
  From tblRegistry
  Where
    tblRegistry.Regkey Like '%SOFTWARE\Microsoft\PowerShell\3\PowerShellEngine'
    And tblRegistry.Valuename = 'PowerShellVersion') SubQuery1
    On SubQuery1.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1 And TsysWaittime.CFGname = 'registry'
Order By tblAssets.Domain,
  tblAssets.AssetName

[kim_kaberg]

Thank you!
This solved the problem.

[AZHockeyNut · Accepted solution]

here you go, quick and dirty version...
mod your join of OS code to include a filter on the name [ and tsysos.OSname like '%win%7%' ]
something like that

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
TsysLastscan.Lasttime As LastRegistryScan,
Case
When TsysLastscan.Lasttime < GetDate() -
1 Then
'Last registry scan more than 24 hours ago! Scanned registry information may not be up-to-date. Try rescanning this machine.' End As Comment,
Case
When SubQuery1.Valuename Is Not Null And SubQuery1.Valuename <>
'' Then 'Yes' Else 'No' End As ValuenameFound,
SubQuery1.Regkey,
SubQuery1.Valuename,
SubQuery1.Value,
SubQuery1.Lastchanged
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode and tsysos.OSname like '%win%7%'
Inner Join TsysLastscan On tblAssets.AssetID = TsysLastscan.AssetID
Inner Join TsysWaittime On TsysWaittime.CFGCode = TsysLastscan.CFGcode
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Regkey,
tblRegistry.Valuename,
tblRegistry.Value,
tblRegistry.Lastchanged
From tblRegistry
Where
tblRegistry.Regkey Like '%SOFTWARE\Microsoft\PowerShell\3\PowerShellEngine'
And tblRegistry.Valuename = 'PowerShellVersion') SubQuery1
On SubQuery1.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1 And TsysWaittime.CFGname = 'registry'
Order By tblAssets.Domain,
tblAssets.AssetName