Forum Discussion

Catherine_Tee's avatar
Catherine_Tee
New Contributor III
4 months ago

User Overview Dashboard different time zone with System Security Logon time

There is different time zone between System - Security Logon time show 3.32pm (UTC+8) and in User Overview Dashboard show 7.32am (UTC). Possible synchronize showing same time zone which follow System - Security Logon time show 3.32pm into dashboard. Please advise how to update in dashboard.

The rule is 

Select Name, Description, Email,LogonTime

--CONVERT(nvarchar, LogonTime, 110) as 'Logon Day'

--DATEDIFF(dd,LogonTime,GetUTCDATE()) AS 'Days since last Log on'

from secuser

 

 

Dashboards
Reports
  • sameburn's avatar
    sameburn
    Contributor II
    4 months ago

    Hi Catherine_Tee , you would need to move your logic to a Dashboard DataSet, but you could do something like this example.  Here we retrieve the columns you want with a modified SQL query e.g. LogonTime does not exist in SecUser table so you need a Join and also Null handling on the LogonTime column.  Then you can get the Local LogonTime using the TimeZoneInfo object and then convert the UTC time (LogonTime) to Local Time (LocalLogonTime)

    Hope this helps

    				' Declare stringbuilder
				Dim sb As New Text.StringBuilder				
				
				' Declare SQL (you need to create a Join with UserLogonActivity to retrieve LogonTime) and add placeholder column LocalLogonTime
				Dim sql As String = "SELECT s.[Name],s.[Description],s.[Email],ISNULL(u.[LogonTime], '1900/01/01 00:00:00') AS [UtcLogonTime],'Unknown' AS [LocalLogonTime]
									 FROM [SecUser] s
									 LEFT JOIN [UserLogonActivity] u
									 ON s.Name = u.[UserName]
									 ORDER BY s.[Name]"
				
				' Declare placeholder
				Dim dt As New DataTable()
				
				'Execute Query on FrameWork DB
				Using dbConnFW As DBConnInfo = BRApi.Database.CreateFrameworkDbConnInfo(si)
					' Populate DataTable
				    dt = BRAPi.Database.ExecuteSql(dbConnFW, sql.ToString, True)
				End Using	
				
				' prepare placeholder column
				dt.Columns("LocalLogonTime").ReadOnly = False
				dt.Columns("LocalLogonTime").MaxLength = -1		
				
		        ' Get the local time zone of the user
		        Dim localTimeZone As TimeZoneInfo = TimeZoneInfo.Local
								
				' Loop DataTable Rows
	            For Each dr As DataRow In dt.Rows()
	                'Process rows
					dr.BeginEdit
						' Convert UTC DateTime to Local DateTime
	                	dr("LocalLogonTime") = TimeZoneInfo.ConvertTimeFromUtc(dr("UtcLogonTime"), localTimeZone)
					dr.EndEdit
	            Next	
					
				' Loop DataTable Rows ➡ Logging
				For Each row As DataRow In dt.Rows()
					For Each col As DataColumn In dt.Columns()
						sb.AppendLine(String.Format("Col ➡ {0}, Row ➡ {1}", col.ColumnName, row(col)))
					Next col
				Next row	
		

				' Log Result ➡ Throw Error
				Throw New XFException(Convert.ToString(sb))