Re-working MIS2AD - Need Help in Overview/Any PowerShell Jedi Passing?

[JRA]

Hello all,

 

Well we've had MIS2AD here for a little while (Python thingy that exports SIMS classes groups, mentor groups, teachers of %student% and teachers of %year% to AD groups for email) and it's starting to get a bit dog-eared.

 

Having a crack at re-writing it and get a little way along, then tend to scrap what I've done and begin again. Time to knock this one out.

 

What I want is essentially it to be spreadsheet-based, as in once a day (in the small hours) it'll:

 

- Export stuff from SIMS, one spreadsheet for each of the four.

- Tinker with each spreadsheet where necessary.

- Maybe export something from AD also and marry that up.

- Create groups and email addresses in an OU of AD if they don't already exist, amend them if they do.

 

We don't have reliable email addresses in SIMS but we DO have reliable UPNs in AD (Office field) , so I'd look to "key" students to this.

 

We also have staff codes from SIMS as initials in AD (initials field) so there's that to "key" the staff to.

 

Of the four "parts" above I've got the SIMS report necessary (on my last stab at it at least) to export the staff with their codes and the students with their UPNs and reg groups into two separate spreadsheets, which I think will form the foundation of a LOT of things, but which at least for the moment forms the basis of something.

 

In moving further though, as VERY MUCH a PowerShell n00b and no veteran of these things, how would you yourself tackle this? I don't necessarily mean write all four of them for me (unless you want to lol!) But it'd be an extremely useful utility to have in the arsenal... Plus I like spreadsheets rather than variables in memory as I can look at them at the various stages if anything's going wrong in future and dissect a little easier.

 

Anybody feel like musing with me over the coming weeks? Needless to say I'll post up what I mash together and if it works at the end then we can all nab it I suppose!

 

Thanks for reading, whoever you are out there!

[mavhc]

Are you talking about https://bazaar.launchpad.net/~alexharrington/mis2ad/0.0/files ?

[JRA]

That's the one yes. I mean, it works (worked) and was ok at the time but it's not as transparent in terms of gubbins as I'd like at all. Developed "quirks" over the years and it's time for something else now.

[Norphy]

To be honest, I’d just contact Salamander and get them to do it. It’s not especially expensive and what money you spend on it will be made back in time saved.

 

I do, however, have a PowerShell script which creates users in AD from SIMS which predates our Salamander install which I’m willing to share if you’re interested.

[mavhc]

Don't start from scratch, refactor the existing code, otherwise you'll just create new bugs.

 

The trick is how you find users in AD and match them to users in SIMS, everything else is simple, so what's the method for that?

[JRA]

Cheers muchly both.

 

To be honest, I’d just contact Salamander and get them to do it. It’s not especially expensive and what money you spend on it will be made back in time saved.

 

I do, however, have a PowerShell script which creates users in AD from SIMS which predates our Salamander install which I’m willing to share if you’re interested.

 

Yeah that's my "break glass" thing but it's about £1k for that. And I'd rather (however ugly) get it done in a way that's transparent and I have control over. What I don't get with Salamander's offerings (although does seem to be good) is the control I'd want. Maybe in future I need something else doing, or someone just sticking into %group% after the fact. If I've done it myself it's a little easier to get stuck into modifying.

 

I would VERY much enjoy your scripts yes please.

Don't start from scratch, refactor the existing code, otherwise you'll just create new bugs.

 

The trick is how you find users in AD and match them to users in SIMS, everything else is simple, so what's the method for that?

 

Yes that's one gag indeed. Another one is spitting the right stuff out of SIMS.

 

And I do want a spreadsheet for each one? as in, one for:

 

Each mentor group (kids + teacher)

Each class (kids + teacher)

Each groups that's "teachers of %student%"

Each group that'd be "teachers of %year%"

 

Something tells me probably so... I don't know what the easiest way would be from a Powershell (or VB) point of view for getting these things into AD.

 

But I'm not entirely sure and I've actually made less progress taking things apart this week. But meh, gotta learn some way or another!

 

