How Unix Edge Collects Performance Data to Keep Refineries Humming

Session date: Apr 10, 2018 08:00am (Pacific Time)

In this webinar, Frank Inselbuch from Unix Edge will share how they collect performance metrics for the industrial machines at their customers’ chemical plants and refineries. They do this by providing specialized devices to their customers that support analog, digital and optical sensors including GPS, temperature, pressure, humidity, hall effect, proximity, and even analog gauges. They store the data collected in an InfluxDB database and use Grafana to build custom dashboards for their customers. Frank will detail the aspects of their test and selection process and how they chose InfluxDB. He will then review their setup and dataflow going into the details of the schema design, retention policies, and continuous queries.

Watch the Webinar

Watch the webinar “How Unix Edge Collects Performance Data to Keep Chemical Plants and Refineries Humming” by filling out the form and clicking on the download button on the right. This will open the recording.

[et_pb_toggle _builder_version="3.17.6" title="Transcript" title_font_size="26" border_width_all="0px" border_width_bottom="1px" module_class="transcript-toggle" closed_toggle_background_color="rgba(255,255,255,0)"]

Here is an unedited transcript of the webinar “How Unix Edge Collects Performance Data to Keep Chemical Plants and Refineries Humming” This is provided for those who prefer to read than watch the webinar. Please note that the transcript is raw. We apologize for any transcribing errors.

Speakers: • Chris Churilo: Director Product Marketing, InfluxData • Frank Inselbuch: President, Unix Edge

Chris Churilo 00:00:00.411 So welcome everybody. My name is Chris Churilo. And I work at InfluxData. And today we have a customer use case webinar with Frank from Unix Edge. And if you have any questions, as I mentioned, please put them in the chat or the Q&A. And we have an actual break of the presentation. Halfway through them we’ll answer those questions. If not, we’ll definitely get to all the questions towards the end of the presentation. So with that, I’m going to pass it over to Frank. Thank you, Frank.

Frank Inselbuch 00:00:32.351 Thanks Chris. Good morning everyone. I’m Frank Inselbuch. I live here in Houston, Texas. I’ve been here since 1988. My background is Computer Science from Washington University in St. Louis. But I joined the oil and gas industry right away, right out of school. I’ve been working with Time Series Databases for the entire time from various vendors and flavors over the year. And I came to InfluxDB about two years ago when I set out on my own after leaving Accenture to build my own solution. And I was looking for a Time Series Database that would give me the kind of performance and features. And I’ve been really successful with that product. And I thought I would talk about my solution and some technical details about it.

Frank Inselbuch 00:01:18.692 This presentation has some slides about hardware that we won’t necessarily go through. But if people are interested in that, the materials are there and I might tough upon them briefly. So basically, what we’re talking about is what everyone’s calling the Internet of Things. And specifically the Industrial Internet of Things. So more and more devices are connected. And the cost of these things has gone down, the cost of the single-board computers, the cost of the modems, the cost of the telecom. What’s it done is enabled things that were previously too expensive to instrument to be connected and to get data from. So anything you can imagine is now cost effective to put a node on, to put a little machine. I call it an Edge Device, right?

Frank Inselbuch 00:02:07.534 To give you an idea of the value of remote monitoring of equipment, an offshore oil and gas platform, a producing platform in the Gulf of Mexico, has various equipment on there. And they have to produce, for example, their own electric power. Because you can’t just run a power cable out miles into the ocean. So they have a gas turbine out there where they make their own electric power. Now that goes down, and they don’t have electric power, then they can’t produce oil. And depending on the price of oil, they could be losing something like $43,000 a minute. So for them, it was absolutely critical to do remote monitoring of equipment. But these days, that same technology can be used for things that don’t have that much money on the line. So you might think about that, “Well, if I were going to build such a platform, I could put that together pretty quickly.” I could get myself a single-board computer like a Raspberry Pi. We all know those things are inexpensive. And I could get a modem from Verizon, a MiFi or something like that. And then, I could get a little board or something that will read analog values connected up to a sensor. And I’ve got a business. I’m up and running. And then, I could put it on the cloud so I don’t have to host any computers. And I could be in business doing something.

Frank Inselbuch 00:03:26.773 And so here’s kind of a summary of how inexpensive it could be to build an edge device and to bring a solution to bear. Use single-board computers, and a hotspot, or a modem, there’s an idea. A sample of an instrument might be just a simple thermocouple to measure temperature. But it could be pressure sensor, or CO2 sensor, or whatever kind of sensor that you can imagine. Hall effect for magnets, and switches, and things like that. You need some wires. You need a box or some kind of an enclosure for it. And then, you need some kind of a database or some kind of a system that you probably want to put in the cloud so that it doesn’t go down when your house is offline. And maybe that’s $50 a month. And you think, “Well, I’m in business and your man stuck in Benjis right away.”

Frank Inselbuch 00:04:13.516 But I would say that the devil is in the details. And sometimes things that are very easy to say on paper wind up being very difficult in practice. And my perfect example of that is the binary search which I think everyone is familiar with. Basically, if you’re looking for a number in a sorted array of numbers, you’d say, “Is it in the top half or the bottom half?” And then, you could take each piece and keep looking. So if it’s in one half, then you look at that piece. And you say, “Is it in the top half of that or the bottom half of that?” So I just explained the binary search to you in 15 seconds. But the reality is that that code was not published without a bug for a bunch of years after the algorithm was invented. And there’s some interesting statistics about how there was a bug in the Java programming library implementation of that binary search for years. So my point is that the devil is in the details. There’s all kinds of things that can go wrong. And here’s just some of the things that you might encounter, issues with cybersecurity, issues with backups, hardware failures, data outages, account sim cards being unseated. There’s all kinds of things that can go wrong. And what I’m going to cover here is some of the steps I’ve taken. And some of the ways that I’ve mitigated that risk to make the system be as reliable as it needs to be to be in production, right?

