您的位置: 首页  >  技术问答  >  ldap查询以获取禁用的用户记录，并在30天内进行更改

ldap查询以获取禁用的用户记录，并在30天内进行更改

分类: 技术问答 • 2022-07-22 21:08:14
问题描述：

我正在尝试运行LDAP查询，以获取过去30天内具有whenchanged属性的禁用用户列表.查询

I am a trying to run an LDAP query to get a list of disabled users with whenchanged attribute within last 30 days. The query 

SELECT Name, description, profilePath, homeDrive, whenChanged, distinguishedName,userAccountControl 
FROM 'LDAP://wusrcpsakc07.abc.com:3268/dc=SAK,dc=com' 
WHERE objectCategory='user' and userAccountcontrol = '514'

运行正常.添加whenchanged属性时，出现错误

runs fine. When I add the whenchanged attribute, I get an error

SELECT Name, description, profilePath, homeDrive, whenChanged, distinguishedName,userAccountControl 
FROM 'LDAP://wusrcpsakc07.abc.com:3268/dc=SAK,dc=com' 
WHERE objectCategory='user' and userAccountcontrol = '514'
and whenChanged > getDate() - 30

答

由于我熟悉SSIS/LDAP集成，因此我认为我应该以Pavel的方向为基础.

Since I am familiar with the SSIS/LDAP integration, I thought I'd build on Pavel's direction.

我假设您正在使用ADO.NET源进行数据流.除日期逻辑外，使您的程序包与过滤器一起正常工作.

I'm assuming you're using the ADO.NET source for your data flow. Get your package working fine with your filter excluding the date logic.

您需要做的第一件事是创建一些SSIS变量.我喜欢变量"，并且看到至少需要创建四个变量.

