Solved: Re: AD group application failing on QMC - Qlik Community

qlikrambo1 · ‎2018-11-30

rwunderlich · ‎2018-12-02

The module bug aside, here is native Qlik script that will convert a hexadecimal SID to a string representation.

Set ParseSubSA = if(len($1)>$2, '-'& num(num#(mid($1,$2+6,2) & mid($1,$2+4,2) & mid($1,$2+2,2) &mid($1,$2,2),'(HEX)') ,'0'),'');
Users:
LOAD
UserName,
UserDN,
objectSID,
'S'
&'-'& num(num#(mid(objectSID,1,2),'(HEX)'),'0') // Revision Level
&'-'& num(num#(mid(objectSID,5,12),'(HEX)'),'0') // Authority
& $(ParseSubSA(objectSID,17))
& $(ParseSubSA(objectSID,25))
& $(ParseSubSA(objectSID,33))
& $(ParseSubSA(objectSID,41))
& $(ParseSubSA(objectSID,49))
& $(ParseSubSA(objectSID,57))
as StringSID
FROM ...

The script assumes a max of 6 sub authority values, if you have more just duplicate the line and add 8 to the second parameter. The ParseSA function will test the length of ObjectSID so no harm in over-defining.

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

View solution in original post

sunny_talwar · ‎2018-11-30

Can you share the script and if possible the log file?

sunny_talwar · ‎2018-11-30

@marcus_sommer

Do you have any idea? It seems weird to me that how is this working

Script

Name>'$(arg)'

Logfile example

and Name>'XYZ'

Do you know what might be going on here?

marcus_sommer · ‎2018-11-30

I'm not absolutely sure but I think it means something like: 'a' precedes 'b' in Qlik and that SQL doesn't need an extra operator to perform a string-comparison and applies it automatically if the operands aren't numerical.

- Marcus

sunny_talwar · ‎2018-11-30

Make sense!! Do you have an idea about the error message? I am not sure what is wrong with it.

marcus_sommer · ‎2018-11-30

No, I haven't. But I think I would TRACE the iterations and the variable-values to see if there is anything unexpected, for example if one of the loop conditions don't work or if and special chars are in the variables or something similar.

rwunderlich · ‎2018-11-30

Nothing wrong with that loop code. I wrote it 🙂

It exits the loop fine, the problem is in the subsequent LOAD:

//************** Create a smaller list of AD groups related to BI applicationsAD_Group_Tmp:
LOAD
*,
upper(SAMAccountName) as SAMACCOUNTNAME,
HexStrToDecStr(objectSID) as SID,
mid(subfield(DistinguishedName,',',-3),4) as OU1,...

"HexStrToDecStr" is an invalid function.

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

qlikrambo1 · ‎2018-11-30

Thanks Rob,

used this piece of VB code in Macro and calling the same from the script

https://community.qlik.com/t5/QlikView-App-Development/Convert-SID-from-binary-to-string-format/td-p...

qlikrambo1 · ‎2018-11-30

the same HexStrToDecStr(objectSID) as SID works using manual reload while it's failing while QMC reload

rwunderlich · ‎2018-11-30

I think that's a bug -- module functions not recognized -- in the server version of QV 12.30. It fails even when all the "allow macro execution on server" boxes are checked in the QMC. But it works on Desktop. I recommend you contact QT support.

-Rob