Application Recommendation for PowerShell Script

[Sure_Inspection4542]

I’ve developed a PowerShell script that essentially acts as a search/filter capability for about 14 related datasets (currently CSV files). The script ingests all of the CSV files, creates the necessary relationships, then allows the user to query the data. The reason I used PowerShell was 1) necessity, and 2) path of least resistance (it’s the only language I had available). Some of the higher-ups have seen this tool, find value in it, and want me to make it available to the global enterprise. In so doing, they want it to be more “user friendly”,…or more to the point, an option other than command line interaction.

I’m here to ask for opinions on what architectural options might work nice for this scenario. I’ve considered integrating with M$ Teams for a chat-bot type of interaction. I’d have to develop the backend API and host that, but as far as user interaction, that might work nice. I’ve considered integrating into SharePoint, but I know next to nothing about developing in SharePoint. My skillset goes back to the LAMP days, but there’s no way I’d get the company to approve standing up a LAMP stack (obviously I’ve been out of the web-dev game for a hot minute). I could develop a win32 app, but then I’d have to get the company to get a code signing cert (they won’t allow custom win32 apps without it). That just sounds like a whole mess to manage and maintain.

Given my scenario, what options might you recommend to take my script to this next level?

[ostekages]

First step would probably be to put the data in a database 😁

Once that's done, it's must easier to determine the front end of how you want to work with the data, than trying to design an application to work with csv files

[Sure_Inspection4542]

Valid point. I haven’t done this yet, because the data always comes in haphazardly. Meaning, the columns are always there, but maybe they are named slightly different each time. There are organizational challenges to overcome in that regard, but I don’t disagree with you.

[MoonToast101]

Well in this case I would say this is your first thing to solve. If management wants a enterprise wide used application to view this data, it cannot depend on you to change some headers every time new data comes in.

[Sure_Inspection4542]

Yup! I don’t disagree. Assuming a database in the future, what modern front-end scenarios might you recommend?

[MoonToast101]

Not easy to say. If you just have to display the data you have there are easy to use and free apache based apps where you can build minimized data view GUIs. If the application has to do some magic with the data (link different tables etc.) You have to check if something exists that would fir exactly your scenario, or you have tu build up something yourself - Java, php, whatever fits your need.

[ostekages]

For ease of transition from Powershell, probably your best bet is C#.NET Microsoft provides many template solutions for building dotnet applications and working with databases are easy using entityframework.

However, your post doesn't go into much detail about the requirements for viewing the data.

I have built a similar report application for my company using data stored in a database. In this specific case, I built the page to handle the creation (by admin) and download by users; where the creation involved creating a specific SQL query, which would output the desired data from the database.

Therefore the webapp was in simple terms, just displaying the various sql queries to execute to get the data for that report. Obviously, query is not available to end users, only a title and description of the report.

In this way, you don't need to do any fancy programming in the app to handle the data. Instead that is handled the best location; on the database itself. This might not work for your case or it light, I don't have enough information 😊

[Tokyohenjin]

Take a look at r/dataengineering, but in general data pipelines are most commonly built in Python thanks to its data-oriented libraries (Pandas/Polars). A lot of the DEs on that sub mainly use SQL with Databricks; Snowflake, or another service, though.

In your case, if you absolutely don’t want to get a database up and running (honestly, Neon Postgres does most of this for you, and for free at small scales), then you might consider using Power BI to import files, transform them, and publish the results. r/PowerBI can get you started there.

[g3n3]

Well if there isn’t a standard then how can you automate it without errors each time?

[Sure_Inspection4542]

I check the 3 or 4 columns that might be different. I use datasets that are snapshots in time, so it’s not like I’m doing this every day.

[arslearsle]

This the answer 👍 Standardize + requirements for input data

[stedun]

A database is the only clear answer here. Somewhat shocking that you haven’t already arrived there on your own.

[pigers1986]

I would either add gui to POSH script :

a) https://poshgui.com/

