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.

[Bruce_B]

The syntax of the report is correct, I've just verified it. It may be that something extra is getting copy/pasted over. Try pasting the query in notepad first to check if there are any hidden characters coming with.

[SBSTech]

when trying to apply the V.6 I get "Invalid SELECT statement. Unexpected token "tblRegistry" at line 15, pos 1.: Unexpected token "tblRegistry" at line 15, column 1" ... Not quite sure why.

[Nick_VDB · Accepted solution]

V.6.0 compatible report.

Select tblAssets.AssetName,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tblADusers.Firstname,
  tblADusers.Lastname,
  FirstKey.Detect,
  SecondKey.Download,
  ThirdKey.Install
From tblAssets
  Left Join (Select tblRegistry.AssetID,
    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.AssetID = tblAssets.AssetID
  Left Join (Select tblRegistry.AssetID,
    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.AssetID = tblAssets.AssetID
  Left Join (Select tblRegistry.AssetID,
    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.AssetID = tblAssets.AssetID
  Left Join tblADusers On tblADusers.Username = tblAssets.Username And
    tblADusers.Userdomain = tblAssets.Userdomain
Order By tblAssets.AssetUnique

[SystemsIT]

Any chance of an updated version that works on v6?

[erankave]

SystemsIT wrote:
Any chance of an updated version that works on v6?

Yes please!

[uke-afu]

SystemsIT wrote:
Any chance of an updated version that works on v6?

i think its will be tblAssets. 🙂

[sundarrs]

Hi

Can someone give the exact way to enable.
I tried to generate a report.But it always showed tblcomputers not found

[Hemoco]

sundarrs wrote:
Hi

Can someone give the exact way to enable.
I tried to generate a report.But it always showed tblcomputers not found

Which Lansweeper version are you using? 4.2 or 5.0? Please note that tblComputers does not exist in 5.0.

[TNTreasury]

The following is working for me. I found a DBA and he put together the following. I did have to resize my screen before i could get results from the report builder. I saved it and no results but i get results from the web interface. I can't explain it but it works.

We are using this report to find machines that are not updating properly.
I hope to be able to expand the report to add a column for the machine contacting the Domain Controller.

I hope this helps someone

Select Top 1000000 c.Computername, c.ComputerUnique, c.Domain, u.Firstname, u.Lastname, fk.Detect, sk.Download, tk.Install From tblComputers As c Left Join (Select r.Computername, r.Regkey, r.Value As Detect From tblRegistry As r Where r.Regkey = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\Results\Detect') fk On fk.Computername = c.Computername Left Join (Select r.Computername, r.Regkey, r.Value As Download From tblRegistry As r Where r.Regkey = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\Results\Download') sk On sk.Computername = c.Computername Left Join (Select r.Computername, r.Regkey, r.Value As Install From tblRegistry As r Where r.Regkey = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\Results\Install') tk On tk.Computername = c.Computername Left Join tblADusers As u On u.Username = c.Username And u.Userdomain = c.Userdomain Order By c.ComputerUnique