→ 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: 
AZHockeyNut
Champion Sweeper III
I grabbed a WSUS report that was posted here, nice report. To turn it to a chart I made a new report that needs to query that existing report. the control seems to save them as DB views, web50rep..... some ID value I am guessing.
when I try to query them, the query tool does not like it at all however, SQK server has ZERO issues with it, so it is NOT a sql syntax thing, it is in the control or its settings. Is there a way to configure the control (like an xml file) I can update or a setting in the web.config?


Select Case
    When [lansweeperdb].[dbo].[web50rep0208b76934134852ae25ab3b26a4fbd7].lastPatchDate >= DateAdd(day, -7,
    GetDate()) Then '1. less than a week'
    When [lansweeperdb].[dbo].[web50rep0208b76934134852ae25ab3b26a4fbd7].lastPatchDate < DateAdd(day, -7,
    GetDate()) And [lansweeperdb].[dbo].[web50rep0208b76934134852ae25ab3b26a4fbd7].lastPatchDate >= DateAdd(day,
    -30, GetDate()) Then '2. less than a month'
    When [lansweeperdb].[dbo].[web50rep0208b76934134852ae25ab3b26a4fbd7].lastPatchDate < DateAdd(day, -31,
    GetDate()) And [lansweeperdb].[dbo].[web50rep0208b76934134852ae25ab3b26a4fbd7].lastPatchDate >= DateAdd(day,
    -90, GetDate()) Then '3. less than 3 months' Else '4. more than 3 months'
  End As DateRange,
  count(*) As NumberOfAssets
From [lansweeperdb].[dbo].[web50rep0208b76934134852ae25ab3b26a4fbd7]
Where [lansweeperdb].[dbo].[web50rep0208b76934134852ae25ab3b26a4fbd7].OSname In ('Win 7', 'Win 8', 'Win 8.1',
  'Win 10')
Group By Case
    When [lansweeperdb].[dbo].[web50rep0208b76934134852ae25ab3b26a4fbd7].lastPatchDate >= DateAdd(day, -7,
    GetDate()) Then '1. less than a week'
    When [lansweeperdb].[dbo].[web50rep0208b76934134852ae25ab3b26a4fbd7].lastPatchDate < DateAdd(day, -7,
    GetDate()) And [lansweeperdb].[dbo].[web50rep0208b76934134852ae25ab3b26a4fbd7].lastPatchDate >= DateAdd(day,
    -30, GetDate()) Then '2. less than a month'
    When [lansweeperdb].[dbo].[web50rep0208b76934134852ae25ab3b26a4fbd7].lastPatchDate < DateAdd(day, -31,
    GetDate()) And [lansweeperdb].[dbo].[web50rep0208b76934134852ae25ab3b26a4fbd7].lastPatchDate >= DateAdd(day,
    -90, GetDate()) Then '3. less than 3 months' Else '4. more than 3 months'
  End
2 REPLIES 2
AZHockeyNut
Champion Sweeper III
That is what I am doing so I must be messing something up. I took screen shots. the first shows that the query is not accepted. the second shows the report I am calling and you should see the rep50* part is the same
mpeter
Engaged Sweeper
This is what mine looks like

Select Top 1000000 Case
When web50rep694c570b7fc546f39e1f6fab6b019a5f.lastPatchDate >= DateAdd(day,
-7, GetDate()) Then '1. less than a week'
When web50rep694c570b7fc546f39e1f6fab6b019a5f.lastPatchDate < DateAdd(day,
-7, GetDate()) And web50rep694c570b7fc546f39e1f6fab6b019a5f.lastPatchDate >=
DateAdd(day, -30, GetDate()) Then '2. less than a month'
When web50rep694c570b7fc546f39e1f6fab6b019a5f.lastPatchDate < DateAdd(day,
-31, GetDate()) And web50rep694c570b7fc546f39e1f6fab6b019a5f.lastPatchDate
>= DateAdd(day, -90, GetDate()) Then '3. less than 3 months'
Else '4. more than 3 months' End As DateRange,
count(*) As NumberOfAssets
From web50rep694c570b7fc546f39e1f6fab6b019a5f
Where web50rep694c570b7fc546f39e1f6fab6b019a5f.OSname In ('Win 7', 'Win 8',
'Win 8.1', 'Win 10')
Group By Case
When web50rep694c570b7fc546f39e1f6fab6b019a5f.lastPatchDate >= DateAdd(day,
-7, GetDate()) Then '1. less than a week'
When web50rep694c570b7fc546f39e1f6fab6b019a5f.lastPatchDate < DateAdd(day,
-7, GetDate()) And web50rep694c570b7fc546f39e1f6fab6b019a5f.lastPatchDate >=
DateAdd(day, -30, GetDate()) Then '2. less than a month'
When web50rep694c570b7fc546f39e1f6fab6b019a5f.lastPatchDate < DateAdd(day,
-31, GetDate()) And web50rep694c570b7fc546f39e1f6fab6b019a5f.lastPatchDate
>= DateAdd(day, -90, GetDate()) Then '3. less than 3 months'
Else '4. more than 3 months' End


I just get the report view value (web50*whatever) from the URL field in the web browser when you go to edit the previous report you are trying to query