AD and Sql Query into one CSV file.

Tagged: ,

This topic contains 7 replies, has 3 voices, and was last updated by Profile photo of Kevin Dunn Kevin Dunn 6 months, 3 weeks ago.

Viewing 8 posts - 1 through 8 (of 8 total)
  • Author
    Posts
  • #35812
    Profile photo of Kevin Dunn
    Kevin Dunn
    Participant

    I am trying to query the AD and obtain the specified properties, and then query a sql server and obtain the desired data. After the queries are finished, I want it to export all results to one CSV. My code is

     Invoke-Sqlcmd -Query "SELECT [CLARITY_EMP].[USER_ID] as [EMP], [CLARITY_EMP].[PROV_ID] as [SER], [ECI_BASIC].[DEPLYMNT_DESC] as [ENV], [CLARITY_EMP].Name
    FROM [CLARITY_EMP]
     LEFT JOIN [EMP_MAP]
                            ON [EMP_MAP].[CID] = [CLARITY_EMP].[USER_ID]
                            LEFT JOIN [ECI_BASIC]
                            ON [ECI_BASIC].[INSTANCE_ID] = [CLARITY_EMP].[CM_PHY_OWNER_ID]
                            WHERE [EMP_MAP].[INTERNAL_ID] = 'user'
                
          SELECT [CLARITY_EMP].[USER_ID] as [EMP], [CLARITY_EMP].[PROV_ID] as [SER] , [ECI_BASIC].[DEPLYMNT_DESC] as [ENV]
                            FROM [CLARITY_EMP]
                            LEFT JOIN [ECI_BASIC]
                            ON [ECI_BASIC].[INSTANCE_ID] = [CLARITY_EMP].[CM_PHY_OWNER_ID]
                            WHERE [CLARITY_EMP].[SYSTEM_LOGIN] = 'user'
                
                SELECT [CID] as [SER]
                            FROM [SER_MAP]
                            WHERE [INTERNAL_ID] ='user'
                            
            SELECT [ID1].[PROV_ID] as [SER]
                            FROM [Clarity].[dbo].[IDENTITY_SER_ID] as [ID1]
                            WHERE [ID1].IDENTITY_TYPE_ID = 'number'
                            AND [ID1].IDENTITY_ID ='user'-ServerInstance "MTDV" -Database "Clarity" | Export-Csv -Append -NoTypeInformation C:\Scripts\QueryTest.csv;
                            get-aduser user -Properties * | 
                select @{name="3-4ID";e={$_."name"}},`
                @{name="FirstName";e={$_."GivenName"}},`
                @{name="Initials";e={$_."Initials"}},`
                @{name="LastName";e={$_."Surname"}},`
                @{name="RoleID";e={"$null"}},`
                @{name="BusinessUnit";e={$_."corpadNet2001-CORPds-extension8"}},`
                @{name="EmployeeType";e={$_."employeeType"}},`
                @{name="Unit";e={$_."extensionAttribute10"}},`
                @{name="Company";e={$_."Company"}},`
                @{name="DepartmentCode";e={$_."extensionAttribute7"}},`
                @{name="Department";e={$_."Department"}},`
                @{name="PositionCode";e={$_."corpadNet2001-CORPds-PositionCode"}},`
                @{name="PositionDescription";e={$_."corpadNet2001-CORPds-PositionCodeDescription"}},`
                @{name="JobCode";e={$_."CorpadNet2001-CORPds-JobCode"}},`
                @{name="JobDescription";e={$_."corpadNet2001-CORPds-JobCodeDescription"}},`
                @{name="Title";e={$_."Title"}},`
                @{name="StreetAddress";e={$_."StreetAddress"}},`
                @{name="City";e={$_."City"}},`
                @{name="State";e={$_."State"}},`
                @{name="PostalCode";e={$_."PostalCode"}},`
                @{name="Phone";e={$_."telephoneNumber"}},`
                @{name="Fax";e={$_."Fax"}},`
                @{name="Email";e={$_."EmailAddress"}},`
                @{name="AuthSource";e={$_."corpadNet2001-CORPds-authoritativesource"}},`
                @{name="Enabled";e={$_."Enabled"}},`
                @{name="LastLogonDate";e={$_."LastLogonDate"}},`
                @{name="WhenChanged";e={$_."whenChanged"}},`
                @{name="WhenCreated";e={$_."whenCreated"}},`
                @{name="NPI";e={$_."corpadNet2001-CORPds-extension9"}} | Export-Csv -Append -NoTypeInformation C:\Scripts\QueryTest.csv 

    When I run the query it states I cannot append. Does anyone know the best way to run multiple queries and have all the results exported to one CSV?

    Thank you

    #35814

    From a quick look at what you're trying to do:
    run 4 SQL queries to get data relating to a user
    get the AD properties of a specific user
    export to a CSV file.

    You can't just append the AD data to the CSV because it's different data – the format isn't the same – different headers, and data types etc.

    What I think you'll have to do is:
    run each SQL query separately and get the results into a variable (4 variables)
    run the AD query and put the results into a variable
    create an object containing the properties you want from each variable and then export it to a CSV

    #35815
    Profile photo of Kevin Dunn
    Kevin Dunn
    Participant

    Ohhhh, that makes sense. Thank you so much.

    #35820
    Profile photo of Dan Potter
    Dan Potter
    Participant

    You can combine both but you aren't selecting the properties correctly. Save yourself some trouble and create a stored procedure for your sql query.

    | select properties of sql output, expression

    Like so

    get-adcomputer mycomputer | select dnshostname,objectclass,@{name="samaccount";e={(get-aduser me).samaccountname}}

    #35821
    Profile photo of Dan Potter
    Dan Potter
    Participant

    No way of testing this so you might have to play with it. Take note of the select

    Clean up all the quotes where they aren't needed. You don't need them around anything but the properties containing hyphens.

    $user = get-aduser user -Properties * 
    
    Invoke-Sqlcmd -Query "SELECT [CLARITY_EMP].[USER_ID] as [EMP], [CLARITY_EMP].[PROV_ID] as [SER], [ECI_BASIC].[DEPLYMNT_DESC] as [ENV], [CLARITY_EMP].Name
    FROM [CLARITY_EMP]
     LEFT JOIN [EMP_MAP]
                            ON [EMP_MAP].[CID] = [CLARITY_EMP].[USER_ID]
                            LEFT JOIN [ECI_BASIC]
                            ON [ECI_BASIC].[INSTANCE_ID] = [CLARITY_EMP].[CM_PHY_OWNER_ID]
                            WHERE [EMP_MAP].[INTERNAL_ID] = 'user'
                
          SELECT [CLARITY_EMP].[USER_ID] as [EMP], [CLARITY_EMP].[PROV_ID] as [SER] , [ECI_BASIC].[DEPLYMNT_DESC] as [ENV]
                            FROM [CLARITY_EMP]
                            LEFT JOIN [ECI_BASIC]
                            ON [ECI_BASIC].[INSTANCE_ID] = [CLARITY_EMP].[CM_PHY_OWNER_ID]
                            WHERE [CLARITY_EMP].[SYSTEM_LOGIN] = 'user'
                
                SELECT [CID] as [SER]
                            FROM [SER_MAP]
                            WHERE [INTERNAL_ID] ='user'
                            
            SELECT [ID1].[PROV_ID] as [SER]
                            FROM [Clarity].[dbo].[IDENTITY_SER_ID] as [ID1]
                            WHERE [ID1].IDENTITY_TYPE_ID = 'number'
                            AND [ID1].IDENTITY_ID ='user'-ServerInstance "MTDV" -Database "Clarity" | select *,`
                            
                @{name="3-4ID";e={$user."name"}},`
                @{name="FirstName";e={$user."GivenName"}},`
                @{name="Initials";e={$user."Initials"}},`
                @{name="LastName";e={$user."Surname"}},`
                @{name="RoleID";e={"$null"}},`
                @{name="BusinessUnit";e={$user."corpadNet2001-CORPds-extension8"}},`
                @{name="EmployeeType";e={$user."employeeType"}},`
                @{name="Unit";e={$user."extensionAttribute10"}},`
                @{name="Company";e={$user."Company"}},`
                @{name="DepartmentCode";e={$user."extensionAttribute7"}},`
                @{name="Department";e={$user."Department"}},`
                @{name="PositionCode";e={$user."corpadNet2001-CORPds-PositionCode"}},`
                @{name="PositionDescription";e={$user."corpadNet2001-CORPds-PositionCodeDescription"}},`
                @{name="JobCode";e={$user."CorpadNet2001-CORPds-JobCode"}},`
                @{name="JobDescription";e={$user."corpadNet2001-CORPds-JobCodeDescription"}},`
                @{name="Title";e={$user."Title"}},`
                @{name="StreetAddress";e={$user."StreetAddress"}},`
                @{name="City";e={$user."City"}},`
                @{name="State";e={$user."State"}},`
                @{name="PostalCode";e={$user."PostalCode"}},`
                @{name="Phone";e={$user."telephoneNumber"}},`
                @{name="Fax";e={$user."Fax"}},`
                @{name="Email";e={$user."EmailAddress"}},`
                @{name="AuthSource";e={$user."corpadNet2001-CORPds-authoritativesource"}},`
                @{name="Enabled";e={$user."Enabled"}},`
                @{name="LastLogonDate";e={$user."LastLogonDate"}},`
                @{name="WhenChanged";e={$user."whenChanged"}},`
                @{name="WhenCreated";e={$user."whenCreated"}},`
                @{name="NPI";e={$user."corpadNet2001-CORPds-extension9"}} 
    #35846
    Profile photo of Kevin Dunn
    Kevin Dunn
    Participant

    Thank you so much. That makes sense creating the store procedure. This has definitely helped trim the fat. I appreciate your wisdom.

    #35853
    Profile photo of Dan Potter
    Dan Potter
    Participant

    NP. Note I tend not to use expressions when I don't have too, I have trouble keeping track of the brackets:-)

    You can do something like this.

    $user = get-aduser user -Properties *

    $sqlstuff = Invoke-Sqlcmd -Query "myprocedure"

    [pscustomobject]@{

    name = $user.name
    sqlprop1 = $sqlstuff.property1
    sqlprop2 = $sqlstuff.property2
    sqlprop3 = $sqlstuff.property3

    }

    #36055
    Profile photo of Kevin Dunn
    Kevin Dunn
    Participant

    That is a huge help. I am with you on the brackets, 9 times out of 10, I miss a bracket.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic.