topic Re: AD group application failing on QMC in QlikView

group application failing

qlikrambo1 — Wed, 25 Nov 2020 16:16:04 GMT

Re: AD group application failing on QMC

sunny_talwar — Fri, 30 Nov 2018 15:55:10 GMT

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

Re: AD group application failing on QMC

sunny_talwar — Fri, 30 Nov 2018 16:43:02 GMT

@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?

Re: AD group application failing on QMC

marcus_sommer — Fri, 30 Nov 2018 16:56:47 GMT

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

Re: AD group application failing on QMC

sunny_talwar — Fri, 30 Nov 2018 17:01:22 GMT

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

Re: AD group application failing on QMC

marcus_sommer — Fri, 30 Nov 2018 18:25:18 GMT

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.

Re: AD group application failing on QMC

rwunderlich — Fri, 30 Nov 2018 20:16:35 GMT

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

Re: AD group application failing on QMC

qlikrambo1 — Fri, 30 Nov 2018 20:40:30 GMT

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/271617

Re: AD group application failing on QMC

qlikrambo1 — Fri, 30 Nov 2018 20:47:25 GMT

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

Re: AD group application failing on QMC

rwunderlich — Fri, 30 Nov 2018 21:44:08 GMT

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

Re: AD group application failing on QMC

qlikrambo1 — Fri, 30 Nov 2018 21:46:17 GMT

Thanks Rob

Re: AD group application failing on QMC

marcus_sommer — Sat, 01 Dec 2018 11:47:41 GMT

Please follow the suggestion from Rob and open by Qlik a request to find out if it's a new bug or if there are some new/changed settings within the config-files which prevent the macro-execution.

Beside this there are various alternatives directly within the Qlik script available.

One would be to use a parametrized variable like a user-defined function. It wouldn't be very elegant but quite similar to your used the vbs-function. I have already seen something similar and if I remember correctly it converted hex-timestamps into normal timestamps and I saw it at least twice but unfortunately, I couldn't find it yet. I think one was within a CAL manager tool, maybe this one: CAL-Document-Manager. Maybe Rob or others could remember it, too.

Another possibility would be to use an additionally load which looped through each char of your field-values. I mean something like:

Load *, 'here comes step one of the converting logic';
Load ID, mid(FIELD, iterno(), 1) as CHAR from Source while iterno() <= len(FIELD);

A concatenating of the single-conversions could be done within another preceeding-load with a peek-function or maybe with another aggregation load with concat(). After that you join/map it back to your source.

Probably even more easier might it be to use a mapsubstring() approach for it. Here are two examples which give you an idea what is meant:

Passing-parameter-strings-that-contain-special-characters
for-each-loop

- Marcus

Re: AD group application failing on QMC

rwunderlich — Sun, 02 Dec 2018 07:21:27 GMT

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

Re: AD group application failing on QMC

qlikrambo1 — Sun, 02 Dec 2018 17:45:59 GMT

Thanks Rob, Will try this.