Custom Attribute Name appearing as Unknown Or Not Appearing At All

Products

• IT Analytics

Issue/Introduction

While reviewing the custom attributes values, while verifying the content of all of the dimensions  for the custom attributes, we ran the query:

Select * from vITAnalytics_DLP_IncidentCustomAttributesFact

This returned results, but the CUSTOMATTRIBUTEINDEXKEY Column was displaying as a scientific notation number, instead of just a number, which was wrong

In a separate circumstance, no results were returned

Environment

ITA For DLP 8.0x

Cause

We reviewed then the SQL function, vITAnalytics_DLP_IncidentCustomAttributesFact - The Select statement generating the data for CUSTOMATTRIBUTEINDEXKEY was problematic, generating the scientific notation values instead of a regular number value.

We isolated the SQL statements to see what the data returns were like.  They returned scientifc notation results for that column. 

In a separate circumstance, the SQL statement was simply calling a SQL function.

Resolution

We need to update the SQL query in the SQL views for CustomAttributesFact to properly convert the data coming from the Oracle DLP database to be displayed as a whole number instead scientific notation.

 

1. Open SQL Management Studio & connect to the SQL Server housing the ITAnalytics database.
2. Find vITAnalytics_DLP_IncidentCustomAttributesFact in the Object view tree of SQL Management Studio under ITAnalytics\Views.
3. Right-click on vITAnalytics_DLP_IncidentCustomAttributesFact and choose Script View As\Alter To\New Query Window
4. Update the SQL query to added the BOLD TEXT in the function to read:

...Select

    (10000000000 * 1)+INCIDENTID as INCIDENTIDKEY

    ,(10000000000 * 1) + CUSTOMATTRIBUTESRECORDID as CUSTOMATTRIBUTESRECORDID

    ,(10000000000 * 1) + convert (nvarchar (255), CUSTOMATTRIBUTEINDEX) as CUSTOMATTRIBUTEINDEXKEY

    --,(10000000001 * 1) as CUSTOMATTRIBUTEINDEXKEY

    ,convert (nvarchar (255), CUSTOMATTRIBUTEVALUE) as CUSTOMATTRIBUTEVALUE 

    FROM

    OPENQUERY....

5. Execute the updated query to update the view.

6. Repeat steps 2-5 for views: vITAnalytics_DLP_EndpointIncidentCustomAttributesFact, vITAnalytics_DLP_NetworkIncidentCustomAttributesFact, & ITAnalytics_DLP_DiscoverIncidentCustomAttributesFact 

Once completed, and reprocessing of the cube had occurred, this resolved the issue for the scientific notation issue.

In cases where the views were calling a function, inserting the SQL statement below the ALTER... line, (changing the OPENQUERY server call to the correct ORACLE host), and saving the new view, correctly populated the views:

(
--INSERT INTO @ret
SELECT 
 (10000000000 * 1) + INCIDENTID as INCIDENTIDKEY
 ,(10000000000 * 1) + CUSTOMATTRIBUTESRECORDID as CUSTOMATTRIBUTEKEY
 ,(10000000000 * 1) + CAST(CUSTOMATTRIBUTEINDEX AS VARCHAR(2)) as CUSTOMATTRIBUTEINDEXKEY
 ,convert(nvarchar(255),CUSTOMATTRIBUTEVALUE) as CUSTOMATTRIBUTEVALUE   
 FROM 
 OPENQUERY ([ITANALYTICS_DLP_ITANALYTICS_10.32.200.120_PROTECT],
'
SELECT i.incidentid, car.CUSTOMATTRIBUTESRECORDID, CUSTOMATTRIBUTEINDEX, CUSTOMATTRIBUTEVALUE
FROM (SELECT CUSTOMATTRIBUTESRECORDID, CUSTOMATTRIBUTEINDEX, substr(CUSTOMATTRIBUTEVALUE, 0, 255) CUSTOMATTRIBUTEVALUE FROM PROTECT.CUSTOMATTRIBUTESRECORD
UNPIVOT (CUSTOMATTRIBUTEVALUE FOR CUSTOMATTRIBUTEINDEX IN (
VALUE1 AS 1, 
VALUE2 AS 2, 
VALUE3 AS 3,
VALUE4 AS 4,
VALUE5 AS 5,
VALUE6 AS 6,
VALUE7 AS 7,
VALUE8 AS 8,
VALUE9 AS 9,
VALUE10 AS 10,
VALUE11 AS 11, 
VALUE12 AS 12, 
VALUE13 AS 13,
VALUE14 AS 14,
VALUE15 AS 15,
VALUE16 AS 16,
VALUE17 AS 17,
VALUE18 AS 18,
VALUE19 AS 19,
VALUE20 AS 20,
VALUE21 AS 21, 
VALUE22 AS 22, 
VALUE23 AS 23,
VALUE24 AS 24,
VALUE25 AS 25,
VALUE26 AS 26,
VALUE27 AS 27,
VALUE28 AS 28,
VALUE29 AS 29,
VALUE30 AS 30,
VALUE31 AS 31, 
VALUE32 AS 32, 
VALUE33 AS 33,
VALUE34 AS 34,
VALUE35 AS 35,
VALUE36 AS 36,
VALUE37 AS 37,
VALUE38 AS 38,
VALUE39 AS 39,
VALUE40 AS 40,
VALUE41 AS 41, 
VALUE42 AS 42, 
VALUE43 AS 43,
VALUE44 AS 44,
VALUE45 AS 45,
VALUE46 AS 46,
VALUE47 AS 47,
VALUE48 AS 48,
VALUE49 AS 49,
VALUE50 AS 50
))) car
INNER JOIN PROTECT.INCIDENT i
ON i.CUSTOMATTRIBUTESRECORDID = car.CUSTOMATTRIBUTESRECORDID
JOIN PROTECT.MESSAGE m ON i.MESSAGEID = m.MESSAGEID      
AND m.messagesource = ''DISCOVER''
WHERE i.ISDELETED = 0 AND i.shouldHideFromReports = 0')
)
  UNION ALL