I am new to applescript, but I have learned to use it to some extent.
What I need to do is take an inventory of certain machines (i.e.: serial number, computer name, ip address, mac address, etc.) and put it in an excel spreadsheet to be uploaded to an asset management system.
I have written a vbscript to do this on the windows machine, but I am unable to use that for Mac OS X.
Using AppleScript I would like to look up the system information (do shell script “system_profiler -detailLevel full -xml > path to desktop/systemInfo.xml”) and then place it in specific cells within an already created excel spreadsheet.
As you can see above I have figured out how to dump the system info into an xml file and am assuming there is a way for met to get the data from that file into excel.
Any insight on how to do this would be greatly appreciated.
Please keep in mind, I am new at this so may need some additional explanations/information.
Also, sorry if this is a commonly asked topic, but I couldn’t find what I was looking for after searching.
Thank you,
Shane
Model: MacBook Pro 15"
AppleScript: 2.4.1
Browser: Safari 534.51.22
Operating System: Mac OS X (10.7)
Well, there is standard XML commands in the system (part of the System Events definitions) like XML file, XML element, XML data and XML attribute. I guess this way you could cycle through the XML file and then input that info into your Excel file quite easily. what is it that you need exactly? Help with XML or Excel?
Model: MacBookPro8,2
Browser: Safari 534.51.22
Operating System: Mac OS X (10.7)
Now that I can get that system info how do I put it in excel? I was able to open the appropriate spreadsheet and I tried the following:
set cell "I2" to IPv4 address
but I came up with an error that says: Microsoft Excel got an error: Can’t set cell “O2” to short user name.
I am also looking for the model name (i.e.: MacBook Pro), serial number, and operating system. Any ideas where I can pull these from easily?
Last question for now: Once I have a row in my spreadsheet that lists the information I need how do I tell excel to put the next set of information on the following row?
Two things: first, you need to set the value of the cell, not the cell itself. Second, you have to request the correct value in the list provided by the get system info command. When you see { } used this way, it’s a list of values that is provided. The variable MachineProps is then set with these values. The contents of the variable MachineProps looks somewhat like this:
In this case you want the second info in the list, therefore you address it as item 2 of MachineProps when you set the value of the cell in Excel:
tell application "Microsoft Excel"
set value of cell "A2" to (item 2 of MachineProps) as text
end
And I coerced it to text just to prevent problems in Excel.
Like Adam said, there is a limited set of things you can get from the system info command. Here’s the list of keywords you can use with it:
AppleScript version
AppleScript Studio version
system version
short user name
long user name
user ID
user locale
home directory
boot volume
computer name
host name
IPv4 address
primary Ethernet address
CPU type
CPU speed:2000
physical memory
It’s not much, but it’s a start. As for the terminal command, granted it provides tons of info, but at your level it might be a little tricky to get the data you need out of it, but not impossible. Good luck!
Model: MacBookPro8,2
Browser: Safari 534.51.22
Operating System: Mac OS X (10.7)
set dt to path to desktop folder as text
do shell script "system_profiler -xml SPHardwareDataType > ~/Desktop/HardwareData.xml"
tell application "System Events"
set hardwareData to XML element 1 of XML element 6 of XML element 1 of XML element 1 of XML element 1 of contents of XML file (dt & "HardwareData.xml")
set hardWareList to {}
set countHardwareData to count XML elements of hardwareData
repeat with i from 1 to countHardwareData by 2
set v to value of XML element i of hardwareData
if v is not "_name" then
set end of hardWareList to {v, value of XML element (i + 1) of hardwareData}
end if
end repeat
end tell
tell (system info)
set end of hardWareList to {"computer name", computer name}
set end of hardWareList to {"IPv4 address", IPv4 address}
set end of hardWareList to {"primary Ethernet address", primary Ethernet address}
set end of hardWareList to {"system version", system version}
end tell
do shell script "rm " & quoted form of (POSIX path of dt & "HardwareData.xml")
tell application "Microsoft Excel"
set maxRow to (countHardwareData div 2 + 3)
set value of range ("A1:B" & maxRow) of active sheet to hardWareList
end tell
If I may, I would suggest this simpler command to get to the essential stuff, because your terminal command in your original post gives a s**tload of data :). Here’s what I used a few years ago in one of my apps:
set scanOfSystemHardwareRaw to (do shell script "system_profiler SPHardwareDataType")
and it gives this back:
Of course I removed some data like the serial number but it works. And then when I wanted to get to some part of it, like in this case the serial number, I used this code. It’s a loop:
set {thisModelName, thisModelIdent, thisModelCPUName, thisModelCPUSpeed, thisModelCPUNumber, thisModelCPUCores, thisModelRAM, thisModelBusSpeed, thisModelSerial, hardDriveSize, hardDriveFreeSpace, currentIpv4, currentOSVersion, currentShortName, currentLongUserName, currentBootVolume, currentComputerName, currentPrimEthernetAdd, entourageDatabaseSize} to {"UNKNOWN", "UNKNOWN", "UNKNOWN", "UNKNOWN", "UNKNOWN", "UNKNOWN", "UNKNOWN", "UNKNOWN", "UNKNOWN", "UNKNOWN", "UNKNOWN", "UNKNOWN", "UNKNOWN", "UNKNOWN", "UNKNOWN", "UNKNOWN", "UNKNOWN", "UNKNOWN", "UNKNOWN"}
try
set scanOfSystemHardwareRaw to (do shell script "system_profiler SPHardwareDataType")
set myText to every paragraph of scanOfSystemHardwareRaw --changed the data in the previous line to a list of paragraphs so it can be cycled through with the repeat command.
set myOldDelimiters to AppleScript's text item delimiters --save the current ATID for later
set AppleScript's text item delimiters to {": "} --this is what separates a kind of data from the value. ATID is very useful for this sorta thing.
repeat with myCounter from 5 to (count of items in myText) --repeat through the paragraphs, starting at item 5 because we know there is nothing before #5, we save a couple of milliseconds this way :)
set myLine to item myCounter of myText
if myLine is "" then
--there is nothing, so ignore for this item in the list
else
set {kindData, theData} to text items of myLine
--display dialog (kindData & return & theData)
if kindData contains "Model Name" then set thisModelName to theData
if kindData contains "Model Identifier" then set thisModelIdent to theData
if kindData contains "Processor Name" then set thisModelCPUName to theData
if kindData contains "Processor Speed" then set thisModelCPUSpeed to theData
if kindData contains "Number Of Processors" then set thisModelCPUNumber to theData
if kindData contains "Total Number Of Cores" then set thisModelCPUCores to theData
if kindData contains "Memory" then set thisModelRAM to theData
if kindData contains "Bus Speed" then set thisModelBusSpeed to theData
if kindData contains "Serial Number" then set thisModelSerial to theData
end if
end repeat
set AppleScript's text item delimiters to myOldDelimiters --very, very important to do this in the end.
end try
I put everything in a try…end block just to be safe. First I set all the values to default “UNKNOWN”, this way we do get some info and we’ll know this way which one is missing.
Have fun!
Model: MacBookPro8,2
Browser: Safari 534.51.22
Operating System: Mac OS X (10.7)
You have all been extremely helpful and I greatly appreciate it!
I just have a few more questions:
I have the script working to put the information into a specific cell… but I need it to go to the next row if the serial number is already filled in. How do I right a repeat loop to check and see if the value of the cell is blank (if not blank I want to make it move to the next row)? Along with that I need to be able to place the values into the next open cell and I don’t know how to set the value without stating a specific cell.
The IPv4 address is apparently only the ethernet address. Is there an option to pull the wireless address and wireless mac address as well?
Then you need to keep track of the cells. I would define a few properties (variable that remain accessible throughout the script) for your rows and columns. Something like this:
property colOfIPaddresses : "C"
property colOfSerialNumbers : "G"
property startRowNumber : 3
property endRowNumber : 50 --or any other numbers. this is just for the sake of the example.
and then do a repeat loop like that (untested):
tell application "Microsoft Excel"
repeat with currentRow from startRowNumber to endRowNumber
if value of cell ((colOfIPaddresses & currentRow) as string) is "" or value of cell ((colOfIPaddresses & currentRow) as string) is missing value then
set value of cell ((colOfIPaddresses & currentRow) as string) to (item 2 of MachineProps) as text
exit repeat --no need to continue, we found an empty cell and put data into it.
end
end
end
of course, this is not the fastest or most efficient way to do it, but is simple enough for you to implement, I think. Basically what it does is scan the contents of cells row by row in a column, and once it finds an empty cell, it puts the current info in the cell. But not knowing your exact needs, nor the wanted result with your Excel document, this is just a best guess scenario. Sorry!
Unless I am mistaken, the IP address provided is the one who is currently active. The OS can keeps track of many IP addresses but will only use one, and the fastest link always wins. Therefore, ethernet trumping wifi, you get the IP of the ethernet connexion. I don’t know of a way to get the other IP addresses. Perhaps the full system report would?
Good luck!
Model: MacBookPro8,2
Browser: Safari 534.51.22
Operating System: Mac OS X (10.7)
Basically what it does is scan the contents of cells row by row in a column, and once it finds an empty cell, it puts the current info in the cell. But not knowing your exact needs, nor the wanted result with your Excel document, this is just a best guess scenario. Sorry!