→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
doodguy
Engaged Sweeper II
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.
1 ACCEPTED SOLUTION
sukaitsu
Champion Sweeper
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
Thank you, Jeffrey Smith Enterprise Applications Security (319) 499-6310 JefSmith@geico.com

View solution in original post

11 REPLIES 11
sukaitsu
Champion Sweeper
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
Thank you, Jeffrey Smith Enterprise Applications Security (319) 499-6310 JefSmith@geico.com
doodguy
Engaged Sweeper II
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
Champion Sweeper
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
Thank you, Jeffrey Smith Enterprise Applications Security (319) 499-6310 JefSmith@geico.com
doodguy
Engaged Sweeper II
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
Champion Sweeper
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
Thank you, Jeffrey Smith Enterprise Applications Security (319) 499-6310 JefSmith@geico.com
doodguy
Engaged Sweeper II
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

Screenshot
sukaitsu
Champion Sweeper
Sure thing. Which version of SQL are you running and what edition?

Thank you,

Jeffrey
Thank you, Jeffrey Smith Enterprise Applications Security (319) 499-6310 JefSmith@geico.com
doodguy
Engaged Sweeper II
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
Engaged Sweeper II
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?