#NAME?

#NAME?

--Feburary 2011

 

declare @hostname nvarchar(max)

--Change the hostname using % as a wildcard e.g %bmena% searches for everthing with bmena in the hostname

set @hostname = '%cavon%'

 

"SELECT     ConfigRequestTbl.Name, DATEDIFF(d, ConfigRequestTbl.LastConfigRequest, GETDATE()) AS [Days since in contact with NS], CASE WHEN DATEDIFF(d, "

"                      ConfigRequestTbl.LastConfigRequest, GETDATE()) > 5 THEN 1 ELSE 0 END AS [>5days Since Last contact with NS], "

"                       dbo.Inv_AeX_HW_Serial_Number.[Asset Tag],  dbo.Inv_AeX_HW_Serial_Number.[Computer Model], dbo.Inv_AeX_HW_Serial_Number.[System Manufacturer], CASE WHEN dbo.Inv_AeX_HW_Serial_Number.[Computer Type] IS NULL "

                      THEN 'Unknown' WHEN dbo.Inv_AeX_HW_Serial_Number.[Computer Type] = 'Low Profile Desktop' THEN 'Desktop' WHEN dbo.Inv_AeX_HW_Serial_Number.[Computer Type]

                       = 'Main Server Chassis' THEN 'Server' WHEN dbo.Inv_AeX_HW_Serial_Number.[Computer Type] = 'Mini Tower' THEN 'Desktop' WHEN dbo.Inv_AeX_HW_Serial_Number.[Computer Type]

                       = 'Notebook' THEN 'Laptop' WHEN dbo.Inv_AeX_HW_Serial_Number.[Computer Type] = 'Portable' THEN 'Laptop' WHEN dbo.Inv_AeX_HW_Serial_Number.[Computer Type]

                       = 'Lunch Box' THEN 'Desktop' WHEN dbo.Inv_AeX_HW_Serial_Number.[Computer Type] = 'Space Saving' THEN 'Server' WHEN dbo.Inv_AeX_HW_Serial_Number.[Computer Type]

"                       = 'Tower' THEN 'Desktop' ELSE dbo.Inv_AeX_HW_Serial_Number.[Computer Type] END AS [Asset Type], "

"                      dbo.Inv_AeX_HW_Serial_Number.[Serial Number], LOG_HD.[Total Size in GB] AS [Total Logical Disk Size (GB)], "

"                      LOG_HD.[Total Free Space in GB] AS [Total Logical Disk Free Space(GB)], LOG_HD.[Number of disks ], "

"                      dbo.Inv_AeX_HW_Memory.[Total Physical Memory] / 1048576 AS [Total Ram(MB)], dbo.vComputer.[OS Name], "

                      dbo.Inv_Global_Active_Directory_Details.[Distinguished Name] AS [Active Directory OU]

FROM         dbo.Inv_Global_Active_Directory_Details LEFT OUTER JOIN

                      dbo.vComputer ON dbo.Inv_Global_Active_Directory_Details._ResourceGuid = dbo.vComputer.Guid RIGHT OUTER JOIN

                          (SELECT DISTINCT 

"                                                   _ResourceGuid, ROUND(SUM([Size in MBytes] / 1024), 3) AS [Total Size in GB], SUM([Free Space in MBytes] / 1024) "

"                                                   AS [Total Free Space in GB], COUNT(Name) AS [Number of disks ]"

                            FROM          dbo.Inv_AeX_HW_Logical_Disk

                            WHERE      (Description = 'Local Disk')

                            GROUP BY _ResourceGuid) AS LOG_HD ON dbo.vComputer.Guid = LOG_HD._ResourceGuid RIGHT OUTER JOIN

                      dbo.Inv_AeX_HW_Serial_Number ON dbo.vComputer.Guid = dbo.Inv_AeX_HW_Serial_Number._ResourceGuid RIGHT OUTER JOIN

"                          (SELECT     MachineNames.Name, LastConfigReq.EventTime AS LastConfigRequest, MachineNames.Guid"

"                            FROM          (SELECT DISTINCT i.Name, i.Guid"

                                                    FROM          dbo.Item AS i CROSS JOIN

                                                                           dbo.Evt_NS_Client_Config_Request AS nscr

                                                    WHERE      (i.Guid IN

                                                                               (SELECT     ResourceGuid

                                                                                 FROM          dbo.Evt_NS_Client_Config_Request AS nscr)) AND 

                                                                           (i.ClassGuid LIKE '539626D8-A35A-47EB-8B4A-64D3DA110D01') AND (i.Guid NOT IN

                                                                               (SELECT DISTINCT _ResourceGuid

                                                                                 FROM          dbo.Inv_AeX_AC_Identification

                                                                                 WHERE      ([OS Type] LIKE '%server%'))) AND (i.Guid IN

                                                                               (SELECT DISTINCT _ResourceGuid

                                                                                 FROM          dbo.Inv_AeX_AC_Client_Agent

                                                                                 WHERE      ([Agent Name] LIKE 'Altiris Agent')))) AS MachineNames LEFT OUTER JOIN

"                                                       (SELECT     ResourceGuid, MAX(_eventTime) AS EventTime"

                                                         FROM          dbo.Evt_NS_Client_Config_Request

                                                         GROUP BY ResourceGuid) AS LastConfigReq ON MachineNames.Guid = LastConfigReq.ResourceGuid) AS ConfigRequestTbl ON 

                      dbo.vComputer.Guid = ConfigRequestTbl.Guid LEFT OUTER JOIN

                      dbo.Inv_AeX_HW_Memory ON dbo.vComputer.Guid = dbo.Inv_AeX_HW_Memory._ResourceGuid

WHERE     (ConfigRequestTbl.Name IS NOT NULL AND ConfigRequestTbl.Name LIKE @Hostname)