Powershell - Compare CSV against AD Group

[Chris_Jones]

Hello all,

 

I am attempting to compare a CSV File against an Active Directory Group.

The outputs I want are:

File1 - List of AD Group members who do not appear in CSV

File2 - List of AD Group members who do appear in CSV, along with their IP Address and Timestamp of the entry.

 

The CSV I have is auto-generated from a syslog server (It is output from Cisco Context Directory Agent)

2015-04-21 15:22:23,local0.notice,10.10.10.35, 2015-04-21T14:22:28.964Z 0000131841 40051 NOTICE ContextManager: Updated Mapping Record From Active Directory, cda-request={entity-attr:entity-id:ip=192.168.0.139, entity-attr:op=update, entity-attr:value:domain=MyDomain, entity-attr:value:mapping-origin=MyDomain, entity-attr:value:mapping-type=dc, entity-attr:value:responds-to-probe=true, entity-attr:value:time-stamp=2015-04-21T14:22:20Z, entity-attr:value:user-name=zoe}\n
2015-04-21 15:22:23,local0.notice,10.10.10.35, 2015-04-21T14:22:28.965Z 0000131842 40051 NOTICE ContextManager: Updated Mapping Record From Active Directory, cda-request={entity-attr:entity-id:ip=192.168.2.2, entity-attr:op=update, entity-attr:value:domain=MyDomain, entity-attr:value:mapping-origin=MyDomain, entity-attr:value:mapping-type=dc, entity-attr:value:responds-to-probe=true, entity-attr:value:time-stamp=2015-04-21T14:22:20Z, entity-attr:value:user-name=wash}\n
2015-04-21 15:22:25,local0.notice,10.10.10.35, 2015-04-21T14:22:31.196Z 0000131843 40051 NOTICE ContextManager: Updated Mapping Record From Active Directory, cda-request={entity-attr:entity-id:ip=192.168.3.2, entity-attr:op=update, entity-attr:value:domain=MyDomain, entity-attr:value:mapping-origin=MyDomain, entity-attr:value:mapping-type=dc, entity-attr:value:responds-to-probe=true, entity-attr:value:time-stamp=2015-04-21T14:22:21Z, entity-attr:value:user-name=river}\n
2015-04-21 15:22:29,local0.notice,10.10.10.35, 2015-04-21T14:22:35.652Z 0000131844 40051 NOTICE ContextManager: Updated Mapping Record From Active Directory, cda-request={entity-attr:entity-id:ip=192.168.0.13, entity-attr:op=update, entity-attr:value:domain=MyDomain, entity-attr:value:mapping-origin=MyDomain, entity-attr:value:mapping-type=dc, entity-attr:value:responds-to-probe=true, entity-attr:value:time-stamp=2015-04-21T14:22:26Z, entity-attr:value:user-name=simon}\n
2015-04-21 15:22:36,local0.notice,10.10.10.35, 2015-04-21T14:22:42.352Z 0000131845 40051 NOTICE ContextManager: Updated Mapping Record From Active Directory, cda-request={entity-attr:entity-id:ip=192.168.0.19, entity-attr:op=update, entity-attr:value:domain=MyDomain, entity-attr:value:mapping-origin=MyDomain, entity-attr:value:mapping-type=dc, entity-attr:value:responds-to-probe=true, entity-attr:value:time-stamp=2015-04-21T14:22:34Z, entity-attr:value:user-name=mal}\n

 

So Far I can get a list of the AD Group members who have logged on in the last 7 days using this section, which saves a list of the usernames in alphabetical order in the $adusers variable

# Set Date Parameter to 1 week ago
$date = (Get-Date (Get-Date -Format d)).AddHours(-160)
# Get list of users who are CDA group members and have logged on in the last week
$adusers = get-aduser -filter {( memberof -recursivematch "CN=G-CDAUsers,OU=DomainGroups,DC=mydomain,DC=local") -and (lastlogondate -gt $date)} -Properties SamAccountName, lastlogondate | Select SamAccountName | Sort SamAccountName

 

And I can import the CSV using this section

$CDAMaps = import-csv -Path \\myserver\CiscoCDA.log -Header Time,Priority,Source,Data,ClientIP,A,B,C,D,E,F,Username|Select Time,ClientIP,Username

 

Looking at the $CDAMaps Variable after this gives the following output

Time                                                            ClientIP                                                        Username                                                      
----                                                            --------                                                        --------                                                      
2015-04-21 15:22:23                                             cda-request={entity-attr:entity-id:ip=192.168.0.139             entity-attr:value:user-name=zoe}\n                            
2015-04-21 15:22:23                                             cda-request={entity-attr:entity-id:ip=192.168.2.2               entity-attr:value:user-name=wash}\n                           
2015-04-21 15:22:25                                             cda-request={entity-attr:entity-id:ip=192.168.3.2               entity-attr:value:user-name=river}\n                          
2015-04-21 15:22:29                                             cda-request={entity-attr:entity-id:ip=192.168.0.13              entity-attr:value:user-name=simon}\n                          
2015-04-21 15:22:36                                             cda-request={entity-attr:entity-id:ip=192.168.0.19              entity-attr:value:user-name=mal}\n                            

 

At this point, however I am stuck. I cannot find a way to remove the surplus information from the Client IP and username lines and keep the table intact.

 

Running a command such as

$CDAMaps.ClientIP.TrimStart("cda-request={entity-attr:entity-id:ip=")

Trims the surplus text from the IP Address field, but does not write these changes back to the table.

 

Any suggestions on how to get this table formatted correctly, and how to then compare to my $adusers list to get the two outputs would be greatly appreciated

 

Diolch

[halbaradkenafin]

You'll need to iterate over the CDAMaps with a for loop and assign the new value to each one using something like this:

 

for ($i = 0; $i -lt $CDAMaps.Length; $i++) {

$CDAMaps[$i].ClientIP = $CDAMaps[$i].ClientIP.TrimStart("cda-request={entity-attr:entity-id:ip=")

}

 

Adding in a second line there for the Username with the same idea and it should remove that extra information too.

 

To compare that to a list of users from the AD Group you can use Compare-Object and it will tell you which are in one list but not in the other. You might need to do some fancy work with what objects you're passing to it, probably something like:

 

Compare-Object -ReferenceObject $CDAMaps.Username -DifferenceObject $adusers

 

That's just a guess at what you'd have to call as I haven't tested it myself with any similar data but should be close enough to get you started on it. The output will be a list of names and then either <= or => in the second column, <= means it's in the reference object ($CDAMaps) but not in the AD Group and => means it's in the difference object ($adusers) but not in the $CDAMaps. The output can easily be parsed with a foreach and if statements to check for the <=/=> as they are just strings so -eq will work on them.

[Chris_Jones]

Cheers halbaradkenafin, that got it working.

[mbedford]

Use this function

 

 

 

function removeExtra ($table)

{

foreach ($field in $table)

{

$number = $field.clientip.indexof("ip=")

$number = $number + 3

$field.clientip = $field.clientip.substring($number)

$results += $field

}

return $results

}

 

usage

 

Place this at the start of your script

$results = @()

 

Then use the function like this

removeExtra $CDAmaps

 

Your results should be in $results

 

Thanks

Mike