Hi,
I need help storing the output of a stored procedure into a view or a table. I'm referring specifically to [dbo].[ReportLocationConsumptions] under Programmability.
The code to execute the stored procedure is the following:
USE [FNMScompliance]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[ReportLocationConsumptions]
SELECT 'Return Value' = @return_value
GO
Thanks!
Jul 01, 2022 10:57 AM
Hi Sabrina,
You can always insert the data returned from a stored procedure into a table.
See the following code as an example:
IF OBJECT_ID ('tempdb..#ReturnTable') IS NOT NULL DROP TABLE #ReturnTable;
CREATE TABLE #ReturnTable
(
RowIndex INT
, LicenseName NVARCHAR(256)
, LicenseType NVARCHAR(256)
, CostPerRight MONEY
, Publisher NVARCHAR(64)
, CoveredProducts NVARCHAR(MAX)
, LicenseVersion NVARCHAR(60)
, LicenseEdition NVARCHAR(60)
, LocationHierarchicalName NVARCHAR(MAX)
, Purchased NVARCHAR(256)
, Consumed NVARCHAR(256)
, LicenseCost MONEY
, ComplianceCost MONEY
, ShortfallAvailability INT
, Level1 NVARCHAR(256)
, Level2 NVARCHAR(256)
, Level3 NVARCHAR(256)
, Level4 NVARCHAR(256)
, PurchasedLocally INT
, ConsumedLocally INT
, LocalShortfallAvailability INT
, LocalCompliance MONEY
, UsedLocally INT
, RolledUpPurchased INT
, RolledUpConsumed INT
, RolledUpUsed INT
, AssignedLocally INT
, LocationTreeLevel INT
, CoveredProductsAndEdition NVARCHAR(MAX)
, LocationPath NVARCHAR(256)
, Path NVARCHAR(256)
, CoveredInstallations NVARCHAR(MAX)
)
INSERT INTO #ReturnTable
EXEC [dbo].[ReportLocationConsumptions]
SELECT * FROM #ReturnTable
Hover, converting the procedure into a multi-statement table-valued functions (MSTVF) is a more elegant solution.
T-SQL does not allow using temp tables in a function, so you have to convert all temp tables into table variables first. See the attached file 'ReportlocationconsumptionsMSTVF.sql' as an example.
You can call the function by simply doing a select:
SELECT * FROM [dbo].[ReportlocationconsumptionsMSTVF] (NULL)
Please note:
The original (FNMS 2022 R1) procedure 'ReportLocationConsumptions' is not only very poorly formatted and commented, it also has a bug.
I added the missing condition in line #35/36 in the attached file 'ReportLocationConsumptions_FNMS2022R1_CORRECTED.SQL'
Jul 02, 2022 10:28 AM
Hi Sabrina,
You can always insert the data returned from a stored procedure into a table.
See the following code as an example:
IF OBJECT_ID ('tempdb..#ReturnTable') IS NOT NULL DROP TABLE #ReturnTable;
CREATE TABLE #ReturnTable
(
RowIndex INT
, LicenseName NVARCHAR(256)
, LicenseType NVARCHAR(256)
, CostPerRight MONEY
, Publisher NVARCHAR(64)
, CoveredProducts NVARCHAR(MAX)
, LicenseVersion NVARCHAR(60)
, LicenseEdition NVARCHAR(60)
, LocationHierarchicalName NVARCHAR(MAX)
, Purchased NVARCHAR(256)
, Consumed NVARCHAR(256)
, LicenseCost MONEY
, ComplianceCost MONEY
, ShortfallAvailability INT
, Level1 NVARCHAR(256)
, Level2 NVARCHAR(256)
, Level3 NVARCHAR(256)
, Level4 NVARCHAR(256)
, PurchasedLocally INT
, ConsumedLocally INT
, LocalShortfallAvailability INT
, LocalCompliance MONEY
, UsedLocally INT
, RolledUpPurchased INT
, RolledUpConsumed INT
, RolledUpUsed INT
, AssignedLocally INT
, LocationTreeLevel INT
, CoveredProductsAndEdition NVARCHAR(MAX)
, LocationPath NVARCHAR(256)
, Path NVARCHAR(256)
, CoveredInstallations NVARCHAR(MAX)
)
INSERT INTO #ReturnTable
EXEC [dbo].[ReportLocationConsumptions]
SELECT * FROM #ReturnTable
Hover, converting the procedure into a multi-statement table-valued functions (MSTVF) is a more elegant solution.
T-SQL does not allow using temp tables in a function, so you have to convert all temp tables into table variables first. See the attached file 'ReportlocationconsumptionsMSTVF.sql' as an example.
You can call the function by simply doing a select:
SELECT * FROM [dbo].[ReportlocationconsumptionsMSTVF] (NULL)
Please note:
The original (FNMS 2022 R1) procedure 'ReportLocationConsumptions' is not only very poorly formatted and commented, it also has a bug.
I added the missing condition in line #35/36 in the attached file 'ReportLocationConsumptions_FNMS2022R1_CORRECTED.SQL'
Jul 02, 2022 10:28 AM
Thank you for the answer and explanation why. I was able to create the tableau and bring it into Tableau.
Jul 12, 2022 09:41 AM
Erwin,
Thank you for your help on this.
How would one go about adding domains to this code.
Thanks
Sep 28, 2022 12:14 PM
Hi Sabrina,
Not sure what you mean by 'adding domains' in this context?
The procedure will break down license consumption data by location.
There is a table that stores Windows domain names (named '[ComplianceDomain']), but these Windows domains are linked to Windows users (stored in the [ComplianceUser] table), and not related to licenses.
Sep 28, 2022 01:54 PM
Hi Erwin,
I was thinking domains could perhaps give some clues as to what the location may be for some of the 'Null' locations. Please advise.
Thanks
Sep 29, 2022 03:59 PM
Hi Sabrina,
The ‘location’ as well as other group properties (‘corporate unit’, ‘cost center’) cannot be retrieved automatically by an inventory source like the Flexera agent. These group properties must be imported from another data source, like from a CMDB (Computer Management Data Base).
Usually, these properties are updated in an asset. Updates done to these properties on an asset will by default synchronize to the inventory that is linked to the asset.
FNMS has an optional setting named ‘Syncronize device locations with site subnets’ that allows creating up updating the location of your computers.
Using this feature requires that site and subnet information is available in FNMS. If this information is maintained in Active Directory (AD), it will be imported from AD if you are using the standard AD integration.
Sep 30, 2022 04:01 AM
Thank you Erwin, will try your suggestion above. Just to confirm, domains have nothing to do with License Compliance so there is no way to tie them in, correct?
Oct 11, 2022 03:44 PM
Hi Sabrina,
That is correct - Windows domains are not relevant for license compliance.
Under the assumption that your organization is using country specific Windows domains, you could use the domain of computers (inventories) or the domain of users assigned to computers (assigned user, calculated user) for setting the correct location of the inventories. That is not a common request though.
'Group properties' - location, organizational unit and cost center - of inventories are not relevant for the global license balance, too.
However:
Oct 11, 2022 05:34 PM - edited Oct 11, 2022 05:36 PM
Hi Erwin,
You mentioned that we could use the domain of the users assigned for setting the correct location. Is there a guide for this? I know for some of our Null location users, we do have domains for some of them.
Thanks
Oct 12, 2022 04:21 PM
Hi Sabrina,
The following SQL joins FNMS inventories with users and user domains and returns inventories without any location, together with the user assigned to the inventory and with the Windows domain of that user.
;WITH cteCompplianceComputers(ComplianceComputerID, ComputerName, UserID) AS
(
SELECT
cc.ComplianceComputerID
, cc.ComputerName
, ISNULL(cc.AssignedUserID, cc.CalculatedUserID) AS UserID
FROM [ComplianceComputer] cc
WHERE cc.LocationID IS NULL
)
SELECT
ComplianceComputerID
, ComputerName
, cu.UserName
, cd.FlatName AS WindowsDomainName
FROM cteCompplianceComputers ccc
JOIN [ComplianceUser] cu ON cu.ComplianceUserID = ccc.UserID
JOIN [ComplianceDomain] cd ON cd.ComplianceDomainID = cu.ComplianceDomainID
If you are able to link Windows domains to FNMS locations, you can use this as the basis for building an UPDATE statement that sets the 'LocationID' property for Inventories in the [ComplianceComputer] table.
Before updating the location of your inventories, make sure that the output of the SELECT statement above correctly reflects the locations that you want to assign to your inventories, please.
Oct 12, 2022 05:34 PM
Hi Erwin,
I will use the domain method as an temporary solution to get some locations derived from the blank locations for licenses.
Is there a way to convert the code you provided on 7/2 into a view so it's always updated as opposed to the one time table it created?
Thanks
Oct 19, 2022 12:46 PM
Hi Sabrina,
You cannot convert the output of a stored procedure into a SQL view.
However, you can simply convert the output of the Table Valued Function (TVF) that I provided on 7/2 into a view. Just use:
CREATE VIEW View_ReportlocationconsumptionsMSTVF AS
(
SELECT * FROM [dbo].[ReportlocationconsumptionsMSTVF] (NULL)
)
Oct 19, 2022 03:44 PM