Frank Inselbuch 00:05:38.943 So when I say actual, what I mean is that this is something that I learned and experienced in the field really working with the system. Just the same way that Commander Adama would say, the “Galactica Actual.” So here’s some of my keys to reliability. The kind of choices and decisions that were made. So what platform to use, what software to use, what pieces of hardware to use, etc. And here’s the platform that I actually used for this particular project. So I’ve used several different single-board computers in 46309. And then, in DragonBoard 410c and many different boards. And each one has its ups and downs, and its benefits and its drawbacks. The biggest value of the Raspberry Pi is that there’s 10 million plus of these have been installed. Which means that if anybody has a problem, someone else has likely already addressed that issue. And you’ve got a hundred million users out there that are testing it for you. So that’s one of the reasons that I chose that particular machine. It’s been very reliable. It’s kind of finicky about power so I have to be careful and supply it with really clean power. But that’s done with some inexpensive hardware devices. And it turned out not to be an issue. Another possible drawback might be the SD cards which don’t have an unlimited life with reads and writes. And maybe some of the other boards that use EMC or something like that are more reliable. But I’ll talk about SD cards in a moment, right?

Frank Inselbuch 00:07:19.213 So in the backend, I wanted to use Linux because I didn’t feel that I needed to pay a license fee for Microsoft Windows. And because I think Linux is very reliable and mature platform. And of course, I have familiarity with that. It’s funny that old guys like me, we were doing Unix and C programming in college. That’s what we did our homework in. And now these days, all the kids are doing .NET Framework, and Java virtual machines, and all that. And now that we’ve come to the Industrial Internet of Things, it’s kind of gone full circle. And that my skills from 1985 are very relevant and applicable on a tiny Edge Device that you want to be reliable. And Linux and C have come out as quite useful. Don’t get me wrong, I love Python. There’s a lot of things that I would prefer to do at a high-level language. And I’ve got some examples of that right there. So I knew also I wanted to be on a cloud platform. And so I looked at Amazon and I looked at Microsoft Azure. And both of these were quite expensive. Now, Microsoft Azure was the most expensive. Amazon Web Services was a little bit less but still expensive. And I wound up using a company called Linode which runs Linux boxes and just been absolutely delighted. I paid them about $150 a month for my two nodes and they do backups. And one of them is quite large that host the production machine. And the other one is my development machine, and my web server, and things like that. And just had really good success with that. As far as media inside the machines, because the Raspberry Pi uses SD cards, microSD cards to be specific, I have a lot of experience in sort of trying different cards to see what’s good and what’s bad. And what I would recommend is get the pretty fast one because when you’re writing the images to these disks, when you’re building them, it can take longer. It puts significant amount of time if it’s a slow card. And I’d also buy it from a reputable brands. So I’ve used lots of different brands. But I wouldn’t recommend just off-brand flash cards from Steve.

Frank Inselbuch 00:09:34.330 And of course, there’s recommendation for how to treat the microSD cards. So SD and flash memory in general, does not tolerate an unlimited number of reads and writes. So certain chatty things in an operating system, I view those to a RAM Disk instead of to the card. So there’s a lot that I keep, and there’s swap file, and things like that so I moved those things unto a RAM Disk. In that way, I don’t read and write to the SD card a lot of times. And of course, we got some recommendations from the Internet there about where to store your SD cards. Don’t put them in Lava. Because it was on the Internet, must be true. So I got a quote for that here. There’s a link if you want to go read about it. So another thing to think about an SD card, is that if you’re going to build an image that will fit on an 8 gigabyte card, you want to make sure that it’s smaller than 8 gigabytes. So you have an IMG file, an image file that you’re going to build. If you build it with an 8 gigabyte card, then when you go to write it to another 8 gigabyte card, there’s a chance that it will be a little bit too large. And it could happen because the SD card that you had targeting, that you’re trying to write to has got some bad sectors, or blocks, or whatever they call it. And so those have been marked as bad. So it’s just a tiny bit too small for the 8 gigabyte image. So to avoid that I write my images on a 4 gigabyte card. That’s like my master copy of the particular revision of my software. And then, I make an image file from the 4 gigabyte card. In that way, I know always fits on the 8 gigabyte card when I go to write them. And of course, that can be done with 8 and 16 into the future as a 4 gigabyte cards become more and more difficult to find. I’ve got a small collection of them here.

Frank Inselbuch 00:11:24.158 So one of the things that you want to do when you’re building a solution, and this is where InfluxData and InfluxDB have been really terrific, is I don’t want to have to do a lot of manual steps when one of my devices, one of my Edge Devices, my UEDs, Unix Edge Devices, when one of those boxes comes online, I don’t want to have to run around and do a lot of configuration. One of the nice things about my solution about InfluxDB is that, I can just turn the box on and he will self-identify. Basically, he finds a unique identifier within the hardware, and using that unique identifier, you start sending data to InfluxDB. InfluxDB will just go ahead and create the data. I don’t have to pre-provision or pre-allocate or pre-configure anything in my schema for that, it just starts collecting the data. Now I will layer in the metadata later to say, “This data is coming from this particular type of truck or this particular type of piece of equipment.” But basically, I use the unique identifier of the hardware to allow these devices to self-identify. Now, when I wrote this line I was using the MAC address of the ethernet device on the Raspberry Pi 2 or 3. But I’ve also wanted to support these devices that don’t have an ethernet adapter. So there is no eth0 device on these. So I’ve switched my solution to go over and use the CPU ID which is located in a file under Linux /proc/cpuinfo or something like that. And so that way, my solution will run on both the nodes that have an ethernet 0 and don’t have an eth0. And I don’t actually use eth0. It’s just that because it was present, it had a unique MAC address. That was the identifier that I was using. So now I’m using the CPU ID and so that works on the Pi Model A or whatever they’re called that are the smaller low-powered ones. And what I found is that it’s really easy to cobble together a solution. But when you’re going to production, you need to be really disciplined about what you’re doing with versions and things like that. Because a bug can creep in, and then you don’t want to replicate that bug. You don’t want regression. So I’ve been using Git and that’s where I keep all of my various pieces of software. It also serves as a backup for me. So I like the fact that I’ve got multiple copies of the software on different computers, different hard drives. And also that I can track changes that I’ve made. And it’s multiplatform so some people that I engage are working on Mac, and some people are on Windows so that works really well.