How would you be going about the AD import bit if you were doing this yourself? Separate .csv for each group of the four "modules" above? I wouldn't even really mind phases that just knocked up the groups kinda independent of each other; that is with separate SIMS reports and everything. Just not sure on the best way to go about it. But I'd sacrifice a lot of "efficiency" for "transparency" of it. By which I mean "I can see what it's doing at every step" thoroughly beats "it's slick and graceful."

[Norphy]

See attached. This will need some adaptation, it's very much geared towards one of my schools. I've also done some anonymisation in the script that may well have broken it:

 

create staff users - automated anonymised.txt

 

Along with the script, you need a report in SIMS which looks like this:

 

 

That script does staff. It should be a simple matter to adapt it for students as well.

[JRA]

You're a gent Mr Norphy thank you.

 

I'm just going to have an experiment for the rest of today. Well, experiment and coffee. Whatever goes on, and understanding of one of three bits won't be a hindrance! The bits being export from SIMS, mash things into shape, import into AD.

 

again too.

[mavhc]

https://code.google.com/archive/p/sims2ad/downloads is mine, vbs. Matches on upn if that exists, otherwise tries to match on first and surnames

[JRA]

Eagerly downloaded for a look and a tinker thanks muchly for that one!

[JRA]

Had a rummage, it does for all the world seem like MOST of the universe creates SIMS accounts for staff and students and then based off reports of what's in SIMS makes up AD accounts and there it all goes.

 

I do seem to be the only one doing it the wrong way 'round... maybe stuck in my ways but I like being able to create AD accounts first and then send the list of emails to the admin team once I know I'm doing John Smith as jsmith1 or jsmith2 depending on how many John/Jane/Julius Smiths I've got in the school.

 

Regardless of admissions process though, I'd still like this running as a standalone thing. Getting there with my SIMS reports still, should be kinda fun to fumble through in fact!

 

Thanks everyone for reading and once I knock something together I'll share it.

[dhicks]

Regardless of admissions process though, I'd still like this running as a standalone thing. Getting there with my SIMS reports still, should be kinda fun to fumble through in fact!

 

Sorry, I missed your oroginal post, otherwise I'd have replied sooner. I have a chunk of Python code that, similar to what you're doing, takes some data from the MIS (in our case, iSAMS) and does various stuff with it - we use Salamander to do Active Directory provision, so my code is doing other stuff such as preparing CSV exports for various external systems and doing various school-specific things. I've split that code up into a selection of scripts to try and be as modular as possible, but some of those scripts could do with re-factoring and being better organised for modularity. I might get to spend some more time soon on those scripts, possibly including more functionality to process extracted data ready for presentation as statistical data in other tools (SSRS / Power BI / etc).

 

If it's any help, one part I've split off into its own stand-alone functionality is a utility to allow end-users to trigger a script to run on the server with a simple web-based interface (literally a large, green button with "Run" written on it in the middle of the screen - can't get simpler UI design than that!). That part is written in Go, and works as its own stand-alone web server, with binaries available for most platforms (Windows, Mac, Linux and Raspberry Pi). Do say if you think that would be handy, I could do with having it tested on a vfew more systems!

[JRA]

Oh boy, I would say so but Python is a mystery to me. It'd probably come out super but I fundamentally don't know it. I couldn't take it apart and tinker if it went, well, "funny" in future.

 

But thanks HUGELY though.

 

Also, dear diary...

 

Today I got little done BUT it seems like the BIG one (making email groups for "teachers of %student%") will be getting done first.

 

I have gotten SIMS to barf out this one: (attached)

 

Redacted info (you might be able to notice btw) but we got columns! Kid's name, kid's UPN, staff code of mentor in column 3 (don't need that anymore tbh) and column 4 staff codes of all their teaching staff!

 

With this one specifically then I gotta figure out how to make:

 

- An AD group with the email address "[email protected]" but the actual name of "Teachers of John Smith" if it's not already there. If it is ofc update the members.

 

- Bung the staff who teach the kids into it with Powershell or VB (not fussy) but not the kid themselves.UPNS of kids are in AD in the "Office" field and Staff Codes are in AD in the "Initials" field.

 

I'm going to ponder this. By my reckoning I'm 1/3 of the way through 1/4 of the "project" and I deserve a curry lol.

 

Thanks everyone for sticking around also.

Edited February 17, 2021 by JRA