CSV alternative to manage requests.

Question

Hello, here i am again to search for the wisdom of the experienced ones :)
What i have:
Domain Users (group memebers) have access to one shared csv file with write rights.
User executes a PowerShell script on his pc. This adds an entry to the csv with computer name and request variable.
Another script on the server is running every minute, reads from the file and executes actions based on pc name and request type.
Once done, the line with corresponding computer name will set with a new request variable once finished.
I made sure the user script makes few reties one second apart if the remote file is locked at that moment by other user or the server.
However i find my solution to be a crude method.

What i am looking for:
I want to replace the csv with a different mechanism that will store the requests in a more object queue like structure.
Where i can access the objects more directly. Something not depending on a csv file.
I am looking for PowerShell native solution, without the need to install additional modules or software on the client side.
However i am open to add something to the server side.

Please let me know what would be a good option that you know off, or are using now.
my regards :)

Answer 1

If you require simultaneous access to the data then using a file-based model isn't going to work. You need a database capable of managing the access. Microsoft SQL server (if you already have one), or any other multi-user database (MySQL, Oracle, etc.) would be your best choice. Microsoft Access is capable of multi-user operation but it requires that the database be split into a cline/server operation -- and that means installing Access on each computer (which could be costly) and installing MS Office products on a server is (I believe) supported. You'd still have to install software on individual computers to get the SQL database drivers.

Another way would be to use a web server to do the work. Installed on a SQL server the driver software wouldn't be required on the clients. You'd have to rewrite the code you're using now, and write more code to run on the web server, but you wouldn't have to install any software on the clients.

Answer 2

Hi @Rich Matheisen ,
you are right about sql, it would be an option for simultaneous access. Thank you for your insightful reply :)

I can try go around the sql driver install on client side by allowing the client to establish a pssession and/or invoke command to the server.
With the SqlServer module on the server execute the sql query. Users need to have database write rights in that case.

Nevertheless, I was hoping for a solution with background jobs or other objects natively supported that i would be able to get and set.
Background jobs are a pain to acces across sessions, but i read somewhere about creating scheduled tasks instead.

Answer 3

Another alternative is to modify the script on the client to create a unique directory on a centralized file share (maybe named using the machine name an user name, if one doesn't exist) and then deposit the information in the directory.

The server script would be modified to look within each directory and, if the last-modified date/time on the csv was more recent than the last time it was checked, process the information.

I can see this as a problem if you have thousands, or tens of thousands, of machines and users log on to more than one machine. The number of directories might become unmanageably large. In that case, splitting the machines by something like their location into a share per location might be helpful.

If your "something other than a csv" is a requirement you might consider an XML or JSON format for the data. Individual elements would then be directly addressable, and you can also add structure to the data instead of dealing with a "flat" representation in a CSV.

Using a PowerShell session might become a problem, depending on the number of simultaneous connections the remote machine is capable of supporting.

Answer 4

hi @Rich Matheisen
a colleague of mine already suggested the one file per user approach, and i considered it because the current need is only for a very small group of around 250+ users.
I did not thought about the last modified date/time, even i use it in a different file-synch script.
However, i agree that if the solution needs to be used on the thousands of company computers it would be not so appealing.

I have not considered XML or JSON as i was not aware it is possible to access individual elements. I have to read some documentation on that.

Invoking session seems to be ok for the small number of users, but i should really consider installing the sql driver on clients.
Also there will be 2 requests per day in average from a single user.

With that being said i have decided to go with your first recommendation - using SQL.
It seems to be the best option and i have not done any script taking or storing data in it, so it is new and exciting :)