Importing XML format System Profiler reports into FMP

[markuswarren]

Good morning all.

I've got an interesting one for you this morning. The System Profiler Utility in Mac OS X (I'm using 10.4.8, but it does the same under any version of 10.3 or 10.4) allows you to save a report as XML, whether you run the application, or if you use the command line version (in this way you could use cron to schedule a report every month, for example).

I'd like to be able to import this XML report from each of the computers I have into a filemaker database, as it would allow me to see the specs of all the machines I have, plus, by keeping existing records when importing new reports I can see a "history" of any hardware changes on the machine.

I'm pretty new to the XML import facitlies of FMP but I'm fairly sure I'll need an XSL file to tell FMP how to deal with the data. I've looked at a few example ones and the XML files themselves are not that complex, if you look at the system profiler report you'll see that there are muliple items with arrays and such, so it's not your normal column and row type of data.

So, the question, can I do what I want, and could anyone give me any tips on what the XSL template might look like?

If it's not possible to do with FMP then is there another solution, preferably mac based, but if not so be it, which would allow the import of this XML data into a database.

[Fenton]

Yes, you can certainly do this. It's going to be a bit tedious, depending on how much data you want. Using command line you can specify which sections of the System Profile you want, even multiple sections, then write them out to xml. I have several examples doing this, all of them using AppleScript and do shell script, works well with FileMaker.

I've also done some of the xsl for import into FileMaker. Only for some hardware specs however; as I said, tedious -]

I'm attaching both files, one with the FileMaker import stuff, another one with various ways of getting specific pieces out using AppleScript and command line.

System_Profile_xml.zip

system_profiler.zip

[markuswarren]

Thank you very much indeed! I shall download and view the attached files :)

[markuswarren]

Had a quick look, hence the next silly question. Is it possible for the XSL stuff to deal with an item such as the memory, or usb devices, where the data isn't "flat" but there are nested items, eg; for memory you'd have an item for each slot?

[Fenton]

Yes indeed. This is where xml/xsl shows its superiority to flat formats such as tabbed or csv. However, there are considerations. First, if the data is nested, it is relational. FileMaker is also relational. To import relational data into FileMaker you do a separate Import for each table, with a unique ID to tie them together.

It's the same with xml/xsl. I would say that the main Hardware section is the parent record. The Memory, and other sections that would have multiple items, are children; each should have their own table. Each DIMM would be imported as its own record.

[Alternatively you could create sets of fields for Dimm0, Dimm1, etc.. But that's kind of clunky.]

Memory would need its own section of xsl. I put it in a separate xsl file. It keeps the files simpler. I imagine you could put it all in one xsl file, and set the Import order to only match the fields you want. I prefer a separate xsl.

Each xsl would define only the fields for its table. To get multiple items you just need to start your parsing at the level where the multiple item is, rather than above it where the data is one.

You would also need to bring in a unique ID. You can "reach up" in the xml, to get one from a higher level. In this case I'm using the Serial Number.* If none is available, there are other methods; xsl can generate a unique ID, and populate the children; or you can do it with a script in FileMaker during the multiple imports.

This new file is just the Hardware & Memory sections.

*You'll notice that my computer does not have a Serial Number. That is because Apple replaced the motherboard (one of the early ones). When they do that they don't always reset the Serial Number; unless you tell them to. I didn't know that. It hasn't caused any problems so far, though I worry what Microsoft will think of that, since I have Windows installed also.

HardMemory_xml.zip

[markuswarren]

Thanks, I'll have a look at that. Good to know it can be done, it's something that has been bugging me for a while.

As for the UniqueID thing. I agree Serial Number is nice to use but as you have found, it's not always present, so you need to depend on something else, and I'm thinking that the Primary ethernet address might be a good field to base it on, though I have a feeling that might be one of the nested values used, which could make using it as a uid a bit problematic.

Edited October 6, 2006 by Guest

[Fenton]

Well, you can get any data anywhere in the xml with xsl. So it would be available. You just have to figure out where. The MAC Address is in the "SPNetworkDataType", it appears more than once. I'd have to do some counting to see which "dict[n]" to use.

P.S. If you got the Network section, it would be 2nd, after the Hardware. The 1st instance of MAC would be:

Edited October 6, 2006 by Guest
MAC

[markuswarren]

OK, maybe if I used my brain now and then I'd realize that it might be an idea to change the table/layout being viewed and THEN import the data.

Correct me if I'm wrong but as I'm importing into different tables, for each "pass" (one for hardware, another for memory) I'd need to switch the table I'm viewing to thus allow me to import data into that table.

I think I get where you're coming from when you said doing this was tedious, as I doubt there is a way to automate this switching, plus if I wanted to import the whole hardware file I'd be looking at a whole lot of layout switching before an import.

Edited October 6, 2006 by Guest

[Fenton]

Yep, apparently you still have to go to the correct layout first to import xml.

[markuswarren]

That does make a fair bit of sense, but it wasn't, at first glance, intuiative, and it does not make this kind of importing overly easy, but hey ho, it does indeed work

Thanks for all the information and example files. I'm going to have a play around and see what I can come up with.

[Fenton]

Yes, you need to switch layouts for each table's import. But you can script this. Each Import is importing from the same xml file, so that can be set into a global Variable ($$xml_file), whereas each Import uses a separate xsl file ($xsl_file), and happens on a separate layout.

I often pre-define the paths as unstored calculation fields, just so I can see my mistakes -]

