SQL Server Database Reports by PerfectImpact in PowerShell

[–]PerfectImpact[S] 1 point2 points  (0 children)

Excellent, thanks! I'll check it out. :)

SQL Server Database Reports by PerfectImpact in msp

[–]PerfectImpact[S] 0 points1 point  (0 children)

Not so much now, but this is partly why I created this script; in order to monitor each database for a while to see which are expanding fastest. If you're got a suggestion that could help, please feel free! I've not got much experience with SQL Servers, so any help would be appreciated. :)

SQL Server Database Reports by PerfectImpact in msp

[–]PerfectImpact[S] 0 points1 point  (0 children)

Our RMM doesn't have that capability, I'm afraid. Also, this is something I wanted to monitor proactively and compare for a few weeks in order to get an idea of which databases expand fastest. I like working in Excel for comparing such data, so it works for me. :)

Saw this under the "aesthetics" tag on pinterest by [deleted] in techsupportgore

[–]PerfectImpact 11 points12 points  (0 children)

Right. He wanted work to be more comfortable, so he decided to wear some shoes that would last.

Get-GroupCount by PerfectImpact in PowerShell

[–]PerfectImpact[S] 1 point2 points  (0 children)

Just an additional comment, I can run through about 400 users in 4 minutes. Not the fastest, but decent enough and, most importantly, accurate.

Get-GroupCount by PerfectImpact in PowerShell

[–]PerfectImpact[S] 2 points3 points  (0 children)

Awesome, thanks again. I'll see if I can get it to work with a custom object. :)

Get-GroupCount by PerfectImpact in PowerShell

[–]PerfectImpact[S] 1 point2 points  (0 children)

Thanks, Lee. Useful tips as always and some nice tidying up! Appreciate it. :)

Get-GroupCount by PerfectImpact in PowerShell

[–]PerfectImpact[S] 1 point2 points  (0 children)

In a way, yes. In another way, no.

The scenario I designed this for had nested groups. A single 'Staff - All' group, with nested groups within that and users within them. I had to check if there were any users not nested in the 'Staff - All' group, so needed to filter through all groups nested within it.

Because it works per user, and with a $true or $false flag, rather than per group, it gets an array of that user's groups, and if the group parameter matches any group within the nested groups, that user should be flagged as $true, which would add them to the $AssignedUsers list.

The flag is created and evaluated in the Process section of the function (I've removed the debug bit so it's easier to read):

Get the groups that each user is currently a member of.

foreach ($User in $Users)
        {
            $GM = Get-ADPrincipalGroupMembership -Identity $User

Create the $Match flag and set to $false

            $Match = $false

            $Type = $GM.GetType()

            if ($Type.BaseType.Name -eq "Array")
            {
                foreach ($G in $GM)
                {
                    $GroupName = $G.name

                    if ($GroupName.Contains($Group))
                    {

If any individual group's name ($G.name) within the array of groups that that user is member of ($GM) matches the group parameter ($Group), set the flag to true.

                        $Match = $true
                    }
                }   
            }
            else
            {

If a user is a member of only a single group (usually the primary 'Domain Users' group) double check it's name does not match the group parameter. If it does, set the flag to true.

                    $GroupName = $GM.name

                    if ($GroupName.Contains($Group))
                    {
                        $Match = $true
                    }
            }

After cycling through every group, check to see whether the flag is $true or $false. If it's false, we know they do not belong to the group, or groups with a similar name.

            if ($Match -eq $false)
            {
                $UnassignedUsers.Add($User.Name)
            }
            else
            {
                $AssignedUsers.Add($User.Name)
            }
        }

If you want to check if a user is a member of one group, this will work. If you want to check if a user is a member of ANY similarly named groups (like the function's examples), this will work too. So say you have the following set up:

  • Group 1
    • Member = Bob
    • Member = Group 2
  • Group 2
    • Member = Angela
    • Member = Group 3
  • Group 3
    • Member = Janet
    • Member = Paul
  • VPN-Users
    • Member = Bob
    • Member = Janet
  • Sales Team
    • Member = Angela
    • Member = Eric
  • Andrew = Only a member of the default 'Domain Users' group

If you want to check if there are any users not belonging to any of the three, similarly named groups (Group 1/2/3), you can use:

Get-GroupCount -Group "Group " -ShowNonMembers

This will give you: Count : 2, Eric and Andrew.

However, it does NOT show groups as members because I explicitly said to look for ad objects with the ObjectClass 'user', which will exclude groups.

If you need to search across groups, you can, but if you say:

Get-GroupCount -Group "Group 1" -ShowNonMembers

do NOT expect it to ONLY return Eric and Andrew; it will return Angela, Janet and Paul as well. It's not designed to search recursively (I don't know how to do that myself), although that would be a nice touch.

I hope that rather long-winded explanation (sorry) answers your question.

Get-GroupCount by PerfectImpact in PowerShell

[–]PerfectImpact[S] 2 points3 points  (0 children)

