This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

MS SQL Application Collector: Error validating attribute value type to its data type. Refer to 'endTime' in the following data

Hi,

We are collection users, permissions and assignments from an MS SQL DB (2019).
The assignment table has a column endTime(datetime) that we want to "map" to IGA "Permission Assignment Attribute" endTime(Date) but we get [Error validating attribute value type to its data type. Refer to 'endTime' in the following data] when we do the collection.

I'm unsure if I do the transformation wrong or if there is someting else that wrong.

Q: What's the expected datetime format for the "Permission Assignment Attribute" endTime(Date) attribute?

Q: If I transform to the correct datetime format, does that solve the validation error or do I have to do someting else?

IGA 3.7.3

Regards,

Mange

  • IG stores all dates as a Unix Epoch - i.e. number of seconds since Jan 1 1970.    I'm fairly certain transforming to that format will fix your issue.

    --Jim

  • Verified Answer

    Greetings,

    The JDBC collectors currently convert incoming values that are of type Timestamp to a string. If one is going to map these to ID Gov attribute that is of type String then "all is good". However, if one is mapping to an ID Gov Attribute of type Date (for example the Permission Assignment Attribute: Assignment End Time), then it will be necessary to parse the String into parts of date (Year, Month, Day, Hour, Minutes, and Seconds) and then create a Date with the parsed values (you will also need to know if the database Value should be stored in UTC or not).

    For Example:

    if (inputValue === '0') {
      outputValue = null;
    } else {
      var yearStr=inputValue.substring(0,4);
      var month=inputValue.substring(5,7)-1;
      var day = inputValue.substring(8,10);
      var hour=inputValue.substring(11,13);
      var minute=inputValue.substring(14,16);
      var second=inputValue.substring(17,19);
      /*var newdate = new Date(Date.UTC(yearStr,month,day,hour,minute,second)); db value is UTC*/
      var newdate = new Date(yearStr,month,day,hour,minute,second); /* db value is not UTC */
      finalDate = newdate.getTime().toString();
      outputValue=finalDate;
    }


    There is a Backlog to change the JDBC Collectors so that they will not convert Timestamps on the way in by default anymore.

    Sincerely,
    Steven Williams
    Principal Enterprise Architect
    Micro Focus

  • This is our script that works(MS SQL datetime format)

    if( inputValue !== null) {
        var year=inputValue.substring(0,4);
        var month=inputValue.substring(5,7) - 1; 
        var day=inputValue.substring(8,10);
        var hour=inputValue.substring(8,10);
        var minute=inputValue.substring(10,12);
        var second=inputValue.substring(12,13);
        var newdate=new Date(Date.UTC(year,month.toString(),day,hour,minute,second)); 
        var outputValue=newdate.getTime().toString();
    }
    else {
        outputValue='';
    }

  • Greetings,
        The example I provided was from an Oracle set-up. It might be necessary to adjust based upon the database that one is collecting from.

      in your example the same value is being used for Day and Hour (inputValue.substring(8,10). If you have not already, I would strongly suggest viewing the raw value from performing a Test Collection and then test your transformation script with the Transform Tester utility (located in the idgov/bin directory). This way you can test different values without the need to either (a) Fully collect or (b) use Test Collection -> Transform.   Also, I would not set the output to an empty string as that is different than null.  Null will make it so that no value is set.

    Sincerely,
    Steven Williams
    Principal Enterprise Architect
    Micro Focus