Raw Software Usage - where is the data stored in the database

[ppyrzynski]

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ł

[jevans · Accepted solution]

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

[jevans · Accepted solution]

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

[ppyrzynski]

Thanks a lot!

[ppyrzynski]

@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?

[ChrisG · Accepted solution]

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

[ppyrzynski]

Hi Chris, 

But that still does not give me the last date an application was used, right? Or am I missing something?

[jevans]

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

[mfeinman]

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

[ChrisG]

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.)

[ppyrzynski]

Where did you get 7 from? I believe screenshot was taken on 21st, so only 4 days passed including that Monday.

[ChrisG]

Good point. In that case I don't know how the "Days" values have got so large so quickly.