Using TEM to update Muliple Endpoints from a DB or txt file

Customizations
1

I have a project that I have been ask to perform. The basis behind the project is that the Servers administrators group has a database they keep pertinent server information. I am able to pull the data from their db, parse it and tell me what is different between yesterday and today. I need to then write that information into a registry location on each endpoint…

What I am trying to figure out is what is the best way to go about taking the data, either from db or csv file and write it to the correct endpoint using the computer name as a reference and have it do all the endpoints without having to create a new task for each one. I would like to have a task that runs daily and does this. Someone suggested using Powershell, but unfortunately we still have some 2003 servers that don’t have that installed and aren’t allowed to install it. Any other suggestion would be greatly helpful. I appreciate your help.

Troy

2

I am doing something similar with tagging my systems. I wrote an SSIS job that dumps out my information from the SQL server to an FTP server and is on a daily schedule at 11:00PM. Then at midnight I pull those files down and either set a registry value or use the data directly, like setting my POS system sales tax rates. I will throw in that I am doing this with a 26kb vb.net app to do some of that background processing and using endpoint manager to just push my application down to my targets, so your mileage may vary.

3

You could have the Endpoint download the entire CSV file, then use Relevance to parse the CSV file for the row that contains the Endpoint’s information and store it in the registry. This would be the easiest way to do this with a single task.

3 Likes
4

We do this several ways from various systems. In one case, we do relevance substitution to build a unique SQL to that endpoint/location, then query the database directly. If your database uses LDAP authentication, then runascurrentuser could be leveraged to get the credentials to authenticate to the database. Otherwise credentials could be either decrypted on demand or compiled into a small executable.

For some other ERP systems, we dump out data either daily or ad hoc into a directory structure based on particular attributes that correspond to unique endpoint. We web-enable the folder structure, then have the endpoint download using relevance substitution within the url. Note that this particular method intentionally by-passes the relay hierarchy by design and goes directly to the web server (in our case on an ERP system).

download now as download_file_name http://web_server/ERP_app/{unique attribute that corresponds to endpoints}

The unique attribute(s) could be in either folder names and/or file names in the web structure. This works well for a single file.

We use both approaches successfully. This works great for dynamic data such as from an ERP system. The caveat is that you have to accept the that these downloads are not signed. That is the trade-off for real-time dynamic data without continually taking new actions.

Both approaches assume thoughtful conventions for endpoints that can be leveraged to correlate with attributes in the database.

1 Like
5

A third approach (twist on the second approach in my previous post) that also works well in situations where your target consists of multiple dynamically named files such as an ERP system would generate.

Use relevance substitution to generate a wget script to facilitate getting multiple dynamic files or even a whole web.

wget.exe http://web_server/ERP_app/{unique attribute that corresponds to endpoints}

Note: There are many switches and options with wget that can assist you in getting the desired effect.

6

Hi,

Can someone help to do some client setting using csv file. csv file format shown as below.

7

Something like this:

setting "{ tuple string item 1 of line whose(it starts with (computer name) AND (preceding text of first "," of it as trimmed string = (computer name))) of file "client_settings.csv" }"="{ tuple string item 2 of line whose(it starts with (computer name) AND (preceding text of first "," of it as trimmed string = (computer name))) of file "client_settings.csv" }" on "{parameter "action issue date" of action}" for client

I think in general it is probably not a great idea to have many custom client settings. A few are probably fine, but I wouldn’t recommend replicating the data from one system into the other completely. Instead I think it makes sense to create a mapping between BES Computer ID and/or the Hardware Serial Number and whatever the primary ID is in the secondary system. Then use session relevance / APIs to query a combined result on the fly in the other system or a web page.

8

Hi,

I would like to say thanks a lot to you for your quick response and I am sorry to ask more questions related to my previous query.

Do you think that first we need to validate the computer name whether that computer is present or not in IEM console then do the corresponding setting given against that computer name ?

Thanks
Niraj

9

Hi,

I did little bit modification with your given syntax as it was not working. Could you please advise me whether I am doing right or wrong.

setting “{ preceding text of firsts “,” of (following texts of firsts “,” of lines whose (it starts with (computer name )) of file “C:\Temp\test.csv”) }”="{ following text of firsts “,” of (following texts of firsts “,” of lines whose (it starts with (computer name )) of file “C:\Temp\test.csv”) }" on “{parameter “action issue date” of action}” for client

Thanks
Niraj

1 Like
10

I have made some progress but am still in need of guidance. I am able to successfully get the data pulled from the database and then we do a job that finds what has actually changed. This is then extracted into server.csv. Here is an example of what the file contains.
Server,winDomain,sn,manufacturer,description,model,os,location,techcontact,bus_owner,environment,whencreated,client,application,pci_roc,monitor,patch_no_reboot