Frank Inselbuch 00:14:08.194 Collaboration is important as well because I’ve got a developer in London, I’ve got a developer in Russia. And if they’re going to do some work, they need a way to share, not just the files and the source code, but also a way to communicate. And I think for that we use Slack which we found as a pretty handy way of sharing, being able to chat, but also sharing files and things like that. So when I have an installer go to the field, for example, he can go to Slack and just take a picture of the piece of equipment. And then, we get what the model number is and what the customer’s identifier is for that machine. And then, we can configure that metadata just to line it up with that unique identifier that’s in the hardware, right? So those are my three most active channels on there. There’s the install channels and some other ones too. And it also supports audio calls which is nice when someone is in the field.

Frank Inselbuch 00:15:05.914 So for reliability, there several things that we do. GPS signals are not available all the time. And sometimes the equipment that I’m using, the customer’s equipment is inside a tank, or something like that, and doesn’t have a clear view of the sky. So I actually use GPS information from two different sources, one is from the cellular towers and one is from the satellites in the sky. So that way, I have a better chance of getting a position for my piece of equipment. Of course, if you get position from two different sources, you run into the challenge of, well, which one is right? If they are slightly different, which one is most accurate? So there’s certain amount of logic that you have to put on top of your data collection. That and also spike detection and some disregard some start-up values that are ridiculous to some. Some logic that you put on top of any piece of data that comes in.

Frank Inselbuch 00:15:59.447 I also implemented, and this was key, is remote access to these devices. So I’m able to log into my devices whenever they’re online. And I do that with what’s called a reverse tunnel. In that way, I don’t have to have a public IP on that box. But I can still get on to it if I want to do an update of the software, or reboot it, or something like that. I also can send an SMS message to the box to tell it to reboot. So even if I don’t have a good clear connection, or something like that, I can do a reboot. And of course, the devices update themselves. They use Git commands to, shell script, to go and get the latest version of the software on a schedule. In that way, I’m able to have the devices update to later versions of the software at the time.

Frank Inselbuch 00:16:46.374 Key piece of reliability is testing. So I had some experience with hardware problems, some faulty GPS antennas, so I had some bad power converters. I’ve had to go down to Tosche Station to get some new power converters. And I had to be able to run these devices with different battery levels in the trucks, and then the compressors, and things like that. So there’s a lot of testing that we do. So my test bench over here, I’ve got signal generator, and a power supply, and various other oscilloscope, and things like that, to be able to test different scenarios, and come up with a very robust platform. I really like the physical log when I do testing. So just a little book that I write it in. It seems to be better than trying to do that electronically. So I have a book when I do testing.

Frank Inselbuch 00:17:38.702 You wouldn’t believe it. But my little company that is just collecting temperatures and things like that, it seems to be of interest to some hackers. And there’s a bad naughty person somewhere out there in China that was trying to hack into my machine. And if you take a look at your authorization log, you’ll see that people are trying to login with sysadmin, or DBA, or root, and just trying passwords again, and again, and again. And that was quite concerning because, although I don’t have anything worth stealing, people can be malicious just for the sake of fun. And so I implemented a thing called Fail2ban So that means, basically when someone tries to login multiple times, it will temporarily ban their IP address for a while. So that guy 221.194, they would block that IP address for a while until it will allow it in later unless he does it again, right?

Frank Inselbuch 00:18:35.038 One of the keys to not getting in trouble with data theft is don’t have any data that for someone to steal. So temperatures and pressures, they’re associated with the CPU IDE are not particularly valuable. There’s no context for them. But I don’t have any personally identifiable information. There’s no social security numbers, or bank accounts, or anything like that, so that kind of protects me from having anything valuable stolen.

Frank Inselbuch 00:19:00.693 I need to support a range of voltage coming from these as the batteries go down on trucks or as they start to get charged. And so, some of the machines use 2 batteries so it’s a 24 volts system. Some use single battery at 12 volts system. So I investigated and started using these little devices called Buck-Boost. And the buck will keep the voltage below at a maximum. And boost will bring the voltage up. It’s kind of an interesting device because it’s got some oscillators and some capacitors in there. And the younger people that work with me say that they can hear them running because they sort of whistle. But at my age, I can’t hear anything above 11 thousand hertz and so I can’t hear them. So that works out pretty well for me.

Frank Inselbuch 00:19:46.771 I track the voltage. The customer didn’t particularly ask for that value to be tracked. But that’s an example of a piece of data that I do track because it turns out to be quite valuable, not just for my purposes, but also for the customer. If they’ve got a bad battery or it’s not charging, then it’s good for them to know about it. But also, if the battery starts to run down, I can shutdown my box. And I can say, “Look, I’m not going to collect data because I don’t want to be responsible for driving your battery voltage down below a lower limit.” Unless you know anything about battery technology, certain batteries don’t do well when they get deeply discharged and as opposed to deep-cycle batteries. So I wanted to be sensitive to that. And that’s an example of a feature that was added to the product after it went to production. So one of the things that you learn when you went into the field.

