0

I'm trying to write a simple powershell script to pull software and service data off a machine. So I'm trying to figure out the best way to consolidate the data onto one cvs file instead of two separate csv files. I wanted to use the below, but it would save time in the end for both sets of data to be combined.

Get-Services | Select StartType, Status, Name, DisplayName |
Export-CSV -path $servicescsv

Get-ItemProperty HKLM:\Software\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\* |
Select-Object DisplayName, DisplayVersion, Publisher, InstallDate | 
Export-CSV -path $softwarecsv

So the above is probably quite simple, but I could just create two arrays for software and services. But then what is the best method to combine the two arrays without writing over each other? I know one of the issues is that DisplayName for software conflicts with DisplayName for services. So right now $array3 = $services + $software only adds the software display name to the array, but the other columns are lost.

What is the best approach to this problem?

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
Elminster
  • 1
  • 1
  • how do you want to structure the resulting CSV? note that there is no connection between your two data sets ... [*frown*] – Lee_Dailey Dec 17 '21 at 18:20
  • Probably new columns to contain the software data set. I wasn't really too sure which direction to go to be honest. – Elminster Dec 17 '21 at 18:31
  • I would stick with two csv files if I were you as the data is completely unrelated. Why is it important to you to merge them? – Theo Dec 17 '21 at 18:38
  • Just to minimize the number of files ,but all the data is just being added to a dataset which is represented in PivotTables. The PivotTables would show a report of the software and services for all computers in a network. – Elminster Dec 17 '21 at 19:42
  • You might use this [`Join-Object script`](https://www.powershellgallery.com/packages/Join)/[`Join-Object Module`](https://www.powershellgallery.com/packages/JoinModule) (see also: [In Powershell, what's the best way to join two tables into one?](https://stackoverflow.com/a/45483110/1701026)) which is also able to do a side-by-side join: `Import-Csv $servicescsv | FullJoin (Import-Csv $softwarecsv) -Name Service,Software` – iRon Dec 17 '21 at 19:43
  • @Elminster - there is no relation between the two sets of data. that means you will have to make _something_ to connect them. i presume you could use the computer name. ///// in addition, you will need to define a structure that holds all the various vaguely-related properties. the `Join-Object` module mentioned above may do that for you. – Lee_Dailey Dec 17 '21 at 22:36
  • Joining data (in a relational database) depends on a relationship between the two datasets. This relationship is generally an item of data that is common to both datasets. Here, there isn't any relationship (unless DisplayName contains commn values, which I don't think it does). So no (equi-)join is possible. As far doing a Cartesian join goes, you'll really regret that if your data is big. – Walter Mitty Dec 19 '21 at 13:26

1 Answers1

1

Without an example, of the current CSV files and your desired output, it's not clear what your intended goal is.

I think you could accomplish what you're trying to do like this:

Get-Service |
    Select-Object StartType, Status, Name, DisplayName, DisplayVersion, Publisher, InstallDate |
    Export-CSV -Path $CombinedCsv

Get-ItemProperty HKLM:\Software\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\* |
    Select-Object StartType, Status, Name, DisplayName, DisplayVersion, Publisher, InstallDate |
    Export-CSV -Path $CombinedCSV -Append

The issue is that Export-Csv uses the first objects in the pipeline to identify the available properties. If you need to include properties that don't exist at the start, you should specify all of them. That will cause Select-Object to create new properties that are blank, which will then export appropriately to CSV.

If, on the other hand, your goal is to try to join $servicescsv and $softwarecsv like two SQL tables with a common field... well, you can do that but it's significantly more complicated and requires key fields to join on. I don't think the DisplayName property of the registry key you list and the services module are very likely to agree on a name. Especially because lots of entries in the registry don't have a DisplayName.

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
  • Ok this helps me generate one .csv file for the data. Thank you. Is there a way to temporarily rename the item property DisplayName of software to DisplayNameSoftware so it's in a new column? Maybe establishing an array for software data and then just renaming the first row header names so the two arrays could be combined maybe? Ideally, I would want the CVS file to have this format. StartType, Status, Name, DisplayName, DisplayNameSoftware, DisplayVersion, Publisher, InstallDate – Elminster Dec 17 '21 at 19:29
  • @Elminster It sounds like what you'd want is a [calculated property](https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_calculated_properties?view=powershell-7.2). But I'm still not really clear what you're looking for. It' just doesn't make sense to me. – Bacon Bits Dec 18 '21 at 22:32