I'm moving a query from the v1 sensor to the new v2 type. I've created the SQL file in the correct place, selected the correct database and have tried to run the sensor.
When run, the sensor returns 'Must declare the scalar variable "@totalSeats".' as an error. The full SQL script is below:
/* Vantage license query - returns the number of licenses free for users to obtain */ /* Declarations - leave these alone */ DECLARE @totalSeats INT; DECLARE @reusableSeats INT; DECLARE @usedSeats INT; DECLARE @serialNum DECIMAL; /* End Declarations */ /* Set the license serial number to check here */ SET @serialNum = 'serial number here'; /* Get the total number of seats we are licensed for */ SELECT @totalSeats = SUM(maxusers) FROM liccnfg WHERE serialnum = @serialNum; /* Get the number of seats released by clients which can be picked up by others */ SELECT @reusableSeats = COUNT(*) FROM licuser WHERE DATEDIFF(second, DATEADD(second, lasttime, lastdate), GETDATE()) > "timeout" AND "licnum" > 0 AND "sessiontype" = ''; /* Get the total number of seats currently in use */ SELECT @usedSeats = COUNT("licnum") FROM licuser WHERE "licnum" > 0 AND "sessiontype" = '' /* Select the number of available licenses a client can use */ SELECT (@totalSeats - @usedSeats) + @reusableSeats as "Available Licenses";
I can run this query successfully from SQL Server Management Studio, it returns one column named "Available Licenses" with the number of licensed seats available.