Parsing a csv

Customizations
1

(imported topic written by SystemAdmin)

I’m putting together a analysis that will hold all of the VMWare data, because we have thousands of guests at many many

sites it gets confusing which VC hosts each guest, using the powershell CLI tools I can query each VC and determine which

host each guest is sitting on. My plan then is to output a csv file with this information and then push that file out to each

guest thats on vmware, then I can have the guest pull it own information from CSV file and report it back to bigfix

here is the information im getting for each guest

“Name”,“PowerState”,“Version”,“Notes”,“Guest”,“NumCpu”,“MemoryMB”,“VMHost”,“VMResourceConfiguration”,“Uid”

the question I have is whats the best way to split this data up, the fields will always remain the same… here is an example

"serverp001",
"PoweredOn",
"v4",
"",
"serverp001:Suse Linux Enterprise Server (32-bit)",
"1",
"512",
"esxhostp002.pek0.domain.com",
"CpuShares:Normal/1000 MemShares:Normal/5120",
"/VIServer=a.domain.wwe00a@serverlp002.pek0.domain.com:443/VirtualMachine=VirtualMachine-vm-1110/" 
"serverp002",
"PoweredOn",
"v4",
"",
"serverp002:Microsoft Windows Server 2003, Standard Edition (32-bit)",
"1",
"512",
"esxhostp004.pek0.domain.com",
"CpuShares:Normal/1000 MemShares:Normal/5120",
"/VIServer=a.domain.wwe00a@server002.pek0.domain.com:443/VirtualMachine=VirtualMachine-vm-113/"

all i want to do is pull the pull field 2 “PoweredOn” from the line where it equals my server name “serverp001”

then field 3 and 4 and 5 and so on

Q: item 1 of it of (line whose(it as uppercase contains “serverp001”) of file “C:\Utils\powercli_scripts\all_guests.csv”)

E: The tuple index 1 is out of range.

Q: (line whose(it as lowercase contains “serverp001”) of file “C:\Utils\powercli_scripts\all_guests.csv”)

A: serverp001,PoweredOn,v4,serverp001:Suse Linux Enterprise Server (32-bit),1,512,esxhostp002.pek0.domain.com,CpuShares:Normal/1000 MemShares:Normal/5120,/VIServer=a.domain.wwe00a@serverlp002.pek0.domain.com:443/VirtualMachine=VirtualMachine-vm-1110/

Q: item 1 of (substrings separated by “,” of(line whose(it as lowercase contains “serverp001”) of file “C:\Utils\powercli_scripts\all_guests.csv”))

E: The tuple index 1 is out of range.

2

(imported comment written by NoahSalzman)

I haven’t finished my coffee… and didn’t feel like searching for how to cast a plural string into set (so that you could use “item 1”)… so I just fell back to the inelegant but satisfying solution:

Q: parenthesized part 1 of matches (regex "^.*,(.

),.

,.

,.

,.

,.

,.

,.

,.*$") of (lines whose (it contains “serverp001”) of file “/Users/noah/Documents/temp.csv.txt”)

A: “PoweredOn”

