Search query returns wrong results

Stephane_Bouillon

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

Stephane_Bouillon

As an example of one of the items returned by the above query, the history clearly shows the date of yesterday.

Brian_Wiest

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_Bouillon

What does today mean ? Is it 2018-11-09 00:00:00 ? Our portal is version 8.2.0.2016

Brian_Wiest

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_Bouillon

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_Lane

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:

<div>DECLARE @StartDate DATETIME = CONVERT(Date, GETDATE(), 101); -- TODAY</div><div>DECLARE @ServerUTCOffset INT = DATEDIFF(HOUR, GETDATE(), GETUTCDATE());</div><div><br></div><div>SET @StartDate = DATEADD(HOUR, @ServerUTCOffset, @StartDate); -- Offset For UTC</div><div><br></div><div>SELECT</div><div>	 WorkItem.WorkItemId AS [ID]</div><div>	,WorkItem.Title</div><div>	,StatusDS.DisplayString AS [Status]</div><div>	,WorkItem.AssignedUser</div><div>	,CASE WHEN TRY_CAST(WorkItem.PriorityId AS UNIQUEIDENTIFIER) IS NULL&nbsp;</div><div>		THEN WorkItem.PriorityId</div><div>		ELSE PriorityDS.DisplayString</div><div>		END AS [Priority]</div><div>	,CategoryDS.DisplayString AS [Category]</div><div>	,WorkItem.AffectedUser</div><div>	,TierDS.DisplayString AS [Support Group]</div><div>	,WorkItem.LastModified</div><div>	,WorkItem.ResolvedDate&nbsp;</div><div>	,WorkItem.CompletedDate</div><div>	,WorkItem.ClosedDate</div><div>FROM</div><div>	WorkItem</div><div>LEFT JOIN DisplayString AS StatusDS ON WorkItem.StatusId = StatusDS.ElementID</div><div>LEFT JOIN DisplayString AS TierDS ON WorkItem.TierId = TierDS.ElementID</div><div>LEFT JOIN DisplayString AS CategoryDS ON WorkItem.CategoryId = CategoryDS.ElementID</div><div>LEFT JOIN DisplayString AS PriorityDS ON TRY_CAST(WorkItem.PriorityId AS UNIQUEIDENTIFIER) = PriorityDS.ElementID</div><div>WHERE</div><div>	(</div><div>		WorkItem.ResolvedDate >= @StartDate OR</div><div>		WorkItem.CompletedDate >= @StartDate OR</div><div>		WorkItem.ClosedDate >= @StartDate</div><div>	) AND</div><div>	WorkItem.ClassId IN (</div><div>		 'A604B942-4C7B-2FB2-28DC-61DC6F465C68' -- System.WorkItem.Incident</div><div>		,'04B69835-6343-4DE2-4B19-6BE08C612989' -- System.WorkItem.ServiceRequest</div><div>	) AND</div><div>	WorkItem.StatusId IN (</div><div>		 '2B8830B6-59F0-F574-9C2A-F4B4682F1681' -- IncidentStatusEnum.Resolved</div><div>		,'BD0AE7C4-3315-2EB3-7933-82DFC482DBAF' -- IncidentStatusEnum.Closed</div><div>		,'C7B65747-F99E-C108-1E17-3C1062138FC4' -- ServiceRequestStatusEnum.Closed</div><div>		,'B026FDFD-89BD-490B-E1FD-A599C78D440F' -- ServiceRequestStatusEnum.Completed</div><div>	)</div>

Brian_Wiest

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_Wiest

I Installed the latest release v8.9.5.2016 and it still returns the same offset. Opening a ticket with Cireson.

Stephane_Bouillon

Thanks for confirming I'm not crazy Brian