Useful reports

[Mich]

Hey all,

I was wondering what useful reports you have created in SMS 2003. I'm constantly trying to find another use for this system (to justify the cost). Here is one I wrote that will pull a computer name, computer model, user logged in, operating system, and asset tag number,

select * from v_inventory_view

Where v_inventory_view is,

CREATE VIEW dbo.[Inventory View]
AS
SELECT TOP 100 PERCENT dbo.Computer_System_DATA.Name0 AS [Computer Name], dbo.Computer_System_DATA.Model0 AS Model, dbo.Computer_System_DATA.UserName0 AS [User Logged In], dbperating_System_DATA.Caption0 AS [Operating System],dbo.System_Enclosure_DATA.SerialNumber00 AS [Asset Tag #]
FROM dbo.Computer_System_DATA INNER JOIN dbperating_System_DATA ON dbo.Computer_System_DATA.MachineID = dbperating_System_DATA.MachineID INNER JOIN dbo.System_Enclosure_DATA ON dbperating_System_DATA.MachineID = dbo.System_Enclosure_DATA.MachineID
ORDER BY dbo.Computer_System_DATA.Name0

I'm no SQL expert, so I'm sure there is a better way to get this information. Anyway, does anyone have any SQL statements they would like to share?

Thanks,
Mick

 

[NoWittyName]

I find this query useful it displays all the updates that each computer requires

select Client.Name, Client.LastLogonUserName, SMS_G_System_PATCHSTATE.Product, SMS_G_System_PATCHSTATE.Title, SMS_G_System_PATCHSTATE.QNumbers, SMS_G_System_PATCHSTATE.Status from SMS_R_System inner join SMS_G_System_PATCHSTATE on SMS_G_System_PATCHSTATE.ResourceID = SMS_R_System.ResourceId inner join SMS_R_System as Client on Client.ResourceId = SMS_G_System_PATCHSTATE.ResourceID where SMS_G_System_PATCHSTATE.Status != "Installed" order by Client.Name

Master of Disaster.....Recovery