The first thing we need to determine is what your criteria for the "last 7 days" and "last 60 days" are. Are you interested in:
- Release Date (by vendor) (All updates or just Approved Updates?)
- Arrival Date (on WSUS) (All updates or just Approved Updates?)
- Approval Date
- Installation Date (and with respect to this value, any updates installed, any updates installed via WSUS, or only the updates installed expressly via a Patch Manager task?)
Second, posting the "XML code" is of no real help to us. If you have a Report Definition, please EXPORT the Report Definition, which will create an XDQ file, and then attach the XDQ file to a reply.
We can then import the XDQ file into our test systems and work from there.
Thank you for the XDQ. I loaded it and ran it on my system and it works perfectly.
So let's talk about exactly what you mean by "doesn't work".
One note. There is no way to get a report based on the "last 30 days", because that filter is not provided. You can run a report based on updates released This Month, or The Past Two Months, or you can run a report for Release Date Greater Than and specify an exact Release Date to start the range (e.g. 11/3/2014) to get the last 30 days.
But, since you posted the "Last 7 Days" report, which works perfectly, let's start there.
Also, I'm a bit confused as to the significance of the "Workaround.docx", which seems to be related to extracting Task History events from the Patch Manager database.
So...... Yes. The report works perfectly until you schedule it. Then after the report runs the first time, the xml file hardcodes the date and the results for the "last seven days" will never change because the GETDATE statement is gone ie.
DATEADD(DAY, DATEDIFF(DAY, 7, GETDATE()), 0) and DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) this is what development gave me to try for the last seven days instead of the %LASTSEVENDAYS system variable. However, the result is the same
WHERE dbo.dt_wsus_computers.lastsynctime between '20141118' and '20141125 23:59:59.997' This is what the xml file looks like after the first scheduled run completes ( I got my report folders mixed up and this was wrong, fixed now)
The significance of the workaround document that Development wrote is that Pavlo?? in Development was trying to solve the hardcoding issue that the system is doing. I don't know why he thought using a report from Task History would help.
I realize that last xx days is not a filter option but if the code can be made to work for the last seven days, I can make it work for any number of days/months.
My issue is that the report will not work out of the box so we need to get that working first.
I just spoke with Phillip the Tech Support Manager and he assures me you are the man for the job.
Thank you for your help.
Then after the report runs the first time, the xml file hardcodes the date and the results for the "last seven days" will never change because the GETDATE statement is gone
Ahhh.. yes... I do vaguely recall encountering this defect a few years ago.
The only real way to make this work is to extract the SQL code from the Patch Manager console, and run it in SQL Server Reporting Services, or as a scheduled stored procedure in conjunction with Integration Services to get the export. These methodologies will allow you to retain the GETDATE() call in the WHERE filter.
I am going to guess that there is no plan to fix it anytime soon and there is no workaround other than what you have mentioned. I am no programmer but it would seem that there would be a way to prevent the xml file from being changed while the report runs. No? OK. Let me investigate my options. Thank you
Unfortunately, that is the design of the product. The report definitions are stored as XML and the SQL is dynamically generated at runtime from the XML.
But when a Scheduled Task gets created, the SQL gets hard-coded into the scheduled task.
Your other option would be to rebuild the task as a OnceOnly task every week/month, in which case that instance of the task would have the correct dates.
I cannot really say that there is, or is not, a plan to fix it. This is, from my memory, only the second time the limitation has been encountered in production, and the first was a few years ago.
I will certainly send a note to the Product Manager about this scenario
I understand what you are saying about it being designed that way but if a user can build a report for the last seven days and schedule it, it seems to me it should work. OK. Thanks again for your help.