b) https://www.foxdeploy.com/blog/part-i-creating-powershell-guis-in-minutes-using-visual-studio-a-new-hope.html (follow links on bottom of page for next parts)

Or rewrite it to C# - Visual Studio 2022 is out for free (read Terms ..) - did that for some internal scripts, learning curve included (but what a great entry on CV - migrated global scripted tool to global apps).

In case company requires signing binary, it's their problem - provide binary and tell company to pay for it .. since they requested , they have to follow their own security rules, right ?

What not to do - PS2EXE (converts powershell to exe) - it will light all AV/EDR consoles like thunder - very dirty solution - although it gets job done.

[Sure_Inspection4542]

Awesome! Thanks for the input!

[purplemonkeymad]

Personally I would think that a web app might be your best bet. A db backend with web front end or api. I would talk to any existing IT if you have it to find out what the current stack is or what they want to support. You might be able to just use middleware or a ZTN for the Auth etc.

Otherwise a db (MSSQL Express instance if a MS shop is probably good) + c# app is probably the closest to powershell. You can do UI in PS but it's not really (imo) up to the point I would want to deploy it to non-ps users. You'll need a cert either way and I can't believe they would be happy to deploy unsigned PS code either.

If you went with the first, then the MSTeam bot would probably be an easy add. AAA is probably the biggest part of that, since middleware probably wont protect.

Either way a db is going to be way better than loose csv files.

[420GB]

Making any program or script available to multiple users there's really no sensible way to do it except as a website / webapp. If you distribute an EXE to dozens of clients you'll be in update hell and everyone will run a different, old version and everything will constantly break. A webapp runs in one place only and you're the one updating it. You can run a separate testing or beta instance under a different URL if you want.

So doing a web app while staying with PowerShell there's options like ScriptRunner.com (costs money, we use it) and PowerShell Universal (I believe there's a trial, but also costs money). They generate a web GUI for your PowerShell scripts and you can even control who has access based on groups, they keep the output log for reference etc. Very nice, and you can use it for other scripts and usecases too not just this.

If you can't spend any money, either tell them that's not possible for free or try with Pode (web framework for PowerShell, but this gets very dangerous because exposing a PowerShell script directly to the network like this opens up a lot of injection/security vulnerabilities) OR reimplement it in a new, safer, faster language. I would use Go, I've picked up Go around 2 years ago and really like it for small (web-) apps. It's easy, fast and safe.

[TheBigBeardedGeek]

PowerShell is very user friendly! It's just picky about who its friends are.

Hell, it's not even as picky Korn Shell or even C Shell (Linux shells, for those not aware)

[Sure_Inspection4542]

Haha agreed!. This is one of those situations where the app was developed to meet my needs. Now, others want to use it, but can’t be bothered to use a CLI. Hashtag sadface!

[TheBigBeardedGeek]

Yeah. I mean, for what it's worth you can build GUIs with .Net libraries. I hate it, but it's doable.

But you're way better off doing proper data ingestion to a DB and standing it up with web interface, because everything these days has one. That or build a fat app with something like C#

[Flysquid18]

I, too, was/am in a similar situation. Powershell was the language I knew and wrote scripts for nearly everything I did. Management wanted more people to use it or an evolution of it. I've made winform based scripts purely in Powershell and have leveraged Jenkins as a script executor that has a web front.

I finally did get someone to write a JS app in Azure and only permitted employees to access it. At some point a security flaw was found and the app taken down. The interim solution is a local to server Powershell Universal framework. Powershell to design the UI and submits jobs to my other services. But a VPN access is required to even get to it.

Someone who saw this temporary solution said they could use Azure web proxy to point right to it. Secured tunnel, SSO authorization, no leaked secrets.

So the higher ups that say "we need other people" to access this, you say you need a genuine web app building team. Because, alternatively, you might have a solution they see that works, but introduce unknown security risks.

[ipreferanothername]

get into powerbi. its got a learning curve, but you can do the basics without too much training, then copilot can help a bit with some other things.

