Sua import error

Usage and Config BFI
21

@michalpaluch @susvar @wkariuki

Facing the same issue in one of our customer environment. What was the fix?
Can someone please share the sql query for datediff function?

Thanks in Advance

22

Run SQL

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION dbo.date_diff_seconds
(
@date1 datetime,
@date2 datetime
)
RETURNS bigint
AS
BEGIN
return (convert(bigint, datediff(day, @date1, @date2)) * 24 *
60 * 60)
- (datediff(second, dateadd(day, datediff(day, 0,
@date1), 0), @date1))
+ (datediff(second, dateadd(day, datediff(day, 0,
@date2), 0), @date2))

END
GO

alter table sam.app_usage_property_values_scd drop column
avg_runs_per_day
go

alter table sam.app_usage_property_values_scd add
avg_runs_per_day as
( CASE
WHEN (total_runs IS NULL) THEN 0
WHEN (first_used IS NULL) OR (last_used IS NULL) THEN 0
WHEN (dbo.date_diff_seconds(first_used, last_used) <= 0)
THEN 0
WHEN (dbo.date_diff_seconds(first_used, last_used)/86400
< 1) THEN total_runs
ELSE total_runs / dbo.date_diff_seconds(first_used,
last_used) / CAST(86400.0 as FLOAT)
END
)
go

alter table sam.software_facts_scd drop column avg_runs_per_day
go

alter table sam.software_facts_scd add avg_runs_per_day as
( CASE
WHEN (total_runs IS NULL) THEN 0
WHEN (first_used IS NULL) OR (last_used IS NULL) THEN 0
WHEN (dbo.date_diff_seconds(first_used, last_used) <= 0)
THEN 0
WHEN (dbo.date_diff_seconds(first_used, last_used)/86400
< 1) THEN total_runs
ELSE total_runs / dbo.date_diff_seconds(first_used,
last_used) / CAST(86400.0 as FLOAT)
END
)
go

alter table sam.computer_group_catalog_hierarchy_rollups drop
column avg_runs_per_day
go

alter table sam.computer_group_catalog_hierarchy_rollups add
avg_runs_per_day as
( CASE
WHEN (total_runs IS NULL) THEN 0
WHEN (first_used IS NULL) OR (last_used IS NULL) THEN 0
WHEN (dbo.date_diff_seconds(first_used, last_used) <= 0)
THEN 0
WHEN (dbo.date_diff_seconds(first_used, last_used)/86400
< 1) THEN total_runs
ELSE total_runs / dbo.date_diff_seconds(first_used,
last_used) / CAST(86400.0 as FLOAT)
END
)
go

Once you have this sql then do the following:

  1. Stop the server
  2. Backup the DB.
  3. In a Windows command window: sqlcmd -U -P
    -d
    <database_name> -i fix_datediff_overflow.sql
  4. Restart the server.

The sql script replaces the calculated columns that use the
datediff
function with calls to a new function that does not have the
integer
restriction
the system(s) with incorrect date ranges is probably not their
SUA/database server, but rather an endpoint that is scanned for
SUA

1 Like
23

@michalpaluch

Thank you, It Worked!

1 Like