A new Flexera Community experience is coming on November 25th. Click here for more information.

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

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

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ł

(2) Solutions
jevans
By Level 7 Flexeran
Level 7 Flexeran

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

View solution in original post

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
(Did my reply solve the question? Click "ACCEPT AS SOLUTION" to help others find answers faster. Liked something? Click "KUDO". Anything expressed here is my own view and not necessarily that of my employer, Flexera.)

View solution in original post

(10) Replies
jevans
By Level 7 Flexeran
Level 7 Flexeran

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

Thanks a lot!

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

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
(Did my reply solve the question? Click "ACCEPT AS SOLUTION" to help others find answers faster. Liked something? Click "KUDO". Anything expressed here is my own view and not necessarily that of my employer, Flexera.)

Hi Chris, 

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

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

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:

tempo.png

--Mark

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

(Did my reply solve the question? Click "ACCEPT AS SOLUTION" to help others find answers faster. Liked something? Click "KUDO". Anything expressed here is my own view and not necessarily that of my employer, Flexera.)
Where did you get 7 from? I believe screenshot was taken on 21st, so only 4 days passed including that Monday.
Good point. In that case I don't know how the "Days" values have got so large so quickly.
(Did my reply solve the question? Click "ACCEPT AS SOLUTION" to help others find answers faster. Liked something? Click "KUDO". Anything expressed here is my own view and not necessarily that of my employer, Flexera.)