Author Posts

February 29, 2016 at 8:59 am

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

February 29, 2016 at 9:26 am

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

February 29, 2016 at 9:35 am

Ohhhh, that makes sense. Thank you so much.

February 29, 2016 at 12:13 pm

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

February 29, 2016 at 12:20 pm

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

March 1, 2016 at 4:47 am

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

March 1, 2016 at 7:05 am

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

}

March 4, 2016 at 6:24 am

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