→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
djolly
Engaged Sweeper
We have a load of new laptops with names "English-01, English-02, English-03 etc.etc.

They all have the same purchase date, warranty and cost (which we are using custom field 1 for).

Rather than manually enter this information on each laptop, is there a way to run an SQL query in the Lansweeper configuration app?

Any help greatly appreciated!
9 REPLIES 9
Hemoco
Lansweeper Alumni
This may be possible to do, but we do not have the code for such a query.
djolly
Engaged Sweeper
Bump, could do with some help on this please
djolly
Engaged Sweeper
Nearly there! Just need help with one more. I am not sure if this is possible syntacticly speaking but here goes.

I have many bar code entries I need to input, it is a four digit number that increments by 1 each time. e.g.

UPDATE tblCompCustom
SET BarCode= '3001'
FROM tblCompCustom INNER JOIN
tblComputers ON tblCompCustom.Computername = tblComputers.Computername
WHERE (tblComputers.Computer LIKE 'R21-STN01')


Then the next one ...

UPDATE tblCompCustom
SET BarCode= '3002'
FROM tblCompCustom INNER JOIN
tblComputers ON tblCompCustom.Computername = tblComputers.Computername
WHERE (tblComputers.Computer LIKE 'R21-STN02')


Etc. Is there a way of entering this all in one go in one SQL statement?

djolly
Engaged Sweeper
That worked perfectly. Thank you.

I was doing this the long way by entering some dummie information in the Custom field1 then running the previous query, but this way saves me a lot of time. Thanks again
Hemoco
Lansweeper Alumni
This may happen if those specific computers have no value for any of the fields in tblCompCustom. Could you try the script below instead. It forces a value into tblCompCustom, so the Custom1 field can be updated.

INSERT INTO tblCompCustom (Computername)
SELECT Computername
FROM tblComputers
WHERE (Computername NOT IN (SELECT Computername FROM tblCompCustom AS tblCompCustom_1))
GO
UPDATE tblCompCustom
SET Custom1 = '£259.00'
FROM tblCompCustom INNER JOIN
tblComputers ON tblCompCustom.Computername = tblComputers.Computername
WHERE (tblComputers.Computer LIKE 'R21-STN%')
djolly
Engaged Sweeper
That worked great for the English laptops but I am now trying this on another name and having an odd problem.

The SQL statment says "The command completed successfully" but when I check the entry in the browser the field is still blank.


UPDATE tblCompCustom
SET Custom1 = '£259.00'
FROM tblCompCustom INNER JOIN
tblComputers ON tblCompCustom.Computername = tblComputers.Computername
WHERE (tblComputers.Computer LIKE 'R21-STN%')


Basically we have a numbre of machines called R21-STN01, R21-STN02 etc.etc.


Am I missing something?
Hemoco
Lansweeper Alumni
This works the same way. Simply replace "Custom1" in the code.

Change purchase date for computers whose name starts with "English":
UPDATE tblCompCustom
SET PurchaseDate = 'YourInfo'
FROM tblCompCustom INNER JOIN
tblComputers ON tblCompCustom.Computername = tblComputers.Computername
WHERE (tblComputers.Computer LIKE 'English%')


Change warranty date for computers whose name starts with "English":
UPDATE tblCompCustom
SET WarrantyDate = 'YourInfo'
FROM tblCompCustom INNER JOIN
tblComputers ON tblCompCustom.Computername = tblComputers.Computername
WHERE (tblComputers.Computer LIKE 'English%')
djolly
Engaged Sweeper
Thank you for your reply. I will give that a try.

Is there any way of doing something like this with purchase date and warranty date?


Hemoco
Lansweeper Alumni
An example can be found below. Execute this SQL script in your Lansweeper configuration console under Lansweeper\Database Scripts to update the "Custom1" field for all computers whose name starts with "English". Replace "YouInfo" with the information you want to put in the field.

UPDATE tblCompCustom
SET Custom1 = 'YourInfo'
FROM tblCompCustom INNER JOIN
tblComputers ON tblCompCustom.Computername = tblComputers.Computername
WHERE (tblComputers.Computer LIKE 'English%')