Q: parenthesized part 1 of matches (regex "^.

,.

,(.

),.

,.

,.

,.

,.

,.,.$") of (lines whose (it contains “serverp001”) of file “/Users/noah/Documents/temp.csv.txt”)

A: “v4”

Q: preceding texts of first “,” of following texts of first “,” of lines whose (it contains “serverp001”) of file “/Users/noah/Documents/temp.csv.txt”

A: “PoweredOn”

3

(imported comment written by MattBoyd)

You just wanted an excuse to use regular expressions.

4

(imported comment written by SystemAdmin)

I think the inspector both of you guys are thinking of is the "tuple string item " inspector:

Q: “thing 1”, now, “thing 3”, 4

A: thing 1, ( Wed, 06 Apr 2011 14:29:01 -0700 ), thing 3, 4

T: 0.062 ms

Q: tuple string item 1 of “thing 1, ( Wed, 06 Apr 2011 14:28:35 -0700 ), thing 3, 4”

A: Wed, 06 Apr 2011 14:28:35 -0700

T: 10.957 ms

This inspector lets you pull out specific items of a string that was originally a tuple. However the key here is that tuples are not CSV, so it will not work on the document your using. Noah’s regex approach is how i would do it too.

-Zak

5

(imported comment written by SystemAdmin)

Awsome thanks guys…

that worked great

1 issue… it seems that i always need to have spaces after the “,” it fails if it’s not formated exactly correct

Q: tuple string item 0 of “serveraccp001, PoweredOn, v4, 1, 512, esxserverhstp002.pek0.domain.com”

A: serveraccp001

T: 0.020 ms

I: singular string

Q: tuple string item 0 of “serveraccp001,PoweredOn,v4,1,512,esxserverhstp002.pek0.domain.com”

E: Singular expression refers to nonexistent object.

6

For anyone else trying to read the contents of a CSV file …

I needed to have my Endpoints parse an Excel file saved as a CSV file.

The task I’m performing is similar to what the Location Wizard does, but because we have several thousand Subnets, using the Wizard is impractical. Instead I decided to use a CSV file to associate Subnet information with Physical Location information and Relay Affiliation information.

I needed to break the comma separated values into their constituent parts and be able to assign them to the correct Settings/Properties.

I found this old forum thread, but apparently the IEM command “tuple string item x of …” doesn’t like strings where the comma is not followed by a space, which is how Excel saves a CSV file (no space). But, I realized I can break the Excel CSV line into it’s comma separated components, and reassemble them with the CONCATENATION command using ", ", then the tuple string item command should work. Problem solved!

Q: (lines of file “NetworkLocations.csv” of (folder "C:")) whose (it as string starts with (“123.456.789.0”))
A: 123.456.789.0,MY BUILDING 10TH FLR,BUILDING1-DEDICATED;*

Q: concatenation ", " of (substrings separated by “,” of (lines of file “NetworkLocations.csv” of (folder "C:")) whose (it as string starts with (“123.456.789.0”)))
A: 123.456.789.0, MY BUILDING 10TH FLR, BUILDING1-DEDICATED

Q: tuple string item 0 of (concatenation ", " of (substrings separated by “,” of (lines of file “NetworkLocations.csv” of (folder "C:")) whose (it as string starts with (“123.456.789.0”))))
A: 123.456.789.0

Q: tuple string item 1 of (concatenation ", " of (substrings separated by “,” of (lines of file “NetworkLocations.csv” of (folder "C:")) whose (it as string starts with (“123.456.789.0”))))
A: MY BUILDING 10TH FLR

Q: tuple string item 2 of (concatenation ", " of (substrings separated by “,” of (lines of file “NetworkLocations.csv” of (folder "C:")) whose (it as string starts with (“123.456.789.0”))))
A: BUILDING1-DEDICATED;*

For anyone who might want to recreate a similar process …

  • I replace (“123.456.789.0”) with (registration subnet address of client as string) (does return < error > on clients that are outside our network, you may need to use a different command).
  • Rather than looking for the file at C:\NetworkLocations.csv, I actually add the file to a Custom Site that All Computers are subscribed to and use the relevance file “NetworkLocations.csv” of (client folder of site whose (name of it = “My Custom Site”)) to reference the file.
  • In my CSV file, the 3rd column (tuple string item 2) is the list of Relay Affiliation Groups that the clients in the given subnet should attempt to contact. The Group names are separated by a semicolon (;).
1 Like
7

Good info, thanks for posting. I’m doing something similar in my environment, attaching a CSV file to a custom site and having the clients parse it out.

I actually end up running VBScript on Windows or a bash script on Linux to convert the CSV into an INI-file format so I can query “key X of file Y” in Relevance. This way my individual fixlets do not need to track which column number contains each setting, instead of “tuple string item 5 of lines whose (tuple string item 0 of it as lowercase = hostname)”, I can query instead for ’ key “Symantec_Server” of file “output.ini” of data folder of client ’

I have a Fixlet trigger updating the INI file, with the relevance "exists file (“myfile.csv” of client folder of current site) AND (not exists (file “output.ini” of data folder of client) whose (modification date of it > file “myfile.csv” of client folder of current site)) so my INI gets regenerated every time I post a new CSV.

The nice thing about INI parsing is that when you query ’ key X of file Y’, you get the first result returned, not multiples; so I parse the CSV into several INI file sections, where each Section Name is tied to a specific Row from the CSV. A client may match mutliple Rows, resulting in multiple INI sections, and if I build the INI with the most specific result first the values override the way I’d like. For instance the CSV for a particular client may end up with an INI file that looks like

[myhostname]
LocalAdminMembers=user1,user2
PhysicalLocation=“US, Texas, Houston, 123 Main St, Room 4232 Floor Item 14”

[MyIPSubnet]
BESRelayAffiliationGroup="123MainSt-Houston-TX"
SplunkServer=splunk2.mydomain.com

[MyDivision]
BESOperatorsGroupSpec=COMPANY-IT-US-WEST

This makes it much easier to enforce configurations that vary between sites, subnets, or individual machines, by keeping things like “AntivirusServer”,“SplunkServer”,“LocalAdminMembers”, etc. defined in the CSV instead of having multiple Fixlets to configure different values for the same configuration item.

8

Hey @TimRice , this is an old thread but it is exactly what we are doing to determine the Location By Subnet, set the relay affiliation based on the location and finally set the failover relays. The Security team is looking to implement a new solution that will require is to use a network range instead of the registration subnet address of client. I can easily add a column to the current CSV with the last IP in the range but how do we create relevance to check for it?

Has anyone else tried setting the Location By Subnet property using a CSV that contains a range instead of the subnet?

9

Yes, there’s actually a radio button in the “Location Property Wizard” to switch to an IP Range format instead of a Subnet Format

10

We don’t want to use the wizard because we have too many subnets. This is why we used a file instead as Tim described. We still used the range in the wizard to help with the relevance but we can’t figure out where we are going wrong.

For example: this is what the wizard provides as relevance for the range

((not exists setting "Location By Subnet" of client) OR ((not exists effective date of it) OR ((effective date of it <= universal time "Mon, 22 May 2023 14:06:48 +0000") AND ((not exists value of it) OR (value of it does not equal ((if (exists ((addresses whose (it as string != "0.0.0.0") of ip interfaces whose (loopback of it = false) of network) as string as version) whose (it >= "192.168.100.0" as version and it <= "192.168.100.127" as version)) then "California" else "N/A") as string))))) of setting "Location By IP Range" of client)

We are currently testing with this statement to pull the location:

tuple string item 1 of (concatenation ", " of (substrings separated by "," of (lines of file "SubLocations.csv" of (folder "C:\temp")) whose (it as string starts with ("192.168.100.0"))))

This works fine but as soon as we switch to a range as per the wizard, we get an error “E: Singular expression refers to nonexistent object.”

Here is the relevance we are using.

tuple string item 1 of (concatenation ", " of (substrings separated by "," of (lines of file "SubLocations.csv" of (folder "C:\temp")) whose (it >= "192.168.100.0" as version AND it <= "192.168.100.127" as version)))