Seeking report for Windows workstations that do NOT have a particu - mofmaste

Subselect queries will be your new best friend:

http://www.myitforum.com/articles/1/view.asp?id=179

--
Standarize. Simplify. Automate.

Seeking report for Windows workstations that do NOT have a particular app installed - JumpingBean

Next on my neverending list of reports: a list of all Windows
workstations that do not have a particular app installed.

I tried modifying a report that was originally used for a postivie
match and basically gives me, for example, all the workstations that
don't have Microsoft Office installed, but it's based on the
assumption that they DO have Windows Installer.  They all should, but
this report would miss any workstations that for whatever reason don't
have Windows Installer.

When I try to just have it report all "not like %Microsoft Office%"
then I hit my max row count b/c it displays all workstations and all
apps installed other than Office.

I know there is a more exact way to say "give me all Systems running
Windows that do NOT have any entries that contain "Microsoft Office"
in their names, but I can't figger it yet...




Select Sys.Netbios_Name0, MAX(OSDetail.Caption0) AS 'Operating
System',
MAX(NETW.IPAddress0) AS 'IP Address', MAX(OUNam.System_OU_Name0) AS
'Active Directory OU',
MAX(Sys.User_Name0) AS 'Last Logged In User', MAX(SS.LastScanDate) AS
'Last Inventoried',
ARP.DisplayName0 AS 'Office Product'

FROM v_R_System Sys
LEFT JOIN v_GS_NETWORK_ADAPTER_CONFIGUR NETW ON
NETW.ResourceID=Sys.ResourceID
LEFT JOIN v_RA_System_SystemOUName OUNam ON
OUNam.ResourceID=Sys.ResourceID
LEFT JOIN v_GS_OPERATING_SYSTEM OSDetail ON
OSDetail.ResourceID=Sys.ResourceID
LEFT JOIN v_GS_ADD_REMOVE_PROGRAMS ARP ON
ARP.ResourceID=Sys.ResourceID
LEFT JOIN v_GS_LastSoftwareScan SS ON SS.ResourceID=Sys.ResourceID
WHERE ARP.DisplayName0 like '%Windows Installer%'
AND ARP.DisplayName0 not like '%Microsoft Office%'
AND Sys.ResourceID IN (SELECT ResourceID FROM v_CM_RES_COLL_SMS00001)
GROUP BY Sys.Netbios_Name0, ARP.DisplayName0
ORDER BY Sys.Netbios_Name0