Insight Discovery MS SQL Pattern fails for large Database

Issue Summary

Insight Discovery MS SQL Pattern fails for large Database

This is reproducible on Data Center: yes

Steps to Reproduce

The below command in the MS SQL Pattern file will fail to execute in large databases and result in an “overflow” error because the SQL statement is tried to be written into an INT instead of a BIGINT in case of large databases.

 sqlcmd -E -Q "SELECT DB.name, SUM(CASE WHEN type = 0 THEN MF.size * 8 / 1024 ELSE 0 END) AS DataFileSizeMB, SUM(CASE WHEN type = 1 THEN MF.size * 8 / 1024 ELSE 0 END) AS LogFileSizeMB, @@servicename as InstanceName FROM sys.master_files MF JOIN sys.databases DB ON DB.database_id = MF.database_id GROUP BY DB.name"

Expected Results

The command in the pattern file executes without any issues and returns the Scan results.

Actual Results

Execution of the pattern files doesn't provide the expected results.

Workaround

The Column "size" needs to be CAST to data type bigint in the pattern file.

• Current SQL Code: MF.size * 8 / 1024
• Fixed SQL Code: cast(MF.size as bigint) * 8 / 1024