[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 994
  • Last Modified:

NEED TO IMPORT LOGONHOURS FROM ACTIVE DIRECTORY TO SQL SERVER?

0  down vote  favorite
      

HI ALL, I am trying to pull all the users from the Active Directory, I am able to pull all the records but when i read the data in the tables it displays as SYSTEM.BYTE[](for logonhours), need to convert before inserting that in to SQL table. I did a googling and found some solution like first converting that into byte then into a string and inserting it, where you can read the data, but I am not able to implement that. Please help me out.

I am inserting into SQL Server 2005 and importing all the data using SSIS ( since there is a limitation on ADSI when you query, you get only 1000 records I am using a custom script and setting a page default to more than 1000 to retrive all the records from active directory) below is my custom script how i am pulling my records from ADSI:
Option Strict Off
Imports System
Imports System.Data
Imports System.DirectoryServices
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub CreateNewOutputRows()

        Dim arrayDomains As String()
        ReDim arrayDomains(1)
        arrayDomains(0) = "LDAP://DC=abc,DC=bca,DC=com"
        arrayDomains(1) = "LDAP://DC=bac,DC=cba,DC=com"
        Dim Domain As String
        For Each Domain In arrayDomains

            Dim objSearch As New DirectorySearcher()
            objSearch.SearchRoot = New DirectoryEntry(Domain)
            objSearch.Filter = "(&(objectclass=user)(objectcategory=Person)(samAccountType=805306368))"
            '(|(&(!(groupType:1.2.840.113556.1.4.803:=1))(groupType:1.2.840.113556.1.4.804:=14))(samAccountType=805306368))
            objSearch.SearchScope = SearchScope.Subtree
            objSearch.PageSize = 999
            objSearch.ServerTimeLimit = New TimeSpan(0, 15, 0)
            objSearch.ClientTimeout = New TimeSpan(0, 30, 0)
            objSearch.PropertiesToLoad.Add("cn")
            objSearch.PropertiesToLoad.Add("c")
            objSearch.PropertiesToLoad.Add("department")
            objSearch.PropertiesToLoad.Add("displayName")
            objSearch.PropertiesToLoad.Add("distinguishedName")
            objSearch.PropertiesToLoad.Add("employeeID")
            objSearch.PropertiesToLoad.Add("extensionAttribute14")
            objSearch.PropertiesToLoad.Add("extensionAttribute2")
            objSearch.PropertiesToLoad.Add("givenName")
            objSearch.PropertiesToLoad.Add("l")
            objSearch.PropertiesToLoad.Add("lastLogon")
            objSearch.PropertiesToLoad.Add("logonHours")
            objSearch.PropertiesToLoad.Add("mail")
            objSearch.PropertiesToLoad.Add("manager")
            objSearch.PropertiesToLoad.Add("physicalDeliveryOfficeName")
            objSearch.PropertiesToLoad.Add("postalCode")
            objSearch.PropertiesToLoad.Add("pwdLastSet")
            objSearch.PropertiesToLoad.Add("sn")
            objSearch.PropertiesToLoad.Add("st")
            objSearch.PropertiesToLoad.Add("streetAddress")
            objSearch.PropertiesToLoad.Add("telephoneNumber")
            objSearch.PropertiesToLoad.Add("title")
            objSearch.PropertiesToLoad.Add("userAccountControl")
            objSearch.PropertiesToLoad.Add("whenCreated")

            Dim colQueryResults As SearchResultCollection
            colQueryResults = objSearch.FindAll()

            Dim objResult As SearchResult
            For Each objResult In colQueryResults
                'Console.WriteLine("3")
                '
                ' Add rows by calling AddRow method on member variable called "<Output Name>Buffer"
                ' E.g., MyOutputBuffer.AddRow() if your output was named "My Output"
                '
                Output0Buffer.AddRow()
                If objResult.Properties.Contains("cn") AndAlso objResult.Properties("cn")(0) IsNot Nothing Then
                    Output0Buffer.cn = objResult.Properties("cn")(0).ToString()
                End If

                If objResult.Properties.Contains("c") AndAlso objResult.Properties("c")(0) IsNot Nothing Then
                    Output0Buffer.c = objResult.Properties("c")(0).ToString()
                End If
                If objResult.Properties.Contains("department") AndAlso objResult.Properties("department")(0) IsNot Nothing Then
                    Output0Buffer.Department = objResult.Properties("department")(0).ToString()
                End If
                If objResult.Properties.Contains("displayName") AndAlso objResult.Properties("displayname")(0) IsNot Nothing Then
                    Output0Buffer.DisplayName = objResult.Properties("displayName")(0).ToString()
                End If
                If objResult.Properties.Contains("distinguishedName") AndAlso objResult.Properties("distinguishedName")(0) IsNot Nothing Then
                    Output0Buffer.DistinguishedName = objResult.Properties("distinguishedName")(0).ToString()
                End If
                If objResult.Properties.Contains("employeeID") AndAlso objResult.Properties("employeeID")(0) IsNot Nothing Then
                    Output0Buffer.EmployeeID = objResult.Properties("employeeID")(0).ToString()
                End If
                If objResult.Properties.Contains("extensionAttribute14") AndAlso objResult.Properties("extensionAttribute14")(0) IsNot Nothing Then
                    Output0Buffer.ExtensionAttribute14 = objResult.Properties("extensionAttribute14")(0).ToString()
                End If
                If objResult.Properties.Contains("extensionAttribute2") AndAlso objResult.Properties("extensionAttribute2")(0) IsNot Nothing Then
                    Output0Buffer.ExtensionAttribute2 = objResult.Properties("extensionAttribute2")(0).ToString()
                End If
                If objResult.Properties.Contains("givenName") AndAlso objResult.Properties("givenName")(0) IsNot Nothing Then
                    Output0Buffer.GivenName = objResult.Properties("givenName")(0).ToString()
                End If
                If objResult.Properties.Contains("l") AndAlso objResult.Properties("l")(0) IsNot Nothing Then
                    Output0Buffer.L = objResult.Properties("l")(0).ToString()
                End If
                If objResult.Properties.Contains("lastLogon") AndAlso objResult.Properties("lastLogon")(0) <> 0 AndAlso objResult.Properties("lastLogon")(0) IsNot Nothing Then
                    Output0Buffer.LastLogon = DateTime.Parse(DateTime.FromFileTime(objResult.Properties("lastLogon")(0).ToString()))
                End If
                'If objResult.Properties.Contains("pwdLastSet") AndAlso objResult.Properties("pwdLastSet")(0) <> 0 AndAlso objResult.Properties("pwdLastSet")(0) IsNot Nothing Then
                '    Output0Buffer.PwdLastSet = DateTime.Parse(DateTime.FromFileTime(objResult.Properties("pwdLastSet")(0)).ToString())
                'End If
                If objResult.Properties.Contains("logonHours") AndAlso objResult.Properties("logonHours")(0) IsNot Nothing Then
                    Output0Buffer.LogonHours = objResult.Properties("logonHours")(0).ToString()
                End If
                If objResult.Properties.Contains("mail") AndAlso objResult.Properties("mail")(0) IsNot Nothing Then
                    Output0Buffer.Mail = objResult.Properties("mail")(0).ToString()
                End If
                If objResult.Properties.Contains("manager") AndAlso objResult.Properties("manager")(0) IsNot Nothing Then
                    Output0Buffer.Manager = objResult.Properties("manager")(0).ToString()
                End If
                If objResult.Properties.Contains("physicalDeliveryOfficeName") AndAlso objResult.Properties("physicalDeliveryOfficeName")(0) IsNot Nothing Then
                    Output0Buffer.PhysicalDeliveryOfficeName = objResult.Properties("physicalDeliveryOfficeName")(0).ToString()
                End If
                If objResult.Properties.Contains("postalCode") AndAlso objResult.Properties("postalCode")(0) IsNot Nothing Then
                    Output0Buffer.PostalCode = objResult.Properties("postalCode")(0).ToString()
                End If
                If objResult.Properties.Contains("pwdLastSet") AndAlso objResult.Properties("pwdLastSet")(0) <> 0 AndAlso objResult.Properties("pwdLastSet")(0) IsNot Nothing Then
                    Output0Buffer.PwdLastSet = DateTime.Parse(DateTime.FromFileTime(objResult.Properties("pwdLastSet")(0)).ToString())
                End If
                'If objResult.Properties.Contains("pwdLastSet") AndAlso objResult.Properties("pwdLastSet")(0) IsNot Nothing Then
                '    Output0Buffer.PwdLastSet = objResult.Properties("pwdLastSet")(0).ToString()
                'End If
                If objResult.Properties.Contains("sn") AndAlso objResult.Properties("sn")(0) IsNot Nothing Then
                    Output0Buffer.Sn = objResult.Properties("sn")(0).ToString()
                End If
                If objResult.Properties.Contains("st") AndAlso objResult.Properties("st")(0) IsNot Nothing Then
                    Output0Buffer.St = objResult.Properties("st")(0).ToString()
                End If
                If objResult.Properties.Contains("streetAddress") AndAlso objResult.Properties("streetAddress")(0) IsNot Nothing Then
                    Output0Buffer.StreetAddress = objResult.Properties("streetAddress")(0).ToString()
                End If
                If objResult.Properties.Contains("telephoneNumber") AndAlso objResult.Properties("telephoneNumber")(0) IsNot Nothing Then
                    Output0Buffer.TelephoneNumber = objResult.Properties("telephoneNumber")(0).ToString()
                End If
                If objResult.Properties.Contains("title") AndAlso objResult.Properties("title")(0) IsNot Nothing Then
                    Output0Buffer.Title = objResult.Properties("title")(0).ToString()
                End If
                If objResult.Properties.Contains("userAccountControl") AndAlso objResult.Properties("userAccountControl")(0) IsNot Nothing Then
                    Output0Buffer.UserAccountControl = objResult.Properties("userAccountControl")(0).ToString()
                End If
                If objResult.Properties.Contains("whenCreated") AndAlso objResult.Properties("whenCreated")(0) IsNot Nothing Then
                    Output0Buffer.WhenCreated = CDate(objResult.Properties("whenCreated")(0).ToString())
                End If

            Next

        Next

    End Sub

End Class

Open in new window

0
ChWCIT
Asked:
ChWCIT
  • 7
  • 7
1 Solution
 
roshnipatelCommented:
What datatype are you trying to store it as?
LogonHours is a byte[] (array), so you'll need to parse the data into a readable format.  

http://directoryprogramming.net/forums/thread/6194.aspx
Has a good function to help make it readable, but if possible you may want to store the value as binary(168) - since there are 168 bits for LogonHours.

remove the ToString() [that returns the datatype
Try
Output0Buffer.LogonHours = objResult.Properties("logonHours")(0)

Open in new window

or
Output0Buffer.LogonHours = objResult.Properties("logonHours")(0).Value

Open in new window

0
 
roshnipatelCommented:
You also want to make sure that Output0Buffer.LogonHours  is a byte()
0
 
ChWCITAuthor Commented:
Hi Roshni,
Thanks for the response
I am confused can you please let me know where I need to declare Output0Buffer.LogonHours  is a byte()....
because I am trying to parse it to byte but it throws an error instead declaring it as byte.....
Thanks
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
roshnipatelCommented:
Under Input\Outputs, change the column LoginHours DataType Properties
Datatype = binary
length = 168
0
 
roshnipatelCommented:
If it still errors about casting a byte[] to binary, then try this:
Output0Buffer.LogonHours = BitConverter.ToString(objResult.Properties("logonHours")(0)) 

Open in new window

0
 
ChWCITAuthor Commented:
I don't see binary, but i am considering it as byte stream and removed .ToString() and ran the package, it loads but this time instead of system.byte[] i see the values in the below format :
"0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF"
 is there a way, can this be converted into specified hours before inserting into a table or any TSQL to convert this Hex into readable values....
   and i have gone through the site you provided but i find some useful information but i am not sure how do i embed that code in the code i provided above....
if you can provide me some hint, that would be more helpful.....
0
 
roshnipatelCommented:

Sure...
How do you want the output the look?
ie.  Sunday, 1pm - 5PM, Monday 2 PM - 6PM

Remember, the byte array is storing a 1 or 0 for each hour,  M - F

ie. byte[] = 111100001010110101...010 (168 bits long)
= Logon Hours :
Monday 12:00 AM True
Monday 1:00 AM True
Monday 2:00 AM True
Monday 3:00 AM True
Monday 4:00 AM False
Monday 5:00 AM False..
etc.


It will be a very long and hard to parse the string if you wanted to store it that way.  I think your best bet is to store the value as Binary (or VarBinary) and use the UI or Report that queries this data to make it readable.
0
 
ChWCITAuthor Commented:
"Store the value as Binary(or VarBinary)" you mean in the table.
0
 
ChWCITAuthor Commented:
if you mean table i am storing them in a varbinary(max), and when i run a select on that by converting that into datetime i throws an error (conversion failed from varbinary to datetime, but when i referred the standardized data types sheets it says we can convert ), I am not sure roshini how many types of errors i am facing ....
    these are the queries I am running:

select logonhours from dbo.tblLogonhours--here logonhrs column is varbinary(max)
result: 0x460046002D00460046002D00460046002D00460046002D00460046002D00460046002D00460046002D00460046002D00460046002D00460046002D00460046002D00460046002D00460046002D00460046002D00460046002D00460046002D00460046002D00460046002D00460046002D00460046002D0046004600

select convert(timestamp,logonhours)  from dbo.tblLogonhours
result: 0x460046002D004600 (for some value)
for nulls the result is 0x

select convert(datetime,logonhours) from dbo.tblLogonhours
result: Conversion failed when converting datetime from binary/varbinary string.

is there any thing I am doing wrong......
but at the end I need the logonhours from Monday-Friday to be displayed.....
0
 
roshnipatelCommented:
Ok... to store the value try this:

Output0Buffer.LogonHours = String.Concat("0x", BitConverter.ToString(objResult.Properties(("logonHours")(0)))

Can you post the results of select logonhours from dbo.tblLogonhours
atfer you incoporate the code above?
0
 
ChWCITAuthor Commented:
Hi Roshini,

After making changes, Load was successful and here are the results when I run the query:

0xFF-FF-FF-FF-FF-FF-FF-FF-FF-FF-FF-FF-FF-FF-FF-FF-FF-FF-FF-FF-FF.

one more question by looking at the result I can't figure out the logonhours, instead of displaying as above is there a way can we display as Monday 8-6 as logonhours and so on.......
Thanks
0
 
roshnipatelCommented:
Yes, but , it's a bit messy if you want a sql query to do it, it's a bit cleaner in c# or VB.  What language do you need it in -I'm assuming you need it in sql?

Basically, you need to do a bitwise & for each hour
0
 
ChWCITAuthor Commented:
can you please provide me in all the three ways :), i can play around with those and get familiarized with the code.
0
 
ChWCITAuthor Commented:
Hi Roshini,
Just a follow up on the above addressed issue. Can you please provide me in all the three options(in SQL, VB and C#)
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now