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.

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

1
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

Answered 01/21/2016 by: Hobbsy
Red Belt

  • That's impressive. I'll file that one away.
  • Thanks Hobbsy. Bit of a pain, but works.
    Thanks.
Please log in to comment

Answers

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!
Answered 10/14/2016 by: ClayVan
Senior White Belt

Please log in to comment
This content is currently hidden from public view.
Reason: Removed by member request
For more information, visit our FAQ's.

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
Answered 01/21/2016 by: StockTrader
Red Belt

Please log in to comment
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
Answered 01/22/2016 by: flip1001
Fourth Degree Green Belt

Please log in to comment
Answer this question or Comment on this question for clarity

Share