March 6, 2017 at 8:37 am
My query which I found online, is below. It worked fine until I added a column from v_R_User. As soon as I do that, the report comes out blank with headers only. I'm terrible at joins and suspect this might be cause of my problem. Can anyone confirm?
SELECT DISTINCT
Site = v_R_System.Resource_Domain_OR_Workgr0,
[Computer Name] = v_R_System.Netbios_Name0,
[Full Name] = v_R_User.Full_User_Name0
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';
March 6, 2017 at 8:55 am
Was v_r_user in the join before? Or you added the column and the table?
March 6, 2017 at 8:58 am
The original code was this:SELECT DISTINCT
v_R_System.Name0
FROM
v_R_System
JOIN v_FullCollectionMembership FCM
ON FCM.ResourceID = v_R_System.ResourceID
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';
and that worked fine. I added some columns and the v_R_User table join and now it's blank.
Editor: Reformatted code
March 6, 2017 at 11:31 am
Ah, some sort of SMS or Ops Manager inventory, right?
The problem I've seen with these is that the data isn't always clean. What you should check is some specific values from v_r_user and verify there are matching rows in the other table that match your criteria.
I would also use "inner join" not join. It's best to clarify. While inner is the default, just use inner, right outer, left outer, cross to describe what is taking place. The join clauses should work before the WHERE, so comment out the WHERE. Do you get data?
Likely this is a problem with values, but you'll have to debug section by section.
March 6, 2017 at 12:39 pm
Finally got it working by "thinning" the code. Thanks for replying though!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply