Data Warehouse Calculation fails

any details from the logs? check out the armServer.log for errors at the time of the execution (should be visible in the job overview)

Hello,

what version of ARM are you using?

We had the same problem with version 2020.2 and also 2020.2.1 and opened a support ticket.

With a development version for testing, the error was gone, so i think it will be fixed with the next upcoming version.

If you use the latest release, yes. It's a faulty t-sql query.

[ 17478|armserveserver |armServer |7904.1|195|domain\account |pn.database.msSql.M…lDataBaseTransaction|200709|20:01:50.834|Error ] DataBaseHelper.HandleDataBaseSqlException(Object source, SqlException exception, String sqlCommand): System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting date and/or time from character string.
--BEGIN-OF-EXCEPTION------------------------------------------------------------
System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting date and/or time from character string.
at pn.database.msSql.MsGenericDataBaseTransaction`2.ExecuteSqlCommandWithResults(String sqlCommand, SqlParameter[] parameterList)
ClientConnectionId:348ff6a7-a562-46a5-8114-f851f2503557
Error Number:241,State:1,Class:16
Additional infos that might be helpful on research: ErrorNumber: 241, ErrorClass: 16, ErrorState: 1, ErrorCode: -2146232060
--END-OF-EXCEPTION--------------------------------------------------------------
[ 17479|armserveserver |armServer |7904.1|195|domain\account |pn.database.msSql.M…lDataBaseTransaction|200709|20:01:50.834|Error ] DataBaseHelper.HandleDataBaseSqlException(Object source, SqlException exception, String sqlCommand): ;WITH details AS(
SELECT XXXX as _scan_id, e._entry_id AS _resource_id,
COALESCE(e._display_name, e._name, e._account_name) AS _resource_name,
e._path AS _resource_path,
_sid AS _account_id,
_guid AS _account_guid,
CONVERT(datetime, CASE WHEN PATINDEX('__/__/160_ __:__:__', ep._value) != 0 THEN '' ELSE ISNULL(ep._value_big, ep._value) END, 101) AS _timestamp,
DATEDIFF(DAY,CONVERT(datetime, CASE WHEN PATINDEX('__/__/160_ __:__:__', ep._value) != 0 THEN '' ELSE ISNULL(ep._value_big, ep._value) END, 101),GETUTCDATE()) AS _daysAgo,
CASE WHEN e._user_account_control & 2 != 2 THEN 1 ELSE 0 END AS _isEnabled
FROM ad._active_directory_entries_XXXX e
INNER JOIN ad._active_directory_entry_property_mapping_XXXX m ON m.[_entry_id] = e.[_entry_id]
INNER JOIN ad._active_directory_entry_properties_XXXX ep ON m.[_property_id] = ep.[_property_id]
WHERE e._object_class = 6 AND ep._name_id = (SELECT [_property_name_id] FROM ad._active_directory_property_names_XXXX WHERE _property_name = 'lastlogontimestamp'))
INSERT INTO [dw].[details_533080ab41ec4e9399eab0a7aa546c4f] ([_scan_id],[_resource_id],[_resource_name],[_resource_path],[_provider],[_sid], [_guid], [_lastLogon],[_daysAgo],[_is_enabled])
SELECT _scan_id, _resource_id,_resource_name,_resource_path,'domain.de', _account_id, _account_guid, _timestamp,_daysAgo, _isEnabled
FROM details
WHERE _daysAgo>=31;
SELECT COUNT(*) AS _count FROM [dw].[details_533080ab41ec4e9399eab0a7aa546c4f];

It started to fail after the update to Version 2020.2.1 

The armserver.log is really big (how big should it normally be?) and I see only confusing messages but I'll try to find something ...

I opened a support case, too, like cnfloege  hope they will come back to me

thx for your help!

Same here.

It's a bug in the current 2020.2 version. The DevVersion they sent out to me has the fix.