Frank Inselbuch 00:20:36.593 So as far as reliability, you could write all kinds of software yourself. You could say, “I’m going to write my own database or I’m going to store this in a text file.” But my experience has always been that if I can buy something that is commercial, and in production, or open-sourced in production, and then used that, and I can reduce the total number of lines of custom code, I’m going to have a solution which is more reliable. I don’t want to be in-charge of debugging thousands of lines of code for my solution. I’d rather be in-charge of debugging the 64 lines of code that I have for this customer which basically embody his business logic. So I don’t need to be thinking about things like what is the key store, and what disk I’m I going to store this data on, and things like that. I can worry about things like how many times in a row this guy flipped this switch. That’s the kind of business logic that we want to encode.

Frank Inselbuch 00:21:32.832 I do have some code in there for what I would call analytics which is the very lowest level analytics. I’m not doing vibration analysis, although I could. There’s enough power on these Edge Devices. But that it’s really been more about is it on? Is it running? Is it idling? And has it been idling for a long time? And when is it expected to run out of fuel and things like that? It’s analytics but it’s pretty low-level analytics.

Frank Inselbuch 00:21:57.498 Now the user-interface that I use is Grafana which has been terrific. And the performance of InfluxDB has been tremendous. And that’s what’s required, right? Because if you’re going to have a user-interface that’s reading data directly from the database and the database is not responsive, then your user-interface will be unresponsive. And you know that users are unwilling to wait. They get impatient quite quickly if they asked to bring up some data and it doesn’t come up immediately. So that’s worked quite well. But Grafana is an open-source project as well. And they support multiple databases. So sometimes I have manipulated the data to get it into just the right format for Grafana to display it. So there’s some code that I do for that.

Frank Inselbuch 00:22:42.953 And then, I generate my reports into Microsoft Excel using a Python module called XlsxWriter. So that way, I can get the report nicely formatted for the end-user. And I did that because I noticed that he was going to the Grafana page, and then copying all the data, and pasting it into Excel. And I said, “Well, instead of you doing that, I’ll just give you a link here, and you can just download the Excel report yourself.” That’s a nice thing about Python is that anything you want to do you can just search and someone’s probably written a module for it.

Frank Inselbuch 00:23:17.679 So here’s an example of one of the dashboard screens. And basically, it’s a list of all of their machines, and its location, the model number of the machine. And then, color coded on the cell is based on whether or not it is beyond its maintenance window, whether or not it’s being aggressively utilized, and things like that. All that is based on data directly from InfluxDB. So run hours since reset is done by basically an integration of data under a curve. So basically, an accumulator. So if the machine is on, then I keep accumulating the time that it’s on. And I do that using interpolated values from InfluxDB. So basically, I can take samples. In that way, I can get down to the granularity that’s required for that to be pretty accurate. It’s interesting that I went back in somewhere in the middle of the project, and I increased the granularity, and went and recalculated all of the run times. And there were some small changes. But it was nice to see that I could get more accurate data based on the raw data just by making a slight change in my code, in my queries.

Frank Inselbuch 00:24:28.212 So of course, I use a graphical component because everybody wants to see geographically where is their stuff. So there’s one machine way over here on the left side, that’s my house. So that is when I was testing a box so it came up online over there. And it’s all based on the GPS position. And pursuant to my previous statement about not writing code if I can avoid it, all this is on the Google Maps and with their little API. So I don’t have to draw these maps. I don’t have to. I can just say, “Put a pin here, and that kind of thing. And label it.” And of course, when you zoom in, the density of this cluster of machines would spread out. And so you’re down there to a single machine. And you can even click on the machine, and go all the way down to a detailed page with just a small amount of JavaScript, or something like that. When you get down to the details of the machine, I can take a look at specific events when it went in for maintenance, what it’s last known position was, and the time of that last known position. And then, the trend charts that tell you things like what’s the battery voltage doing. So you can see when the battery voltage is falling off. And then, you can see when it’s being charged, and things like that. So that’s another one of those screens.

Frank Inselbuch 00:25:37.656 This is another customer and he’s got a vacuum truck. And he wants to be able to charge the customer for certain activities. And in the past, you just had to sort of guess and estimate what the truck was doing. So if the truck is at their maintenance facility, or it’s driving over to the customer site, or it’s waiting in front of the customer site, they get in through the gate, or it’s sitting waiting inside the customer facility, or it’s actually doing vacuuming or discharge, those are all different events. And basically, I have got some sensors on, some switches, and some levers. And based on the position of those switches, I encode that data as a run state. The state that it’s in so it can be run time, idle, facility travel, etc. In that way, they can bill. And that’s kind of an important concept as well is just that you can bill something that’s cool and then engineers like to look at it. But there has to be some monetary value. Some commercial value to it to the business for them to want to run and operate the system.

Frank Inselbuch 00:26:37.445 And my model is a monthly subscription fee per truck, per asset, per compressor, per machine. And so that way, you need to be showing them how you’re preventing them from unnecessary maintenance, or you’re reducing their fuel consumption, or you’re enabling to build more accurately, or quickly, or things like that.

Frank Inselbuch 00:26:57.429 So I put in this presentation some technical information about InfluxDB. Things that I learned over the years. And so lots of different ways to store your data inside InfluxDB and I chose to store all of my time series data in an unfortunately named table called compressor. Because my first project was compressors. But later on, when it was vacuum trucks, it also goes in the compressor table. And once you built a measurement in InfluxDB, it’s not that easy to rename it. So I could copy all the data into a new measurement. But I just thought, I would go ahead and leave it as compressor. And when I get a chance to bring on a new customer, I can do some renaming and things.

