Advertisements

Newest #Qlikview toy, Margin Analysis

Posted on September 21, 2012 by mellerbeck

So I recently created this little APP for accounting. It basically pulls in the costing information, last sales information, and lets you play with the margins. What happens if costs go up 2% and we increase prices by 3% and they take their 2% discount. And what if volume goes down a little on one item? That sort of thing

 

It also implemented this awesome post http://www.qlikfix.com/2012/08/22/search-by-copy-and-pasting-from-an-external-list/

So they copy and paste Products from excel into a input box and then go and select those!  This select in field string is the magic

=’(‘ & Replace(Replace(Replace(Trim(‘$(vProductList)’), ‘  ‘, ‘|’),’ ‘, ‘|’), chr(10), ‘|’) & ‘)

Advertisements

Filed under: Qlikview | Leave a comment »

#Meraki, what a pleasure to work with

Posted on September 20, 2012 by mellerbeck

It’s not often that I use technology that is an actual pleasure to work with. #Equallogic is at the top of that list, but now I have a new one: #Meraki

So, they first suck you in with their very nice (free!) MDM. MDM stands for mobile device management (i.e. visibility/management into your iPhones/pads/andriods etc…)

Then just for watching a webinar they will send you a free Access Point. So we pull it out of the box and have that sucker configured in about 20 minutes! We did run into an interesting hiccup though, the AP is able to provide multiple SSID’s so you can have one for an internal network, and another for a guest network that can just get to the Internet Etc… We configured the guest network and choose the setting to disallow access to the local LAN… well we have a very screwy ip scheme so the default “LAN Isolation” Link didn’t work for us,

When your turn on the option labled ” Prevent users from accessing your LAN?”, which enables a feature sometimes referred to as “LAN Isolation”, the following configuration is applied to your Meraki network:

  • Any traffic from a client to the following IP ranges is silently dropped: 10.0.0.0/8, 172.16.0.0/12, 192.168.0.0/16
  • The above applies to all traffic except DNS requests – they are passed regardless of the destination IP address

Note that the above configuration does suggest some situations in which Meraki users might be able to access your LAN – for instance, if devices in your LAN are configured with IP addresses that are outside the above ranges, the Meraki network will route traffic to those destinations.

So to fix it, I just added an additional Deny Policy on the layer 3 firewall rules, and used CIDR notation to specify the Destination.

The meraki interface is very clean and mainly logical. You can feel the google heritage (especially the nicely added two factor authentication!)

I look forward to the iphone app since I am assuming this will provide much better GPS location data for the devices.

 

Filed under: Applied IT, Iphone, Meraki | Tagged: , | Leave a comment »

How I’ve been Qlikviewing Lately #Qlikview

Posted on September 13, 2012 by mellerbeck

So I’ve finally ditched doing things with ODBC and gone towards an OLEDB

One thing to note about the OLEDB connection you cannot line wrap it, it must be all one line. I like this approach because you can also specify the Application Name, so when you are looking on SQL server you can identify which Qlikview is currently hammering the server 🙂

LET vMyDatabaseName = ‘mydatabase’;

LET vMyServerName = ‘myserver’;

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=$(vMyDatabaseName);Data Source=$(vMyServerName);Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;Application Name=MyAwesomeQlikview;];

I stole this technique from http://qlikshare.com/545 his is actually more elaborate with storing the connection outside in a text file, but this is sufficient for now.

In my case since we don’t run as admins on our desktop I had to create a shortcut to QV.exe and then shift click run as different user (windows 7) then when I reload happens it runs under the correct security context.

One other interesting thing I do, is when we create a .QVD I pop it in a folder on the Qlikview server and then share that folder. Then when creating the .Qvw I just drag the qvd out from the share into my qlikview window and bam it triggers the wizard. This is nice because when I publish the qlikview on the server the path to the QVD is still correct. It basically loops back through the share to do the reload. I suppose this could cause performance problems?? but not at the size of our Qlikview. This is also how we allow our users to self create QVW’s, point them at the share drag it into Qlikview and go to town. Then when they publish it paths are all correct as well.

I would love to hear similar techniques, and other peoples best practices!

Filed under: Qlikview | 2 Comments »

Certificate authentication, Iphone VPN on demand, BigIP F5, godaddy

Posted on September 7, 2012 by mellerbeck

So we have this cool VPN device from F5 that gives us basically single click entry into our Qlikview. Unfortunately, a certificate expired and then everything broke. This was setup by an F5 consultant, and wasn’t documented. (Note to self, have your consultants document what they do)

So now the crap hit the fan, after spending all day on the phone with F5 support finally they were able to get us up and running again! Thanks F5 guys for working it out!

So, step one. We needed a SSL certificate (my boss didn’t like the last place) we decided to go with a simple godaddy 5 year ssl cert for $60 versus a verisign which I think cost like $1000 (crazy)

Now actually getting to where you can submit your certificate is a little bit cryptic. After you buy it, you go to the Manage Certificates and your like, so now what? So you click the Credits, and then you click the refresh arrow, and then your credit will show up.

 

 

So now you can redeem your credit, so now on the BigIP size of things you need to create your certificate signing request, (CSR)

 

So goto Local Traffic, SSL Certificates, Create, Give it a name, choose Certificate Authority, fill in the common name (I pretty sure this has to match the DNS entry) make it 2048 bits and fill in the rest of the stuff.

Godaddy has a page describing this as well

http://support.godaddy.com/help/article/5597/generating-a-certificate-signing-request-csr-f5-bigip-loadbalancer?pc_split_value=1

 

 

 

Hit finished copy the encryption garbage into your clipboard and then paste it into the godaddy window that looks like this.

Ok Godaddy will chug on it for a while and then you need to prove that you own the domain. The easiest way was to click on the ‘whats the hold up’ link for go daddy and the create the simple little html file proving you own the site. After you have proved you own it, then you can download the certificate, and the godaddy bundle.

So now you need to import the certificate, and the key. So back to the Local traffic, SSL certificates screen, press import, give it a name (i chose one that matched the domain). There, now you have a server SSL certificate.

http://support.godaddy.com/help/article/5511/installing-an-ssl-certificate-in-f5-bigip-loadbalancer?pc_split_value=1

You have to set your SSL client profile to use this for the certificate & the key.

Ok, now are you ready for even more fun????

So we also need to verify that our iphone clients have a client certificate (in order to do the Iphone VPN demand you need to use certificate based vpn) (we also NT authenticate as well)

So in order to do this you need to create a certificate authority, create and sign client certificates, and then configure the F5 to use them, and then export in an iphone friendly format.

Here we go, so get yourself a copy of openssl.cnf (in my case F5 support had one) and then used winscp to copy it over to /tmp/ert/openssl.cnf

This post has pretty much the same steps, but uses different dir(s)

http://www.gomiworld.net/securing-the-web-with-ssl-client-certificates

Now ssh into your F5 box, I suppose you could do this anywhere openssl was installed.

And then run the following procedure

Creating a CA certificate

Create a directory to contain your CA certificate by typing the following command syntax:

mkdir /tmp/cert

Create a private directory in your CA directory by typing the following command syntax:

mkdir /tmp/cert/private

Create a client certificate directory in your CA directory by typing the following command syntax:

mkdir /tmp/cert/client

Create a serial number file for your CA by typing the following command syntax:

echo “0001” >/tmp/cert/ca.srl

Create a CA certificate and key by typing the following command syntax:

  • openssl req -new -x509 –days 365 -keyout /tmp/cert/private/nffckey.pem -out /tmp/cert/private/nffccert.pem -config /tmp/cert/openssl.cnf

Creating and signing a client certificate

Create a client certificate request by typing the following command syntax:

openssl req -new -newkey rsa:512 -nodes -out /tmp/cert/client/client.req -keyout /tmp/cert/client/client.key

Sign the client certificate by typing the following command syntax:

openssl x509 -CA /tmp/cert/private/nffccet.pem -CAkey /tmp/cert/private/nffckey.pem -CAserial /tmp/cert/ca.srl -req -in /tmp/cert/client/client.req -out /tmp/cert/client/client.pem -days 365

 

Results

You created the CA located at:

/tmp/cert/private/nffccert.pem

/tmp/cert/private/nffckey.pem

You created a client cert located at:

/tmp/cert/client/client.pem

Export to PKCS#12

openssl pkcs12 -export -clcerts -in /tmp/cert/client/client.pem -inkey /tmp/cert/client/client.key -out /tmp/cert/client/client.p12

—-

So now you need to import Your CA cert and Key into the F5, go to the same SSL certificate place. I named them the same and then the system combined them together into a Certificate & Key.

So now under the Local Traffic  ››  Profiles : SSL : Client

I click my vpn profile and change the Trusted Certificate Authorities to my newly created one, and also changed the advertised certificate authorities to the new one.

Then I email out the P12 to the iphone clients, change over the profile to use the new certificate and we are back in business!

joy

 

 

 

 

 

Filed under: Applied IT, F5 | 1 Comment »