My CSV reports that NetDB creates are having an issue when loaded in Excel. Every other line has the firstseen,lastseen dates on a second line, instead of all fields pertaining to that record in a single line.
I never used to have this problem until I started importing the OUI.txt file from the Internet. Sure enough, I took a look at the netdbreport.csv file created on the server under /var/www and it appears there are ^M returns after every vendor code. This puts the firstseen,lastseen on a second line. My Excel reports look a little like Swiss cheese now.
Does anybody know how to fix this in code somewhere?
Off the top of my head, it sounds like the OUI.txt file might have been created on a Windows system. You can try running dos2unix on OUI.txt and see if that helps. I don't personally use the excel feature so I just always put a "remove MS newlines" snippet in my scripts anytime I use the vendor code. I can't remember if I ever tried to fix the issue or not.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I'm doing wget from the IEEE source so if that's the case then there is no Unix version, but I'll give that a shot. What I'd like to find is the code syntax that creates the CSV files for you. Maybe I can alter the regex to remove ctrl-M.
Edit: It does appear the oui.txt file now includes carriage returns. There is a sanitized version that does not. https://linuxnet.ca/ieee/oui/
However, it is still happening. I think I need to delete the part of the database that has the OUI matches to IP's and let it rescan them. This may be a SQL question now.
Last edit: Robert H 2019-12-17
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
So for posterity, this was fixed a couple ways.
No longer download the original oui.txt file. It has the carriage return issues which infects other aspects of netdb. Start using the sanitized one from this site: https://linuxnet.ca/ieee/oui/
I backed up the database and performed a search & replace to remove the ^M after the vendor descriptions.
sed "s/\\r',NULL/',NULL/g" backup.sql > fixed.sql
Restored database
(both backup and restore via the netdbctl command)
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
My CSV reports that NetDB creates are having an issue when loaded in Excel. Every other line has the firstseen,lastseen dates on a second line, instead of all fields pertaining to that record in a single line.
I never used to have this problem until I started importing the OUI.txt file from the Internet. Sure enough, I took a look at the netdbreport.csv file created on the server under /var/www and it appears there are ^M returns after every vendor code. This puts the firstseen,lastseen on a second line. My Excel reports look a little like Swiss cheese now.
Does anybody know how to fix this in code somewhere?
Example, slightly obfuscated.
Last edit: Robert H 2019-12-16
Off the top of my head, it sounds like the OUI.txt file might have been created on a Windows system. You can try running dos2unix on OUI.txt and see if that helps. I don't personally use the excel feature so I just always put a "remove MS newlines" snippet in my scripts anytime I use the vendor code. I can't remember if I ever tried to fix the issue or not.
I'm doing wget from the IEEE source so if that's the case then there is no Unix version, but I'll give that a shot. What I'd like to find is the code syntax that creates the CSV files for you. Maybe I can alter the regex to remove ctrl-M.
Edit: It does appear the oui.txt file now includes carriage returns. There is a sanitized version that does not.
https://linuxnet.ca/ieee/oui/
However, it is still happening. I think I need to delete the part of the database that has the OUI matches to IP's and let it rescan them. This may be a SQL question now.
Last edit: Robert H 2019-12-17
So for posterity, this was fixed a couple ways.
No longer download the original oui.txt file. It has the carriage return issues which infects other aspects of netdb. Start using the sanitized one from this site: https://linuxnet.ca/ieee/oui/
I backed up the database and performed a search & replace to remove the ^M after the vendor descriptions.
sed "s/\\r',NULL/',NULL/g" backup.sql > fixed.sql
Restored database
(both backup and restore via the netdbctl command)