Scripting assistance

[Deeohem]

My management team has me pulling some reports for them, including comparisons from the day before. Right now, I'm pulling each day's data from one website, using a snippet of vb script to pull differences and then filtering through an access database (adding some columns, doing some simple calculations and filtering) and then on some of the reports, I have to check with AD, and then on others I'm running some windows command lines tools and recording the results.

Strange as it may seem, I consider this to be semi-useful, but my management team has been happy to let more parts get added to these reports and I'm starting to spend more time than I want to doing this.

I'd love to automate the process. I know I can almost completely automate it using AutoHotkey and possibly Auto-It, but neither is allowed at UPS.

What I think I'm stuck with are VB script, VBA (there's a lot of excel spreadsheets with VB macros running around doing things still), the Windows Scripting Host, and PowerShell. Any other tools floating around that are allowed within UPS?

Can anyone offer any advice on approaches to this? I know I'm mixing a log of different tasks together, and I'm hoping that I can have a single script (with many subroutines) do this.

Thanks,

 

[randomUPSISer]

My management team has me pulling some reports for them, including comparisons from the day before. Right now, I'm pulling each day's data from one website, using a snippet of vb script to pull differences and then filtering through an access database (adding some columns, doing some simple calculations and filtering) and then on some of the reports, I have to check with AD, and then on others I'm running some windows command lines tools and recording the results.

Strange as it may seem, I consider this to be semi-useful, but my management team has been happy to let more parts get added to these reports and I'm starting to spend more time than I want to doing this.

I'd love to automate the process. I know I can almost completely automate it using AutoHotkey and possibly Auto-It, but neither is allowed at UPS.

What I think I'm stuck with are VB script, VBA (there's a lot of excel spreadsheets with VB macros running around doing things still), the Windows Scripting Host, and PowerShell. Any other tools floating around that are allowed within UPS?

Can anyone offer any advice on approaches to this? I know I'm mixing a log of different tasks together, and I'm hoping that I can have a single script (with many subroutines) do this.

Thanks,

Sounds like your management team is getting in over their head for IS stuff? Be careful! Too much of this user developed stuff eventually ends up in IS when it gets to be too much for the end users to support it.

I can honestly say I've spent my entire IS career fixing things the users created that they shouldnt have

 

[Catatonic]

Sounds like your management team is getting in over their head for IS stuff? Be careful! Too much of this user developed stuff eventually ends up in IS when it gets to be too much for the end users to support it.

I can honestly say I've spent my entire IS career fixing things the users created that they shouldnt have

Users, except for the random person, do not have the skills to run these type of scripts. UPS does not pay these people enough to get this skill set.

Agree 100% that most of these reports should have never become on-going reports developed by IS. That is the purpose of Datawarehouse and Infolib.

 

[freeloader]

Can't you install auto-it somewhere else and just compile some .exe scripts? Then just schedule them with task scheduler.

Seems like a helluva lot of work just for some reports for a manager. But like Hoax said, why not just query datawarehouse or infolib?

 

[Deeohem]

These reports ARE a lot of work. The workload will drop once the win2k sunset is over (most of what I'm flagging are either new XP replacing 2K or the old 2K getting removed from use) But even then, with the churn in systems and keeping everything up to date, it's still gonna be measurable time each day and I'd rather have that time free for tasks that more directly benefit my internal and external customers.

It seems like I have three responses.

1) Don't do it, it's only be bug-ridden and IS will inherit it and be stuck trying to support it.
I understand this argument and sympathize. There is a need to maintain control of projects and IS too often gets stuck cleaning up the mess. This is why the CTSG Project Notebook had a section for Functional Applications, Those which were sorta rolled out, but that did not have the standard corporate support structure. Hoax, Random, It probably will have a bunch of bugs. I'm not trained to be a programmer and I'm not going to be 100% comfortable with the tools I'd need to use. But I will be hammering it until the bugs are either gone or less annoying than some of the time outs I get using I doubt IS will ever know it exists. This isn't SPARCS or any of the other func apps. It has a planned audience of 1 maybe 2 (if I ask the my partner to run it during vacation coverage) Management isn't asking me how I'm massaging the data only where it came from. I figure as the user, I'd be stuck supporting myself with it.

2) use Auto-It anyway and compile the exe
This is very tempting. Is there still a copy of the Auto-It V2 executable on the Windows build CDs? Honestly though, if I were to go that route, I'd use AutoHotkey. I'm more familiar with it's tricks and foibles. And yes, I'd have to violate company policy concerning 3rd party software in order to do it. but... If I'm using my personal computer at home via Remote.ups....