Frank Inselbuch 00:27:40.974 I have a single tag in that database which is that unique identifier that we talked about. So I was using the MAC address. Now, I use the CPU ID. And then, I get fields in there. And that’s another neat thing about InfluxDB is just that if I want to add a new field because the customer added a new instrument, I don’t have to change the schema, or go in and do any maintenance. I just simply start collecting it. And InfluxDB will allocate whatever storage is required for it to put that new field in there. And I’ll talk a little bit about some of the gotchas around that and how you have to be a little bit careful. But it’s really nice that I don’t have to touch the database. So I can just bring a new instrument reading online.

Frank Inselbuch 00:28:21.572 So whenever you have a Time Series Database, that’s a great place to store the time series data. But you really want the other type of data like when did it have maintenance? Even that might be considered time series because there’s a timestamp. What’s the model of truck? Well, that’s not really time series data but I have to store it somewhere. Is it a 24 volt system or a 12 volt system? What is the product that is stored inside this vessel? Those things are not time series data. I call them metadata, business data. And I wanted to put that data somewhere. And I thought, “Well, I could just use a second database for that. I could use MySQL or something like that.” But the idea that you would complicate your solution, and build a second database, and have a second database to support. And then, the concomitant testing that we required when they release a new version, that was unattractive to me. So I just decided to use InfluxDB itself for my metadata. And I just have to be aware that it’s not really time series data.

Frank Inselbuch 00:29:26.366 So I just jammed my metadata into a measurement. InfluxDB gives it a timestamp which I largely ignore. And if I want to change the metadata, I just put a new value and it will get a newer timestamp. And I can keep them as long as I like or I can put a retention policy in there to let the old ones drop off when they’re done. And then, when you’re going to examine the metadata, you would just look for the most recent one. Because that’s the one that’s currently in effect. Now if some solutions that you really want to have the metadata historized, so that can be handy as well. To say, “Well, the product was A-if the product is A now. But before 10 o’clock in the morning it was product B.” So that’s one way to approach it.

Frank Inselbuch 00:30:06.485 I would encourage people to get feedback to InfluxData if they think that other features or other types of data stores might be valuable. I’ve mentioned this one to them. And I think that there’s some critical mass around the idea of an asset framework. Some kind of an encapsulating database that you can link values together. Think about it this way, a well has many pressures and temperatures associated with it. So in InfluxDB, you can certainly store all the time series data, all those temperatures, all those pressures with no difficulty. But how are they connected? How are they associated with each other? Well, you could build a more complicated data structure inside InfluxDB where you would have each well would have a field for each one of those values, and you would record-anyway, I think that another idea might be to have some kind of overarching framework that describes the connections between multiple pieces of time series data.

Frank Inselbuch 00:31:10.073 So another thing that would be nice inside InfluxData and this is one of the reasons that I use Python, and I talked about formatting my data, is that sometimes you want an integer value to be represented by a text string. And it would be nice to be able to configure that inside the database. So various production Time Series Database s have an answer to that InfoPlus.21 and OSIsoft PI. But what I do for now is I just simply store both the text representation of it and the integer representation of it just for convenience and ease of use. But in the future, I would like to be able to either have Grafana do that decode for me or to have the database itself. And you can simply say, “How do I want to display this integer value? And give it the choice of in [inaudible] it’s what’s called a selector or it’s called a-I forget in OSIsoft PI.”

Frank Inselbuch 00:32:05.233 I see your question about how we’re getting the data into the database. We’re going to get to that in a little while there.

Frank Inselbuch 00:32:10.513 One of the challenges with time series data from instruments is that the world is not as smooth as it is a lab, in your mind. So I’ve got a height sensor, an ultrasonic sensor that measures the bed height of these trucks. But when the truck is bouncing around, I might get a spurious value which we call a spike. So the bed didn’t go from 3 feet up to 7 feet up, and then back to 3 feet up in a second, right? So there’s a Tukey outliers algorithm which I was introduced to by the folks at InfluxData at their conference InfluxDays in New York. So I’ve implemented that but I’m testing it now. And that will go into production to just discard the values that are unreasonable. And I’ll keep the raw data cause it’s always good to have the system of record. But I’ll have a second parallel stream of data where I have the cleansed data in there. And that’s an algorithm and based on the work of a mathematician named John Tukey, who has also been incredibly important in the world of Fast Fourier Transform. And so we owe a debt of gratitude to that guy.

Frank Inselbuch 00:33:16.540 So here’s how the data flows. And we’ll get to your question in a moment there. But basically, I have a custom program that I use to read my sensors. So depending on which the sensors where and what generation of the software, I had a C program at first. More and more I’m using Python to get the sensor data. And that’s because a lot of people have libraries for their particular sensors. But if you need to be really low level, you do it in C, and you get the data. Now once I’ve done the data, what I’m I going to do with it? Well, I want it to be in a persistent format. In other words, I want the data to stick around if the telecoms, are down or the machine reboots, or something like that. I don’t want to lose any data.

Frank Inselbuch 00:33:56.951 So there’s various queuing technologies. I could use MQTT, or some other kind of layer that would buffer that data, and deliver it when it could. But I was unfamiliar with those things when I first implemented this solution about three years ago. So I just went with my good old friend, the trusty text file, to store this data. And there was another reason that I did that was because I didn’t know which backend database I was going to be sending it to. And I wanted to support multiples. At the time, I was using a different database when I went to InfluxDB. It was about two years ago. So there’s very little required for me to change to switch from one backend to another. The whole edge device program remained the same.

Frank Inselbuch 00:34:40.095 So I generate a text file. And then, I compress the text files. So that’s another thing about text files is that they compress nicely. And also the encryption that’s available in the compression is a nice way for me to protect the data. So it’s all password-protected when I compress it. And of course, you’re sending the data over cellular, and you’re tracking how much data your sending, it’s nice to be able to compress it down to a smaller size.

