Show MySQL permissions AKA GRANTS

Listing current permissions including passwords in MySQL can be easily achieved using show-grants-mysql.sh POB script, for example:

 
$ MYSQL_PASSWORD=myPassword
$ history -d $((HISTCMD-2)) && ./show-grants-mysql.sh "-u root -p$MYSQL_PASSWORD"
$ MYSQL_PASSWORD=

Note the pasword doesn't stay in the history which is a wise thing to do.

Installing netcat in Solaris 10

Netcat or nc is the Swiss Army Knife of the Network-Security-Sysadmin Engineer. Here is a POB recipe I put together to ensure we push the package into our Solaris boxes.

In reality it is better to build a wrapper that will install any packages from sunfreeware. That is left to reader. Any takers?

Ubuntu Couldn't find your SSL library files for Monit

DevOps is I would argue used by many people in wrong terms nowadays (more on this in upcoming posts but be aware of the hype). In reality the Ops in the equation (meaning the sysadmin) should strive for identical Servers. Management through Recipes is the way to go, however many feel like a sysadmin should not code and that is wrong. The sysadmin should have nothing to do (probably) with OOP but with scripting and automation, that should be part of their daily work.

Today we got a weird error in one of the Ubuntu Servers. Monit would not install from the POB Recipe we have been using successfully, but instead fail with the below error:

$ ./configure --prefix=/usr/sbin --bindir=/usr/sbin --sysconfdir=/etc/monit/ 
...
checking for static SSL support... disabled
checking for SSL support... enabled
checking for SSL include directory... /usr/include
checking for SSL library directory... Not found

Couldn't find your SSL library files.
Use --with-ssl-lib-dir option to fix this problem or disable the
SSL support with --without-ssl
...

The only explanation for these inconsistencies is actually not managing the servers through recipes. With time the manual actions done in certain servers are different than in others.

Here is how we solved it in case someone else is having a similar error:

$ ./configure --prefix=/usr/sbin --bindir=/usr/sbin --sysconfdir=/etc/monit/ --with-ssl-lib-dir=/usr/lib/x86_64-linux-gnu
...
checking for static SSL support... disabled
checking for SSL support... enabled
checking for SSL include directory... /usr/include
checking for SSL library directory... /usr/lib/x86_64-linux-gnu
...

So try to keep your actions as sysadmin versioned in scripts which at the same time access versioned configurations. Then deploy always from scripts/recipes to have consistent environments.

Mount CIFS or NFS from a POB Recipe

I have packaged these so frequent actions in a couple of bash scripts: mount-cifs.sh and mount-nfs.sh

As a reminder you can run this scripts directly from root or you can use remoto-it to run them remotely in your servers.

Jasper Reports from XML Datasource : Rendering the Enterprise Hierarchical Data

Data is hierarchical by nature. Whether you model it with a relational database or a noSql database (it looks like nowadays anything non sql92+ compliant is simply noSQL and you do not need to talk about the big differences about graph, OO, XML and more) the fact is you need to report out of that data.

Your hierarchical data (did I say that any report will ultimately render hierarchical data?) can be rendered with the help of iReport at Design time and JasperReports at runtime. A combination of XML+XPATH and sub-reports will do the job for us.

Here is a showcase to illustrate how to build a report which parses a potentially big XML containing several companies to extract employees and their contacts. The source code can be downloaded from https://nestorurquiza.googlecode.com/svn/trunk/jasper-reports/xml-report/

The key here is to understand that you need a datasource per report and so to show hierarchical data you will need sub-reports. We will use bands which will result in rendering all nodes matching the xpath provided. Probably using tables for rendering tabular data is a better idea but for simple reports this should be enough.

