Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

PowerShell Loop, Compare and Output

Posted on 2011-02-28
8
Medium Priority
?
945 Views
Last Modified: 2012-08-14
Hi all,

I'm just starting with Powershell and I need some help.  I have two files that I need to compare against each other.  I need powershell to compare a similar column in each csv and if information from file1 exists in file2, pull the information from file2 and output.  There is a column with the same name in each file.

I would appreciate a jump start on this.  I'm not having much luck searching for this since I don't really know what to search for.

Thanks.
0
Comment
Question by:jhsaun
  • 4
  • 4
8 Comments
 
LVL 27

Expert Comment

by:KenMcF
ID: 35003704
Can you post an example of the CSV files, and what you want the output to be?
If the one column matches in both files will the other columns match or will there be different data?
Do you want just the data in the column in the output or the whole line, and do you want the to just output to the console?
0
 

Author Comment

by:jhsaun
ID: 35003732
Here's what the headers look like in the CSV files:

CSV FILE1:
MacAddress, IPAddress, Date, Time


CSV FILE2:
SwitchIP, SwitchName, MacAddress, PortNumber

I would like the output to contain all of the information in CSV FILE2 if the MacAddress matches one of the entries in CSV FILE1.  I would then like it to output to HTML.

For example, I might have only one device listed in FILE1, but 4500 in FILE2.  I would only want to see the information from FILE2 for just the one device in FILE1.
0
 
LVL 27

Expert Comment

by:KenMcF
ID: 35003809
You could try something like this


$1 = import-csv C:\temp\1.csv
$2 = import-csv C:\temp\2.csv
$arr = @()
$1 | %{$arr += $_.IPAddress}
$2 | %{
If ($arr -contains $_.SwitchIP){
$_ | Select *
}} | ConvertTo-Html | out-file c:\temp\out.html

Open in new window

0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:jhsaun
ID: 35011035
Ok, that works.  Thanks.

I have one other small problem and then I think I have it.  The second CSV file has spaces between the quote and the data.  This is how it is generated.

Example:  "  00-4a-5f-c4-55-a9   "

Is there anyway that I can tell it to ignore the spaces and only focus on the data (MAC)?  If I manually take out the spaces it is fine, but with them in it my output is blank.

Thanks
0
 
LVL 27

Expert Comment

by:KenMcF
ID: 35011264
Are you comapring the MAC addresses in each CSV file?
0
 

Author Comment

by:jhsaun
ID: 35011295
That is correct.
0
 
LVL 27

Accepted Solution

by:
KenMcF earned 2000 total points
ID: 35011345
You can try this
$1 = import-csv C:\temp\1.csv
$2 = import-csv C:\temp\2.csv
$arr = @()
$1 | %{$arr += $_.MacAddress}
$2 | %{
$MAC = $_.MacAddress -replace " ",""
If ($arr -contains $MAC){
$_ | Select *
}} | ConvertTo-Html | out-file c:\temp\out.html

Open in new window

0
 

Author Comment

by:jhsaun
ID: 35012189
That got it.  Thanks much.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A recent project that involved parsing Tableau Desktop and Server log files to extract reusable user queries for use in other systems. I chose to use PowerShell to gather the data, and SharePoint to present it...
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Loops Section Overview

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question