How to get a readable DateTime from the report

Nov 17, 2011 at 8:17 PM

I would like to send a report to the Primary DBA with the failures from the EPM run, but the DateTime fields are in a useless number format. How can I change this to a humanly readable date format?

Here is an example from the XML. The datetime fields are in BOLD

 

<TypeClass>DateTime</TypeClass>
<?char 13?>
<Name>LastBackupDate</Name>
<?char 13?>
<ResultObjType>System.DateTime</ResultObjType>
<?char 13?>
<ResultValue>634075356020000000</ResultValue>
<?char 13?>
</Attribute>
<?char 13?>
<Function>
<?char 13?>
<TypeClass>DateTime</TypeClass>
<?char 13?>
<FunctionType>DateAdd</FunctionType>
<?char 13?>
<ReturnType>DateTime</ReturnType>
<?char 13?>
<ResultObjType>System.DateTime</ResultObjType>
<?char 13?>
<ResultValue>-8589302516236000408</ResultValue>

Coordinator
Dec 3, 2011 at 1:38 AM

What information does the DBA need? If you've setup EPMFramework, why not just point them to the EPMF reports on the Reporting Services instance? That dashboard view should have everything they want. Added bonus is you can setup Reporting Services subscriptions and they can get the reports mailed to them on a schedule.

Mar 29, 2012 at 9:12 PM

Thank you for the response. I have over 600 instances and the reports do not seem to run off a EPMF with so many instances in it. These are just my production instances. We have a team of primary DBAs that each manage a certain group of instances. We have a separate repository where each instance is associated with a primary DBA. I have a perl script that sends each DBA their daily report, using the info in PolicyHistoryDetail.

I am not opposed to using the reports. I can make the same connection using the supplied reports, but I think the DB is just to big. It is currently 220GB in size and I purge PolicyHistory every day to keep only 7 or 14 days worth of info. PolicyHistory is at 90GB and PolicyHistoryDetail at 120GB. I can purge PolicyHistory even more, but I do not believe the reports use it at all.

I have to admit, I have not looked at tuning the SQL at all, so if you have a few 'off-the-top-of-your-head' ideas, let me know.

When I leave the reports to see if they will complete, I will eventually run out of space on either log space or TempDB (not sure which one now).

Any advice will be appreciated. Is it really necessary for all the info in PolicyHistoryDetail? Why do I care about a failure from a year ago?

Thanks in advance.

Mar 30, 2012 at 8:55 AM

@dawidjordaan unfortunately there is nothing you can do about the datetime data. Its frustrating that something so simple could be so botched and obscured.

As for your space problem, the main cause for this will be the XML data in the PolicyHistory and PolicyHistoryDetail tables. I'm struggling for space myself and have come to the conclusion that I'll need to pull apart the XML and store only the relevant info in another table. 

It'll mean a bunch of changes to the reports, etc... but I can't see any other means to provide any useful historical reporting. Would be nice if the EPMFramework provided this as an option. Haven't had the time/resources to do much myself, but would be interested in hearing from you if you do work something out.

 

Cheers

Phil

Mar 30, 2012 at 5:55 PM

Thanks Phil. This actually makes me feel a lot better. I thought I was missing something :-D I think I will start a project here at the office to pull that XML apart and store it differently. I will definitely keep you (and the rest of the community) updated.

Cheers,

Dawid