3) Use Data Warehouse or Infolib, that's what they're there for
This sounds like the best way to continue. Do either one of them contain information from EARS? I'm sure I could talk my manager into approving an ID request. Then I'd just need to revisit the code for comparisons, decide if I need to do it in VBA or SQL, I think I could remotely run the commands from Access on each record in the various queries, record results in temp tables and then combine in new queries and export as necessary. I think I could do this.

As part of my reports, I'm checking AD Tools and OSP's SIRF to check AD status on inventory. Does Infolib or Data Warehouse allow read access to AD? or would I still have to do that part manually?

 

[bit]

Hoaxster is right

However, if you want to become that random person, take a look on your computer, I'll bet perl is a ( very hidden) part of the standard image. You can do some AMAZING stuff with Perl/Python, the hard part was getting ppm to work with the proxy.... Infolib is easier to script against if you can manage to get an Oracle client installed and use sqlplus to spool results off, or use pass through queries on Infolib. Access is great when the data is small, but if you're dealing with large data sets, it's a pig. The other thing I don't like about Access is that any VBA you write for it is a moving target and you may have to port code to the new platform when you upgrade in 3 years.

My real piece of advice is to not screen scrape websites, and ask for access to the back end - or maybe ask them to create a tuned view that creates the data set you need.

I started where you were and now I'm a data architect ( albeit elsewhere, I was just dropping in on the boards to see how things were ) . It's a fun path and it's not for everyone and occasionally you'll want to rip your eyeballs out of the sockets, but that's the job

 

[BossofMSDOS]

for sunset you can...

1. pull baseline from ears - import to access
2. query ldap for computer names (need to know target ou's) and import directly to another table
3. compare names fallen off domain and added.

sounds like you have enough skills to make the daily repeated tasks steps 2 and 3 automated enough to just push buttons on a access form.

would need to know what your end goal is for other ideas. .vbs wmi control can get you a lot of the data.

 

[jimmythetech]

These reports ARE a lot of work. The workload will drop once the win2k sunset is over (most of what I'm flagging are either new XP replacing 2K or the old 2K getting removed from use) But even then, with the churn in systems and keeping everything up to date, it's still gonna be measurable time each day and I'd rather have that time free for tasks that more directly benefit my internal and external customers.

It seems like I have three responses.

1) Don't do it, it's only be bug-ridden and IS will inherit it and be stuck trying to support it.
I understand this argument and sympathize. There is a need to maintain control of projects and IS too often gets stuck cleaning up the mess. This is why the CTSG Project Notebook had a section for Functional Applications, Those which were sorta rolled out, but that did not have the standard corporate support structure. Hoax, Random, It probably will have a bunch of bugs. I'm not trained to be a programmer and I'm not going to be 100% comfortable with the tools I'd need to use. But I will be hammering it until the bugs are either gone or less annoying than some of the time outs I get using I doubt IS will ever know it exists. This isn't SPARCS or any of the other func apps. It has a planned audience of 1 maybe 2 (if I ask the my partner to run it during vacation coverage) Management isn't asking me how I'm massaging the data only where it came from. I figure as the user, I'd be stuck supporting myself with it.

2) use Auto-It anyway and compile the exe
This is very tempting. Is there still a copy of the Auto-It V2 executable on the Windows build CDs? Honestly though, if I were to go that route, I'd use AutoHotkey. I'm more familiar with it's tricks and foibles. And yes, I'd have to violate company policy concerning 3rd party software in order to do it. but... If I'm using my personal computer at home via Remote.ups....

3) Use Data Warehouse or Infolib, that's what they're there for
This sounds like the best way to continue. Do either one of them contain information from EARS? I'm sure I could talk my manager into approving an ID request. Then I'd just need to revisit the code for comparisons, decide if I need to do it in VBA or SQL, I think I could remotely run the commands from Access on each record in the various queries, record results in temp tables and then combine in new queries and export as necessary. I think I could do this.

As part of my reports, I'm checking AD Tools and OSP's SIRF to check AD status on inventory. Does Infolib or Data Warehouse allow read access to AD? or would I still have to do that part manually?

Is all of this work being done to accomplish anything or is this just so your management can argue about numbers with corporate?

 

[moreluck]

Proverbs, chapter 17, verse 1.............oops, I thought it said Scripture assistance....Nevermind!

 

[Deeohem]

Been doing it to try and keep track of the loss of PCs in our district. At the beginning we were catching computers in training rooms and server rooms that had been unplugged (naughty naught BD) and we've caught a handful that have stopped talking to EARS even though the Endpoint says it's good. Now though I'm not sure how much of that we're seeing. Mostly I see all the new PCs and old PCs for the Win2K sunset.