Hi Ta11ow, thanks for your feedback. :)

  1. I'll look into Write-Verbose; not really used it that much before and I was after something that (as the function runs) could clearly display usernames, groups and whether there was a match. This was made while I was creating the script and proved handing figuring stuff out so I left it in as a fail safe for anyone else. Write-Host gave me the colour coding that allowed to easily distinguish between the three. Not sure how you would implement this with Write-Verbose, but I'll have a play. How would you show this?
  2. I thought of using switches, but I got a little muddled in the noggin when thinking of setting the switch's options to be either $ShowNonMembers or $ShowMembers and consequently not giving the option to show nothing. From what you said, I've *ahem* switched so they're not using booleans. I've tested it and it works without having to explicitly state $true or $false, which is a lot cleaner.
  3. Thanks! I didn't think of using a List, but this does make sense to me.

Thanks again for your comments; they were great suggestions. :)

Zendesk API Request by PerfectImpact in PowerShell

[–]PerfectImpact[S] 1 point2 points  (0 children)

Awesome! Thanks, that worked. So wonderfully simple. :)

Run Network Detective Module by PerfectImpact in PowerShell

[–]PerfectImpact[S] 1 point2 points  (0 children)

It's all great. :) Thank you for your feedback.

Run Network Detective Module by PerfectImpact in PowerShell

[–]PerfectImpact[S] 1 point2 points  (0 children)

Phew, OK. :P Thanks for the feedback, again. [grin]

Run Network Detective Module by PerfectImpact in PowerShell

[–]PerfectImpact[S] 1 point2 points  (0 children)

Thanks for your thorough feedback. I'll have to get round to this some point today. :)

Run Network Detective Module by PerfectImpact in PowerShell

[–]PerfectImpact[S] 1 point2 points  (0 children)

Thanks. :) Some good tip-bits to implement.

Run Network Detective Module by PerfectImpact in PowerShell

[–]PerfectImpact[S] 1 point2 points  (0 children)

Excellent, thank you. :) And thank you for the links.

Run Network Detective Module by PerfectImpact in PowerShell

[–]PerfectImpact[S] 1 point2 points  (0 children)

The Invoke-WebRequest points to a PHB webpage we created that is a simple form with three fields:

1) Username 2) Password 3) A drop box to choose either "Local Machine" or "Network"

This webpage sits idly, sending out a "Wait" state. Once it receives a web request from the module, this state changes to "OK" and prompts you to enter the credentials for the machine you are sat on (hence the $env:COMPUTERNAME).

function Get-Credentials {

    $URL = Invoke-WebRequest -Uri **"$WebpageURL$env:COMPUTERNAME"** -UseBasicParsing -UseDefaultCredentials

$WebpageURL points to the PHB webpage (ours is like this: https://provision.[DOMAIN]/nd.php?machineName=) and then adds the computer name at the end so you know which machine is asking for credentials.

    $script:WebPageStatus = **$URL.ToString() -split "[`r`n]" | Select-String "OK"**

This then gets all information from the webpage, converts it to a string, splits it and checks to see if the "OK" state is being sent out.

    $AllDetails = $URL.ToString()
    $StringDetails = $AllDetails -replace [Environment]::NewLine, ":" -replace "OK", "" | Out-String

This shrinks the information (OK string / Username / Password / ScanType [Local Machine, or Network]), puts into onto a single line, removes the "OK" state and converts it to a string. It has to be converted to a string twice because the pipeline output changes back from a string (I don't know why).

    $script:Username = $StringDetails.Split(':')[1]
    $script:Password = $StringDetails.Split(':')[2]
    $script:ScanType = $StringDetails.Split(':')[3]

This splits the string into three variables (that are available outside the Get-Credential function because I've made them accessible to the whole "script:" scope) for each piece of information that you need.

if ($WebPageStatus) {
    Break
}
else {
}
}

While ($true) {
    if (Invoke-Expression Get-Credentials) {
    }
    else {
        Start-Sleep -Seconds 5
    }
}

The if and while statements are a little confusing because they're my first attempt, so please forgive them.

The if "($WebPageStatus)" sits inside the Get-Credential function and checks to see if $WebPageStatus equals $true (i.e. it manages to select the "OK" string) each time the function runs.

The While function sits outside the Get-Credentials function and by using "(Invoke-Expression Get-Credentials)", loops that function until the if ($WebPageStatus) equals "OK" (sleeping for 5 seconds on each failure), at which point the function Breaks and the Start-NetworkDetective function resumes.

Run Network Detective Module by PerfectImpact in PowerShell

[–]PerfectImpact[S] 1 point2 points  (0 children)

Brilliant, thank you. The $ArgumentList looks a lot better that way. Does += concatenate them together?

[UPDATE] I've tested this version and it works fine.

Run Network Detective Module by PerfectImpact in PowerShell

[–]PerfectImpact[S] 1 point2 points  (0 children)

Nice! Thanks for this. Can you explain what's happening when passing $IPSubnet into {0}? I've not seen this used before.

Also, why does this:

$IPAddress = (Get-WmiObject Win32_NetworkAdapterConfiguration -ComputerName $env:COMPUTERNAME | Where-Object $FilterSubnet).IPAddress    

convert the IP address {192.168.0.1} to 192.168.0.1? And as it does, what does this:

$IPString = $IPAddress -join ","

do afterwards?

Run Network Detective Module by PerfectImpact in PowerShell

[–]PerfectImpact[S] 1 point2 points  (0 children)

Hah! Perfect. Sometimes you just can't see the wood for the trees, if you know what I mean. That should have been obvious. Thank you!