NCTS1,local.domain,KQAXK9Y,IBM,P | NDC | M-AJ64 | Terminal Server | DTOPS,eserver xSeries 346 -[884045U]-,Windows Server 2003,NDC,OP DTOPS,Distributed Technologies,LAN,2005-11-16 19:52:00,TDR,Terminal Server,None,NULL,NULL
NCTS2,local.domain,KQAXK8Y,IBM,P | NDC | M-AJ64 | Terminal Server | DTOPS,eserver xSeries 346 -[884045U]-,Windows Server 2003,NDC,OP DTOPS,Distributed Technologies,LAN,2002-08-17 02:51:00,TSYS Internal,Terminal Server,None,NULL,NULL

My plan is to upload to all the servers every night a copy of the server.csv file, then have the endpoint parse it looking for its computer name, then creating the registry entries or modifying the ones that have changed.

I tried to do some of the registry editing, but it always fails, I am not sure it likes my relevance substitution. Here is the relevance I am trying to use.

action uses wow64 redirection false

delete __createfile
delete wizardedit.reg

createfile until @end_create_reg_file
Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Software\MyCompany\Servers]
“Description”={preceding text of first “,” of following text of first “,” of following text of first “,” of following text of first “,” of following text of first “,” of lines starting with (computer name) of file “C:\Program Files (x86)\BigFix Enterprise\BES Client\custom\Server.csv”}
@end_create_reg_file

move __createfile wizardedit.reg
waithidden regedit /s “wizardedit.reg”

Thank you so much for the great thoughts, any other help is greatly appreciated.

11

That is actually how I initially was going to do it but then I thought I’d try “tuple string item” since it might work, and if it does, then “tuple string item” will work for a more arbitrary set, rather than just a key/value pair. I didn’t have a sample CSV to work from, so I wasn’t certain “tuple string item” would work for you. The issue is that “tuple string item” is very picky about there being a comma followed by a single space.

There are some minor issues with your relevance. You should add:

 ( it as trimmed string ) of

to the beginning to strip out any possible white spaces that might be present at the beginning or end of the key/value pair being parsed out of the CSV file.

The other problem is that you are only checking that the line starts with the computer name, but you are not validating that it starts with EXACTLY the computer name, which is not the same. If you had one computer named ABC1 and you had another named ABC123, then the computer name ABC1 would match both lines, which would cause the relevance to fail or cause unexpected results.

So to break it down, this should give you the correct line of the CSV file:

line whose ( ( it as lowercase starts with (computer name as lowercase) ) AND ( preceding text of first "," of it as trimmed string as lowercase = (computer name as lowercase) ) ) of file "C:\Temp\test.csv"

This should be the setting name:

( it as trimmed string ) of preceding text of firsts "," of (following texts of firsts "," of lines whose ( ( it as lowercase starts with (computer name as lowercase) ) AND ( preceding text of first "," of it as trimmed string as lowercase = (computer name as lowercase) ) ) of file "C:\Temp\test.csv")

This should be the setting value:

( it as trimmed string ) of following text of firsts "," of (following texts of firsts "," of lines whose ( ( it as lowercase starts with (computer name as lowercase) ) AND ( preceding text of first "," of it as trimmed string as lowercase = (computer name as lowercase) ) ) of file "C:\Temp\test.csv")

This should be the combined action script:

setting "{ ( it as trimmed string ) of preceding text of firsts "," of (following texts of firsts "," of lines whose ( ( it as lowercase starts with (computer name as lowercase) ) AND ( preceding text of first "," of it as trimmed string as lowercase = (computer name as lowercase) ) ) of file "C:\Temp\test.csv") }"="{ ( it as trimmed string ) of following text of firsts "," of (following texts of firsts "," of lines whose ( ( it as lowercase starts with (computer name as lowercase) ) AND ( preceding text of first "," of it as trimmed string as lowercase = (computer name as lowercase) ) ) of file "C:\Temp\test.csv") }" on "{parameter "action issue date" of action}" for client
12

Try this:

regset64 "[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Software\MyCompany\Servers]" "Description"="{ tuple string item 4 of concatenation ", " of (it as trimmed string) of substrings separated by "," of line whose ( ( it as lowercase starts with (computer name as lowercase) ) AND ( preceding text of first "," of it as trimmed string as lowercase = (computer name as lowercase) ) ) of file "C:\Program Files (x86)\BigFix Enterprise\BES Client\custom\Server.csv" }"

Note: ( tuple string item (number) ) uses 0 for the first item. So “winDomain” should be tuple string item 1.

1 Like
13

This worked very well, I just need to add the additional fields to the string so it writes all the settings, but that shouldn’t be too hard. Thank you VERY much for the help. This puts me way ahead of the curve for getting this completed

1 Like
14

Additionally, you should never reference a file like this:

file "C:\Program Files (x86)\BigFix Enterprise\BES Client\custom\Server.csv"

There is a much better and more dynamic way to do this.

See this analysis for ways to use relevance to reference BES Client folders dynamically: http://bigfix.me/analysis/details/2994718

In particular there is the property for the data folder:

( (if (exists properties whose(it as string starts with "data folder of " AND it as string contains "client")) then (data folder of client) else NULL)|(folder "/var/opt/BESClient/__BESData")|(folder "/Library/Application Support/BigFix/BES Agent/__BESData")|(folder "__BESData" of parent folder of client) )

Which on windows will generally be:

C:\Program Files (x86)\BigFix Enterprise\BES Client\__BESData

So to reference “Server.csv” dynamically, do the following:

file "Server.csv" of folders "CustomSite1" of ( (if (exists properties whose(it as string starts with "data folder of " AND it as string contains "client")) then (data folder of client) else NULL)|(folder "/var/opt/BESClient/__BESData")|(folder "/Library/Application Support/BigFix/BES Agent/__BESData")|(folder "__BESData" of parent folder of client) )

This should work regardless of where the client is installed.

You can extend this same concept to reference the Logs folder:

folder "Logs" of folder "__Global" of ( (if (exists properties whose(it as string starts with "data folder of " AND it as string contains "client")) then (data folder of client) else NULL)|(folder "/var/opt/BESClient/__BESData")|(folder "/Library/Application Support/BigFix/BES Agent/__BESData")|(folder "__BESData" of parent folder of client) )
1 Like
15

The best option for dynamically referencing a program location other than the BES Client is to use its “InstallLocation” value in the uninstall key of the registry.

This example uses the BigFix client, but it really should be applied to other programs, and less so the BigFix client.

folders "Logs" of folders "__Global" of folders "__BESData" of folders ( (it as string as trimmed string) of values "InstallLocation" of keys whose(value "DisplayName" of it as string as lowercase contains "endpoint manager client") of keys "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall" of (x64 registries;x32 registries) )

The above relevance concept should work to reference files or folders for any software which properly sets the “InstallLocation” value. This is great if the program happens to be installed on a different volume, or it will also work regardless of the client system being x32 or x64 bit since it does not hardcode the program files location. This even works in cases where “Program Files” is not in english, in which case you can’t even assume that the installation folder starts with “Program Files”.

16

Here is the relevance I used:
regset64 “[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Software\TSYS\Servers]” “Manufacturer”="{ tuple string item 3 of concatenation ", " of (it as trimmed string) of substrings separated by “,” of line whose ( ( it as lowercase starts with (computer name as lowercase) ) AND ( preceding text of first “,” of it as trimmed string as lowercase = (computer name as lowercase) ) ) of file “Server.csv” of folders “ServerInfo” of ( (if (exists properties whose(it as string starts with "data folder of " AND it as string contains “client”)) then (data folder of client) else NULL)|(folder “/var/opt/BESClient/__BESData”)|(folder “/Library/Application Support/BigFix/BES Agent/__BESData”)|(folder “__BESData” of parent folder of client) )

Here is the response I got in the log file

Command failed (Relevance substitution failed) regset64 “[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Software\TSYS\Servers]” “Manufacturer”="{ tuple string item 3 of concatenation ", " of (it as trimmed string) of substrings separated by “,” of line whose ( ( it as lowercase starts with (computer name as lowercase) ) AND ( preceding text of first “,” of it as trimmed string as lowercase = (computer name as lowercase) ) ) of file “Server.csv” of folders “ServerInfo” of ( (if (exists properties whose(it as string starts with "data folder of " AND it as string contains “client”)) then (data folder of client) else NULL)|(folder “/var/opt/BESClient/__BESData”)|(folder “/Library/Application Support/BigFix/BES Agent/__BESData”)|(folder “__BESData” of parent folder of client) ) (action:28768)

I think it has something to do with the addition of the last part for the besclient folder stuff. Any thoughts?

17

I would test it in the Fixlet Debugger on the client you are testing against. I also recommend creating an analysis property for parts of the relevance to make sure it works.

Why is the file located in “/ServerInfo/Server.csv” of the BES Data folder? is this a site file? how is the file getting there in the first place?

18

You seem to be missing a final }"

19

I was able to make some progress on this project and have been able to get it to write to the registry successfully everytime now, here is what I used.

regset64 “[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Software\TSYS\Servers]” “Manufacturer”="{ tuple string item 3 of concatenation “, " of (it as trimmed string) of substrings separated by “,” of line whose ( ( it as lowercase starts with (computer name as lowercase) ) AND ( preceding text of first “,” of it as trimmed string as lowercase = (computer name as lowercase) ) ) of file “Server.csv” of folder “ServerInfo” of parent folder of regapp “besclient.exe” }”

Now my problem is the following
Server.csv
Failed
[-]
[+] Download error: "Unexpected HTTP response: 403 Forbidden"
Download requested on server:
URL: http://BigFix:52311/Uploads/ServerInfo/Server.csv
Hash:
Size:
Next retry: 7 minutes. Retry now

However, if I paste the URL into a web browser either on my pc or on the server, it displays the information just fine. Not sure why it would be doing this from within the application.

20

What is the actionscript that is being used to download the file to the client?

It might be that the URL is not whitelisted for no-hash downloads.