SQLTeam.com | Weblogs | Forums

Report to find computers WITHOUT certain software


#1

Hello,
I've been trying various ways to achieve the above. I've tried coding a report for just McAfee Agent and then I tried to do it via parameter prompt. Does anyone have a template of sorts to achieve this? I have tried several things I found online, but nothing has worked so far. Frustrated.

Jeff


#2

Powershell is probably the best tool to use for this type of queries across a group of computers. You don't have to be a powershell expert, there are a lot of examples e.g. here.


#3

My query which I found online, is below. It worked fine until I added a column from v_R_User. I'm terrible at joins and suspect this might be cause of my problem. Can anyone confirm?

select distinct v_R_System.Resource_Domain_OR_Workgr0 as 'Site',
v_R_System.Netbios_Name0 as 'Computer Name',
v_R_User.Full_User_Name0 as 'Full Name'

from v_R_System inner join v_FullCollectionMembership FCM on FCM.ResourceID = v_R_System.ResourceID
join v_R_User on v_R_System.SID0 = v_R_User.SID0

where v_R_System.Name0 not in (select distinct v_R_System.Name0 from v_R_System inner join v_GS_ADD_REMOVE_PROGRAMS on v_GS_ADD_REMOVE_PROGRAMS.ResourceId = v_R_System.ResourceId
where v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 = 'McAfee Agent') and FCM.CollectionID = 'SMS00001'


#4

Do you get an error message, or does it return no rows at all? If you are getting an error message, what does it say? If it returns no rows at all, try a left join as shown below. That will return you null values for Full Name if there are no matching rows in the v_R_User table.

SELECT DISTINCT
        v_R_System.Resource_Domain_OR_Workgr0 AS 'Site' ,
        v_R_System.Netbios_Name0 AS 'Computer Name' ,
        v_R_User.Full_User_Name0 AS 'Full Name'
FROM    v_R_System
        INNER JOIN v_FullCollectionMembership FCM ON FCM.ResourceID = v_R_System.ResourceID
        LEFT JOIN v_R_User ON v_R_System.SID0 = v_R_User.SID0
WHERE   v_R_System.Name0 NOT IN (
        SELECT DISTINCT
                v_R_System.Name0
        FROM    v_R_System
                INNER JOIN v_GS_ADD_REMOVE_PROGRAMS ON v_GS_ADD_REMOVE_PROGRAMS.ResourceId = v_R_System.ResourceId
        WHERE   v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 = 'McAfee Agent' )
        AND FCM.CollectionID = 'SMS00001';

#5

It was no rows at all. After making the change you suggested, I now have this:


#6

That means there are no rows in the v_R_User table with SID0 column that matches the SID0 column from v_R_System table. Should there be?

Look in v_R_User table to see what it contains (SELECT * FROM v_R_User) and look for the SID0 column values. Compare with what you see in the v_R_System table.


#7

You're right. I changed it over to ResourceID, but still nothing in those 3 columns. I know they have data. Do I need I different key field for the User Join?


#8

I don't know enough about your data to answer that question. Inspect the two tables, v_R_System and v_R_User and see what columns they have in common - if any. If there are any, that would give you some indication as to what to join on. If they don't seem to have any common columns, look to see if there other tables that have columns that relate to these two tables?


#9

ok, I'll look into it further. Thank you very much for your help!