Parameter problem for file lookup client setting

Content Authoring
1

(imported topic written by GwyndafDavies)

I have a csv file full of computer hostnames which have specific information for each. e.g. region, country, department etc. in the csv file, they are separated by commas.

I have the following expression that gets the first property in the csv file. This works fine as a stand alone expression and I can retrieve the information from the csv file. However, when I add this as a parameter in an action script in order to set a client setting, I get an error.

prefetch TEST.csv sha1:71ea452e2017cd3d4aac436bc7fc2cd59b6453a1 size:42 http://CentralServer:52311/Uploads/TEST.csv

parameter “Region_1” ="{preceding text of first “,” of (following text of first “,” of (lines whose (preceding text of first “,” of it = computer name as string) of file “actionsite__Download\TEST.csv”))}"

setting “Custom_Region1”="{parameter “Region_1”}" on “{now}” for client

What could I be doing wrong here? the parameter doesn’t seem to be able to work the relevance statement out, however it works fine on it’s own if you run the below via fixlet debugger:

preceding text of first “,” of (following text of first “,” of (lines whose (preceding text of first “,” of it = computer name as string) of file “actionsite__Download\TEST.csv”))

Regards,

Gwyn

2

(imported comment written by gearoid)

Have you tried out the parameter setting part in the action tab of the debugger ?

It’s working in mine. It does error if the computer name isn’t in the file.

Also computer name is a string so you could drop that as string cast.

parameter "compname" = "{preceding text of first "," of (following text of first "," of (lines whose (preceding text of first "," of it = computer name as string) of file "c:\gwt\TEST.csv"))}"
3

(imported comment written by GwyndafDavies)

that works for me too in
the debugger, but i still get the following when I put it in an action though. When I use a parameter I might have to set the working directory before assuming it can reach the file.

4

(imported comment written by gearoid)

You may want want to test for the existence of your computer name in the file before you then parse out the rest of the line.

If your computer name’s not in the file this relevance will set your parameter to an empty string rather than throwing a relevance evaulation error.

if exists first "," of (lines whose (preceding text of first "," of it = computer name ) of file "c:\gwt\TEST.csv") 
then
preceding text of first "," of (following text of first "," of (lines whose (preceding text of first "," of it = computer name ) of file "c:\gwt\TEST.csv")) 
else ""
5

(imported comment written by JasonWalker)

The ActionScript looks at file paths relative to the site name - so if you’re running as a Master Operator, you’re “already in” the actionsite folder. Relative to this path, you’ve downloaded the file to “__Download\TEST.csv”, so your parameter should probably instead be

parameter “Region_1” ="{preceding text of first “,” of (following text of first “,” of (lines whose (preceding text of first “,” of it = computer name as string) of file “__Download\TEST.csv”))}"

(assuming the Relevance is right in the first place, I haven’t built a .csv to test with)

As has already been pointed out, you should really check first that the file exists, and that your hostname is listed in the file.

Once you go a little further with this, you’ll find it’s a real pain to have to update your fixlet with the new sha1 and size of the file every time you update the .CSV. Consider whether the file is small enough to attach it to your Site and have it sent to clients - then they always have the latest version, and you don’t have to update the fixlet when the file changes.

6

I’ve developed this further and have it working however it now fails if there are duplicates in the csv file.

Additionally, I’m trying to extract the relevant line for the particular host name into a property, so I can just look up the property to set each setting (and not do multiple lookups of the file).

so, my relevance looks like the following when it’s working, but fails if there are duplicates:

//sets parameter for location of csv file
parameter “ragcsv” = “{pathname of parent folder of parent folder of client folder of site “actionsite” & “/__BESData/actionsite/__Download/RAG.csv”}”

//checking if the computer exists int he file

if {exists file (parameter “ragcsv” as string) whose (exists (lines of it) whose (it contains ((if computer name contains “.” then (preceding text of first “.” of computer name) else computer name)as uppercase)))}

//some computer names are fully qualified in IEM, but in the csv file, they are hostname only - so this accounts for that

parameter “computer_name” = “{((if computer name contains “.” then (preceding text of first “.” of computer name) else computer name)as uppercase)}”

//set parameter for one of the values in the file
parameter “_Company” = “{preceding text of first “,” of (following text of first “,” of (lines whose (preceding text of first “,” of it = (parameter “computer_name”)) of file (parameter “ragcsv” as string)))}”

setting “_Company”="{parameter “_Company” of action}" on “{parameter “action issue date” of action}” for client

now…this works fine, however I have multiple parameters i’m taking out of the file so multiple lookups of the csv file. I want to set a property to make it more efficient

property “inventory_string” = (lines whose (preceding text of first “,” of it = computer name) of file (parameter “RAG.csv”))

this should set the parameter as, for example: “HOSTNAME,UK,WALES,IBM”

Q: (lines whose (preceding text of first “,” of it = computer name) of file “C:__Temp\RAG.csv”)
A: HOSTNAME,UK,WALES,IBM
T: 1.468 ms

which I should be able to use to get my values for my settings,