1. Create an XML datasource pointing to the file (In our case companies.xml)
2. Check "Use the report XPath expression when filling the report" option Use as name the file for example "companies.xml"
3. Go to File|New|Report|Blank|Open this template and name the report something like "employee"|Next Finish
4. From the left pane remove all bands (right click|delete band) but the "title", "column header" and "detail" bands. Choose as title "Employees"
5. Click om the Report Query button (database with arrow icon) | Xpath as query language. On the right pane of the Report Query Window drill down until you find the node containing the fields to present, in this case "employee". Right click and select "Set record node (generate xpath)". The text "/companies/company/employees/employee" appears as a result and you can see the selected nodes are 2. Drag name and phone to the fields pane and click OK.
6. On the left pane (Report Inspector) the fields are now accessible. Drag and drop them in the details band. Two labels appear in the Column header band and two fields appear in the details band. Adjust the height of the two bands so they do not take more space than needed by a typical row
7. Click on Preview and the two employees will show up. Now let us jump into the subreport to show the contacts below each employee
8. Create a new report as explained before but name it "employee_contacts". Use as title "Contacts". Use as Datasource root the "contact" node (note the xpath is now /companies/company/employees/employee/contacts/contact) and drag and drop the name and phone for that node. Use as Title "Contacts".
9. Hit Preview and note we have a problem, we are not filtering by a specific employee name. Let's go back to the Report Query and change the xpath to "/companies/company/employees/employee[@name='$P{employee_name}']/contacts/contact"
10. In Report Inspector pane create a Parameter called "employee_name". Hit Preview and you will be prompted for an employee name. Pick John or Paul to get content back.
11. Go back to the "employee" report and expand the details band so there is space for the "employee_contacts" subreport.
12. Drag and drop the Subreport component from the Palette pane into the details band. Select "Use existing subreport" and point to "employee_contacts.jasper", click next to accept "Use the same connection used to fill the master report". Click next. For the parameter expression pick from the dropdown "name field" (F${name}) which is the employee name. Use option for absolute path and click Finish.
13. Clicking preview at this point at least in version 4.1.2 will end in the below error, reason why we migrated to 4.1.7:

    Error filling print... null java.lang.NullPointerException     at net.sf.jasperreports.engine.fill.JRPrintBand.addOffsetElements(JRPrintBand.java:101)     at net.sf.jasperreports.engine.fill.JRFillElementContainer.addSubElements(JRFillElementContainer.java:623)     at net.sf.jasperreports.engine.fill.JRFillElementContainer.fillElements(JRFillElementContainer.java:600)     at net.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:406)     at net.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:352)     at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillColumnBand(JRVerticalFiller.java:2023)     at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillDetail(JRVerticalFiller.java:755)     at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportStart(JRVerticalFiller.java:265)     at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:128)     at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:836)     at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:118)     at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:435)     at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:271)     at com.jaspersoft.ireport.designer.compiler.IReportCompiler.run(IReportCompiler.java:991)     at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:572)     at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:997) Print not filled. Try to use an EmptyDataSource...

14. open the XML and add to subreport node below the declaration for report element node:

    <subreportParameter name="XML_DATA_DOCUMENT">
     <subreportParameterExpression>$P{XML_DATA_DOCUMENT}</subreportParameterExpression>
    

15. Remove the absolute path and use just relative path. You can also remove the below node:

    <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
    

16. Go back to the Designer Editor and make sure it shows up as parameter. I have found sometimes it does not in which case closing and opening the properties editor or saving xml and switching to Designer or just closing and opening the report will restore it. For this subreport we have two parameters as the jrxml shows.
17. Here are a couple of screenshots of how it looks locally for me:
18. To invoke the report from java using the JasperReports library you go like

    Document document = JRXmlUtils.parse(xmlFile);               reportParameters.put(JRXPathQueryExecuterFactory.PARAMETER_XML_DATA_DOCUMENT, document);
    jasperPrint = JasperFillManager.fillReport(jasperFilePath, reportParameters);
    

The version on SVN shows a header just to demonstrate how it correctly shows the page numbers and total pages in the report header even when the subreport is the one printing full pages. The trick here is that you use the same variable for current page number and for total pages but for the latter the field has an attribute (evaluationTime="Report") setting that will cause the Jasper Engine to evaluate when the whole report has been pre-rendered, at that moment Jasper knows how many total pages will be in the final report.

Solaris Monit installation from POB recipe - Unattended Security Installation

Use reinstall-monit-solaris10.sh POB Recipe together with Remoto-IT or directly as root in your server to install a specific monit version in an unattended and secure way. The script should be idempotent BTW.

Here is how to call it locally to install version 5.5:

./reinstall-monit-solaris10.sh  5.5

SSH Sessions in multiple tabs from one command

Seriously, aren't you tired of typing or clicking hundreds of times to get to hundreds of remote linux machines? Well I got tired today and I have to confess I barely interact with less than a dozen of them and only when there are serious issues. I cannot imagine what the life of those issuing commands the whole day to linux servers can be without having a way to open up connections to a whole farm of servers from just one command.

I created run-in-tabs.sh which only works for the gnome-terminal. You can do similar stuff with iTerm or plain Terminal plus Applescript in OSX even though that is out of the scope of this post as I am trying to push the team to work with Desktops that are closer to the servers where the applications are hosted.

Suppose you have server1 accessible, server2 and server 3 accessible from server1 and server 4 only accessible from 3 and 2. It is not hard to find this kind of situations especially in environments where security must be put in place with scarce resources. What do you do?

1. Download the script.
2. Create wrappers for your SSH connections especially if they are multi-hop
3. Include the commands in a file like billing-environment.txt

   #billing-environment.txt
   
   #A simple direct ssh
   ssh -t user@server1
   #Wrapped (for simplicity) ssh commands
   /home/nestor/ssh-billing-server2.sh
   /home/nestor/ssh-billing-server3.sh
   /home/nestor/ssh-billing-server4.sh
   #A complex direct ssh equivalent to /home/nestor/ssh-billing-server4.sh
   #ssh -t user@server1 \"ssh -t server2 \"ssh -t server4\"\"
   

   Here is for example /home/nestor/ssh-billing-server4.sh:

   ssh -t user@server1 "ssh -t server2 \"ssh -t server4\""
   

4. Run just one command and get the 4 tabs with an ssh session to a different server each:

   $ ./run-in-tabs.sh billing-environment.txt