WSUS Reports

[TNTreasury]

I'm needing to build a report which displays values from the registry for WSUS detect, download and install.

I have the registry scanning options set and this is working well. The problem becomes in reporting as all three valuenames are the same "LastSuccessTime"

The report is generating and giving me three lines for each PC
pc1 detect date
pc1 download date
pc1 install date

pc2 detect date
pc2 download date
pc2 install date

pc3 detect date
pc3 download date
pc3 install date



Ideally, I would like to have
Detect Download Install
PC1 date date date
PC2 date date date
PC3 date date date
PC4 date date date


From the report builder, here's the code:

Select Top 1000000 tblRegistry.Valuename, tblRegistry.Value,
tblRegistry.Computername, tblComputers.LastknownIP, tblComputers.Computer,
tblComputers.Username, tblADusers.Firstname, tblADusers.Lastname,
tblRegistry.Regkey
From tblRegistry Inner Join
tblComputers On tblComputers.Computername = tblRegistry.Computername
Inner Join
tblADusers On tblADusers.Username = tblComputers.Username



any help / thoughts would be greatly appreciated.

[Hemoco]

TNTreasury wrote:
Thank you. I copy / pasted this code and the report list the computer one time along with the user's first and last name. There are colums for Detect, download and Install, but there are no values.

Do I need to include the tblRegistry ?

TblRegistry is already included in the report, through the derived tables. Have values for the registry keys you are after actually been scanned? If not, could you perform a Full Rescan of your machines.

[TNTreasury]

Lansweeper wrote:

TNTreasury wrote:
Thank you. I copy / pasted this code and the report list the computer one time along with the user's first and last name. There are colums for Detect, download and Install, but there are no values.

Do I need to include the tblRegistry ?

TblRegistry is already included in the report, through the derived tables. Have values for the registry keys you are after actually been scanned? If not, could you perform a Full Rescan of your machines.

Yes... to confirm there's data in the tblRegistry, here's a screen shot and code

Select tblComputers.Computername, tblComputers.ComputerUnique,
  tblComputers.Domain, tblRegistry.Computername As Computername1,
  tblRegistry.Regkey, tblRegistry.Lastchanged, tblRegistry.Value,
  tblRegistry.Valuename
From tblComputers Inner Join
  tblRegistry On tblComputers.Computername = tblRegistry.Computername

[img][/img]

[Hemoco]

Could you try the report below instead.

Select tblComputers.Computername, tblComputers.ComputerUnique,
tblComputers.Domain, tblADusers.Firstname, tblADusers.Lastname,
FirstKey.Detect, SecondKey.Download, ThirdKey.Install
From tblComputers Left Join
(Select tblRegistry.Computername, tblRegistry.Regkey, tblRegistry.Value As
Download
From tblRegistry
Where
tblRegistry.Regkey = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\Results\Download')
SecondKey On SecondKey.Computername = tblComputers.Computername Left Join
(Select tblRegistry.Computername, tblRegistry.Regkey, tblRegistry.Value As
Install
From tblRegistry
Where
tblRegistry.Regkey =
'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\Results\Install') ThirdKey On ThirdKey.Computername = tblComputers.Computername Left Join
(Select tblRegistry.Computername, tblRegistry.Regkey, tblRegistry.Value As
Detect
From tblRegistry
Where tblRegistry.Regkey = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\Results\Detect') FirstKey On FirstKey.Computername =
tblComputers.Computername Left Join
tblADusers On tblADusers.Username = tblComputers.Username And
tblADusers.Userdomain = tblComputers.Userdomain
Order By tblComputers.ComputerUnique

[TNTreasury]

Thanks so much for the fast response.

I copied and pasted the code, but do not get results.

Select Top 10000 tblComputers.Computername, tblComputers.ComputerUnique,
tblComputers.Domain, tblRegistry.Value As Detect,
tblRegistry1.Value As Download, tblRegistry2.Value As Install
From tblComputers 
Inner Join tblRegistry On tblComputers.Computername = tblRegistry.Computername
Inner Join tblRegistry tblRegistry1 On tblComputers.Computername = tblRegistry1.Computername 
Inner Join tblRegistry tblRegistry2 On tblComputers.Computername = tblRegistry2.Computername
Where tblRegistry.Regkey = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\Results\Detect'
And tblRegistry1.Regkey = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\Results\Download'
And tblRegistry2.Regkey = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\Results\Install'

If I remove this, i do get results
And tblRegistry1.Regkey

Where tblRegistry.Regkey = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\Results\Detect'
And tblRegistry1.Regkey = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\Results\Download'
And tblRegistry2.Regkey = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\Results\Install'

If i add the tblADusers, I do get the names, etc ... but that is with the lines removed.

[Hemoco]

The solution proposed by wcb will work. If you wish to include tblADusers in your report, don't forget to link it to tblComputers on both the "Username" and "Userdomain" fields.

[wcb]

If I got my cut and paste correct, this should work as a start:

Select Top 10000 tblComputers.Computername, tblComputers.ComputerUnique,
tblComputers.Domain, tblRegistry.Value As Detect,
tblRegistry1.Value As Download, tblRegistry2.Value As Install
From tblComputers 
Inner Join tblRegistry On tblComputers.Computername = tblRegistry.Computername
Inner Join tblRegistry tblRegistry1 On tblComputers.Computername = tblRegistry1.Computername 
Inner Join tblRegistry tblRegistry2 On tblComputers.Computername = tblRegistry2.Computername
Where tblRegistry.Regkey = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\Results\Detect'
 And tblRegistry1.Regkey = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\Results\Download'
 And tblRegistry2.Regkey = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\Results\Install'

Basically you are joining to the same table multiple times and using an alias for the additional references. Lansweeper support may come back with something similar or completely different but this is what I did for a similar situation.

[Hemoco]

Can you post an extract from the results table and your defined registry keys please.

[TNTreasury]

Lansweeper wrote:
Can you post an extract from the results table and your defined registry keys please.

In Registry Scanning we have the following defined.

Detect

RootKeyHKEY_LOCAL_MACHINE
RegPathSOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\Results\Detect
RegvalueLastSuccessTime

Download

RootKeyHKEY_LOCAL_MACHINE
RegPathSOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\Results\Download
RegvalueLastSuccessTime

Install

RootKeyHKEY_LOCAL_MACHINE
RegPathSOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\Results\Install
RegvalueLastSuccessTime




RegistryID Computername Regkey Valuename Value Lastchanged
1 125 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\Results\Detect LastSuccessTime 2012-04-30 16:00:45 4/30/2012 11:50:25 AM
2 125 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\Results\Download LastSuccessTime 2012-04-30 08:00:36 4/30/2012 11:50:25 AM
3 125 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\Results\Install LastSuccessTime 2012-04-30 08:00:27 4/30/2012 11:52:25 AM
4 460 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\Results\Detect LastSuccessTime 2012-04-30 05:50:20 4/30/2012 12:17:16 PM
5 460 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\Results\Download LastSuccessTime 2012-04-12 08:09:25 4/30/2012 12:17:16 PM