ServiceDesk

 View Only
Expand all | Collapse all

Last Posted Date in Reporting for Service Desk

  • 1.  Last Posted Date in Reporting for Service Desk

    Posted Mar 20, 2012 04:47 PM

    Hello everyone,

        Using SD 7.1.2...   we are trying to get off Altiris 6 Help Desk solution and one of the MAJOR fields we did a lot off of was Last Modified.   Well...  They want it in 7 and only thing we can find is last posted.   If I bring that up in a report for incidents management/tasks, we are getting every comment for that task.   So we getting more than wanted.   We just want to most recent.   We are kind of hoping this can stay in the native GUI because managers would need to use this field in similar reports.   I am hoping I do not need to add another field to the IncidentManagement stuff just to house this information.   Thoughts?   Ideas?   Good recipes for Long Island Iced Teas?  

     

    Thanks,

    Jamie



  • 2.  RE: Last Posted Date in Reporting for Service Desk

    Posted Mar 20, 2012 07:40 PM
    We wanted this field too but unfortunately you would have to add that field to the datatype, put this field in the process view pages, update this field into the various workflows and it just seemed like too much work to do.


  • 3.  RE: Last Posted Date in Reporting for Service Desk

    Posted Mar 20, 2012 07:40 PM
    We wanted this field too but unfortunately you would have to add that field to the datatype, put this field in the process view pages, update this field into the various workflows and it just seemed like too much work to do.


  • 4.  RE: Last Posted Date in Reporting for Service Desk

    Posted Mar 20, 2012 08:50 PM

    I really would have hoped with Altiris 6 to 7, they would have kept something like that. 

    I already told that idea of adding another field to the db but if they decide to lock down the Incident Management code in the next version, we are back to what do we do.  

    Thank you for your reply!



  • 5.  RE: Last Posted Date in Reporting for Service Desk
    Best Answer

    Posted Mar 23, 2012 01:44 PM

    I'm not sure if this is exactly what you are looking for, but I might have a solution for you.  If you create a database view, and then create a profile that points to that view, you can use it in the report designer.

    For example, if you just want the latest date that a comment was posted, you can use this query to create your view.

    CREATE VIEW dbo.[LastModified]
    AS
    SELECT SessionID, MAX(DatePosted) AS [LastModified]
    FROM reportProcessComment
    GROUP BY SessionID
    GO

     

    Then go to Admin -> Data -> Lists and Profiles and click on the plus icon and select "Add Profile Definition (Existing Table)."  For the Reference Type, choose Workflow Process, choose a name for the profile, and for the table name use the name of the view you created.  Click go and for the Select ID Field, choose SessionID.  Then your  LastModified field will show up, and you can check the box next to it, and hit the Generate button.

    In the report you want to add the LastModified date to, make sure "Add Processes To Report" is selected, and then under Process Management, check Workflow Profile.  For Editor Data, choose the Profile you just created and hit the Add button.  Then enter any criteria you want for the Last Modified.  I just use Is Not Null, so it doesn't do any filtering.  You can check "Editable At Runtime" if you want the end user to be able to change this.  Then click "Ok" and you will see the LastModified columns show up on the right hand side, ready to be added.  Check Last Modified, and the column will be added to the report.

    I have found this method is useful when you want to be able to use the designer and not a SQL report - if you need parameters, or you want to include process actions.  I have seen some limitations, though.  The main problem seems to be that group aggregations don't work correctly on profile data, so you can't use charts with anything other than counts.



  • 6.  RE: Last Posted Date in Reporting for Service Desk

    Posted Apr 30, 2012 04:01 PM

    Sorry for the late reply but I finally was able to get this working today.  

    So now I have one workflow process for last modified and one for resolved.   Thank you!



  • 7.  RE: Last Posted Date in Reporting for Service Desk

    Posted May 24, 2012 03:33 PM

    Hey Josh, I got this Last Modified column implemented and it works GREAT!  One thing I was wondering I see the date only changes when comment is added or ticket is reassigned.  Is there a way for this Last Modified field to change when someone uses the "Post Process Time" feature? 



  • 8.  RE: Last Posted Date in Reporting for Service Desk

    Posted May 30, 2012 08:50 AM

    I ran in to something horrible.   The SQL server is now having an issue with running views... so when using this method..if the view is running stupid, then your report will time out.   I am thinking maybe using a table and having a SQL agent job update the table.



  • 9.  RE: Last Posted Date in Reporting for Service Desk

    Posted May 30, 2012 03:31 PM

    You should be able to use the post process time as well by adding ReportProcessTiming to the view query. This query should work:

     

    SELECT SessionID, MAX(LastModified) AS [LastModified]
    FROM
    ((SELECT SessionID, MAX(DatePosted) AS [LastModified]
      FROM ReportProcessComment
      GROUP BY SessionID) 
     UNION
     (SELECT SessionID, MAX(DatePosted) AS [LastModified]
      FROM ReportProcessTiming
      GROUP BY SessionID)) lastmodified
    GROUP BY SessionID


  • 10.  RE: Last Posted Date in Reporting for Service Desk

    Posted May 30, 2012 04:05 PM

    What is I wanted the comment to go with this view?   I am trying to tighten everything up and trying to see if I can reduce the views relying on other views.



  • 11.  RE: Last Posted Date in Reporting for Service Desk

    Posted May 30, 2012 04:08 PM

    Josh, I will give this a try but one other question for you.  I noticed with the last query I had set the Last Modified to "Is Not Null" but was missing new created tickets that were assigned directly to a group due to there not being any modifications yet to those tickets.  Is there a good field that can be added to the query that will always have a result so it can be set to 'Is Not Null"..like maybe the affected user as there is always an affected use on a ticket?  Thanks!



  • 12.  RE: Last Posted Date in Reporting for Service Desk

    Posted May 30, 2012 04:43 PM

    What version of ServiceDesk are you running?  I am using 7.1 SP2, and at the start of every ticket I get a comment with this :  User Comment: Ruleset [Initial Routing] was executed.

    You should be able to add the status changes to the query, and then there should always be a result.  Just add a union with ReportProcessStatusHistory like I did with the ReportProcessTiming above



  • 13.  RE: Last Posted Date in Reporting for Service Desk

    Posted May 30, 2012 04:54 PM

    This query should work (I just used the original view query, and not the one with additional tables)

     

    SELECT lastmodified.SessionID as [SessionID], LastModified, CommentBrief, Comment
    FROM
     (SELECT SessionID, MAX(DatePosted) AS [LastModified]
      FROM reportProcessComment
      GROUP BY SessionID) lastmodified
       LEFT OUTER JOIN
     ReportProcessComment rpc ON (rpc.SessionID = lastmodified.SessionID)