/build/static/layout/Breadcrumb_cap_w.png

Creating a label from an excel list of pc's

Hi guys.

Best of the new year to you all.

My Question today is...

I have a list of PC's on an excel sheet. 500 to be exact.

I want to create a label with all these machines

in my k1000.

Is it possible to search multiple machines in the inventory,

by importing the list somehow.

I have heard people ask this before I...I think. Any ideas??

Thanks guys.


0 Comments   [ + ] Show comments

Answers (4)

Answer Summary:
Posted by: Hobbsy 8 years ago
Red Belt
2

Top Answer

OK, so this is a bit of a pain, but it can be done with a combination of your K1000 and excel. The steps I have gone through to achieve this are as follows:

1, First create a new Device Smart Label on your K1 and use the wizard to add in 3 or 4 machines by name



2, Save the Label and then go and edit the SQL, it should look something like this



From this we can see the format of the SQL statement that includes multiple machine names in particular the Where statement that is formatted like this:

 WHERE ((MACHINE.NAME = 'TESTWORKVM1') OR (MACHINE.NAME = 'PRIMARY-SERVER') OR (MACHINE.NAME = 'DASHBOARD'))  

3. Next go to your excel sheet, and I assuming you have a column list of machine names like this



4, You will need to use the CONCATENATE command in excel to create the text to go in your WHERE statement of your smart label.

=CONCATENATE("OR (MACHINE.NAME = '",A2,"')")

This will produce text formatted like this

OR (MACHINE.NAME = 'Machine Name 1')

4. You can now copy all of the cells containing your formatted text and paste into notepad like this



5. Now format your text onto a single line ready to be cut and paste into your labels WHERE statement



NB remove the OR statement and spaces from before the first machine name

6. Finally cut and paste into your Labels WHERE Statement, making sure you list of machines is enclosed with a single set of brackets


Comments:
  • That's impressive. I'll file that one away. - rockhead44 8 years ago
  • Thanks Hobbsy. Bit of a pain, but works.
    Thanks. - akmagnum 8 years ago
  • Hey guys!
    This didn't work for me!! The label is empty! Any ideas? - egas 6 years ago
  • Tried this for roughly 400 computers, ended up with 2000+ in the smart label. How is it adding computers that aren't in the list? Tried again with 100 computers and still ended up with almost 200, again many not in my list. How are you supposed to manage thousands of computers if you can't group a few hundred at a time? - swalker804 5 years ago
Posted by: anonymous_129525 7 years ago
Senior Yellow Belt
0
Hello - I actually just posted a How-To article that uses this same premise, but uses a Powershell script to automate the generation of the SQL query.


Hope this helps!
Posted by: StockTrader 8 years ago
Red Belt
0
Hello,

If the computers have something in common is better to focus on what they have in common and then create a smart label to group them (even more than a smart label and then another one to group them)
Or you can order you excel by name, you inventory by device name and manually apply a static label.

Another crazy idea that I have not tried (and might even not work well) is to create a device smart label to target a device with a specific name, then edit the SQL of the label in something like this one:

Original SQL: (if you have ORGS may differ a bit but the important part to amend is the WHERE clause)

SELECT MACHINE.NAME AS SYSTEM_NAME, SYSTEM_DESCRIPTION, MACHINE.IP, MACHINE.MAC, MACHINE.ID as TOPIC_ID FROM MACHINE  WHERE ((MACHINE.NAME = 'bbbb'))  

Modified SQL:

SELECT MACHINE.NAME AS SYSTEM_NAME, SYSTEM_DESCRIPTION, MACHINE.IP, MACHINE.MAC, MACHINE.ID as TOPIC_ID FROM MACHINE  
WHERE 
MACHINE.NAME IN ('bbbb','cccc',dddd',.....500 names) 

If the statement is too long you may hit some limits...I do not really recommend this approach but you may try the query before in a KACE custom SQL report to find out if the DB is able to process it.
 Kind regards,
 Marco - StockTrader

Comments:
  • I tried this (but only with a dozen or so names); it works. - kentwest 3 years ago
Posted by: flip1001 8 years ago
Black Belt
0
This might work also

1. Create a text file with all the computer names.

Computer1
Computer2
Computer3
etc....

2. Create a kscript to copy the text file to C:\Windows\manual-label-computers.txt on ALL computers.

3. Create a custom inventory rule called CIR-Whatever that says FileExists (C:\Windows\manual-label-computers.txt) AND ShellCommandTextReturn(cmd /q /c type C:\Windows\manual-label-computers.txt | find /i "%computername%")

4. Create a smart label where CIR-Whatever is not null

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