[markuswarren]

Firstly, thanks for all the help so far :

I'm looking at the network section of the system profiler report and I'm wondering if you could help me out.

I've modied the memory.xsl file so it's finding the correct section in the xml file. The problem is that the first part where it would get a separate record:

Is getting me the items Built-in Ethernet, Airport and Firewire as three records. That's fine, however, I can't seem to be able to get the other items, such as whether it's a static ip, or dhcp, and then getting the items below that, for example, in the case of it being dhcp, things such as dhcp_domain_name_servers.

As you can tell I'm a little new to XSL and so I'm not sure what I should be searching on. Any help greatly appreciated :)

[Fenton]

There's different ways to approach this; depending mostly just want a "record" is in all that Network data. I can't really say, 'cause I don't know enough about it. My profile xml tends to only have one of most of this stuff, perhaps 'cause I'm a network weenie -] So, with that in mind, this is how I'd go after the stuff you mentioned.

You'll notice I used other tests, besides just position ones [1], [2], etc.. Because it is hard for me to say whether those positions would always be the same in different reports from different computers. You can also test by name of the element or its contents. Which is safer, if the position could vary; but is bound to be slower.

Apple has done a good job with the plist xml structure. It is abstract and flexible, so you can put all kinds of different stuff in it, with any number of levels in the hierarchy. But it makes it a little more work to get it out.

<?xml version="1.0" encoding="utf-8"?>







	

	0

	

	

	

		

		

		

		

		

		

		

		

	

	

		

			

			



			

			

				

				

				

				

						

			

			

			

				

				

				

				

			

			

			

			

				

					

					

						

					

					

				

	

				

					

					

						

					

					

				

				

					

					

						

					

					

				

				

					

					

						

					

					

				

				

					

					

						

					

					

				

				

					

					

						

					

					

				

				

			

		



	

	

[Thirdwaver]

My situation is similar but it has a twist. I'm pretty capable inside of FMPro, but XSL/XML's documentation is overwhelming and none of the reading I've done (and I've done a lot) talks about what I'm trying to do.

I have a table that tracks tasks for technicians. We have an oracle system here that maintains information about the machines themselves. The oracle team has provided me a URL that when I embed the key field into the url, it spits back the contents of the record in XML format. I need to store a snapshot of that info in the task (rather than relating it). I've got a basic XSL sheet written which translates some of the XML to the format Filemaker is looking for.

The problem is, while some info is one-per-machine (i.e. serial number), some of it can repeat (i.e. OS - the machine could be a dual boot machine). To keep the number of fields manageable, I configured the task record schema with repeating fields (4 repetitions each) for the fields which have multiple values.

I really don't want to recreate the related structure that Oracle has because it's overkill for storing a snapshot. Is it possible for me to load repeating fields during an import? I'm suspecting that I need multiple tags inside tags but I'm not sure how it should look. I've attached my XSL as it stands now...

<?xml version="1.0" encoding="UTF-8"?>



   0

   

   

   

      

      

      

      

      

      

      

      

      

      

      

      

   

   

      

         

            

         

         

            

         

         

            

         

         

            

         

         

            

         

         

            

         

         

            

         

         

            

         

         

            

         

         

            

         

         

            

         

         

            

         

      

   

But keep in mind that I stopped when I got to my first field (OS) that could return 1-4 values in XML. Any help would be most appreciated!

Sean