however, I cannot use the property if it returns multiple lines form the file if there are duplicates!..

Q: (lines whose (preceding text of first “,” of it = computer name) of file “C:__Temp\RAG.csv”)
A: HOSTNAME,UK,WALES,IBM
A: HOSTNAME,UK,WALES,IBM
A: HOSTNAME,UK,WALES,IBM
A: HOSTNAME,UK,WALES,IBM
T: 2.812 ms

how to do I take just the first result of that…I’ve tried the following but it doesn’t work if there are no duplicates…

//when duplicates exist…

Q: preceding text of first “|” of (concatenations “|” of lines whose (preceding text of first “,” of it = computer name) of file “C:__Temp\RAG.csv”)
A: ADMINIB-TJ8REI0,UK,WALES,IBM
T: 1.142 ms

//when no duplicates exists…
Q: preceding text of first “|” of (concatenations “|” of lines whose (preceding text of first “,” of it = computer name) of file “C:__Temp\RAG.csv”)
E: Singular expression refers to nonexistent object.

7

Try out unique

Q: unique values of lines whose ...

And the relevance guide can be useful online and pdf

8

I gave up trying to kill it in one relevance statement so I used an if statement to use specific relevance for each case:

if (number of lines whose (preceding text of first “,” of it = (parameter “computer_name”) of file (parameter “ragcsv” as string)) > 1

parameter “inventory_info” = “{preceding text of first “|” of (concatenations “|” of (lines whose (preceding text of first “,” of it = (parameter “computer_name”)) of file (parameter “ragcsv” as string)))}”

else

parameter “inventory_info” = “{lines whose (preceding text of first “,” of it = (parameter “computer_name”)) of file (parameter “ragcsv” as string)}”

endif
…

not the most elegant way but it works across the 70K estate :sunny:

1 Like
9

I’m doing something similar. But I found it easier to attach the csv to the site, and then use scripts (VBScript on Windows and BASH on Linux) to parse the csv out into a .ini file.

The shell scripts handle getting the “right lines” from the csv, and build INI file sections matching the host name, subnet address, and some other custom client settings we apply like ‘site’, ‘location’, etc.

Then we use they ‘key X of file Y’ inspectors for easier relevance. When you build the INI file sections in the right order, this allows you to specificy general sttings for a site/location, which can be overriden by more specific settings assigned by hostname.

10

I did consider attaching it to the site but thought it could potentially cause too much network traffic as my CSV file was inventory data for about 95K endpoints, and thus was about 400Kb. It’s since been split into windows and unix so have two smaller files that get distributed based on OS. If I had it on the site I wouldn’t have much control on the distribution, so by doing it within a task I can stagger it across a few hours, and also schedule it to only run under low service hours. Currently thinking about an API call to do it all automatically when a new CSV file is published with up to date info…

My other option was to do something very similar to you Jason, however I was pretty determined to carry on with using relevance after the time I’d invested into it initially :smile:

11

Ok, there is always more than one way to handle it. Are you satisfied it’s working the way you want? I only see a couple of things to tweak at this point.

As pointed out earlier, you can use ‘unique values of strings’ to remove duplicates, if the duplicates are identical. If you have a system listed twice with different data though, you need to decide whether to allow it to fail, or to take the first value. You could use ‘preceding text of first “|” of concatenation “|” of …’ to concat the multiple results, then filter to the first one.

You may want to look up the use of a Manifest file as well. That allows you to keep the size and sha1 of your CSV in a separate file, so you don’t have to update your fixlets every time it changes.

Also the Action Regenerator may be helpful. It can reschedule an action on a schedule. I think it was originally made to distribute antivirus signatures that change frequently.

12

Yes I’m pretty happy with it at the moment - but I will definitely look at using a manifest file as that could make the automation much easier. I’ve not used the action re generator before - I was going to look at what mechanism SUA uses to push a new catalog update and use the same principals.

At the moment the automation bit is not a must though, just something to play with if there’s time. Thanks for the pointers!

13

Here is a single relevance statement that should give you exactly what you need, regardless of duplicates:

preceding text of first “|” of (it & “|”) of concatenation “|” of (lines whose (preceding text of first “,” of it as trimmed string = preceding text of first “.” of (computer name & “.”)) of file “TEST.csv” of folder “__Download” of client folder of current site)

The trick is just to add a “.” at the end of the computer name so that is always at least 1 “.” in the computer name, then get the preceding text of the first “.” which will be either the first part of the FQN or the whole computer name if there is not a “.” in the computer name otherwise.

The same trick is used to add a “|” at the end of the concatenated string of lines of the file so that if there is only a single line (no duplicates), there will still be at least one “|” at the end to get the preceding text of.

ALSO note this: file “TEST.csv” of folder “__Download” of client folder of current site

The above relevance will refer to the file no matter which site the action is taken from. It appears the relevance you were using would only work if taken from the Master Action Site. This might always be the case for this particular task, but it is better to write the relevance so that it can refer to the file no matter which site the action is taken from.

14

This is a related thread: