locked
Where is the "Last Modified Date"? RRS feed

  • Question

  • I'm in Report Builder trying to create an Incident report that includes the "Last Modified Date" value.  However, I can't seem to find it in any of the views using the Report Builder wizard. 

    Can someone point me to the right source for that value?

    Thanks,
    Chuck

     


    Chuck Roy, Pennsylvania Turnpike Commission
    Thursday, February 17, 2011 9:03 PM

Answers

  • We don't sync the full history log into the DW, but to get the LastModifiedDate you can get it by walking up the class hierarchy chain. All instances of classes which derive from the System.Entity class are instances of System.Entity also. Therefore, any dimensions based on these classes can be joined to EntityDim to get the LastModifiedDate like below:

    Select Top 10 
    	incident.Id
    	, incident.Title
    	, entity.LastModified
    From	IncidentDimvw incident 
    Join	EntityDimvw entity on incident.EntityDimKey = Entity.EntityDimKey 
    
    

    Does that make sense?

    Tuesday, March 1, 2011 7:04 AM

All replies

  • I'm still stumped about this field.  The IncidentDimvw view and IncidentDim table contain the LastModifiedSource values but not the "Last Modified Date" or "Last Modified By" data.

    The date is available for use with Views and Notification Templates so it must be somewhere in the data, I just can't seem to figure out how to get it.

    We are trying to produce a report for IT Management that we had set up in our previous system.  The report lists tickets over 10 days old and we send it out every Friday.  Management wants to know how long it's been since anyone did anything with these tickets to be sure that none of them are slipping through the cracks. 

    Any help or suggestions will be appreciated.

    Chuck


    Chuck Roy, Pennsylvania Turnpike Commission
    Wednesday, February 23, 2011 4:35 PM
  • Would it be fair to say that if an analyst added a comment or updated the action log or logged time against it you would want to use those dates as well as if a property of the incident changed as well?

    Sunday, February 27, 2011 7:25 PM
  • Chris,

    For reports, I would want to use the same data that SCSM uses in Views and Templates.  When creating a View for Incidents you can select based on the Last Modified Date.  When you create an email Template for Incidents, you can select the Last Modified date to be inserted into the template.

    In order for our report to display the exact same Incidents as a View that selects based on Last Modified Date, the report needs to use the Microsoft definition of Last Modified Date.

    Generally, I agree with your definition.   However, an easier definition may be that Last Modified Date equals the most recent History log date on the History tab. 

    Either way, as I mentioned above, it needs to be the same as is used in Views and Templates, recognizing that there are timing differences between the data base and data warehouse.

    Chuck


    Chuck Roy, Pennsylvania Turnpike Commission
    Monday, February 28, 2011 3:54 PM
  • We don't sync the full history log into the DW, but to get the LastModifiedDate you can get it by walking up the class hierarchy chain. All instances of classes which derive from the System.Entity class are instances of System.Entity also. Therefore, any dimensions based on these classes can be joined to EntityDim to get the LastModifiedDate like below:

    Select Top 10 
    	incident.Id
    	, incident.Title
    	, entity.LastModified
    From	IncidentDimvw incident 
    Join	EntityDimvw entity on incident.EntityDimKey = Entity.EntityDimKey 
    
    

    Does that make sense?

    Tuesday, March 1, 2011 7:04 AM
  • What about the last modified date field if we are using OLAP Cubes for reports?
    Wednesday, April 2, 2014 7:06 PM
  • Still searching for any answers on getting this into OLAP Cube reports. We don't have the luxury of having anyone on staff that can write SQL reports with any level of competency, and while the OLAP Cubes in SCSM work decent, they are missing quite a few things that would be helpful to report on, such as last modified date :(

    Is there anyway to take the query above and use that to create a new dimension that could be added to the workitemscube or the servicecatalogcube?

    Thursday, April 3, 2014 4:22 PM
  • Still searching for any answers on getting this into OLAP Cube reports. We don't have the luxury of having anyone on staff that can write SQL reports with any level of competency, and while the OLAP Cubes in SCSM work decent, they are missing quite a few things that would be helpful to report on, such as last modified date :(

    Is there anyway to take the query above and use that to create a new dimension that could be added to the workitemscube or the servicecatalogcube?


    I'm in the same boat. Anyone have any suggestions?
    Friday, August 5, 2016 2:42 PM
  • From what I can tell the solution above gives you the same date provided from the views. If you check the live database this follows the same schema, there is no last modified field in the MTV_System$WorkItem$Incident table.
    Friday, June 22, 2018 3:26 PM