I need a query (Manage engine support center) that show me the information like screenshot below,

and then the second report like this,

Please help me to get that report.

Anyway, thank you.