Frank Inselbuch 00:35:04.637 I see that-yeah. So then, how do I send the data? So I send the data over to SFTP which is basically the SSH protocol so it’s secure. And then, I send it over whatever the telecom layer is. So it could be Wi-Fi or it could SigFox, or it could be cellular. Most commonly, it’s cellular because I don’t know where these vehicles are going to be. And so they move around and I need to have coverage to every place that I go. And certain cellular providers are actually more reliable in certain places than others. So I have both relationship with Verizon and with T-Mobile.

Frank Inselbuch 00:35:43.951 So once I get the data, the compressed file arrives, then it gets decompressed. And then, text files get processed by a custom program which is written in Python which sticks the data into InfluxDB. I’m actually going to be learning about MQTT some more tonight, Thursday at a meet up here in Houston. I’ve been looking at Telegraf and the various plugins. At the time that I was first looking at Telegraf, one of the plugins that I would thought about using was not production yet. So I just said, “I’m not in a rush. I’ll let that kind of grow naturally.”

Frank Inselbuch 00:36:21.754 Now, there’s been some surprises that I’ve had with InfluxDB. There’s some things you have to be careful about. So I get a few sort of cautionary tales about InfluxDB. So if you’re familiar with InfluxDB, you know that you can just do an insert statement and create a measurement on the fly like that. Now, if I say insert M1, right? And I want to make sure that the data type of a particular field is a float, or an integer, or whatever. I want to be careful and specify that. So you can see that in that first line, I say, “F1 or Field 1 is 17i.” And that means, be sure that you treat this as an integer. Because once the field is created with the data type, you can’t change it. So you could drop the measurement and recreate it, or you could create another field. But just heads up, if you want to force or coerce something to a particular type, that’s something to think about in your insert statements.

Frank Inselbuch 00:37:23.316 You also have to be aware that the tag and a field are different things. The tags are always strings in there. That’s the key. That’s what they’re sorted by in addition to time, very rapid for breaking up the data. But you can have a field and a tag by the same name. And I would suggest that maybe this shouldn’t be allowed. So you can see in that insert statement there, I’ve used a tag of ID where I say, “ID equals Frank.” And I’ve used a field of ID which is ID equals 67. And this is actually what InfluxDB did with that. It basically created that measurement with those two different things, one is called IDE and the other thing is represented here as ID_1.

Frank Inselbuch 00:38:11.172 Let me just clear my throat. Whoops, I meant to mute before I did that.

Frank Inselbuch 00:38:18.651 So that’s something to be careful about as well. I would say if you do a little thinking before you do an insert to make sure that you don’t overlap your texts and your fields.

Frank Inselbuch 00:38:30.781 Let’s see. Another sort of cautionary tale is if people say, “Oh well, I want to move data from one measurement to another. And isn’t it great they have this SELECT * INTO?” You can move all these data from one measurement into another measurement. And it’s really, really quick so I can show you. I’ve got a measurement called Assets 3 and it’s got two tags Asset ID and the friendly name of it, right? And this is an example of a one row of data from it. It’s got a timestamp which I don’t use because it’s metadata. It’s got its unique identifier that we talked about which is a tag which is the Asset ID. It’s got a friendly name. Then, it’s got a couple of fields over here, the model of the compressor, and then this is the unique ID for the device which is located inside it, and which those can swap out over time. Now, if I do a SELECT * INTO. It will copy all 112 rows of data into it. But if I don’t do it properly, then I will wind up with losing the fact that they were tags. And they would be converted to fields. And that’s very bad, right? You don’t want your database to be. And I see that I have copied the wrong data here. So let me just fix that real quick. What you need is a group by clause. And I’ll just change that slide to make sure that everyone can get it correctly, right? So this should say over here, this should say, “group by asset_ID”, friendly” right? That one would preserve the tag keys on the SELECT INTO. And this one which is bad would not. So there, I fixed the slide on the fly. And we’ll go back to here, right?

Frank Inselbuch 00:40:31.779 So here’s a sample of a Python program and some things that I’ve learned over the years. InfluxDB stores all of its data in UTC. And that’s going to be a kind of a constant challenge, is just how do I deal with time? I want to display it to the customer in one format. I want to think about it in one format. I want to be able do math on it in another format. And so here’s some of the ways that I do it. So I get my zones. So I get my local timezone. And I get the UTC zone and that’s with the tz right here, tz.tzutc() and tzlocal(). And here I connected to my database. I get the current time which I always seem to need inside any of my programs. But when you get the time that way in Python it’s called naive. Meaning that it doesn’t have a time zone associated with it. So then, I jammed in the timezone into it with this now .replace. And I specify the tz info in it, right? So now I’ve got a variable called now in the local timestamp. And while I’m at it, I make myself a variable called now and utc, which I do by taking my now local and doing as timezone so from one zone to another. And then, later on in the program I have the ability to use those variables whatever I need and depending on whether or not it’s a program that runs one time true or in a loop, I will have these inside the loop so it’s always updating the time at the top of the loop or something like that.

Frank Inselbuch 00:41:58.737 And here’s how I pull data out of InfluxDB and Python when I get MySQL statement just as a character string. And you can do this substituting things in with the format command in Python. So you can say, “Insert inside these parenthesis a particular string so that you can match.” Then, I basically tell the InfluxDB client to go ahead and query the database. And I always do this statement as one. So I query, then I get the points so to pull the data in. And then, I tell it to treat it as a list inside Python. In that way, I can traverse the list. So I’ve got my list of assets here. And then I can just loop through the assets. And then, I can use each asset one by one to do whatever data that I need out of it.

