SQL top 10 largest tables

Query returns TOP 10 largest db tables

  

select top 10 schema_name(tab.schema_id) + '.' + tab.name as [table],

    cast(sum(spc.used_pages * 8)/1024.00 as numeric(36, 2)) as used_mb,

    cast(sum(spc.total_pages * 8)/1024.00 as numeric(36, 2)) as allocated_mb

from sys.tables tab

    inner join sys.indexes ind 

        on tab.object_id = ind.object_id

    inner join sys.partitions part 

        on ind.object_id = part.object_id and ind.index_id = part.index_id

    inner join sys.allocation_units spc

        on part.partition_id = spc.container_id

group by schema_name(tab.schema_id) + '.' + tab.name

order by sum(spc.used_pages) desc

Check GPO polices status by categories

 Run in DC admin console command:

auditpol /get /category:*

Exceeded the Maximum Number of Computer Accounts Allowed to Create in this Domain

 It happens because of default AD ms-DS-MachineAccountQuota value which is 10.

The workaround is to delegate custom rights for specific account:

1. Open the Active Directory Users and Computers snap-in.

2. Right-click the container under which you want the computers added, and press Delegate Control.

3. Press Next.

4. Press Add.

5. After adding all the users and/or groups, press Next.

6. Select Create custom task to delegate and press Next.

7. Select Only the following objects in the folder, check Computer objects, check the Create selected objects in this folder box, and press Next.

8. Check the Create all child object box and press Next.

9. Press Finish.

 Source: community.spiceworks.com

Show old Procurve LLDP

 In CLI use command: show lldp info remote-device

Reset MySQL root password on Windows

Stop MySQL service
Give user full access to /data folder

Command line:

mysqld --skip-grant-tables
mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password') where USER='root'; 

mysql> FLUSH PRIVILEGES;

HPE advanced CLI

_cmdline-mode on

1910 password: 512900

1920 password: Jinhua1920unauthorized

Delete windows recovery partitions

Run PowerShell as admin
Run DISKPART.

Find disk ID:
DISKPART> list disk

Select disk you want to modify:
DISKPART> select disk 1

Find partition ID:
DISKPART> list partition

Select partition you want to delete:
DISKPART> select partition 5

Delete selected partition:
DISKPART> delete partition override
DISKPART> exit

Source: www.lifewire.com

VLAN ranges

0 & 4095 -> full range
0 -> internal system reserved
1 -> Cisco default
2-1001 -> used for ethernet VLANS
1002-1005 -> FDDI & Token Ring
1006-4095 -> extended VLANS for other uses

Recycle Bin is corrupted

Run Command prompt as Administrator.

rd /s /q C:\$Recycle.bin

This clears out the $Recycle.bin folder from the C:\ partition. You may have to do this for each hard disk partition in your system

Source: https://social.technet.microsoft.com

Remove duplicate lines with Notepad++

In replace dialog select "Regular expression" and ". matches newline"

Find what:^(.*?)$\s+?^(?=.*^\1$)
Replace with:

Source: https://stackoverflow.com

Remote desktop connection not working after CredSSP update

After Windows 10 1803 Cumulative update it's not allowed to connect with RDP to unpatched machines.
To disable security check - create DWORD AllowEncryptionOracle in registry:

HKLM\Software\Microsoft\Windows\CurrentVersion\Policies\System\CredSSP\Parameters\
Set AllowEncryptionOracle = 2

Be aware that disabling security check makes your machine vulnerable!

Source:  support.microsoft.com

Log off user from remote machine

Find user's session ID:
QWINSTA /SERVER:<hostname>

Log off user:
LOGOFF <sessionid> /SERVER:<hostname> /V

Send test email from PowerShell

Send-MailMessage -SMTPServer 192.168.0.1 -To to@email.com -From from@email.com -Subject "This is a test email" -Body "Hi, this is a test email"

Source: https://serverfault.com

Remote Desktop Connection - How to get the certificate prompt back?

Delete server connection keys from here:
HKEY_CURRENT_USER\Software\Microsoft\Terminal Server Client\Servers
HKCU\Software\Microsoft\Terminal Server\Client\LocalDevices\<name>

Export Putty settings

regedit /e "%USERPROFILE%\putty.reg" HKEY_CURRENT_USER\Software\SimonTatham

Identify Windows 10 installation media build

Run Command Prompt as administrator
adism /Get-WimInfo /WimFile:F:\sources\install.esd /index:1

Windows 10 lock screen picture file location

If you want to save some Windows Spotlight picture - open the folder:
C:\Users\<User>\AppData\Local\Packages\Microsoft.Windows.ContentDeliveryManager_cw5n1h2txyewy\LocalState\Assets\

Look for the files with current date greater than 400KB.
Open files with MSPaint to find the exact one. Save it to *.jpg

Change AD user profile path

1. Find users SID:
wmic useraccount where name="USER" get sid

2. Open in regedit key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProfileList\User SID

3. Modify ProfileImagePath value

Delete old computers from Active Directory

Get list of inactive computer for 4 weeks:
dsquery computer –inactive 4

Source: portal.sivarajan.com

Change Office 365 sign-in name for AD synced user

To change Office 365 sign-in (Principal) name for user synced with Onsite AD:

1. Start PowerShell
2. Make sure the Microsoft Online module is loaded
3. Execute the following command:

set-msoluserprincipalname -newuserprincipalname newuser@emailaddress.com -userprincipalname user@emailaddress.com.

Source: msonlinehelpdesk.zendesk.com