A new Flexera Community experience is coming on November 18th, click here for more information.
Hi Guys,
What are the underlying tables/views in FNMSCompliance database, that make up the table shown on Raw Software Usage page in WebUI?
Thanks,
Paweł
‎Dec 19, 2023 07:12 AM
Good afternoon Pawel, I hope you're keeping well?
The Raw Software Usage view is based on a Function within the FNMSInventory/IM database.
This is a table function called List_SoftwareUsageRecords, which should be given a startdate and enddate value of DATETIME format.
You should therefore be able to query this manually by specifying both @startdate and @enddate of DATETIME datatype, then running:
SELECT * FROM List_SoftwareUsageRecords(@startdate,@enddate)
Kind regards and thanks,
Jack Evans (He/Him)
Team Lead
Flexera Support
‎Dec 19, 2023 08:59 AM
Here's a query which will provide a lower-level view of usage data than what you get from the List_SoftwareUsageRecords function:
SELECT
c.ComputerCN,
u.UserCN,
sv.SoftwareName,
sv.Version,
sfn.Name,
supw.StartOfWeek,
supw.Days,
supw.Sessions,
supw.Duration,
supw.ActiveTime
FROM dbo.SoftwareUsagePerWeek AS supw
JOIN dbo.Computer c ON c.ComputerID = supw.ComputerID
JOIN dbo.[User] u ON u.UserID = supw.UserID
LEFT JOIN dbo.SoftwareVersion sv ON sv.SoftwareID = supw.SoftwareID
LEFT JOIN dbo.SoftwareFileUsage sfu on sfu.SoftwareFileUsageID = supw.SoftwareFileUsageID
LEFT JOIN dbo.SoftwareFileName sfn on sfn.SoftwareFileNameID = sfu.SoftwareFileNameID
‎Dec 20, 2023 01:50 AM
Good afternoon Pawel, I hope you're keeping well?
The Raw Software Usage view is based on a Function within the FNMSInventory/IM database.
This is a table function called List_SoftwareUsageRecords, which should be given a startdate and enddate value of DATETIME format.
You should therefore be able to query this manually by specifying both @startdate and @enddate of DATETIME datatype, then running:
SELECT * FROM List_SoftwareUsageRecords(@startdate,@enddate)
Kind regards and thanks,
Jack Evans (He/Him)
Team Lead
Flexera Support
‎Dec 19, 2023 08:59 AM
Thanks a lot!
‎Dec 19, 2023 12:56 PM
@jevans, one more question. Is there a way to tell date when a given application on a given device (from this list) was last used?
‎Dec 19, 2023 03:23 PM
Here's a query which will provide a lower-level view of usage data than what you get from the List_SoftwareUsageRecords function:
SELECT
c.ComputerCN,
u.UserCN,
sv.SoftwareName,
sv.Version,
sfn.Name,
supw.StartOfWeek,
supw.Days,
supw.Sessions,
supw.Duration,
supw.ActiveTime
FROM dbo.SoftwareUsagePerWeek AS supw
JOIN dbo.Computer c ON c.ComputerID = supw.ComputerID
JOIN dbo.[User] u ON u.UserID = supw.UserID
LEFT JOIN dbo.SoftwareVersion sv ON sv.SoftwareID = supw.SoftwareID
LEFT JOIN dbo.SoftwareFileUsage sfu on sfu.SoftwareFileUsageID = supw.SoftwareFileUsageID
LEFT JOIN dbo.SoftwareFileName sfn on sfn.SoftwareFileNameID = sfu.SoftwareFileNameID
‎Dec 20, 2023 01:50 AM
Hi Chris,
But that still does not give me the last date an application was used, right? Or am I missing something?
‎Dec 20, 2023 08:46 AM
Hello again Pawel,
I'd argue that this query does show the last date of usage.
FNMS rounds up usage to a weekly basis - that Last Used Date is usually reported as a Monday, at least for the FNMS Inventory Agent.
That supw.StartOfWeek column should therefore correspond with your Last Used Date, assuming that the associated Applications' usage requirements have been met (in Application Properties > Usage in the FNMS Web UI).
Kind regards and thanks,
Jack Evans (He/Him)
Team Lead
Flexera Support
‎Dec 20, 2023 09:44 AM
Interesting topic.
I have a question about the definition of the Days column in the SoftwareUsagePerWeek table.
The schema documentation says that the Days column of the SoftwareUsagePerWeek table is defined as "the number of distinct days the application was used within the week."
Using the query @ChrisG provided above, can someone explain why I am seeing values in the Days column that exceed 7 days in a week? An example:
--Mark
‎Dec 21, 2023 10:05 AM
Days are counted per-session (process). So having 4 sessions running on every day of the week would likely explain the "28" Days figure shown in the screenshot (4 * 7 = 28). (Edit: This statement is probably incorrect.)
‎Dec 21, 2023 04:49 PM - edited ‎Dec 21, 2023 06:37 PM
‎Dec 21, 2023 05:01 PM
‎Dec 21, 2023 05:27 PM
User | Count |
---|---|
8 | |
7 | |
3 | |
3 |