Best way to simplify

ericrobinson7 · 2018-06-07T17:30:15.000Z

I have a CSV fie with over 4000 rows. There are 2 columns that I want to filter on and I want to be able to simplify my where statement if possible. I was looking at building an array, but that doesn’t work. The more and more I exclude the longer this line gets.

Can anyone think of a way to simplify this Where-Object?

where-object {($.Type -Like “Windows” -or $.Type -Like “Server” -or $.Type -Like “mac” -and $.type -NotLike “Management”)} | Where-object {($.device -notlike “Datto” -and $.device -notlike “SAN*” -and $.device -notlike “QNAP” -and $.device -notlike “barracuda” -and $.device -notlike “controller” -and $.OS -notlike “Linux NSM*” -and $.Device -NotLike “ilo” -and $.Device -NotLike “mas90”)}

Neally · 2018-06-07T17:36:06.000Z

Welcome.

If you post code, please use the ‘Insert Code’ button. Please and thank you!

PLEASE READ BEFORE POSTING! Read if you're new to the PowerShell forum! Programming & Development

Hi, and welcome to the PowerShell forum! Don’t apologize for being a “noob” or “newbie” or “n00b.” There’s just no need – nobody will think you’re stupid, and the forums are all about asking questions. Just ask! Use a descriptive subject. Don't say "Need help" or "PowerShell Help", actually summarize what the problem is. It helps the rest of us keep track of which problem is which. Don’t post massive scripts. We’re all volunteers and we don’t have time to read all that, nor will we copy, past…

Can you share the entire code? Sometimes you can filter as well.

It’s tough to say if you can simplify it.

tulioarends · 2018-06-07T18:53:03.000Z

Please do as Neally asked in the future.

As to your problem, REGEXs to the rescue!

where-object {($_.Type -iMatch "windows|server|mac" -and $_.type -iNotMatch "management" -and $_.device -iNotMatch "Datto|SAN|QNAP|barracuda|controller|ilo|mas90" -and $_.OS -iNotMatch "Linux NSM?")}

Note that i specifically used the -i versions of -Match and -NotMatch to explicitly denote a case insensitive operation.

Give it a test.

@alexw

psophos · 2018-06-07T18:56:38.000Z

The more and more I exclude the longer this line gets.

It happens.

Though I’m not sure the regex solution will be simpler, it certainly is a powerful option.

What are you trying to achieve?

onecogmind · 2018-06-07T18:58:08.000Z

I apologize for this suggestion as I know your looking for a powershell way… but might be a whole lot easier to dump into Excel and just filter it out… just sayin…

EDIT: Ohh your first post! Welcome to Spiceworks! Hope my comment didnt deter you away just trying to help you get a task accomplished as quickly as possible.

tulioarends · 2018-06-07T19:13:20.000Z

M Boyle:

The more and more I exclude the longer this line gets.

It happens.

Though I’m not sure the regex solution will be simpler, it certainly is a powerful option.

What are you trying to achieve?

I reduces the conditional operators (-and/-or) to just 4, logically groups what needs to be excluded and included and does the entire filtering in a single pipeline stage.

For further improved readability you could rewrite it like this as part of a script:


```
$typesWanted="windows|server|mac"
$typesNotWanted="management"
$devicesNotWanted="Datto|SAN|QNAP|barracuda|controller|ilo|mas90"
$osesNotWanted="Linux NSM?"
.....
where-object {($_.Type -iMatch $typesWanted -and $_.type -iNotMatch $typesNotWanted -and $_.device -iNotMatch $devicesNotWanted -and $_.OS -iNotMatch $osesNotWanted)}
```

Now it would be easier to tweak the exclusions.

Edit: grammar.

psophos · 2018-06-07T20:45:54.000Z

I reduces the conditional operators (-and/-or) to just 4, logically groups what needs to be excluded and included and does the entire filtering in a single pipeline stage.

I was just referring to the learning curve that comes with regex in the first place.

The re-written code is excellent and has now been added to my local pile-o-code

ericrobinson7 · 2018-06-08T18:09:30.000Z

