Here is the xml code that won't work... This is a 30 day report. The problem lies in getting the current date. I have edited the xml files to death to try to get it to work and even development can't get it to work. Anyone?
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:
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.
My apologies then. Hopefully, the attached files will help.
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.
Lawrence,
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
