Custom MySQL Reports
-
Apologies if I’m not using the right terminology – I’m currently working on setting up some custom reporting, since I’m looking into adding information about serial keys into a new table into FOG.
However, I’m getting stuck at my first step - I want to create a csv file that excludes my VMs, and be able to update that file remotely. I have remote access and querying working properly, but something goes wrong with sed to convert the mysql output into a csv. Here’s my code:
mysql.exe" -h192.168.4.132 -ufog-remote-mysql -pPASSWORD -Dfog -B -e “SELECT * FROM inventory WHERE iSysman NOT LIKE ‘VMware%’;” | sed s/\t/“,”/g;s/^/“/;s/$/”/;s/\n//g > fog_mysql_licenses.csv"
Running the “SELECT * FROM inventory WHERE iSysman NOT LIKE ‘VMware%’;” returns the correct output (all of my machines that aren’t VMs) – both locally on the FOG server with a direct connection to mysql, and using mysql.exe on my windows machine. However, using sed to convert the output into a csv always includes my VMs.
If anyone has any insight, I would love to hear it, because I am all kinds of stumped.
-
sed can only act on the data given to it. if no vmware info is returned by the query, then sed knows nothing about it. If vmware info is return, then sed will process those lines all the same.
Try experimenting with count(*) and no sed. See how many you get back NOT LIKE ‘VMware%’ versus how many are LIKE ‘VMware%’ and how many total.
Once you are sure you getting exactly the ones you want, append the pipe to sed, but check your escape sequences. I don’t see why are you putting a " after mysql.exe and after > fog_mysql_licenses.csv".
Try limiting the results to 1 or 2 from the mysql query and outputting the data to the screen instead of a file until you get theissue working out.
I tried this on Ubuntu 10.04 LTS Server, and had to fidget with the substitutions and escape sequences to get it to print.
Piping to sed required a \t to catch the tabs and wouldn’t catch the ^,$, or the \n at all.
Someone more experienced with sed and piping should probably chime in here once you get the SQL straightened out.
-
Oh apologies - the quotes are from a windows batch script, and mysql.exe and sed.exe are being stored on a network path with spaces in it. I cut the network path out since it’s long and not really relevant. I missed deleting those quotes when pasting the information in the forums.
Running SELECT COUNT(*) FROM inventory WHERE iSysman LIKE ‘%VMWare%’; returns a count of 2 (which is correct - I have a Windows 7 Professional and Enterprise VM in the database). Using NOT LIKE also returns the correct count.
I think the SQL statement itself is working. If I run the query in a cmd window, I can scroll up and verify that the VMs are not included – which just confuses the hell out of me, because I don’t know why or how sed is pulling the information about the VM, since it’s not being ouput by the SQL query. I know I’m not just accidentally looking at a saved CSV file either, since I’m deleting the old CSV before creating a new one each time.
I wonder if the batch file is getting thrown off by the %.
-
It must be, some way or another. Running the exact same line of code by typing it out in cmd, and executing it as a batch file (both with and without escape characters for the %) returns different results. The expected results come from typing it out, and the incorrect results come from executing it in a batch file.
Time to move over to vbs, I think.
-
I think know how much happier you’ll be with vbs. You can probably make a command line php script to do this or copy one from the web and change the sql statement and output file location.
-
Yeah… vbs never makes me happy. I enjoy how much it’s capable of (some days), but some of the steps required to get there are… confusing, to say the least.
php is something I don’t know anything about. I typically go to VBS or BAT because I know all of our Windows machines will be able to run the scripts without any extra installations. Is php executable without any extra compiling/standard on all versions of Windows?
-
Also, I currently have a view set up in MySQL that is essentially just SELECT * FROM inventory WHERE iSysman NOT LIKE ‘%VMWare%’;
Maybe I’ll just run with that. Seems a bit silly to have an extra view for something like that, though, haha.
-
Man I hate batch script some days. I actually got it working with my .bat file.
I had to use set vmvar=VMWare%% (double for the escape)
and then use SELECT * FROM inventory WHERE iSysman NOT LIKE ‘%vmvar%’;Batch must’ve been trying to do something with the % originally, but once I had it placed in a variable it knew not to touch it. What a headache. >_<
Thanks for the help! I’ll have to look into php sometime when I’m not on a bit of a time crunch.