I like to be able to enter the string I am looking for into one spot and
have it search the ARP string and the product name. Can also search the
vendor all byt typing it once.
Also like ot see the user and some other info
Declare @StringToFind NVarChar(50)
Declare @ProductCodeToFind NVarChar(75)
Set @StringToFind = '%AOL%'
Set @ProductCodeToFind = '%{70fa37dc-d030-42cd-a1d0-a78638e80dda}_is1%'
SELECT
vrs.Netbios_Name0 as [ComputerName],
S.ProductName0 as [SoftwareTitle],
S.InstallSource0 as [InstallSource],
S.ProductVersion0 as [Version],
S.SoftwareCode0 AS [ProductCode],
S.InstallDate0 as [InstallDate],
U.Full_User_Name0 AS [PrimaryComputerUser],
U.telephoneNumber0 AS [UserPhoneNumber]
FROM v_R_System VRS
INNER JOIN v_GS_INSTALLED_SOFTWARE S ON VRS.ResourceID = S.ResourceID
Left Join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP MG on MG.ResourceID = VRS.
ResourceID
Left Join V_R_User U on U.Unique_User_Name0 = MG.TopConsoleUser0
WHERE
S.ProductName0 like @StringToFind
OR S.ARPDisplayName0 Like @StringToFind
On Fri, Jun 3, 2016 at 12:02 PM, Murray, Mike <[email protected]> wrote:
> Maybe something like the query here?
>
>
>
>
> https://social.technet.microsoft.com/Forums/en-US/0f1ef5a9-7b83-49cf-b47b-90da8d535e85/need-a-sccm-sql-query-report-for-installed-software-with-packages-and-applications?forum=configmanagergeneral
>
>
>
> SELECT v_GS_COMPUTER_SYSTEM.Name0 as 'Computer Name',
> v_GS_INSTALLED_SOFTWARE.ProductName0 as 'Software Title',
> v_GS_INSTALLED_SOFTWARE.InstallSource0 as 'Install Source',
> v_GS_INSTALLED_SOFTWARE.ProductVersion0 as 'Version',
> v_GS_INSTALLED_SOFTWARE.InstalledLocation0 as 'Installed Location',
> v_GS_INSTALLED_SOFTWARE.InstallDate0 as 'Install Date'
>
> FROM v_GS_COMPUTER_SYSTEM INNER JOIN v_GS_INSTALLED_SOFTWARE ON
> v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_INSTALLED_SOFTWARE.ResourceID
>
> WHERE v_GS_INSTALLED_SOFTWARE.ProductName0 like '%Office 365 Pro%'
>
> ORDER BY v_GS_COMPUTER_SYSTEM.Name0
>
>
>
>
>
> *From:* [email protected] [mailto:
> [email protected]] *On Behalf Of *Brian McDonald
> *Sent:* Thursday, June 2, 2016 6:47 AM
> *To:* [email protected]
> *Subject:* [mssms] Re: Query Help
>
>
>
> Yes. Problem is several of these names aren't coming up when I search the
> Parameter Value under Computers with a specific product.
>
>
>
> Example of a few include: HTMLDOC, Lotus Domino, ClearingHouse,
> CVE-2013-1347, ImageRight Desktop, ImageRightPrinter and more...
>
>
>
> Here is the SQL query I'm using:
>
>
>
> eclare @CollID char(8)
>
> Set @CollID = 'SMS00001'
>
> SELECT DisplayName0, Count(*) AS 'Count', Publisher0, Version0, @CollID as
> CollectionID
>
> FROM v_Add_Remove_Programs arp
>
> JOIN v_FullCollectionMembership fcm on arp.ResourceID=fcm.ResourceID
>
> WHERE fcm.CollectionID = @CollID
>
> GROUP BY DisplayName0, Publisher0, Version0
>
> ORDER BY Publisher0, Version0
>
>
> ------------------------------
>
> *From:* [email protected] <[email protected]>
> on behalf of Garth Jones <[email protected]>
> *Sent:* Wednesday, June 1, 2016 3:45:04 PM
> *To:* [email protected]
> *Subject:* [mssms] RE: Query Help
>
>
>
> Have you looked at the built-in reports for this yet?
>
>
>
>
>
>
>
> Garth Jones
>
> Chief Architect
>
>
>
> *www.Enhansoft.com* <http://www.enhansoft.com/>
>
> [image: Description: Description: cid:[email protected]]
> <http://www.enhansoft.com/>
>
> *Enhancing Your Business*
>
> [image: Description: Description: Description: Description: Description:
> Description: Description: cid:[email protected]]
> <http://www.enhansoft.com/blog>[image: Description: Description:
> Description: Description: Description: Description: Description:
> cid:[email protected]] <https://twitter.com/enhansoft>[image:
> Description: Description: Description: Description: Description:
> Description: Description: cid:[email protected]]
> <http://www.facebook.com/EnhansoftInc>[image: Description: Description:
> Description: Description: Description: Description: Description:
> Description: Description: Description: Description: Description:
> Description: Description: Description: Enhansoft's YouTube Page]
> <http://www.youtube.com/user/Enhansoft/videos>[image: Description:
> Description: Description: Description: Description: Description:
> Description: cid:[email protected]]
> <http://myitforum.com/myitforumwp/community/groups/enhansoft/>
>
>
>
> *Subscribe to Enhansoft’s Newsletter*! <http://www.enhansoft.com/register>
>
>
>
> *From:* [email protected] [
> mailto:[email protected] <[email protected]>] *On
> Behalf Of *Brian McDonald
> *Sent:* Wednesday, June 1, 2016 4:02 PM
> *To:* [email protected]
> *Subject:* [mssms] Query Help
>
>
>
> Hello,
>
>
>
> Management has provided me a list of applications they're researching and
> would like to know which devices have these applications have. Is there a
> SQL query I could use as a template and re-use it by modifying the
> DisplayName?
>
>
>
> For example, one of the apps in question is 'AOL Toolbar'.
>
>
>
> Can someone provide an example of a query I could use for this task? And
> make modifications to the query based on the name?
>
>
>
> Thanks,
>
>
> Brian
>
>
>
>
>
>
>
>