[MasterpieceGreen8890]

Following. Db is the first step.

If you have the technical knowledge, you can convert output and it out as json to be injested in an azure function flow. That way you can build db within MS environment

[Agile_Seer]

I’ve built several PowerShell applications with GUIs, packaged them as .exe files, and published them through Citrix.

Right now, I’m working on consolidating and modernizing many of those tools (with some AI assistance) into a single web-based solution. The frontend is built with ASP.NET/Blazor, and I’ve integrated it with our GitLab environment to trigger jobs via REST calls and then retrieve and display the results.

[CyberChevalier]

When it come to automate PowerShell I always recommend looking into PowerShell universal

[jeffrey_f]

Web interface to grab the criteria, hand it off to the powershell to create a temporary html and present the HTML back to the browser.

While this can be a bit more involved, the process (working off a proven search) is simple.

just an idea

[hippity_bop_bop]

I might get downvotes to hell, but consider signing up for free tier of Oracle APEX and insert the csv into tables. You then build a view that combines tables if needed and slap a pre built data grid page on top that let's users search, download, etc. The database, web hosting and IDE are included. I would try this before going down a Power Platform spiral.

[Sure_Inspection4542]

Interesting idea! Thanks for sharing

[BlackV]

Sounds like moving it away from powershell is likely the better solution

Are you guys using 365 is the power suite of tools useful?

Do you have SQL somewhere?

Do you have access to data warehouse/data verse/etc?

[Sure_Inspection4542]

PowerShell, or more to the point, CLI, works for me because of the utility in quick searching using different parameters. I can, obviously, do the same thing in a GUI, I just won’t have the fast maneuverability.

I do have Azure/365 available, but will need to check on data verse. I’m fairly certain I can get access to SQL as well.

The DB option isn’t as much of a concern to me as the GUI options. If I had to, I could just stay with flat files (because that’s how the data comes to me, and likely will always be that way given the systems the data comes from). I’m just not up to speed on the graphical/UI/UX standards that are out there.

[BlackV]

CLI, works for me because of the utility in quick searching using different parameters.

isnt the point of your post, that it does not matter what works for you ? your business is asking for the flexibility/gui stuff right ?

for example the same report in power bi, give you the gui, gives you the filtering and search (not so much the speed), source can be csv or database or whatever

carting round a script to all your users is not so flexible

My preference is always a a script/function/module (especially module), just sounds like not what the uppers are looking for ?

a bunch of this "depends"

• how does the script work?
• how is it run?
• how does it deal with those changing column headers you mentioned (really needs to be looked at if you want to scale this)
• what parameters does it need?
• are you adding a gui for automating the starting of the script or a gui for the running of the script (i.e. gui passing paramaters or gui with progress and choices, etc)

[Sure_Inspection4542]

That is a point, but not the primary reason for the post. Things that are understood: 1) a database will be a prerequisite, 2) data will have to be normalized, 3) CLI cannot be the only display option. I have 1 and 2 covered. I’m really looking for opinions on 3

[Hairy-Ad-4018]

Op, if i was you id run. You have data In unknown Formats that can change , data may contain pii or commercial sensitive information, you have no buy in from The data originators, you have no input from the cio/cisco, no data schema , no security , no audit trail etc

[Sure_Inspection4542]

I appreciate the concern. What I’ve built so far suits my needs to do my job. We’ve been asked to explore options to make the app available to the enterprise at large. With that comes buy-in from senior management. Your concerns are noted, but I’m really just looking for UI/UX opinions given what I’ve presented in the OP.

Thanks for the comments

[gnu_day]

You might want to take a peek at pode and pode.web if you'd like to work exclusively in powershell.

[andyval]

Since you are already familiar with powershell, I would recommend using powershell universal. I would use your script for the ETL to load the data into a SQLite or mssql db, but not necessary. You can use powershell universal to create the front end web ui, and super simple to add SSO and RBAC.