AD and Sql Query into one CSV file.

system · February 29, 2016, 3:29am

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"}},&#8175;
        @{name="FirstName";e={$_."GivenName"}},&#8175;
        @{name="Initials";e={$_."Initials"}},&#8175;
        @{name="LastName";e={$_."Surname"}},&#8175;
        @{name="RoleID";e={"$null"}},&#8175;
        @{name="BusinessUnit";e={$_."corpadNet2001-CORPds-extension8"}},&#8175;
        @{name="EmployeeType";e={$_."employeeType"}},&#8175;
        @{name="Unit";e={$_."extensionAttribute10"}},&#8175;
        @{name="Company";e={$_."Company"}},&#8175;
        @{name="DepartmentCode";e={$_."extensionAttribute7"}},&#8175;
        @{name="Department";e={$_."Department"}},&#8175;
        @{name="PositionCode";e={$_."corpadNet2001-CORPds-PositionCode"}},&#8175;
        @{name="PositionDescription";e={$_."corpadNet2001-CORPds-PositionCodeDescription"}},&#8175;
        @{name="JobCode";e={$_."CorpadNet2001-CORPds-JobCode"}},&#8175;
        @{name="JobDescription";e={$_."corpadNet2001-CORPds-JobCodeDescription"}},&#8175;
        @{name="Title";e={$_."Title"}},&#8175;
        @{name="StreetAddress";e={$_."StreetAddress"}},&#8175;
        @{name="City";e={$_."City"}},&#8175;
        @{name="State";e={$_."State"}},&#8175;
        @{name="PostalCode";e={$_."PostalCode"}},&#8175;
        @{name="Phone";e={$_."telephoneNumber"}},&#8175;
        @{name="Fax";e={$_."Fax"}},&#8175;
        @{name="Email";e={$_."EmailAddress"}},&#8175;
        @{name="AuthSource";e={$_."corpadNet2001-CORPds-authoritativesource"}},&#8175;
        @{name="Enabled";e={$_."Enabled"}},&#8175;
        @{name="LastLogonDate";e={$_."LastLogonDate"}},&#8175;
        @{name="WhenChanged";e={$_."whenChanged"}},&#8175;
        @{name="WhenCreated";e={$_."whenCreated"}},&#8175;
        @{name="NPI";e={$_."corpadNet2001-CORPds-extension9"}} | Export-Csv -Append -NoTypeInformation C:\Scripts\QueryTest.csv </pre> 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

richard-siddaway · February 29, 2016, 3:56am

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

system · February 29, 2016, 4:05am

Ohhhh, that makes sense. Thank you so much.

dan-potter · February 29, 2016, 6:43am

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}}

dan-potter · February 29, 2016, 6:50am

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"}}

system · February 29, 2016, 11:17pm

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

dan-potter · March 1, 2016, 1:35am

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

}

system · March 4, 2016, 12:54am

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

Topic		Replies	Views
SqlCmd vs Invoke-SqlCmd issues with Export-CSV PowerShell Help	3	175	June 29, 2020
trying to query sql db using a csv PowerShell Help	11	171	November 5, 2015
Exporting to CSV Questions PowerShell Help	7	116	October 4, 2015
Help with export to CSV PowerShell Help	3	155	August 11, 2018
Execute SQL Stored Procedure and export results to CSV PowerShell Help	3	145	December 7, 2016

AD and Sql Query into one CSV file.

Related Topics