Search query returns wrong results

Stephane_BouillonStephane_Bouillon Customer IT Monkey ✭
Hi, I have a search query "Work Done Today" that specifies as one of the filters Where Closed Date >= today, and it returns older incidents. What am I doing wrong ? I want to see the tickets that are resolved and/or closed today.



Stephane

Answers

  • Stephane_BouillonStephane_Bouillon Customer IT Monkey ✭
    As an example of one of the items returned by the above query, the history clearly shows the date of yesterday.


  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    Are you on a current version of the portal? Some older versions had issues with relative date saved searches.

    Have you tried with just the date filter removing the status filters? If the request has a resolved date naturally it will be in a resolved status. Reactivating a request clears the resolved date.

    Is the person doing the search in the same time zone as the portal server/time zone settings on workstation match expected settings?
    If you ran just a query for incidents with create date = Today does it return yesterdays or today's tickets. If it returned yesterdays tickets then you know it is an issue with what the system sees as Today's value. 
    HTH
  • Stephane_BouillonStephane_Bouillon Customer IT Monkey ✭
    What does today mean ? Is it 2018-11-09 00:00:00 ? Our portal is version 8.2.0.2016

  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    edited November 9
    When I performed a test search the post command = GetAdHocResults
    In the Header found
    {"isRelative":true,"relativeDateValue":"today","dateValue":"11/9/2018 8:06 AM","dateTimeValue":"2018-11-09T08:06:00Z"}
    So its taking my localization value and converting it to UTC to match the DB.

    Don't remember what versions had the time issues.
  • Stephane_BouillonStephane_Bouillon Customer IT Monkey ✭
    Is there a way to see the query that is sent to the db for a saved search ? I suspect that my query somehow stored "today" as the creation date of the query, which was yesterday. When I created the query, it looked ok, but now it seems that I have the incidents resolved and closed in the last two days... I'll check again tomorrow to be sure.
  • Ryan_LaneRyan_Lane Customer Advanced IT Monkey ✭✭✭
    From what I've been able to gather the discrepancy is most likely due to the search's value for 'Today' being relative to the server's timezone vs. the UTC timezone that Resolved/Completed/Closed is stored in.  Recreating your search parameters in my environment shows that anything within UTC-8 vs. UTC is returned:

    To work around this I created a dashboard SQL Table Widget with a new SQL Query:

    The following SQL Query (attached file as well) generates these results by getting the server's current time and offsetting it by the difference to UTC to hopefully account for timezone differences. Feel free to modify or remove any of the additional columns like ResolvedDate/CompletedDate/ClosedDate:
    DECLARE @StartDate DATETIME = CONVERT(Date, GETDATE(), 101); -- TODAYDECLARE @ServerUTCOffset INT = DATEDIFF(HOUR, GETDATE(), GETUTCDATE());
    SET @StartDate = DATEADD(HOUR, @ServerUTCOffset, @StartDate); -- Offset For UTC
    SELECT WorkItem.WorkItemId AS [ID] ,WorkItem.Title ,StatusDS.DisplayString AS [Status] ,WorkItem.AssignedUser ,CASE WHEN TRY_CAST(WorkItem.PriorityId AS UNIQUEIDENTIFIER) IS NULL  THEN WorkItem.PriorityId ELSE PriorityDS.DisplayString END AS [Priority] ,CategoryDS.DisplayString AS [Category] ,WorkItem.AffectedUser ,TierDS.DisplayString AS [Support Group] ,WorkItem.LastModified ,WorkItem.ResolvedDate  ,WorkItem.CompletedDate ,WorkItem.ClosedDateFROM WorkItemLEFT JOIN DisplayString AS StatusDS ON WorkItem.StatusId = StatusDS.ElementIDLEFT JOIN DisplayString AS TierDS ON WorkItem.TierId = TierDS.ElementIDLEFT JOIN DisplayString AS CategoryDS ON WorkItem.CategoryId = CategoryDS.ElementIDLEFT JOIN DisplayString AS PriorityDS ON TRY_CAST(WorkItem.PriorityId AS UNIQUEIDENTIFIER) = PriorityDS.ElementIDWHERE ( WorkItem.ResolvedDate >= @StartDate OR WorkItem.CompletedDate >= @StartDate OR WorkItem.ClosedDate >= @StartDate ) AND WorkItem.ClassId IN ( 'A604B942-4C7B-2FB2-28DC-61DC6F465C68' -- System.WorkItem.Incident ,'04B69835-6343-4DE2-4B19-6BE08C612989' -- System.WorkItem.ServiceRequest ) AND WorkItem.StatusId IN ( '2B8830B6-59F0-F574-9C2A-F4B4682F1681' -- IncidentStatusEnum.Resolved ,'BD0AE7C4-3315-2EB3-7933-82DFC482DBAF' -- IncidentStatusEnum.Closed ,'C7B65747-F99E-C108-1E17-3C1062138FC4' -- ServiceRequestStatusEnum.Closed ,'B026FDFD-89BD-490B-E1FD-A599C78D440F' -- ServiceRequestStatusEnum.Completed )
  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    LOL didn't even notice that the post I did my want not converted from EST to UTC. You can see the two values are the same. Sounds like a bug needs to be submitted to Cireson.
  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    I Installed the latest release v8.9.5.2016 and it still returns the same offset. Opening a ticket with Cireson.
  • Stephane_BouillonStephane_Bouillon Customer IT Monkey ✭
    Thanks for confirming I'm not crazy Brian :)
Sign In or Register to comment.