Office365 – Onedrive4Business Storage Limits Report

February 25, 2019

Once in a while you may get an E-mail notification from SharePoint that a user has nearly reached the storage limits.

Which is set at 1 Terrbyte ?

In our case there was something weird going on… We had a user that reported a Mail Archive file (PST) of 278 Gb large ?

While the maximum size of a PST is 50 GB

After deleting it and removing it from the Recycle Bin, the problem was solved Smile

How to check the storage size on OneDrive4Business

Solution :

Go to the storman.aspx page :

https://YourTenant-my.sharepoint.com/personal/Account_Name_YourOrganization_com/_layouts/15/storman.aspx

image

Enjoy !


QlikView – How to Export the Full Data Model to Excel

February 22, 2019

Once you have a QlikView application built. Or someone else did it for you.

When you have a complex data model with a lot of related tables.

image

You would like to document it, and export all Tables / Fields + Data to Excel.

This is a script you will find on the QlikView help

image

Binary "c:\Application.qvw" ;

//Iterate through the loaded tables
For t = 0 to NoOfTables() - 1

//Iterate through the fields of table
 For f = 1 to NoOfFields(TableName($(t)))
  Tables:
  Load 
   TableName($(t)) as Table,
   TableNumber(TableName($(t))) as TableNo,
   NoOfRows(TableName($(t))) as TableRows,
   FieldName($(f),TableName($(t))) as Field, 
   FieldNumber(FieldName($(f),TableName($(t))),TableName($(t))) as FieldNo
   Autogenerate 1;
   
   let vTable = TableName($(t)));
    Store $(vTable) into d:\Temp\$(vTable).csv](txt);
 Next f
Next t;

But the downside is that is will export ALL FIELDS and ALL DATA.

But some table will hold millions of records, so this is a bit over the top.

image

 

Solution :

Use this script to Export MAX X number of records

Binary "C:\Your Application.qvw" ;

// Export number of Rows
let vRows = 1000;

//Iterate through the fields of table
let vtables = NoOfTables();

for i=0 to $(vtables)-1

let vTable = TableName($(i));

// STORE $(vTable) into C:\Temp\Export\$(vTable).csv (txt,delimiter is '|');

ReducedTable:

LOAD *, RowNo()

RESIDENT $(vTable) where RowNo() < $(vRows) ;

	STORE ReducedTable into d:\Temp\Export\$(vTable).csv (txt,delimiter is '|');

DROP Table ReducedTable;

Sleep 500;

NEXT

So you end up with the full data model tables and samples data in Excel

image

Enjoy !


Windows 10 – Reinstall Windows OS using HP Cloud Recovery Tool

February 22, 2019

First check here if your device is listed for a Cloud Recovery of your OS

https://ftp.hp.com/pub/caps-softpaq/CloudRecovery/crsupportedplatform.html

What you need is a USB drive of minimum 16Gb, and the serial number of the HP device.

Download the tool :

Download Cloud Recovery Client


Solution :

Follow these steps, to create the W10 USB bootable flash drive installer.

Select Download tool now, and select Run.
If you agree to the license terms, select Accept.
On the What do you want to do? page, select Create installation media for another PC, and then select Next.
Select the language, edition, and architecture (64-bit or 32-bit) for Windows 10. You want 64 bit.

Select which media you want to use:
USB flash drive.  Plug in a blank USB flash drive with at least 8GB of space. Any content on the flash drive will be deleted.

image

Once Finished you see that OS installation and drivers (Optional) on the bootable pen drive.

IMPORTANT :

1. Delete WIM’s

After completion it leave a copy of the WIM files on your %localappdata% Directory.

image

So best delete it afterwards, because it is a lot of GB’s waste…

Now that you have the bootable USB drive you can install the OS on your device.

2. BIOS Changes

It might be necessary to disable SECURE BOOT in the BIOS,

in order to start the computer using recovery media.

1. Go to System configuration / Boot Options

image

2. Use the down arrow key to select Secure Boot, press Enter,

and then use the down arrow key to modify the setting to Disable and press Enter.

3. Use the arrow keys to select Legacy Support and press Enter,

and then use the arrow keys to modify the setting to Enable and press Enter.

4. Press F10 to accept the changes and exit or use the left arrow key to select the Exit,

use the down arrow key to select Exit Saving Changes and then press Enter to select Yes.

5. The Computer Setup Utility closes and the Operating System Boot Mode Change screen displays,

prompting you to confirm the Boot Options change. Type the code shown on the screen,

and then press Enter to confirm the change.

6. The Computer Setup Utility closes and the computer restarts.
7. Turn the computer off and try to boot from the Recovery media.

Enjoy!


Windows – Using osQuery Tool

February 1, 2019

The osQuery Tool is a cross-platform tool to query your devices like a database for Windows, Linux and iOS.

Osquery uses basic SQL commands to leverage a relational data-model to describe a device.

You can dowload it here

osQuery installation on Windows is running the MSI to get it running.

After the installation you will see the binaries in C:\ProgramData\osquery

image

As you can see it has a few PowerShell scripts as well.

But most importantly is the osQuery Shell called osqueryi.exe

If you run the shell command you can see all features like this :

osqueryi .help

image

in the background it uses SQLite Smile

image

You can list all the tables that can be queried like this

image

image

Since you now have the list of the tables you can start using it in a Query.

How to use it :

Example :

osqueryi -line "select * from video_info"

image

osqueryi -line "select * from cpu_info"

image

PowerShell :

You can use osqueryi.exe in PowerShell like this

image

Enjoy !


Windows Server 2016 – CleanUp Stale Devices – DeviceSetupManager Event ID 121

February 1, 2019

How to cleanup stale devices on your servers …

On one of our servers that is running as a Hyper-V Host and using a non windows backup software to backup the VM’s I see a lot of VSS copies hanging around …. ?

When looking at the HIDDEN devices

image

I saw a lot of Generic volume Shadow Copies

See here to know why this is occurs

image

And as well INACTIVE Storage volumes

image

The know why this is happening see here

Solution :

1. CleanUp all stale devices and registry related entries

Download the DriveCleanup Tool here

image

You can run this command to run in TEST MODE

drivecleanup -t > dc-output.txt

You can see this detailed output

image

In my case it suggest these entries to be removed.

image

I could see a clear relationship between the Event ID 121 Errors and output of the drivecleanup tool

image

image

To delete the stale devices and Registry Keys you need to use the Admin Privileges

Make sure you have good backups before removing anything !

2. Remove all VSS copies

Run this command to cleanup

vssadmin list shadows

vssadmin delete shadows /all

Check again the Event logs after the next backups.

And in my case all disk errors where gone. Smile

Enjoy !