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]

We will investigate this problem. It does appear to be caused by the configuration console window being too small.

The line break problem in the forum thread linked by wcb had a different cause. Report code used to be squeezed into the left pane at the bottom of the report builder, on the left side of the divider. A change was then made so that any code automatically crossed this divider.

The problem now appears to be that the configuration console window itself is introducing unwanted line breaks. If you're viewing the configuration console at a small size and have a bigger screen, maximize the console for now to avoid this issue. The problem only occurs when pasting the code, not when compiling the same report directly in the report builder.

[Salamine]

Maybe is because in 1920x1080 resolution I have enough room. But attached is both the report and the results.

-Sal

[wcb]

and one more bit of information...

I was looking for some other information about the report builder and came across this post:

http://www.lansweeper.com/forum/yaf_postst5381_Report-Builder-corrupt-SQL-query.aspx

Someone else had this wordwrap problem last year. It says they were going to address the issue with an update so maybe it works better than it used to but still has some of the issue?

[wcb]

UPDATE: I did not get results when I viewed the result from the report builder in the config program but when I looked at the report from the website, I do get results. I thought that I had not been able to get the layout to stay but it eveidnetly did save OK.

If you get the code window to keep the strings inside the quotes on one line when you click save, then it seems your report will save OK for the website. Otherwise it saves with incorrect spacing in the string value.

I am attaching screen shots to try to show what I am talking about.

[wcb]

I setup my system to track theses registry entries so I now have data in my system to test with. I took the code that I initially posted and pasted it into a SQL Management Studio query window. The query runs and gives results. I then pasted it into a new report in Lansweeper configuration report builder. I went to the result window and got no results. I ran a SQL Profiler trace and found the problem.

The lansweeper report builder auto formats the SQL code and it inserts a line break when the line is too long. It is inserting a line break in between the words Auto and Update in the first two conditions of the where clause. Interestingly, it leaves the last line alone and does not insert a line break.

Now comes a more interesting part. I changed the default size of the window by dragging the divider between the report definition and the list of reports to the left. This gives more room for the report definition. I added a carriage return or two manually to get the Regkey strings on lines by themselves. Now there is no return inserted between Auto and Update and I get results.

Sadly, I could not get the layout to stick and could not save the report without the errant line break. Hopefully this will allow Lansweeper to update the auto format of the code window and eventually this will work.

[FrankW]

hi,
i'm very interested in this report too, so i tried it on our server. but i'm getting the same error as described: the report fields are empty.
does anyone have a solution for the "empty report" yet?

thx & cheers,
frank

[Salamine]

Check the formatting in the code. There seems to be extra carriage returns and\or spaces in what Lansweeper posted. While is technically the same, until I removed all the extra formatting and let the report editor fix it itself I only got results in the final column as well.

I had to take it out and put it into a text editor and make all the "(Select...) Left Join" lines run on 1 line each and then paste it into the report builder.

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

-Sal

[TNTreasury]

Thank you. I've tried several things and not having any luck.
I'm going to get with a SQL person either Monday or tuesday to see what i'm doing wrong.

i've copied / pasted and tried aligning.

thanks so much

[TNTreasury]

update ... still haven't got this to work.
The format is exactly what we need but the fields are blank.

I'm still searching for a SQL person to check out the copy / paste issue

[TNTreasury]

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 ?