Absolutely. Let me adjust the query to display 'Last Logged on User' or LLU. On the other hand, I can update the .rdl as well as I think it would be advantageous for the report do display the LLU in relationship to the machine. Were you looking at this from Visio or Project standpoint?
Regards
Vikram
hello vikram. im actually looking for implement both Visio and Project reports. if u were able to update the .rdl and/or provide the updated SQL query with LLU for the user detail usage section of the report, you would be awesome!!! thanks.
i forgot to mention that i am also looking to implement your visual studio report. if you are able to do the sql query or .rdl report for visual studio that again would be awesome sir! much apprecaited.
The query for Project would be as below:
SELECT TOP (100) PERCENT dbo.v_R_System.Name0 AS [Device Name], ARP.DisplayName0 AS [Application Name], ARP.Publisher0 AS Publisher, ARP.Version0 AS Version,
dbo.v_R_System.Operating_System_Name_and0 AS [Operating System], dbo.v_R_System.AD_Site_Name0 AS [AD Site], dbo.v_R_System.Last_Logon_Timestamp0 AS [Last Logon],
dbo.v_R_System.User_Name0
FROM dbo.v_Add_Remove_Programs AS ARP INNER JOIN
dbo.v_R_System ON ARP.ResourceID = dbo.v_R_System.ResourceID
WHERE (ARP.DisplayName0 LIKE N'%Project %') AND (NOT (ARP.DisplayName0 LIKE N'%Viewer%')) AND (NOT (ARP.DisplayName0 LIKE N'%MUI%')) AND (NOT (ARP.DisplayName0 LIKE N'%Security%')) AND
(NOT (ARP.DisplayName0 LIKE N'%Update%')) AND (NOT (ARP.DisplayName0 LIKE N'%Service%')) AND (NOT (ARP.DisplayName0 LIKE N'%Compatibility%')) AND (NOT (ARP.DisplayName0 LIKE N'%SDK%')) AND
(NOT (ARP.DisplayName0 LIKE N'%Converter%')) AND (NOT (ARP.DisplayName0 LIKE N'%Add-In%')) AND (NOT (ARP.DisplayName0 LIKE N'%Server%')) AND (NOT (ARP.DisplayName0 LIKE N'%VisualStudio%'))
AND (NOT (ARP.DisplayName0 LIKE N'%Visual Studio%')) AND (NOT (ARP.DisplayName0 LIKE N'%Cloud%')) AND (NOT (ARP.DisplayName0 LIKE N'%Visual Studio%')) AND
(NOT (ARP.DisplayName0 LIKE N'%Preview%')) AND (NOT (ARP.DisplayName0 LIKE N'%Language%')) AND (NOT (ARP.DisplayName0 LIKE N'%Screen%'))
GROUP BY ARP.DisplayName0, dbo.v_R_System.Name0, ARP.Publisher0, ARP.Version0, dbo.v_R_System.Operating_System_Name_and0, dbo.v_R_System.AD_Site_Name0, dbo.v_R_System.Last_Logon_Timestamp0,
dbo.v_R_System.User_Name0
HAVING (ARP.Publisher0 LIKE N'%Microsoft%')
ORDER BY [Device Name], dbo.v_R_System.User_Name0
I will post both the reports by EOD.
Vikram
Funny thing you mention Visual Studio as I had already started building it even prior to this thread and needed to upload it. Will ping you once I complete it an its validated and ready to upload. I want to make sure you can make most of it so I am going to be nice and additionally add the last logged on user to that report too. 😀
Vikram
Let me know if the Visio report works and I will wrap up the other 2 and upload the links.
Visio Report 1.1: https://gallery.technet.microsoft.com/Visio-Version-Report-2f81b392
Vikram
And finally the last one as promised.
Visual Studio Report 1.1: https://gallery.technet.microsoft.com/Visual-Studio-Version-e12c7fc2
Vikram
hmmm, the visio one when i do a Run in sql report builder is asking me to enter "Active Status". the project one simply just runs and shows me the user name.
Active Status = 1 is for active in the last 7 days while Active Status = 0 is for older than 7 days.
If your going to run it in SQL Management Studio than just remove 'AND v_R_System.Active0 = @Active_Status' else you will have to fulfill the value in the parameter. I have striked it out in the code below. This should give you the dataset for the Visio data you are looking for. The alternate is to replace @Active_Status with 1 or 0 as reflected above.
SELECT TOP (100) PERCENT dbo.v_R_System.Name0 AS [Device Name], ARP.DisplayName0 AS [Application Name], ARP.Publisher0 AS Publisher, ARP.Version0 AS Version,
dbo.v_R_System.Operating_System_Name_and0 AS [Operating System], dbo.v_R_System.AD_Site_Name0 AS [AD Site], dbo.v_R_System.Last_Logon_Timestamp0 AS [Last Logon],
dbo.v_R_System.User_Name0
FROM dbo.v_Add_Remove_Programs AS ARP INNER JOIN
dbo.v_R_System ON ARP.ResourceID = dbo.v_R_System.ResourceID
WHERE (ARP.DisplayName0 LIKE N'%Visio %') AND (NOT (ARP.DisplayName0 LIKE N'%Viewer%')) AND (NOT (ARP.DisplayName0 LIKE N'%MUI%')) AND (NOT (ARP.DisplayName0 LIKE N'%Security%')) AND
(NOT (ARP.DisplayName0 LIKE N'%Update%')) AND (NOT (ARP.DisplayName0 LIKE N'%Service%')) AND (NOT (ARP.DisplayName0 LIKE N'%Compatibility%')) AND (NOT (ARP.DisplayName0 LIKE N'%SDK%')) AND
(NOT (ARP.DisplayName0 LIKE N'%Converter%')) AND (NOT (ARP.DisplayName0 LIKE N'%Add-In%')) AND (ARP.Publisher0 LIKE N'%Microsoft%')AND v_R_System.Active0 = @Active_Status
GROUP BY ARP.DisplayName0, ARP.Publisher0, ARP.Version0, dbo.v_R_System.Operating_System_Name_and0, dbo.v_R_System.Name0, dbo.v_R_System.AD_Site_Name0, dbo.v_R_System.Last_Logon_Timestamp0,
dbo.v_R_System.User_Name0
ORDER BY [Application Name], Version, [Device Name]
lemme try my sql report with updated sql query
joy! it is working like a champ now! does the visual studio have the same entry that i need to remove as well? i have not looked at that query yet.
Hello Vikram,
I were able to run your .rdl before but today when run again, I got error
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'Visio_Professional'. (rsErrorExecutingCommand)
