SQL Query for Computers With Disk Space :-

 SQL Query for Computers Disk Space :-



Select v_R_System.name0,

v_R_System.Resource_Domain_OR_Workgr0 as Domain,
v_GS_LOGICAL_DISK.FreeSpace0,
v_GS_LOGICAL_DISK.Size0,
v_GS_LOGICAL_DISK.Caption0,
v_GS_LOGICAL_DISK.DriveType0,
v_R_System.Last_Logon_Timestamp0

from v_R_System

inner Join v_GS_LOGICAL_DISK on v_GS_LOGICAL_DISK.ResourceID = v_R_System.ResourceID

where v_GS_LOGICAL_DISK.DriveType0 = '3'
order by v_R_System.name0

SQL QUERY FOR LAPTOPS & DESKTOPS

SQL QUERY FOR LAPTOPS & DESKTOPS :-


SELECT DISTINCT
 v_GS_COMPUTER_SYSTEM.Name0,'Chassis'=CASE
 WHEN v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 in ('3','4','6','7','15') THEN 'Desktop'
 WHEN v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 in ('8','9','10','21') THEN 'Laptop'
 END,
      v_GS_PC_BIOS.SerialNumber0, v_GS_COMPUTER_SYSTEM.Manufacturer0, v_GS_COMPUTER_SYSTEM.Model0,
                      v_GS_OPERATING_SYSTEM.Caption0, v_GS_OPERATING_SYSTEM.CSDVersion0, v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0,
                      v_R_System.User_Name0 AS [Last Logged], v_GS_PC_BIOS.ReleaseDate0, v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0,
                      v_GS_NETWORK_ADAPTER_CONFIGUR.DefaultIPGateway0, v_R_System.AD_Site_Name0, v_R_System.User_Domain0
FROM         v_GS_PC_BIOS INNER JOIN
                      v_GS_COMPUTER_SYSTEM ON v_GS_PC_BIOS.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID INNER JOIN
                      v_GS_NETWORK_ADAPTER_CONFIGUR ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_NETWORK_ADAPTER_CONFIGUR.ResourceID INNER JOIN
                      v_GS_OPERATING_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID INNER JOIN
                      v_R_System ON v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceID LEFT OUTER JOIN
                      v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID INNER JOIN
                      v_GS_SYSTEM_ENCLOSURE on v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_SYSTEM_ENCLOSURE.ResourceID
WHERE     (v_GS_NETWORK_ADAPTER_CONFIGUR.IPEnabled0 = 1) AND (NOT (v_GS_NETWORK_ADAPTER_CONFIGUR.DefaultIPGateway0 IS NULL)) AND
                      (v_R_System.Operating_System_Name_and0 LIKE '%Workstation%')
ORDER BY v_GS_PC_BIOS.ReleaseDate0

List Of Available Packages :-

List Of Available Packages :- 

SELECT MANUFACTURER, DISPLAYNAME, SOFTWAREVERSION, CI_UNIQUEID, MODELID, SDMPACKAGEVERSION, DATECREATED, LASTMODIFIEDBY
FROM FN_LISTLATESTAPPLICATIONCIS(1033)

ARP Display Name with OS and Region

ARP Display Name with OS and Region : -


SELECT DISTINCT v_GS_SYSTEM.Name0, v_GS_ADD_REMOVE_PROGRAMS.DisplayName0, v_R_System.Operating_System_Name_and0 ,
v_R_System.operatingSystem0,v_R_System.user_domain0,v_R_System.Resource_Domain_OR_Workgr0,
v_R_System.User_Name0
FROM v_GS_SYSTEM INNER JOIN v_GS_ADD_REMOVE_PROGRAMS ON v_GS_SYSTEM.ResourceID = v_GS_ADD_REMOVE_PROGRAMS.ResourceID
INNER JOIN v_R_System ON v_GS_SYSTEM.ResourceID = v_R_System.ResourceID
WHERE (v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) like '%SAP Business Explorer%'
  and v_R_System.Resource_Domain_OR_Workgr0 ='AS'




Select distinct sys.Netbios_Name0, fcm.SiteCode, sys.User_Domain0, sys.User_Name0, arp.DisplayName0 ,
 v_R_User.Full_User_Name0, v_R_User.Mail0, arp.version0

 FROM v_r_system sys
 JOIN v_R_User on sys.User_Name0 = v_R_User.User_Name0
 JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
 JOIN v_FullCollectionMembership fcm on sys.ResourceID = fcm.ResourceID
 WHERE DisplayName0 LIKE '%SAP Business Explorer%' and sys.Resource_Domain_OR_Workgr0 like 'AS%'