get-content "C:\Working\Hardware Inventory Report.csv" | Select -Skip 95 | Set-Content "C:\Working\Invtemp.csv"
Import-CSV c:\working\invtemp.csv |Select @{N="Client";E={$_.txt_HW_AssetsTable_CustomerName}},@{N="Type";E={$_.txt_HW_AssetsTable_DeviceClass}},@{N="Device";E={$_.Device_Name}},@{N="Model";E={$_."Make_Model"}},@{N="OS";E={$_.Reported_OS}},@{N="LastUser";E={$_.Last_Logged_In_User}},@{N="Checked";E={$_.Last_Discovery_Date}} | 
where-object {($_.Type -NotLike "Switc*" -and $_.Type -NotLike "Prin*" -and $_.Type -NotLike "othe*" -and $_.Type -NotLike "stor*" -and $_.Type -NotLike "*management*") -or 
($_.Type -NotLike "Generic Server" -and $_.Device -NotLike "SAN"} | Sort Client | Export-csv C:\Working\hdinv.csv -NoTypeInformation

Sorry wrong code

tulioarends,

You still Rock Dude. I was looking at Regex, but I’m horrible when it comes to that.

ericrobinson7 · 2018-06-08T18:14:08.000Z

$devobj = "*Datto*","SAN*","*QNAP*","*barracuda*","*controller*" 
$OSobj = "Linux NSM*"
$typobj = "*Windows*","*Server*","*mac*","*Management*"
get-content "C:\Working\Hardware Inventory Report.csv" | Select -Skip 95 | Set-Content "C:\Working\Invtemp.csv"
Import-CSV c:\working\invtemp.csv |Select @{N="Client";E={$_.txt_HW_AssetsTable_CustomerName}},@{N="Type";E={$_.txt_HW_AssetsTable_DeviceClass}},@{N="Device";E={$_.Device_Name}},@{N="Model";E={$_."Make_Model"}},@{N="OS";E={$_.Reported_OS}},@{N="LastUser";E={$_.Last_Logged_In_User}},@{N="Checked";E={$_.Last_Discovery_Date}} | where-object {($_.Type -Like "*Windows*" -or $_.Type -Like "*Server*" -or $_.Type -Like "*mac*" -and $_.type -NotLike "*Management*")} | Where-object {($_.device -notlike "*Datto*" -and $_.device -notlike "SAN*" -and $_.device -notlike "*QNAP*" -and $_.device -notlike "*barracuda*" -and $_.device -notlike "*controller*" -and $_.OS -notlike "Linux NSM*")} |Sort Client | Export-csv C:\Working\Inventory.csv -NoTypeInformation

wrong code above This is the correct code

tulioarends · 2018-06-11T13:29:29.000Z

ericrobinson7:

$devobj = "*Datto*","SAN*","*QNAP*","*barracuda*","*controller*" 
$OSobj = "Linux NSM*"
$typobj = "*Windows*","*Server*","*mac*","*Management*"
get-content "C:\Working\Hardware Inventory Report.csv" | Select -Skip 95 | Set-Content "C:\Working\Invtemp.csv"
Import-CSV c:\working\invtemp.csv |Select @{N="Client";E={$_.txt_HW_AssetsTable_CustomerName}},@{N="Type";E={$_.txt_HW_AssetsTable_DeviceClass}},@{N="Device";E={$_.Device_Name}},@{N="Model";E={$_."Make_Model"}},@{N="OS";E={$_.Reported_OS}},@{N="LastUser";E={$_.Last_Logged_In_User}},@{N="Checked";E={$_.Last_Discovery_Date}} | where-object {($_.Type -Like "*Windows*" -or $_.Type -Like "*Server*" -or $_.Type -Like "*mac*" -and $_.type -NotLike "*Management*")} | Where-object {($_.device -notlike "*Datto*" -and $_.device -notlike "SAN*" -and $_.device -notlike "*QNAP*" -and $_.device -notlike "*barracuda*" -and $_.device -notlike "*controller*" -and $_.OS -notlike "Linux NSM*")} |Sort Client | Export-csv C:\Working\Inventory.csv -NoTypeInformation

wrong code above This is the correct code

Do you still need help with this? I don’t mind sorting this out.

If so let me know if you want it optimized for speed or readability.