Frank Inselbuch 00:42:49.078 If I need to write the database, basically you build yourself a JSON structure like that. It has to have the measurement name in it which is at the highest level inside your JSON. It has to have a tags. It has to have a fields. Maybe it doesn’t have to have all of those depending on if you were just writing to-I don’t know if it has to have all those, but I always do. It doesn’t have to have the time for sure. But if you don’t specify time in your JSON and what’s it going to do, it’s going to use the current time when it writes the database. So that’s kind of my sample of a Python program.

Frank Inselbuch 00:43:35.373 So those are all the slides that I have for you today. And now I’ll take a look at these questions over here.

Frank Inselbuch 00:43:40.867 So I’m not using the MQTT protocol right now. But the plan is it’s on my engineering roadmap to go to something better than my text files. But the text files are really good. They stick around on the SD Card. And I don’t rename them or move them until after they’re processed. So they persist in through a data outage, telecom outage, or when I reboot the machine. It will come back up, and if there’s any data that’s remains from the previous run, it will send it through. And I’ve had boxes go offline for three weeks. One of the compressors inside a tank comes out and then it just sends all the data in a gentle. I don’t try to send it all immediately. I send it in baby chunks of 500 data blocks at a time. But MQTT or any other kind of protocol that supports what is called buffering or storing forward where the data would collect and persist, that would be great, especially if I don’t have to write the code to do that but it’s just built-in to the protocol, right?

Frank Inselbuch 00:44:42.362 Data archival and purging mechanisms on InfluxDB. Well, that’s sort of built-in to the database with what they call a retention policy. So you can say, I don’t want this data to stick around here forever. But I haven’t implemented that on my compressor table because I don’t need to. So I have several million values that have been recorded over a couple of years. But there’s no issues with performance. And I get instantaneous response at the user-interface. But you can certainly implement that. You can also use a delete statement. And I’ve done that. Kind of curious is that if you want to delete all the data that’s newer than a certain time, maybe it’s a bug, maybe it’s a feature. But if you say time is greater than, and you specify the time, in the past it will delete everything, but not the most recent value. And that probably has something to do with when it’s getting the end timestamp in the execution. And even between the time that he gets the time and maybe a nanosecond has passed. And so it doesn’t seem to get the most recent one deleted. But you deal with that by saying, “Time is greater than this.” And then, you say, “Unless then.” And you pick a time that’s an hour in the future. And then, that would be a way to make sure that you delete all of those.

Frank Inselbuch 00:46:05.162 Okay. So there’s a question over there. If I’m to describe InfluxDB in three words. Well, that seems a very arbitrary challenge. But I would say, “Performant, flexible, and cooperative.”

Frank Inselbuch 00:46:33.343 Okay. So I guess I don’t see any more questions in the Q&A or in the chat.

Chris Churilo 00:46:37.855 Yeah. So but thank you so much Frank. That was actually a nice unique set of words that you came up with. I like cooperative. I never thought about that one. I actually have a question. And I think we hear kind of a similar conversation when we listen to the guy from Tesla at InfluxDays New York talk about asset management. And how tricky it can be to track the exchange of parts or components for the first, I guess, I call it units that you’re tracking with this Time Series Database . And I find it interesting because I feel like last week’s webinar when we were talking to CJC. Although they were more of a SaaS marketing solution, they actually have the same issue even with servers. Because a lot times they may repurpose the server. So maybe you can talk a little bit more about how you-you talk about tracking of that some asset information in a Time Series Database. But do you need the historical data to understand if a part went from one system to another?

Frank Inselbuch 00:47:45.581 Yeah. So I mean, I would caution people that there are dedicated systems out there for maintenance and work orders, and stuff like that. And so we don’t want to just start from scratch and write all these solutions even if we have a database that can do it. And it certainly handled the capacity. I think that the customer doesn’t want you to write a new accounting system just because you can so to use the right piece of technology. But when you have a limited demand for something like you want to track the arrival, or the departure of a piece of equipment, or it gets assigned to a particular customer, etc, that’s perfectly appropriate to store that inside the Time Series Database . And it can be very useful to know historically. So this compressor was at Shell. And then, it was at Exxon. And then, it came in for maintenance. So you want that history there. And so I have a maintenance table, a maintenance log, which shows every time that it’s appeared at the maintenance facility and then when it leaves the maintenance facility. And I do that by GPS, geofencing. So when it’s arrived at the maintenance facility, it’s within 50 feet of their property, then I regard that as at the maintenance facility. When it goes outside their boundary, it’s gone. And once again, I didn’t write that code or all of it. I used the Google Maps API for that. And they let me draw polygons on the screen for the user to specify what his exact facility, or this refinery, or this chemical plant. They draw that on the screen. And then, I used their library to say, “Is it inside that polygon or outside that polygon?” And yes, we could all write that code ourselves and we could look up the algorithm. But as I said earlier, I’d rather use someone else’s piece of code than my own. But to answer your questi

on Chris, yeah, it can be quite interesting to keep track of the history of the metadata not just the most recent value. With the Time Series Database, I have both. I can ask for last to give me what its current value is, or I can say, “Show me the whole history of it.”

Chris Churilo 00:49:47.740 I have another question about the trucks, the vacuum trucks. So what you talked about you had a compressor that you’re tracking various measurements with. So on that example, so how many things, compressors, and what not, are actually on a truck like that? And then, are you aggregating that data with something on a truck and then sending that up? Or do each of the individual components send the data separately?

