InspectorPS - Provide a Powershell option to use instead of Linked servers for data collection. about undercovertoolbox HOT 17 CLOSED

Awesome! Thanks Shane that would be really handy - I will pop some code up that I have been testing - It could use some tidying up for general release - as a test script it appears to work fine and does the job but needs a Powershell guru's touch.

from undercovertoolbox.

Sounds good. Planning to take a closer, dedicated look at this over the holidays! 👍

from undercovertoolbox.

Currently testing some code I have put together to ensure that the logic is correct - Ultimately this will need to be tidied up and error handling added so hoping for some help with that once the testing is complete - marking as help wanted.

from undercovertoolbox.

If you need an eye to look over the PowerShell then let me know. 👍

from undercovertoolbox.

Guru: No; but I'll definitely take a look and help out! Let me know 👍

from undercovertoolbox.

Here is what I have so far, this is my test version that runs the collection and then the report, once complete it opens the html file using IE (opening IE ideally would be an option along with the option to email the report)

I would imagine you will have questions once you read through my yucky code so feel free to ask away and I will answer any questions you have , thanks again much appreciated!

I forgot to mention - it needs Write-DbaDataTable from DBAtools.

Inspector_Collection.zip

from undercovertoolbox.

Oh sweet!

• If it needs Write-DbaDataTable then you don't need to create connections and can just use Get-DbaDatabase, and then use the .Query() method on the database object!

• We can change Write-Host to Write-Output or Write-Information instead cause...well write-host apparently kills kittens and all that...

• Simple things like we can change the aliases "%" and "?" to ForEach-Object and Where-Object respectively

• Does this bit actually work? I would have thought the Format-Table would feck this up?

#Output the report to a html file
$Report | ft -wrap -HideTableHeaders | Out-file -FilePath $ReportFile

I can take a deeper look tonight when I get home though

But mainly, wow well done! You've done some pretty cool PowerShell here! 👏

from undercovertoolbox.

Thanks! I am a noob when it comes to Powershell all of this code was stuff that I have picked up through self learning , whilst it works it could really use some tidying up and perhaps some functionality changes as my head it very SQL orientated.

Didn't know about those options with Dba-writetable! That is really kool will give that a test later when I get the chance.

Hmm Write-host should be fine to change out , this is the noob in me wanting funky colours in the output :)

Aliases - yep lets get them outta there!

That last part works fine for me, the reason I have the Format-table is because I needed to use -wrap so that it actually output something as the HTML can be pretty large in length - I would imagine there may be a better way to achieve this?

from undercovertoolbox.

First go, basically just a clean up re-write (it's a long script)
Have to say though, you did an amazing job! Here's what I got in the end

We can talk more about what you want to do e.g. turn this into a function, verbose logging so you can turn it off and on, choose either email or report or both.

Tomorrow though...I need sleep now 😄

Inspector_Collection_edited.zip

from undercovertoolbox.

Thanks! I was quite happy with what i managed to achieve but ultimately I knew that it needed the midas touch. I just had a skim over the code and it blew my mind! I love what you have done so far - I will give it a try tomorrow as its getting a bit late now, once I have given it a whirl I will have a chat about those options you mentioned. Thanks again for all your help - really appreciate it :) once we get the specifics ironed out we can get a pull request sorted if you want to do things that way? would be great to see you take some credit for your efforts.

from undercovertoolbox.

Haha thanks man but you shouldn't be so humble. You did the research, you figured out what was needed, and you wrote the code. I'm not going to take credit for what was effectively an "editing" job.

Sounds good! I didn't change too much cause I still have to wrap my head around the concept of what you're doing, in case I remove a step that turns out to be important. 😟

Hit me up whenever and I'll get back to you as soon as I can 👍

from undercovertoolbox.

Finally got round to looking at this properly , I have made some minor changes:
Corrected multiple typos for Variable $CurrentlyProcessedServer

Initated two more variables at the beginning:
$CentralDriveSpace and $CentralGrowths

So far the testing has gone well! its shaping up nicely, I like the idea of this being a function so that it can be called with a few Parameters with an option to email or open the html directly once stored in a path that you specify , I will go through the specifics and decide exactly what I would like it to do and let you know but wanted to at least update you with the small progress I made since you last message.

Will also explore the PSJobs too, I like the idea of that :)

Inspector Powershell collection.zip

from undercovertoolbox.

No worries about the updates. Like I'm grateful for them but

1. I can guess how busy you are with normal job activities,
2. I've seen how much work you and David have put into this so I know it won't be left abandoned,
3. I'm not doing much right now apart from editing so it's not taking up too much of my time.

As for the rest of the message

• Turning this into a function sounds great! To be honest, if you hadn't suggested it then I would have. 😃
• This: "I will go through the specifics and decide exactly what I would like it to do" is epic! Clear, concise instructions and a firm path, I think it's great!
• PSJobs is cool, I'm aiming to get a POC up soon as I haven't used them too much. I think they're trying to make it in-built in Pwsh 6.0 (goes to show how cool Boe's module is)
• I'll look over the code you sent on later today if I get the chance

P.S. I may change the += bits to use System.Collections.Generic.List[]. Performance increase mainly

from undercovertoolbox.

I have completely refactored the code as I realised that the script would need to be maintained when new releases of the Inspector as I had code in there that was version specific and there was also select * statements in there too which I wanted to remove.
The code now handles config output from SQL, there are a number of PS prefixed tables, views and procs which help drive the Powershelll collection script this allows changes to be made in SQL rather than within the powershell script.

@shaneis I have attached the latest script, I am happy with the logic but now Ideally it needs turning into a function, of course if you see any logic in there that you think should be changed for any reason let me know (as I am sure there will be :P ) at the top of the script there are a two mandatory variables and one non mandatory that ideally would become parameters:

$CentralServer - Mandatory
$LoggingDB - Mandatory

$ModuleConfig - Not mandatory

I think I will leave the emailing part within SQL as the SQLUndercoverInspectorReport proc emails out the report anyway , I did toy with providing an option to email out via powershell but I don't think its necessary.

The open in IE part is there purely for testing purposes and happy to remove that option all together as I would imagine it would be no use if this was running as a scheduled task , I have been using it for validating changes etc as it made it quick and easy.

Apologies for posting so late in the end, I had some issues with the script when I decided to add some extra error checking haha!
Inspector Powershell collection.zip

p.s if you need to test it out you will need to grab the latest Inspector version (1.2) the powershell collection will only work on 1.2 and higher now.

from undercovertoolbox.

Changed the minimum requirement for the PS collection to Inspector build 1.3 as I found some SQL code that needed tweaking to allow the collection to run properly , 1.3 is in final testing phase now and a copy of the setup can be found on the InspectorPS branch, I am looking to make a final commit to the master branch later this week.

from undercovertoolbox.

Pre release version committed to master branch along with Inspector V1.3

from undercovertoolbox.

Will close this issue off as new issues can be raised for specific improvements to the powershell now we have an initial build to work with.

from undercovertoolbox.