Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Tuesday, January 5, 2016 6:04 AM
Folks,
We have an existing custom query in SCCM to poll information about application being used and the user information. We needed the last time when the application is being used for the same.
Also, added a query to provide the dates since being used.
This works fine, however instead of showing the latest date, it shows all the instances when the software was used. There is something with group by maybe.
Can someone suggest what is wrong?
SELECT DISTINCT
SYS.Name0 as 'Computer',
Arp.Publisher0 as 'Publisher',
ARP.DisplayName0 As 'Software Name',
Console.TopConsoleUser0 as 'User',
abs(DATEDIFF(day,getdate(),max(usage.LastUsedTime0))) as 'Days Since Used',
max(Usage.LastUsedTime0) as 'Date Last Used',
ARP.InstallDate0 as 'Install Date'
FROM
dbo.v_R_System As SYS
INNER JOIN dbo.v_FullCollectionMembership FCM On FCM.ResourceID = SYS.ResourceID
INNER JOIN dbo.v_Add_REMOVE_PROGRAMS As ARP On SYS.ResourceID = ARP.ResourceID
LEFT JOIN v_GS_System_Console_Usage console on sys.ResourceID = console.ResourceID
JOIN v_GS_CCM_RECENTLY_USED_APPS Usage on sys.ResourceID = Usage.ResourceID
WHERE
FCM.CollectionID = 'SMS00001'
AND
arp.DisplayName0 LIKE '%Corel Draw%'
and Name0 like '%PC%'
group by Name0,LastUsedTime0, Publisher0, arp.DisplayName0, TopConsoleUser0,AD_Site_Name0, Version0, operatingSystem0,InstallDate0
--order by Name0
RESULT
PC NULL Corel Draw Domain\Receptionist 2 2016-01-03 08:06:45.000 NULL
PC NULL Corel Draw Domain\Receptionist 2 2016-01-03 08:10:44.000 NULL
PC NULL Corel Draw Domain\Receptionist 2 2016-01-03 08:11:14.000 NULL
PC NULL Corel Draw Domain\Receptionist 2 2016-01-03 08:15:20.000 NULL
Required Result
Intent is that - It should only show the last entry on when the software was used with the date difference.
Regards, Vik Singh "If this thread answered your question, please click on "Mark as Answer"
All replies (15)
Thursday, January 7, 2016 2:43 PM âś…Answered
I don't link ARP data and v_GS_CCM_RECENTLY_USED_APPS... Only query on V_GS_CCM_RECENTLY_USED_APPS.
Exactly my point, in order to do what VIK want you MUST link it to something other that V_GS_CCM_RECENTLY_USED_APPS (RUA), How else will you know that a computer has Corel Draw but has not used it in a year? If it has not be used in a year then it will not be listed within RUA and therefore you can't reclaim the license.
Garth Jones | My blogs: Enhansoft and Old Blog site | Twitter: @GarthMJ
Tuesday, January 5, 2016 12:22 PM | 1 vote
My quick look at this, I cam see that your group by is setup incorrectly. Therefore you are getting the duplicates from it.
Garth Jones | My blogs: Enhansoft and Old Blog site | Twitter: @GarthMJ
Wednesday, January 6, 2016 5:55 AM | 1 vote
Dear Sir,
As Garth pointed out, please remove LastUsedTime0, and OperatingSystem0, from the group by line.
Best regards
Frank
Wednesday, January 6, 2016 8:11 AM
Good suggestions. It works in a way. Does not work correctly, when I search software with wild card and the result has 2 values. It shows the same lastused date then.
If I search for a exact value of a software it shows 1 value and works fine.
WHERE
FCM.CollectionID = 'SMS00001'
AND
arp.DisplayName0 LIKE 'Corel Draw Version 4.3.2.1 '
and Name0 like '%PC%'
Does not work when I use wild cards, because I get multiple results for %Corel% as I have more than 1 Corel Product on my machine. Result is displayed, however lastused shows up the same for all the softwares.
WHERE
FCM.CollectionID = 'SMS00001'
AND
arp.DisplayName0 LIKE '%Corel%'
and Name0 like '%PC%'
I am guessing, smoething to do with MAX function. There should be a way to max only for first product, then next...
Regards, Vik Singh "If this thread answered your question, please click on "Mark as Answer"
Wednesday, January 6, 2016 2:25 PM | 1 vote
I have looked deeper into this today and your query will NOT work.
Why you ask. Because, their is no link between ARP data and Current usage data. Sure you can connect the computer together but so what. It will not link an application ARP data to Usage data.
Garth Jones | My blogs: Enhansoft and Old Blog site | Twitter: @GarthMJ
Wednesday, January 6, 2016 4:22 PM
Thanks Garth. You are right.
Is it possible to pull the data with %wild cards% then? Please advise.
Regards, Vik Singh "If this thread answered your question, please click on "Mark as Answer"
Wednesday, January 6, 2016 5:06 PM
Thanks Garth. You are right.
Is it possible to pull the data with %wild cards% then? Please advise.
Wild cards to do what? Against what?
Garth Jones | My blogs: Enhansoft and Old Blog site | Twitter: @GarthMJ
Thursday, January 7, 2016 4:55 AM
Garth, Appreciate all your responses so far.
The report works fine when we search for a product called with exact name - "Corel Draw 5.4.3.2"
However, if we use %Corel% as the string search - then the issue comes up where there are 2 Corel Products on the same machine - Corel Draw/Corel Move.
This displays all the fields fine except "Last Used". Both line items will show the exact same time. This could be because of the max function being used.
There is a need of using wild cards to pull out data and review last used.
Regards, Vik Singh "If this thread answered your question, please click on "Mark as Answer"
Thursday, January 7, 2016 11:10 AM
Garth, Appreciate all your responses so far.
The report works fine when we search for a product called with exact name - "Corel Draw 5.4.3.2"
However, if we use %Corel% as the string search - then the issue comes up where there are 2 Corel Products on the same machine - Corel Draw/Corel Move.
This displays all the fields fine except "Last Used". Both line items will show the exact same time. This could be because of the max function being used.
There is a need of using wild cards to pull out data and review last used.
Regards, Vik Singh "If this thread answered your question, please click on "Mark as Answer"
Since there is no link between the views, there is no way to get the data.
Garth Jones | My blogs: Enhansoft and Old Blog site | Twitter: @GarthMJ
Thursday, January 7, 2016 11:48 AM
You must activate Software Metering on specific files to be inventoried and then create one report to display what your need.
Thursday, January 7, 2016 12:13 PM
Garth - any pointers to that?
Regards, Vik Singh "If this thread answered your question, please click on "Mark as Answer"
Thursday, January 7, 2016 12:47 PM
Easier. Do you need InstallDate?
SELECT DISTINCT
SYS.Name0 as 'Computer',
Usage.CompanyName0 as 'Publisher',
Usage.ProductName0 As 'Software Name',
Usage.ProductVersion0 AS 'Version',
Console.TopConsoleUser0 as 'User',
abs(DATEDIFF(day,getdate(),Max_LastUsed)) as 'Days Since Used',
Max_LastUsed as 'Date Last Used'
FROM
dbo.v_R_System As SYS
LEFT OUTER JOIN v_GS_System_Console_Usage console on sys.ResourceID = console.ResourceID
LEFT OUTER JOIN v_GS_CCM_RECENTLY_USED_APPS Usage on sys.ResourceID = Usage.ResourceID
LEFT OUTER JOIN (SELECT MAX(Usage.LastUsedTime0) as 'Max_LastUsed', usage.ResourceID, usage.ProductName0, usage.ProductVersion0, usage.CompanyName0
FROM dbo.v_R_System As SYS
LEFT OUTER JOIN v_GS_System_Console_Usage console on sys.ResourceID = console.ResourceID
LEFT OUTER JOIN v_GS_CCM_RECENTLY_USED_APPS Usage on sys.ResourceID = Usage.ResourceID
WHERE usage.ProductName0 LIKE '%Corel%' group by usage.ResourceID, usage.ProductName0, usage.ProductVersion0, usage.CompanyName0) max_usage on max_usage.ResourceID = usage.ResourceID
WHERE
usage.ProductName0 LIKE '%Corel%'
and Name0 like '%PC%'
Thursday, January 7, 2016 2:30 PM
Easier. Do you need InstallDate?
SELECT DISTINCT SYS.Name0 as 'Computer', Usage.CompanyName0 as 'Publisher', Usage.ProductName0 As 'Software Name', Usage.ProductVersion0 AS 'Version', Console.TopConsoleUser0 as 'User', abs(DATEDIFF(day,getdate(),Max_LastUsed)) as 'Days Since Used', Max_LastUsed as 'Date Last Used' FROM dbo.v_R_System As SYS LEFT OUTER JOIN v_GS_System_Console_Usage console on sys.ResourceID = console.ResourceID LEFT OUTER JOIN v_GS_CCM_RECENTLY_USED_APPS Usage on sys.ResourceID = Usage.ResourceID LEFT OUTER JOIN (SELECT MAX(Usage.LastUsedTime0) as 'Max_LastUsed', usage.ResourceID, usage.ProductName0, usage.ProductVersion0, usage.CompanyName0 FROM dbo.v_R_System As SYS LEFT OUTER JOIN v_GS_System_Console_Usage console on sys.ResourceID = console.ResourceID LEFT OUTER JOIN v_GS_CCM_RECENTLY_USED_APPS Usage on sys.ResourceID = Usage.ResourceID WHERE usage.ProductName0 LIKE '%Corel%' group by usage.ResourceID, usage.ProductName0, usage.ProductVersion0, usage.CompanyName0) max_usage on max_usage.ResourceID = usage.ResourceID WHERE usage.ProductName0 LIKE '%Corel%' and Name0 like '%PC%'
I hate to say it Gaeten, this query will not work as they Vik, wants. There is no link between ARP data and v_GS_CCM_RECENTLY_USED_APPS Data. Without that link, there isn't much that you can do.
Garth Jones | My blogs: Enhansoft and Old Blog site | Twitter: @GarthMJ
Thursday, January 7, 2016 2:33 PM
Easier. Do you need InstallDate?
SELECT DISTINCT SYS.Name0 as 'Computer', Usage.CompanyName0 as 'Publisher', Usage.ProductName0 As 'Software Name', Usage.ProductVersion0 AS 'Version', Console.TopConsoleUser0 as 'User', abs(DATEDIFF(day,getdate(),Max_LastUsed)) as 'Days Since Used', Max_LastUsed as 'Date Last Used' FROM dbo.v_R_System As SYS LEFT OUTER JOIN v_GS_System_Console_Usage console on sys.ResourceID = console.ResourceID LEFT OUTER JOIN v_GS_CCM_RECENTLY_USED_APPS Usage on sys.ResourceID = Usage.ResourceID LEFT OUTER JOIN (SELECT MAX(Usage.LastUsedTime0) as 'Max_LastUsed', usage.ResourceID, usage.ProductName0, usage.ProductVersion0, usage.CompanyName0 FROM dbo.v_R_System As SYS LEFT OUTER JOIN v_GS_System_Console_Usage console on sys.ResourceID = console.ResourceID LEFT OUTER JOIN v_GS_CCM_RECENTLY_USED_APPS Usage on sys.ResourceID = Usage.ResourceID WHERE usage.ProductName0 LIKE '%Corel%' group by usage.ResourceID, usage.ProductName0, usage.ProductVersion0, usage.CompanyName0) max_usage on max_usage.ResourceID = usage.ResourceID WHERE usage.ProductName0 LIKE '%Corel%' and Name0 like '%PC%'I hate to say it Gaeten, this query will not work as they Vik, wants. There is no link between ARP data and v_GS_CCM_RECENTLY_USED_APPS Data. Without that link, there isn't much that you can do.
Garth Jones | My blogs: Enhansoft and Old Blog site | Twitter: @GarthMJ
I don't link ARP data and v_GS_CCM_RECENTLY_USED_APPS... Only query on V_GS_CCM_RECENTLY_USED_APPS.
Thursday, January 7, 2016 2:51 PM
I don't link ARP data and v_GS_CCM_RECENTLY_USED_APPS... Only query on V_GS_CCM_RECENTLY_USED_APPS.
Exactly my point, in order to do what VIK want you MUST link it to something other that V_GS_CCM_RECENTLY_USED_APPS (RUA), How else will you know that a computer has Corel Draw but has not used it in a year? If it has not be used in a year then it will not be listed within RUA and therefore you can't reclaim the license.
Garth Jones | My blogs: Enhansoft and Old Blog site | Twitter: @GarthMJ
OK, that's right I guess...
With the use of Metering, you could uninstall all Softwares that has not been started within x days. So perhaps is this a way to solve problem.