→ 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: 
TNTreasury
Engaged Sweeper
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.
1 ACCEPTED SOLUTION
Nick_VDB
Champion Sweeper III
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

View solution in original post

27 REPLIES 27
Bruce_B
Lansweeper Alumni
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
Engaged Sweeper II
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
Champion Sweeper III
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
Engaged Sweeper III
Any chance of an updated version that works on v6?
erankave
Engaged Sweeper II
SystemsIT wrote:
Any chance of an updated version that works on v6?

Yes please!
uke-afu
Engaged Sweeper II
SystemsIT wrote:
Any chance of an updated version that works on v6?


i think its will be tblAssets. 🙂
sundarrs
Engaged Sweeper II
Hi

Can someone give the exact way to enable.
I tried to generate a report.But it always showed tblcomputers not found
Hemoco
Lansweeper Alumni
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
Engaged Sweeper
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