Monday, April 13, 2009

Query to find computers with user missing application



This is something I did pretty quick, it could easily edited on a computer report

First you need to create a query with no object type (see image) Now paste this into the query editor. You will not be able to use the Query designer for any help. This particular example looks for computers missing a specific Version of Flash. Paired with it are the lasted logged in user, user, title, department. You may need to extened the AD User Discovery as mentioned in a previous blog for title and department information.

-------------------------------------------------------------




select SMS_R_SYSTEM.Name,SMS_R_User.Name,SMS_R_System.LastLogonUserName,SMS_R_System.Name, SMS_G_System_COMPUTER_SYSTEM.UserName,SMS_R_User.title , sms_r_user.department from SMS_R_System
inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId
inner join SMS_R_User on SMS_R_User.Username= SMS_R_System.LastLogonUserName
where SMS_G_System_COMPUTER_SYSTEM.Name not in (select SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%Flash Player%" and SMS_G_System_ADD_REMOVE_PROGRAMS.Version = "10.0.22.87")
--------------------------------------------------------------