The first thing you'll need to do is create some SSIS Variables. I like Variables and see at least 4 Variable that need to be created.

  • SourceDate-DateTime-2014-04-19-EvaluateAsExpression = True.公式= DATEADD( "d",-30, @[System::StartTime] )
  • ADTime-字符串-20140419000000.0Z-EvaluateAsExpression = True.公式= (DT_WSTR, 4)YEAR(@[User::SourceDate]) + RIGHT("0" + (DT_WSTR, 2)MONTH(@[User::SourceDate]), 2) + RIGHT("0" + (DT_WSTR, 2) DAY(@[User::SourceDate]), 2) + "000000.0Z"
    • 如果您想学走路，并且想增加全职时间的部分，则公式将变为(DT_WSTR, 4)YEAR(@[User::SourceDate]) + RIGHT("0" + (DT_WSTR, 2)MONTH(@[User::SourceDate]), 2) + RIGHT("0" + (DT_WSTR, 2) DAY(@[User::SourceDate]), 2) + RIGHT("0" + (DT_WSTR, 2)DATEPART("hour", @[User::SourceDate] ), 2) + RIGHT("0" + (DT_WSTR, 2)DATEPART("minute", @[User::SourceDate] ), 2) + RIGHT("0" + (DT_WSTR, 2)DATEPART("second", @[User::SourceDate] ), 2) +".0Z"
    • SourceDate - DateTime - 2014-04-19 - EvaluateAsExpression = True. Formula = DATEADD( "d",-30, @[System::StartTime] )
    • ADTime - String - 20140419000000.0Z - EvaluateAsExpression = True. Formula = (DT_WSTR, 4)YEAR(@[User::SourceDate]) + RIGHT("0" + (DT_WSTR, 2)MONTH(@[User::SourceDate]), 2) + RIGHT("0" + (DT_WSTR, 2) DAY(@[User::SourceDate]), 2) + "000000.0Z"
      • If you're feeling pedantic and want to build out the full time part, the formula becomes (DT_WSTR, 4)YEAR(@[User::SourceDate]) + RIGHT("0" + (DT_WSTR, 2)MONTH(@[User::SourceDate]), 2) + RIGHT("0" + (DT_WSTR, 2) DAY(@[User::SourceDate]), 2) + RIGHT("0" + (DT_WSTR, 2)DATEPART("hour", @[User::SourceDate] ), 2) + RIGHT("0" + (DT_WSTR, 2)DATEPART("minute", @[User::SourceDate] ), 2) + RIGHT("0" + (DT_WSTR, 2)DATEPART("second", @[User::SourceDate] ), 2) +".0Z"

      看起来很多，但事实并非如此.让我们分解一下. -SourceDate将在程序包开始时间前30天进行计算. -ADTime-这将建立一个字符串YYYYMMDDHHMMSS.0Z(日期，然后是那些批评Ms用法不正确的人的时间部分)，这些值将填充0，以确保像May这样的月份表示为05而不是5 -QueryBase-这提供了查询的基本框架，并带有AND whenChanged >= '这是一个不完整的查询，不会解析，但它是静态片段 -查询-它使用QueryBase和ADTime将动态"元素添加到我们的查询中.由于AD无法处理动态部分，因此我们将采用这种方式.我们通过交换日期和时间部分来构建每次执行时的查询字符串.

      That looks like a lot, but it's not. Let's break it down. - SourceDate is going to calculate 30 days before the package's start time. - ADTime - this builds out a string of YYYYMMDDHHMMSS.0Z (date followed by time parts for those critiquing the ambiguous usage of Ms) These will be 0 padded values to ensure months like May are expressed as 05 and not 5 - QueryBase - This provides the basic framework of your query, with AND whenChanged >= ' That's an incomplete query, does not parse but it's the static piece - Query - This uses QueryBase and ADTime to add the "dynamic" element into our query. Since AD can't handle the dynamic portion, this is how we will trick it. We build our query string per execution by swapping in the date and time portion.

      ADO.NET查询编辑器可与大多数常规使用的SSIS项配合使用(是的，为此屡获殊荣).与OLE DB Source组件不同，您不能在编辑器内部指定要由变量驱动的组件. 相反，您需要右键单击数据流任务"，然后选择属性".

      The ADO.NET query editor is in a tie for most cantankerous SSIS item you may use on a semi-regular basis (yes, there's an award for this). Unlike the OLE DB Source component, you can't specify within the editor itself that you'd like to have it driven by a Variable. Instead, you'll need to right click on the Data Flow Task and select Properties.

      在出现的窗口中，您将找到表达式"椭圆，然后单击它.

      In the resultant window, you'll find the Expressions ellipses and click on it.

      选择一个类似于[ADO.NET源组件的名称].[SqlCommand]的名称，并为其分配@[User::Query]的值.在下图中，您可以看到我将源组件称为"ADO AD"

      Choose the one that looks like [Name of ADO.NET Source Component].[SqlCommand] and assign it the value of @[User::Query]. In the image below, you can see that I called my source component "ADO AD"

      支持和可维护性是我这样做的主要原因.通过单独构建变量，我可以通过在包中放置断点并检查元素或添加触发具有相关信息的信息消息的脚本任务来解决所有出现的问题.尽管我可以将所有变量逻辑都放入源组件的SqlCommand属性中，但是调试是不可能的，因为SSIS没有为您提供检查对象上的表达式的机制.

      Support and maintainability are my primary reasons for doing it this way. By building out the Variables individually, I can troubleshoot anything that's going wrong by putting a breakpoint in the package and inspecting elements or adding a script task that fires and information message with the relevant information. While I could have put all that Variable logic into the source component's SqlCommand property, debugging that is impossible because SSIS does not provide you a mechanism for inspecting the the expressions on an Object.

      514未禁用.我的意思是，是，但是userAccountControl的其他值可能表示已禁用.这确实是一个问题，即userAccountControl在与2进行按位AND运算时是否会产生2.有关我的悲痛故事，请参阅结尾处的第一个链接.

      514 is not disabled. I mean, it is but there are other values for userAccountControl that could indicate disabled. It's really a question of whether the userAccountControl, when bitwise ANDed with 2 yields 2. See the first link at the end for my sad tale of woe.

      对于userAccountControl，值512是活动的AD帐户，值514是禁用的帐户.我在另一个团队的文档中提到了这一点.不幸的是，这不是很准确.该字段实际上是一个位掩码.在实施我的软件包一两年后，我们得知不活跃的人出现在了不可能的提要中，我们将其过滤掉了(514s).然后，我了解到514实际上只是一个512(NORMAL_ACCOUNT)与2(ACCOUNTDISABLE)或.已实施了一个自动过程，该过程创建了具有过期密码或一些此类废话的帐户，这些帐户的最终结果是禁用的帐户读取为546(常规变为544-启用帐户-要求用户在首次登录时更改密码).

      For the userAccountControl a value of 512 is an active AD account, 514 is a disabled account. I have that in documentation from another team. Unfortunately, that's not quite accurate. That field is really a bitmask. A year or two after we implemented my package, we learned that inactive people were showing up in feeds which was impossible, we filter them (514s) out. I then learned that a 514 is really just a 512 (NORMAL_ACCOUNT) ORed with 2 (ACCOUNTDISABLE). An automated process had been implemented that created accounts with expired passwords or some such nonsense which had a net result of disabled accounts reading as 546 (regular became 544 - Account Enabled - Require user to change password at first logon).

      • http://billfellows.blogspot.com /2011/04/active-directory-ssis-data-source.html
      • http://billfellows.blogspot .com/2013/11/biml-active-directory-ssis-data-source.html

        • http://billfellows.blogspot.com/2011/04/active-directory-ssis-data-source.html
        • http://billfellows.blogspot.com/2013/11/biml-active-directory-ssis-data-source.html

          • 我非常喜欢将Biml与SSIS一起使用，因为它允许我与其他人共享我的作品"而无需邮寄包裹.以下代码基于我的 Biml Active Directory SSIS数据源帖子，针对此特定情况进行了一些调整.请注意，我必须双击ADO.NET源组件，单击列"，然后得到警告

          I'm rather fond of using Biml with SSIS as it allows me to "share my work" with others without mailing packages about. The following code is based off my Biml Active Directory SSIS Data Source post with some tweaks thrown in for this particular case. Do note, I had to double click the ADO.NET source component, click Columns and then I got my warnings

          "ADO AD不支持在列"上找到的数据类型"System.Object".此列将转换为DT_NTEXT"

          "The data type "System.Object" found on column "" is not supported for the ADO AD. This column will be converted to DT_NTEXT"

          我确定有一种方法可以使Biml生成准确的程序包，但是，我只需点击几下就可以完成这么少的工作".

          I'm sure there's a way to get the Biml to generate the exact package but, I can live with a few clicks for so little "work".

      <Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <AdoNetConnection Name="AD" ConnectionString="Data Source=HOME.BILLFELLOWS.NET;Provider=ADsDSOObject;Integrated Security=SSPI;" Provider="System.Data.OleDb.OleDbConnection, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
    </Connections>
    <Packages>
        <Package Name="so_23741312" DelayValidation="true" ForcedExecutionValueDataType="Empty" Language="None" ConstraintMode="Parallel" VersionBuild="5" CreatorName="bfellows" CreatorComputerName="WESTMARCH" CreationDate="2014-05-19T22:28:49">
            <Variables>
                <Variable Name="ADTime" DataType="String" IncludeInDebugDump="Exclude" EvaluateAsExpression="true">(DT_WSTR, 4)YEAR(@[User::SourceDate]) + RIGHT("0" + (DT_WSTR, 2)MONTH(@[User::SourceDate]), 2) + RIGHT("0" + (DT_WSTR, 2) DAY(@[User::SourceDate]), 2) + "000000.0Z"</Variable>
                <Variable Name="ADTimePedantic" DataType="String" IncludeInDebugDump="Exclude" EvaluateAsExpression="true">(DT_WSTR, 4)YEAR(@[User::SourceDate]) + RIGHT("0" + (DT_WSTR, 2)MONTH(@[User::SourceDate]), 2) + RIGHT("0" + (DT_WSTR, 2) DAY(@[User::SourceDate]), 2) + RIGHT("0" + (DT_WSTR, 2)DATEPART("hour", @[User::SourceDate] ), 2)+ RIGHT("0" + (DT_WSTR, 2)DATEPART("minute", @[User::SourceDate] ), 2)+ RIGHT("0" + (DT_WSTR, 2)DATEPART("second", @[User::SourceDate] ), 2)+".0Z"</Variable>
                <Variable Name="Query" DataType="String" IncludeInDebugDump="Exclude" EvaluateAsExpression="true">@[User::QueryBase] +  @[User::ADTime] + "'"</Variable>
                <Variable Name="QueryBase" DataType="String" IncludeInDebugDump="Exclude">SELECT Name, description, profilePath, homeDrive, whenChanged, distinguishedName, userAccountControl FROM 'LDAP://DC=home,DC=billfellows,DC=net' WHERE objectCategory='user' and userAccountcontrol = '514' AND whenChanged >= '</Variable>
                <Variable Name="RowCountSource" DataType="Int32" IncludeInDebugDump="Include">0</Variable>
                <Variable Name="SourceDate" DataType="DateTime" IncludeInDebugDump="Exclude" EvaluateAsExpression="true">DATEADD( "d",-30, @[System::StartTime]  )</Variable>
            </Variables>
            <Tasks>
                <Dataflow Name="DFT AD Extract" DelayValidation="true" ForcedExecutionValueDataType="Empty">
                    <Expressions>
                        <Expression ExternalProperty="[ADO AD].[SqlCommand]">@[User::Query]</Expression>
                    </Expressions>
                    <Transformations>
                        <AdoNetSource Name="ADO AD" LocaleId="None" ConnectionName="AD">
                            <DirectInput>SELECT Name, description, profilePath, homeDrive, whenChanged, distinguishedName, userAccountControl FROM 'LDAP://DC=home,DC=billfellows,DC=net' WHERE objectCategory='user' and userAccountcontrol = '514' AND whenChanged >= '20140419000000.0Z'</DirectInput>
                        </AdoNetSource>
                        <DerivedColumns Name="bit bucket Active" LocaleId="None">
                            <InputPath OutputPathName="CSPL Filter Inactive Accounts.ActiveAccounts" SsisName="CSPL Filter Inactive Accounts_ActiveAccounts_bit bucket Active" />
                        </DerivedColumns>
                        <DerivedColumns Name="bit bucket InActive" LocaleId="None">
                            <InputPath OutputPathName="CSPL Filter Inactive Accounts.Default" SsisName="CSPL Filter Inactive Accounts_Default_bit bucket InActive" />
                        </DerivedColumns>
                        <ConditionalSplit Name="CSPL Filter Inactive Accounts" LocaleId="None">
                            <InputPath OutputPathName="DER Check Account Status.Output" SsisName="DER Check Account Status_Output_CSPL Filter Inactive Accounts" />
                            <OutputPaths>
                                <OutputPath Name="ActiveAccounts">
                                    <Expression>IsActive</Expression>
                                </OutputPath>
                            </OutputPaths>
                        </ConditionalSplit>
                        <DataConversion Name="DC NTEXT to DT_WSTR" LocaleId="None">
                            <InputPath OutputPathName="RC Source.Output" SsisName="RC Source_Output_DC NTEXT to DT_WSTR" />
                            <Columns>
                                <Column SourceColumn="distinguishedName" TargetColumn="distinguishedName" DataType="String" Length="500" />
                                <Column SourceColumn="description" TargetColumn="description" DataType="String" Length="500" />
                                <Column SourceColumn="Name" TargetColumn="Name" DataType="String" Length="500" />
                                <Column SourceColumn="userAccountControl" TargetColumn="userAccountControl" DataType="Int32" />
                                <Column SourceColumn="profilePath" TargetColumn="profilePath" DataType="String" Length="500" />
                                <Column SourceColumn="homeDrive" TargetColumn="homeDrive" DataType="String" Length="500" />
                            </Columns>
                        </DataConversion>
                        <DerivedColumns Name="DER Check Account Status" LocaleId="None">
                            <InputPath OutputPathName="DC NTEXT to DT_WSTR.Output" SsisName="DC NTEXT to DT_WSTR_Output_DER Check Account Status" />
                            <Columns>
                                <Column Name="IsActive" DataType="Boolean">(([userAccountControl] &amp; 2) == 2) ? false : true</Column>
                            </Columns>
                        </DerivedColumns>
                        <RowCount Name="RC Source" LocaleId="None" VariableName="User.RowCountSource">
                            <InputPath OutputPathName="ADO AD.Output" SsisName="ADO AD_Output_RC Source" />
                        </RowCount>
                    </Transformations>
                </Dataflow>
            </Tasks>
            <Connections>
                <Connection ConnectionName="AD" />
            </Connections>
        </Package>
    </Packages>
</Biml>

      结果

      由于过去30天内我没有禁用任何帐户，因此这里没有任何内容.

      Results

      Since I haven't disabled any accounts in the past 30 days, there's nothing to see here.

相关推荐