Frank Inselbuch 00:50:18.248 So yeah. So for a vacuum truck, the vacuum truck has got several pieces of equipment on it. And I track several things. So here’s a typical vacuum truck right here, right? So basically, they’ve got a valve that they flip to say, “Open or closed.” So you see this thing in the back. And some of the trucks have two. But basically, he will slide something, or flip something, and it will open this valve, right? So I have got a hall effects sensor, a magnetic sensor on this to tell when the levers are in this position or that position, right? Over here, they’ve got a lever that typically says, I’m either vacuuming or discharging. So that’s a lever that will go either horizontally or vertically. So if it’s horizontal, I use a tilt sensor on that which basically got the component of gravity, so I can tell which orientation the levers in. If it’s like this, I use what’s called a magneto potentiometer. So basically, there’s a little magnetic, it’s a little disk, and as it spins, it can tell where it is in its radial position. I also track whether or not the PTO is on or off. So this one, the pump in the back is driven by the engine of the truck. There’s a shaft that runs back there. And if they flip a switch and then the power is going from the engine to the equipment in the back. I also track GPS position. And I also track bed heights. So I don’t know if this particular truck goes up and down. But the many of them, it does go up or down. And I do that with an ultrasonic sensor. So in that particular truck, I’ve got its GPS coordinates. I’ve got the ambient temperature where it’s located. I’ve got bed height. I’ve got lever position, switch position. I’ve got RPMs of the engine. And I’ve got ignition state if the truck goes on or off. So I think I got those seven different things. They all get collected into my Unix Edge Device, the UED. And then, from there they get compressed, and password-protected, and sent up to the cloud from there.

Frank Inselbuch 00:52:21.657 The compressor that you talked about is basically like a P750 from Ingersoll Rand. And this is a golf club. Compressor. There we go. So that’s one of these things like that. And that has a diesel engine inside it. And basically, I’m trying to get a bigger picture but no matter what I do, it’s the same size picture. But basically, it’s got a diesel engine so I monitor the RPMs off of that engine. And I monitor the battery voltage on it and the GPS position for that customer. And then, the interesting thing about these things is that these are old simple engines in them. They don’t have OBD, onboard diagnostics. So to get things like RPMs, I actually put a sensor on the alternator, on the engine alternator. So as the alternator spins, there’s a magnet on the stator. And so I get a pulse from that every time it goes around. And that is the RPM is a function of that pulse. It’s a 24 volts square wave that comes off of there. So there I was with my oscilloscope measuring that as the engine is running. And then, I chose the right component to do the frequency counting on that. In that way, I can track the RPMs. And with that, you can tell a lot of things. So if the RPMs are jumping around, the engines are not running well, or it’s got a slipping belt, if the engine speed is very stable, maybe they’ve left it running overnight which has happened. And they basically, wasted a $100 worth of diesel fuel and maybe violated the safety concern at the ultimate facility. And then, I can see when they are running at higher RPMs because there’s a demand for pressure and then for air. And then, it will jump down to idle and go up like that. So you can see when they’re really using it. So that’s how I instrument the compressors.

Chris Churilo 00:54:27.000 That’s cool. I mean, I think what you just described helped me to understand that there’s a-grabbing these metrics to understand the efficiency of the various components. And then, there’s some other things like, yeah, compliance like you shouldn’t be running it because, yes, safety concerns [crosstalk].

Frank Inselbuch 00:54:47.385 And there’s some issues with the-so when you move one of these things around, they usually put it on a flatbed, and they drive it over to a place, and they use a crane, and they pull it off of there. Well, what has happened to all the diesel fuel inside that tank from all of that, it’s all the diesel fuel has been shaken about. So there’s all kinds of particulates now that are inside the diesel fuel. So what would be a really smart to do is to not turn it on for an hour and let all that particulates settle down to the bottom of the fuel tank. So we implemented a little blinking light in the front. So basically, the light will blink blue like that, the blue LED nice and bright so you can see the outdoors. And it says basically, wait to start. So I know that it stopped moving. And then, I can say, “Start timer.” In that way, we can prevent them from having to change fuel filters. There’s money on the line for that. We do a similar thing with the diesel particulate filters in the trucks.

Chris Churilo 00:55:39.056 Cool. It really gives me a lot to think about. I think every time I listen to these cases where-these [inaudible] cases, it just makes realize that how little I understand about all of these various components. And actually how much fun it must be to learn about, “Hey, if I look at this data from this perspective or if I gather a little bit more information, I can add a new feature.”

Frank Inselbuch 00:56:06.307 Yeah. What I like to do is to take the technology and actually solve a real problem. It’s quite satisfying to know that your system is providing value, saving the customer money, saving the environment, and protecting people, and keeping them safe.

Chris Churilo 00:56:19.507 Very nice. All right. We have just a minute left. If there are any last-minute questions, otherwise, I could probably talk forever with afraid, but I won’t do that.

Frank Inselbuch 00:56:30.291 Can I just remark Chris that we timed this absolutely perfectly?

Chris Churilo 00:56:33.518 [laughter] You timed it perfectly.

Frank Inselbuch 00:56:36.595 Not on purpose. And I would say thank you very much for the opportunity to speak. I enjoy that. And enjoy sharing my experiences.

Chris Churilo 00:56:44.779 And we love hearing about your experiences. And so just a reminder to everybody this recording will get posted and we’ll send the link out later on. If you do have any other questions for Frank, feel free to just forward them to me or send them to me, and I’ll forward them to Frank. You guys all have my email address. And you can tell he’s not going to be shy about answering your questions. So-

Frank Inselbuch 00:57:06.473 True.

Chris Churilo 00:57:06.169 -feel free. Hey Frank. Thank you so much for the wonderful webinar.

Frank Inselbuch 00:57:11.792 Thank you.

Chris Churilo 00:57:12.615 And wish you a good day. And everyone else that attended, have a great day as well.

Frank Inselbuch 00:57:17.763 Bye.

Chris Churilo 00:57:18.192 Bye.

[/et_pb_toggle]