MS SCCM

Run the following script on the MS SQL database from SCCM. The script creates several additional views. These views are used by the add-on 'SMS' to display inventory data in an additional tab in the ticket.

CREATE VIEW dbo.vComputer_System AS
SELECT MachineID AS dwMachineID, Model00 AS Model0, UserName00 AS UserName0, 
    Description00 AS Description0, Roles00 AS Roles0, CurrentTimeZone00 AS CurrentTimeZone0, 
    Status00 AS Status0, SystemType00 AS SystemType0
FROM Computer_System_DATA
GO
 
CREATE VIEW dbo.vDisk AS
SELECT MachineID AS dwMachineID, Model00 AS Model0, Name00 AS Name0,
    InterfaceType00 AS InterfaceType0, MediaType00 AS MediaType0, Partitions00 AS Partitions0, Size00 AS Size0
FROM Disk_DATA
GO
 
CREATE VIEW dbo.vIdentification AS
SELECT MachineID AS dwMachineID, Domain0, Name0, SMSID0, SystemRole0, SystemType0
FROM System_DATA
GO
 
CREATE VIEW dbo.vLogical_Disk AS
SELECT MachineID AS dwMachineID, Name00 AS Name0, 
    VolumeName00 AS VolumeName0, Description00 AS Description0, FileSystem00 AS FILE_SYSTEM0, 
    FreeSpace00 AS FreeSpace0, Size00 AS Size0, VolumeSerialNumber00 AS SerialNumber0
FROM Logical_Disk_DATA
GO
 
CREATE VIEW dbo.vOperating_System AS
SELECT MachineID AS dwMachineID,  Caption00 AS Caption0, Version00 AS Version0, InstallDate00 AS Installation_Date0, LastBootUpTime00 AS LastBootUpTime0, 
    CountryCode00 AS CountryCode0, OSLanguage00 AS Language_ID0, TotalVirtualMemorySize00 AS TotalVirtualMemorySize0
FROM Operating_System_DATA
GO
 
CREATE VIEW dbo.vPC_Bios AS
SELECT MachineID AS dwMachineID, SerialNumber00 AS SerialNumber0, BIOSVersion00 AS Version0, Name00 AS Name0, 
    ReleaseDate00 AS RELEASE_DATE0, SMBIOSBIOSVersion00 AS Bios_Version, Manufacturer00 AS Manufacturer0
FROM PC_BIOS_DATA
GO
 
CREATE VIEW dbo.vProcessor AS
SELECT MachineID AS dwMachineID, Name00 AS Name0, NumberOfCores00 AS NumberOfCores0, NumberOfLogicalProcessors00 AS NumberOfLogicalProcessors0, 
     Family00 AS Family0, SocketDesignation00 AS SocketDesignation0, IsMobile00 AS IsMobile0, MaxClockSpeed00 AS MaxClockSpeed0, Status00 AS Status0
FROM Processor_DATA
GO
 
CREATE VIEW dbo.vServices AS
SELECT MachineID AS dwMachineID, DisplayName00 AS DisplayName0, StartMode00 AS StartMode0, PathName00 AS PathName0, 
    StartName00 AS StartName0, Name00 AS Name0, ServiceType00 AS ServiceType0, Status00 AS Status0
FROM Services_DATA
GO
 
CREATE VIEW dbo.vSoftware AS
SELECT        MachineID AS dwMachineID, ProductName00 AS Productname0, ProductVersion00 AS Productversion0, InstalledLocation00 AS Installedlocation0, InstallDate00 AS Installation_Date0
FROM  INSTALLED_SOFTWARE_DATA
WHERE (Productname00 LIKE '%Microsoft %' AND Language00 = (SELECT OSLanguage00 FROM Operating_System_DATA WHERE Operating_System_DATA.MachineID = INSTALLED_SOFTWARE_DATA.MachineID))
OR (Productname00 NOT LIKE '%Microsoft %' AND Language00 IN (0, 1033, (SELECT OSLanguage00 FROM Operating_System_DATA WHERE Operating_System_DATA.MachineID = INSTALLED_SOFTWARE_DATA.MachineID)) )
GO