Unused Serial Keys?

[doodguy]

Hello,

I have Lansweeper scanning our network periodically, and have listed all serial keys to all products we own.

Using Adobe Acrobat as an example, we own hundreds of copies of various revisions IE Acrobat 9, 10, 11. Lets say for serial key "X" we have 2 seats, serial key "Y" we have 3 seats, serial key "Z" we have 1 seat.

Lets say we get a user that needs Acrobat installed. Is there an easy way for me to see a serial key that is free so I know which I should be using? Currently we use lansweeper and see ok we own 10 copies, it is showing 4 avail ok this serial key sounds good just use this one.

[sukaitsu · Accepted solution]

Ah ok that is the issue. You are running SQL Compact instead of SQL Express, but I think you can get the report to run if you take out the var in nvarchar with the code above. Try that and see if it works.

Select Top 1000000 tblLicenses.softwareName,
  tblSublicensesOrders.Ordernumber,
  Count(tblSerialnumber.ProductKey) As [In Use],
  tblSublicensesOrders.Nrlicenses Purchased,
  Cast(tblSublicensesOrders.Licensekey As nchar(300)) As [License Key]
From tblLicenses
  Inner Join tblSublicensesOrders On tblLicenses.LicenseidID =
    tblSublicensesOrders.LicenseidID
  Inner Join tblSerialnumber On tblSerialnumber.ProductKey =
    Cast(tblSublicensesOrders.Licensekey As nchar(300))
Group By tblLicenses.softwareName,
  tblSublicensesOrders.Ordernumber,
  tblSublicensesOrders.Nrlicenses,
  Cast(tblSublicensesOrders.Licensekey As nchar(300))

Thank you,

Jeffrey

[doodguy]

sukaitsu wrote:
Ah ok that is the issue. You are running SQL Compact instead of SQL Express, but I think you can get the report to run if you take out the var in nvarchar with the code above. Try that and see if it works.

Select Top 1000000 tblLicenses.softwareName,
  tblSublicensesOrders.Ordernumber,
  Count(tblSerialnumber.ProductKey) As [In Use],
  tblSublicensesOrders.Nrlicenses Purchased,
  Cast(tblSublicensesOrders.Licensekey As nchar(300)) As [License Key]
From tblLicenses
  Inner Join tblSublicensesOrders On tblLicenses.LicenseidID =
    tblSublicensesOrders.LicenseidID
  Inner Join tblSerialnumber On tblSerialnumber.ProductKey =
    Cast(tblSublicensesOrders.Licensekey As nchar(300))
Group By tblLicenses.softwareName,
  tblSublicensesOrders.Ordernumber,
  tblSublicensesOrders.Nrlicenses,
  Cast(tblSublicensesOrders.Licensekey As nchar(300))

Thank you,

Jeffrey

That script works! Thank you for your help.

Now I just have to tweak it a little; for example when I ran it, it shows me I have say 16 office standard in use, but 1 purchased and the license key. I need to tweak this to run a query check all keys I listed as purchased, against the scan of all keys used, and only show the keys that are available/unused. Of course this has to account for number of seats.

I'll dabble and see what I can come up with.

Thank you again.

[sukaitsu]

It seems to be that error is related to the version SQL you have Lansweeper running on. Even if you installed SQL Express on your server/machine, it looks like Lansweeper is running on a SQL Lite / SQL CE database. SQL Server Compact database doesn't support the char/varchardata types as it's purely unicode based. Can you go to Configuration --> Database scripts and tell me what it says under Database Information.

Thank you,

Jeffrey

[doodguy]

sukaitsu wrote:
It seems to be that error is related to the version SQL you have Lansweeper running on. Even if you installed SQL Express on your server/machine, it looks like Lansweeper is running on a SQL Lite / SQL CE database. SQL Server Compact database doesn't support the char/varchardata types as it's purely unicode based. Can you go to Configuration --> Database scripts and tell me what it says under Database Information.

Thank you,

Jeffrey

This is whawt it says:

Database Database: SQL Compact: C:\Program Files (x86)\Lansweeper\Website\..\SQLDATA\lansweeperdb.sdf
Schema Version: 93
Assets: 747
Size: 3609 / 4090 MB

[sukaitsu]

Hmmm. Lets try this. Install SQL Server Management Studio and I can walk you through how to troubleshoot using it. Also try these steps when creating the report.

1. Copy all of the code from the query above
   
2. Create a new report in Lansweeper and delete the prefilled/default code before pasting
   
3. Paste the copied code and then click into a blank area in the top column, where the tables prefill
   
4. The tables should update after a few seconds to reflect the pasted query
   
5. Name the report
   
6. Save & Run

Let me know if these steps help.

Thank you,

Jeffrey

[doodguy]

sukaitsu wrote:
Hmmm. Lets try this. Install SQL Server Management Studio and I can walk you through how to troubleshoot using it. Also try these steps when creating the report.

1. Copy all of the code from the query above
   
2. Create a new report in Lansweeper and delete the prefilled/default code before pasting
   
3. Paste the copied code and then click into a blank area in the top column, where the tables prefill
   
4. The tables should update after a few seconds to reflect the pasted query
   
5. Name the report
   
6. Save & Run

Let me know if these steps help.

Thank you,

Jeffrey

Hey Jeffrey, thank you for your help. I appreciate your prompt replies. I am attaching a screenshot of what I have, maybe you can see what I'm missing?

Screenshot

[sukaitsu]

Sure thing. Which version of SQL are you running and what edition?

Thank you,

Jeffrey

[doodguy]

sukaitsu wrote:
Sure thing. Which version of SQL are you running and what edition?

Thank you,

Jeffrey

I believe it is express; I just ran the one that installs with default installation.

[doodguy]

hey Jeffrey,

Thank you very much for taking the time to respond!

I am not much of a SQL guy so please forgive me, but when I go to new report and paste this into the bottom I get this error:

There was an error parsing the query. [ Token line number = 1,Token line offset = 219,Token in error = max ]

Any idea what that means?