Draytek 2760 and Spanish Orange ONT

Creating another networking post as I did find this critical to working my business from Spain.

With a standard Spanish Oracle connection you get a so called “Livebox” , which is an fairly advanced consumer router. However for my LAN to LAN VPN connection to York, this router is no good and I need my Draytek 2760 to work for this.

This is how to set it up.

Connection diagram:

  • Nokia ONT: connects to Orange fibre network
  • Draytek 2760: VPN router connecting using IPSEC with IKEv2 to York
  • Unifi USG: LAN router
  • Unifi Switch: managed switch

The LAN setup is similar to my previous post on Draytek 2860 and Unifi USG.

However the connecting from a Draytek to a Spanish Orange ONT is as follows:

Draytek 2760 Settings

WAN

General Setup – WAN2:

Settings:

  • Tag value: 832 (required for Orange)
  • Priority: 0
  • WAN2 will enable port P4 as WAN, which should be connected to the ONT
  • Display Name: Oniti is the Orange provider

General Setup – Internet Access

Settings:

  • Obtain an IP address automatically (the blue covered bit are from earlier tests)
  • Enable PING (was the best solution for me but not required)
  • MAC Address: MAC address of the LiveBox (as the installation technician enters this into the ONT, I think)

NAT

DMZ Host

Create a WAN2 DMZ connection to the USG to minimise double NAT

LAN

Just a couple of LAN notes similar to my earlier Draytek 2860 post.

General Setup

VLAN

  • 192.168.9.0/24: DMZ network with DHCP (exposed to the web) connected to USG via port P1
  • 192.168.8.0/24: LAN network with no DHCP as this is controlled by the USG and connected to USG LAN via the Unifi Switch from port P3
  • The is no routing between the 8 and 9 networks (naturally to enforce the USG firewall)
Posted in: Networking, Technical Knowledge by Kent Willumsen Comments Off on Draytek 2760 and Spanish Orange ONT , , ,

Draytek 2860 and Unifi USG

I normally don’t do networking posts but in this case I think there may be a need as I could not find any solutions for this anywhere else.

This solution uses a Draytek 2860 as a VDSL modem, VPN and LAN-LAN device.

Using a Draytek 2860 as modem and Unifi USG as gateway is not without it’s problems.

Why using Draytek as modem:

  • Draytek is a modem working well with a BT FTTC VDSL connection
  • Draytek is great for client VPN and LAN-LAN connections both using IPsec
  • Draytek and USG can always be reached via the client VPN access – so accidental lock-out is (almost) impossible

Why using Unifi USG as gateway:

  • Unifi USG provides great network management via the Unifi controller

Problems to be solved with the solution below:

  • USG is in a double-NAT situation
    • Solved with DMZ on Draytek
  • Draytek incoming VPN wants to route through itself bypassing the USG
    • Solved with policy route on Draytek forcing USG gateway
  • Draytek has two DMZ modes and only one works:
    • Solved by using “Private IP” DMZ
    • Private IP: semi-NAT mode where some functionality works
      • Works: modem, VPN, LAN-LAN and remote router management
      • Does not work: Port forwarding (makes sense)
    • True IP: bridge mode where Draytek only works as modem
      • Works: modem
      • Does not work: everything else
  • Port forwarding to WordPress Server
    • Solved by using port forward on Unifi USG
  • Route VPN and LAN-LAN around the DMZ
    • Draytek LAN1 to USG LAN direct connection via switch
    • USG WAN2 could have been used but this is better to use for WAN failover

Connection diagram:

Draytek Setup (relevant to this scenario): 

  • WAN: nothing special to do
  • LAN: create two networks: DMZ and LAN1:
    • DMZ network: semi-NAT network to Unifi USG
      • For NAT Usage
      • IP Address: 192.168.7.3
      • DHCP Enable Server
      • Start IP address: 192.168.7.100 (USG WAN)
      • Gateway IP: 192.168.7.3
    • LAN1 network: This is used to route around the DMZ
      • For NAT Usage
      • IP Address: 192.168.1.3 (as the USG is 192.168.1.1)
      • DHCP Enable Relay
      • DHCP Server IP: 192.168.1.1 (USG LAN)
    • VLAN: map your LAN1 to port 2 (port 1 is reserved for DMZ)
  • Routing: create a route policy for LAN1
    • Load-Balance/Route Policy:
      • Protocol: Any
      • Source: IP Subnet: 192.168.1.0/24
      • Destination: Any
      • Destination Port: Any
      • Interface: LAN1
      • Specific Gateway: 192.168.1.1 (USG LAN)
      • Priority: 100
  • NAT:
    • DMZ Host: Private IP
    • Private IP: 192.168.7.100 (USG WAN)
  • Firewall: Disable all (USG is the firewall)
  • VPN and Remote Access:
    • Remote Dial-in User:
      • Allowed Dial-in type: L2TP/IPsec with pre-shared key
      • Subnet: LAN1
    • LAN to LAN:
      • Allowed Dial-in type: IPsec Tunnel
      • Local Network IP: 192.168.1.0/24
      • …and much more

Unifi USG Setup (relevant to this scenario):

  • WAN Networks:
    • WAN:
      • IPV4 Connection Type: Using DHCP
  • LAN Networks:
    • LAN:
      • Gateway IP / Subnet: 192.168.1.1/24
      • DHCP server enabled
  • Gateway:
    • Port Forwarding:
      • Name: http
      • From: Anywhere
      • Port: 80
      • Forward IP: WordPress server IP
      • Forward Port: 80
      • Protocol: Both

Posted in: Networking, Technical Knowledge by Kent Willumsen Comments Off on Draytek 2860 and Unifi USG , , ,

Java Web Start

As IE support is dying, Oracle have released a new way to launch the Java forms called Java Web Start – JWS.

JWS was introduced with R12.2.3 and in R12.1.3 with patches.

Have a look at this Oracle blog.

The low-down is you can enable JWS in 2 ways

  1. Update the context file
  2. Update profile options

As changing the context file needs a DBA so it is better just to update the profile options.

The two profile options we need are:

  • Enable Java Web Start (code: FND_ENABLE_JAVA_WEB_START)
  • ICX: Forms Launcher (code: ICX_FORMS_LAUNCHER)

Default values for legacy mode (URL is for R12.2.9 VISION instance):

  • Enable Java Web Start: No
  • ICX: Forms Launcher: http://apps.example.com:8000/forms/frmservlet

Value for JWS are:

  • Enable Java Web Start: Yes
  • ICX: Forms Launcher: http://apps.example.com:8000/forms/frmservlet?config=jws

To update the profile options we have 3 options:

  1. Use the usual System Profile Options Java form (if you have access to this)
  2. Use the web based form
  3. Use a PL/SQL script

Note the profile options can also be set at user level but examples below are for site level only.

Java Form

As responsibility “System Administrator” navigate to: \Profile\System

Web Form

As responsibility “Functional Administrator” navigate to:
\Core Services\Profiles

PL/SQL Script

declare
	procedure set_site_value(p_profile varchar,p_value varchar) is
		l_result boolean;
	begin
		l_result:=fnd_profile.save(
			x_name=>p_profile,
			x_value=>p_value,
			x_level_name=>'SITE'
		);
	end;
	function get_site_value(p_profile varchar) return varchar is
		l_value varchar2(240);
		l_is_defined boolean;
	begin
		fnd_profile.get_specific(
			name_z=>p_profile,
			val_z=>l_value,
			defined_z=>l_is_defined
		);
		return l_value;
	end;
	procedure enable_browser is
	begin
		set_site_value('FND_ENABLE_JAVA_WEB_START','N');
		set_site_value('ICX_FORMS_LAUNCHER','http://apps.example.com:8000/forms/frmservlet');
	end;
	procedure enable_jws is
	begin
		set_site_value('FND_ENABLE_JAVA_WEB_START','Y');
		set_site_value('ICX_FORMS_LAUNCHER','http://apps.example.com:8000/forms/frmservlet?config=jws');
	end;
	procedure show_current is
	begin
		dbms_output.put_line('Current Settings');
		dbms_output.put_line('Enable Java Web Start: '||get_site_value('FND_ENABLE_JAVA_WEB_START'));
		dbms_output.put_line('ICX: Forms Launcher: '||get_site_value('ICX_FORMS_LAUNCHER'));
	end;
begin
	show_current;
	enable_jws;
	commit;
	show_current;
end;
Posted in: Technical Knowledge by Kent Willumsen Comments Off on Java Web Start , ,

R12.2.9 Notes

Just some notes around getting R12.2.9 up and running.

As R12.2.x is using Weblogic Fusion middleware you need at least 16Gb ram

Steps:

  1. Downloads
  2. Install OEL 8
  3. Install VirtualBox 6
  4. Unzip R12 files
  5. Merge R12 files
  6. Import into VirtualBox
  7. First Run
  8. Final Steps
  9. Backup and Restore

Downloads

Download from http://edelivery.oracle.com

  • Oracle Enterprise Linux 8.1 or later
  • Search for OEL
  • Download: V984216-01.iso Oracle Linux Release 8 Update 1 for x86 (64 bit), 7.2 GB
  • Oracle E-Business Suite R12.2.9 on VM
  • Search for VM suite (somehow the search engine does not work with version numbers)
  • Click: Oracle VM Virtual Appliances for Oracle E-Business Suite
  • Click: Oracle VM Virtual Appliances for Oracle E-Business Suite 12.2.9.0.0
  • Download all (63Gb)

Install OEL8

I use OEL8.1 for running VirtualBox.

An earlier version can be used but this version supports NVME on a PCIe card on which supports NVME.

Running the R12.2.2 VM on a NVME is very fast.

The OEL inside the VM is running OEL 6.1 and does not support NVME so by running the VM on OEL8.1 then NVME is supported.

I will not explain how to install Linux as this is trivial.

However note the following:

  • Disable the firewall to void port issues with R12.2.9
  • Note OEL6 uses iptables but OEL8 uses firewalld
  • As root do:
systemctl stop firewalld
systemctl disable firewalld
systemctl status firewalld

Install VirtualBox 6

As root in OEL8 do:

yum install VirtualBox-6.1

Yep that is all.

Unzip R12 files

In Windows, download 7-zip and create a script like this and run it:

@echo off
set topdir=E:\R12.2.9
set curdir=%CD%
cd %topdir%
for /r %topdir% %%i in (*.zip) do (
	echo %%i
	C:\Admin\Cloud\Programs\7-Zip\7z.exe e -y -o%topdir% %%i
)
cd %curdir%
pause

In Linux even easier, just do:

find . -name "*.zip" -exec unzip {} \;

Merge R12 files

We need to concatenate the file to create a single .ova file.

In Windows use the following script:

@echo off
set topdir=E:\R12.2.9
set curdir=%CD%
cd %topdir%
copy /b %topdir%\*.ova.?? %topdir%\r1229.ova
cd %curdir%
pause

or just do:

copy /b *.ova.?? r1229.ova

This can now be transferred to OEL8.

The files with automatically be concatenated in correct sequence.

In Linux just do:

cat *.ova.?? > r1229.ova

Import into VirtualBox

This is all done in OEL8.

Look at the readme:

  • Oracle VM Virtual Appliance for Oracle E-Business Suite Deployment Guide_Release 12.2.9.html
  • Section 3: Deploying the Oracle E-Business Suite 12.2.9 VM Virtual Appliance on Oracle VM VirtualBox

The readme zip file contain instructions on how to do this:

  • Ensure to update the drive to NVME before starting import
  • Use “bridged” network adapter

First Run

Follow readme:

  • Section 4.1: Starting the First Boot

To setup the instance.

Then follow:

  • Section 4.2: Configuring the Application Before the First Use

To enable the APPS users:

/u01/install/APPS/scripts/enableSYSADMIN.sh
/u01/install/APPS/scripts/enableDEMOusers.sh

That’s all. I skipped the rest.

Backup and Restore

To backup just shutdown the VM and use:

vboxmanage export $vmname -o /backup/oracle/somename.ova

To restore, use:

vboxmanage import /backup/oracle/somename.ova

Posted in: R12 Installation, Technical Knowledge, Virtual Manager by Kent Willumsen Comments Off on R12.2.9 Notes , , ,

BI/XML Publisher Notes

My notes for making a simple Excel report in XML Publisher without the need for a developer.

I know it is now called BI Publisher but within E-Business Suite it is mostly referred to as XML Publisher.

I feel the need for this note as there are plenty of blogs just showing examples from the examples supplied by Oracle without really understanding how it works (or why it does not work).

The example below has been tested on a R12.2.9 instance.

Some overall rules:

  • Install the BI Publisher add-in as per instructions below
  • Use .xls Excel 2003 format only
  • Only use the Excel add-in for adding fields and doing preview

BI Publisher Report Process

Overview:

  1. Develop and test SQL
  2. Create Data Template
  3. Register Data Definition
  4. Register Concurrent Program
  5. Produce Preview XML Data
  6. Create Template File in Excel
  7. Register Data Template
  8. Test Final Report

Develop and Test SQL

Rules:

  • Keep it simple
  • Use column names in select

Create SQL and ensure it works, something like this:

select user_name,description,start_date,email_address
from fnd_user
where 1=1
and end_date is null
and encrypted_user_password !='INVALID'
;

This should yield something like this:

Create Data Template

Rules:

  • Data template name should match what you want to register it as
  • Always have at least one group
  • Paste your SQL into the dataQuery CDATA field and remove the “;” if any
  • List all fields in “dataStructure” tag
  • Save file as {data template name}.xml
  • If you use parameters then note they are case sensitive
  • Some best practices to avoid mixing up fields later on:
    • Parameters: start with “P_”
    • Column aliases starts with: “T_” or “A_”
    • Elements starts with: “E_”
    • SQL Statement name: starts with “Q_”
    • Group name: starts with “G_”
<?xml version="1.0" encoding="WINDOWS-1252" ?>
<dataTemplate name="XXKWI_TEST" description="Test Report" Version="1.0">
	<dataQuery>
		<sqlStatement name="Q_USER">
			<![CDATA[
				select user_name t_user_name,description t_description,start_date t_start_date,email_address t_email_address
				from fnd_user
				where 1=1
				and end_date is null
				and encrypted_user_password !='INVALID'
			]]>
		</sqlStatement>
	</dataQuery>
	<dataStructure>
		<group name="G_USER" source="Q_USER">
			<element name="E_USER_NAME" value="T_USER_NAME" />
			<element name="E_DESCRIPTION" value="T_DESCRIPTION" />
			<element name="E_START_DATE" value="T_START_DATE" />
			<element name="E_EMAIL_ADDRESS" value="T_EMAIL_ADDRESS" />
		</group>
	</dataStructure>
</dataTemplate>

Example section with parameters:

<?xml version="1.0" encoding="WINDOWS-1252" ?>
<dataTemplate name="XXKWI_IF_TRX" description="Transaction Errors" Version="1.0">
	<parameters>
		<parameter name="P_OBJECT_NAME" dataType="character" />
		<parameter name="P_OBJECT_ID" dataType="number" />
	</parameters>
...

Register Data Definition

Rules:

  • Ensure you have access to responsibility XML Publisher Administrator
  • Navigate \home\Data Definitions
  • Use name same as data template name above

The new entry should look like this:

Name:{data template name}
Code:{data template name}
Applications:{your application}
Start Date:{backdated date}
Data Template:{your XML file from above}

Register Concurrent Program

This enables us to run the above SQL code in the concurrent manager.

Rules:

  • Login with responsibility System Administrator
  • Navigate \Concurrent\Program\Define
  • Ensure Short Name is same as Data Definition Name
  • XDODTEXE is the XDO XML processor
Program:{user friendly name}
Short Name:{data definition name}
Application:{your application}
Executable Name:XDODTEXE
Output Format:XML
Use in SRS:Yes

Produce Preview XML Data

We will need some example XML data to create the output template that converts raw XML data to Excel format.

Rules:

  • Navigate \View\Requests
  • Submit the {user friendly name} from above
  • Save the output as a .xml file like xxkwi_test_output.xml
  • Check the output contains the required fields and groups

Result should look something like this:

<?xml version="1.0" encoding="UTF-8"?>
<XXKWI_TEST>
<LIST_G_USER>
<G_USER>
<E_USER_NAME>SYSADMIN</E_USER_NAME>
<E_DESCRIPTION>System Administrator</E_DESCRIPTION>
<E_START_DATE>1951-01-01T00:00:00.000-05:00</E_START_DATE>
<E_EMAIL_ADDRESS/>
</G_USER>
<G_USER>
<E_USER_NAME>CBROWN</E_USER_NAME>
<E_DESCRIPTION>Casey Brown</E_DESCRIPTION>
<E_START_DATE>1995-02-19T00:00:00.000-05:00</E_START_DATE>
<E_EMAIL_ADDRESS>nobody@localhost</E_EMAIL_ADDRESS>
</G_USER>
</LIST_G_USER>
</XXKWI_TEST>

Save the file as a XML file.

Create Template File in Excel

Now we have the data XML file we can create the template.

Rules:

  • Only use Excel .xls 2003 format
  • Keep it simple
  • Avoid formatting using the BI Publisher
  • Report output can later the formatted using Excel alone

The following steps must be done:

  1. Upload sample XML output
  2. Create headings
  3. Create field names
  4. Create groups
  5. Preview output

Upload sample XML output:

  • Add your XML output to the Sample XML:

Create headings:

  • Type headings for example in A1

Create field names (automatic method):

  • Click field:
  • Click on A2 and add the data field you want
  • BI Publisher will then create the hidden XDO_METADATA sheet:
  • Select field to create:
  • Ensure the field is having a name like: XDO_?XDOFIELD1?
  • This is translated in the in the XDO_METADATA sheet to <?E_USER_NAME?>
  • Try to click “Names” to verify BI Publisher created a name for this field:

Create field names (manual method):

I prefer this as BI Publisher very easily garble the XDO_METADATA sheet and named ranges.

  • Do not mix manual and automated field names
  • Create the sheet XDO_METADATA manually
  • This sheet should not have any data below “Data Constraints:”
  • When done hide the sheet
  • Click on A2 and enter example data like SYSADMIN
  • Click names (name manager)
  • Add a name like XDO_?{field_name}?
  • The above example should look like this: XDO_?E_USER_NAME?
  • “E_USER_NAME” matches the field name used in the XML template, hence why we need naming standards
  • Click OK

Create Groups:

BI Publisher add-in does not handle groups well so only do this manually.

  • You must have at least one group to create vertical reports
  • If the group is missing the preview will show data horizontally
  • Click on A2
  • If you have more than one field select the full range like A2:B2 but exclude the heading at A1:B1
  • If you display parameters then ensure the group excludes these as well
  • Click names (name manager) and new
  • Enter a group name like XDO_GROUP_?{group_name}?
  • This name must match the name in the XML template, hence the need for naming standards
  • The above name should look like this: XDO_GROUP_?G_USER?
  • G_USER matches the group tab in the XML template
  • Name manager should now look like:

Preview output:

  • Click Preview
  • Expected output:
  • If you get errors at this point check all your settings and in worst case clear the template as described below and start all over

Register Data Template

Rules:

  • Ensure you have access to responsibility XML Publisher Administrator
  • Navigate \home\Templates
  • Name should be same as previous name

Upload your Excel .xls template:

Name:{previous name from above}
Code:{name from concurrent program}
Application{your application}
Data Definition{name from above}
TypeMicrosoft Excel
Start Date{backdated date}
Default Output TypeExcel
Template File{filename from previous chapter}
LanguageEnglish

Test Final Report

Run the program:

Run the concurrent program and verify the output by clicking “Open”:

Should yield something like this:

All done and it works.

Issues

Excel add-in issue overview

  • Only works with Excel 2003 format .xls
  • It is not possible to use the xmlpserver login on R12.2.9 Vision instance (if you know please tell me how)
  • Adding groups using the add-in easily destroys your template; so do it manually
  • The add-in install is dodgy at best (see below)
  • Strange preview output when XDO_METADATA or Excel names are garbled

How to clean-up the Excel sheets

BI Publisher add-in often garbles both the XDO_METADATA sheet and the named ranges:

  • Delete all XDO related names like the XDO_?XDOFIELD1? name below:
  • Unhide the XDO_METADATA sheet and delete all rows under “Data Constraints:”, like the row with XDO_?XDOFIELD1? and <?USER_NAME?> below

Excel Preview all horizontal

If you are missing a group, it looks like this; where data is rendered horizontally:

Excel add-in install

This is most of what I went through to install the add-in (some of the issues below are addressed in Doc ID 2106715.1):

  1. Download from: https://www.oracle.com/middleware/technologies/bi-publisher/downloads.html
  2. Ensure to download “BIPublisherDesktop32.exe” for a 32-bit Microsoft Office Installation, the one you should use anyway to be compatible with WebADI
  3. Right-click and select “Run as Administrator” otherwise the installation fails
  4. Once the installations is complete CD to the directory: C:\Program Files (x86)\Oracle\BI Publisher\BI Publisher Desktop
  5. CD to subdirectory: DotNetInstallFiles
  6. Double-click on ETBAddInSetup to install 32-bit Excel add-in
  7. Once installed go to Start Menu and click: Enable or Disable Template Builder
  8. First click OK on:
  9. Then click OK on:
  10. Run it again and click on Button1:
  11. Click on Button1 again:
  12. Now try and open Excel and look for the “BI Publisher” ribbon:
  13. If it is still missing then try to go to options add-ins:
  14. If present then select Manage COM add-ins and click Go…:
  15. Uncheck the BI Publisher add-in and click OK:
  16. Go back to options and manage add-ins and re-check the add-in and click OK:
  17. That should do the trick otherwise retry from start as it is easy to miss a step
  18. To use the Oracle supplied examples copy the following to a local directory: Template Builder for Word\samples\Excel templates
  19. Install Java jre 8.x and add this to the options:

Excel add-in shows “Preview Failed”

This is caused by using Excel .xlsx format. Save the document as .xls and retry.

After clicking:

image

you get this:

Start Excel Preview
only open: false
mTemplate: C:\Users\Kent\AppData\Local\Oracle\BIPublisher\TemplateBuilderforExcel\tmp\tmp\tmp.xls
mTmpTemplate: C:\Users\Kent\AppData\Local\Oracle\BIPublisher\TemplateBuilderforExcel\tmp/tmp.xls
java.lang.NullPointerException
	at oracle.xdo.excel.user.ximpl.XCellImpl.setStyle(XCellImpl.java:65)
	at oracle.xdo.template.excel.render.BookDataWriter.copyCell(BookDataWriter.java:743)
	at oracle.xdo.template.excel.render.BookDataWriter.finishCell(BookDataWriter.java:696)
	at oracle.xdo.template.excel.render.CellHandler.endElement(CellHandler.java:111)
	at oracle.xdo.template.excel.render.BookDataParser.endElement(BookDataParser.java:215)
	at oracle.xml.parser.v2.XMLContentHandler.endElement(XMLContentHandler.java:211)
	at oracle.xml.parser.v2.NonValidatingParser.parseElement(NonValidatingParser.java:1303)
	at oracle.xml.parser.v2.NonValidatingParser.parseRootElement(NonValidatingParser.java:340)
	at oracle.xml.parser.v2.NonValidatingParser.parseDocument(NonValidatingParser.java:307)
	at oracle.xml.parser.v2.XMLParser.parse(XMLParser.java:298)
	at oracle.xdo.template.excel.render.BookDataParser.parserXMLDocument(BookDataParser.java:146)
	at oracle.xdo.template.excel.ExcelController.processActionLanguage(ExcelController.java:415)
	at oracle.xdo.template.excel.ExcelController.process(ExcelController.java:268)
	at oracle.xdo.template.ExcelProcessor.process(ExcelProcessor.java:244)
	at ExcelPreview.runXDO(ExcelPreview.java:110)
	at ExcelPreview.main(ExcelPreview.java:87)

Posted in: Reporting, Technical Knowledge by Kent Willumsen Comments Off on BI/XML Publisher Notes ,

Dell PowerEdge T20 Notes

Bought one of these for running various business services.

image

Got it cheap with cashback for less than the CPU would cost in retail.

The CPU and motherboard supports:

  • VT-x: Hardware virtualization
  • VT-d: Direct I/O virtualization
  • vPro: Active management technology (AMT)

Standard specifications:

  • Intel Xeon E3-1225V3
  • Ram 4Gb
  • 1Gb hard disk

Additional components:

  • Kingston 2x8Gb Ram KTD-PE313E/8G
  • Syba SATA III 4 Port PCI-e x1 Controller Card
  • Inateck 2x 2.5 Inch SSD to 3.5 Inch Internal Hard Disk Drive Mounting Kit Bracket
  • HP NC364T 436431-001 435506-003 – Quad / 4 Port Gigabit Ethernet Card Adapter

Hardware Notes

The quad NIC adapter is to enable physical separation of intranet and internet traffic.

ESXi likes to use a single NIC for a single virtual switch for a single subnet.

Hence if you want intranet and internet physically separated for VM’s you just have two virtual switches – one for intranet and one for internet – each mapped to a physical NIC.

The router the isolated the physical ports to virtual networks.

This means it is very hard to break in to the intranet even if the internet VM is compromised.

Details about this belongs in a dedicated network and router post later.

AMT Notes

Enabling AMT

During boot press ctrl-P to enable.

Connecting to AMT: http://{hostname}:16992/logon.htm

Even better download Meshcommander

Looks like this:

image

AMT and VPN Issues

If you connect via VPN you may find you cannot connect to AMT.

It took me a while to find out why not…

This may be caused by you assigned a fixed IP address to the server, which is a normal thing to do for server, however it just doesn’t work then.

Be sure to use DHCP and then use the router to force the IP address using bind to IP:

image

So do as above…

Posted in: Server, Technical Knowledge by Kent Willumsen Comments Off on Dell PowerEdge T20 Notes , ,

ESXi Notes

Having previously worked with Oracle VirtualBox I thought this this would be an easy transition – but no way.

ESXi feel much more like a enterprise ready product, with complexities at that level as well.

Most of the things in here will be related to the CLI interface of ESXi version 6.5.

It is work in progress so there will be errors and things I have misunderstood below as it is for my own use.

Storage

Storage is provisioned in 3 layers starting from lowest level:

  1. Physical Disks (or raid/nas/san drives)
  2. Datastores: collection of physical disks making a logical disk
  3. Virtual Disks: VM storage placed in datastores as vmdk files

Simplified diagram:

image

Physical Disks (devices)

Physical disks are located in directory:

/vmfs/devices/disks

Physical hard disks maps to files in /vmfs/devices/disks like this:

t10.SanDisk00Ultra_Fit000000000000004C531001491102119083

Partitions maps to the above filename but ending with “:” and number like this:

t10.SanDisk00Ultra_Fit000000000000004C531001491102119083:1

Listing disks only:

ls -lh /vmfs/devices/disks | grep -v “:[0-9]$” | grep -v “vml\.” | grep -v “total”

Datastores

Datastores are located in directory:

/vmfs/volumes

Datastores are having names like this:

587949b5-46baef34-fb63-1866da2041ef

When a datastore is created and names then it gets a link from the name to the file above like:

SSD -> 587949b5-46baef34-fb63-1866da2041ef

Within the datastore you will find the virtual disks.

Virtual Disks

Virtual disks are located in directory:

/vmfs/volumes/{datastorename}/{vmname}

Together with other VM related files.

Main virtual disk names:

  • {diskname}.vmdk: disk configuration file
  • {diskname}-flat.vmdk: thin provisioned file (full file)
  • {diskname}-rdmp.vmdk: raw provisioned file (link only)

Raw Device Mapping (RDM)

A physical disk can be mapped directly to a VM by creating a link.

Use this command:

vmkfstools -z /vmfs/devices/disks/{physical disk} /vmfs/volumes/SSD/{vmname}/{vmdkname}.vmdk

For example:

vmkfstools -z /vmfs/devices/disks/t10.SanDisk00Ultra_Fit000000000000004C531001491102119083 /vmfs/volumes/SSD/windows/hdd1.vmdk

Creates the following files:

  • {diskname}.vmdk: disk configuration file
  • {diskname}-rdmp.vmdk: raw provisioned file (link only)

Equally to remove the links to a VM do the following:

rm /vmfs/volumes/SSD/windows/hdd1.vmdk
rm /vmfs/volumes/SSD/windows/hdd1-rdmp.vmdk

Virtual Disk Backup

It is easy to do a “cold” backup when the VM is shut down.

Using the command to clone the original disk:

vmkfstools –i /vmfs/volumes/{datastorename}/{vmname}/{vmdkname}.vmdk {backupdest}/{vmdkname}.vmdk -d thin

This command should only be used with thin provisioned disks.

VM Upgrade

When importing a VM from other sources you will find the VM version is older.

I had this problem where I got a warning about the Linux version was not correct.

I could only pick Debian 6 where I was using Debian 8.

This is caused by the VM version is older.

To upgrade the VM version run the following command:

vim-cmd vmsvc/upgrade {vmid} vmx-13

The {vmid} can be obtained by running this command:

vim-cmd vmsvc/getallvms

Extending Virtual Disk

Well you don’t really extend but add another one instead.

Click on the VM details:

image

Click Add hard disk – New hard disk:

image

Adjust disk size:

image

Reboot the VM

List the disk:

# lsblk

NAME               MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda                  8:0    0  100G  0 disk
|-sda1               8:1    0  428M  0 part /boot
|-sda2               8:2    0 18.2G  0 part
| |-turnkey-root   254:0    0   99G  0 lvm  /
| `-turnkey-swap_1 254:1    0  512M  0 lvm  [SWAP]
`-sda3               8:3    0 81.4G  0 part
`-turnkey-root   254:0    0   99G  0 lvm  /
sdb                  8:16   0  100G  0 disk

So new hard disk is: sdb

Initialise the physical volume:

# pvcreate /dev/sdb
Physical volume “/dev/sdb” successfully created

Extend volume group in LVM:

# vgextend turnkey /dev/sdb
Volume group “turnkey” successfully extended

Extend size of logical volume – first try failed as slightly less than 100GB available so just use extents option instead:

# lvextend -L+100G /dev/turnkey/root
Insufficient free space: 25600 extents needed, but only 25599 available
# lvextend -l 25599 /dev/turnkey/root
Size of logical volume turnkey/root changed from 99.02 GiB (25348 extents) to 100.00 GiB (25599 extents).
Logical volume root successfully resized

Resize the file system:

# resize2fs /dev/turnkey/root
resize2fs 1.42.12 (29-Aug-2014)
Filesystem at /dev/turnkey/root is mounted on /; on-line resizing required
old_desc_blocks = 7, new_desc_blocks = 7
The filesystem on /dev/turnkey/root is now 26213376 (4k) blocks long.

Another way to resize, which I used on Ubuntu:

growpart /dev/sda 2
resize2fs /dev/sda2

Where the 2 is the partition 2 in /dev/sda so /dev/sda2

All done:

# lsblk
NAME               MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda                  8:0    0  100G  0 disk
|-sda1               8:1    0  428M  0 part /boot
|-sda2               8:2    0 18.2G  0 part
| |-turnkey-root   254:0    0  100G  0 lvm  /
| `-turnkey-swap_1 254:1    0  512M  0 lvm  [SWAP]
`-sda3               8:3    0 81.4G  0 part
`-turnkey-root   254:0    0  100G  0 lvm  /
sdb                  8:16   0  100G  0 disk
`-turnkey-root     254:0    0  100G  0 lvm  /

Posted in: Server, Technical Knowledge by Kent Willumsen Comments Off on ESXi Notes

Lenovo X1 Carbon Tips and Notes

Very happy with this computer and only have very few issues as per below.

Sierra Wireless EM7345 4G LTE

This WLAN unit seems very unstable and tend to disconnect just after connecting.

It does not help to disable any power settings.

The solution I found was simple – run ping in the background.

Open a command prompt and enter: ping –t bbc.com

Or any other internet address that return the ping:

image 

Just keep this running also when you close the lid and it re-connects when you re-open the lid.

Posted in: General News and Tips, Laptops by Kent Willumsen Comments Off on Lenovo X1 Carbon Tips and Notes

HP ProLiant MicroServer Notes

This server is getting a bit old now but still works well except for some minor problems I will start to record in here.

Excessive disk I/O caused by audiodg.exe

The Microserver does not have a soundcard when using Remote Desktop on Windows 10 it creates a Remote Audio driver .

This remote Remote Audio driver should be disabled using the Device Manager console as per below:

image

Disable by right-clicking and chose “Disable”

Don’t ask me why the disk I/O happens – it’s one of those things…

KVM Viewer does not open

This is a Java based tools and only really works in Internet Explorer.

However IE has changed over time so the pop-up does not work any more.

This is especially true in this case for:

  • Windows 8.1 64-bit  or Windows 10 64-bit
  • Java 6 (if you have any newer then de-install these)
  • Internet Explorer 11
1. Click Launch KVM Viewer

image

2. Click Open

image

3. Pick Application

image

Click: More Options

image

Default Open With./.. windows opens

Click: Look for another app on this PC

image

However this points to x64 programs so change to: Program Files (x86)

image

Click on Java (which is now a 32-bit Java) and go down to the bin directory:

image

Pick the Java Web Start executable: javaws.exe

And the KVM should start just fine…

Posted in: Technical Knowledge by Kent Willumsen Comments Off on HP ProLiant MicroServer Notes

Budgetary Control and Funds Checking Notes

Overview

Modules supporting budgetary control:

  • General Ledger
  • Payables
  • Purchasing
  • Inventory
  • Projects

Limitations:

  • Budgetary control is only available for the primary ledger functional entered currency
  • Budgetary control requires the use of encumbrance or budgetary accounting

Funds checking levels:

  • Detail budgetary control: budget per code combination assigned at budget organization range level
  • Summary budgetary control: budget by summary accounts assigned at summary account template level

Budgetary controls possible:

  • None: no funds checking and no funds reservation
  • Advisory: funds checking and if no funds, warning is issued and funds are reserved
  • Absolute: funds checking and if no funds, transaction is blocked and funds are not reserved

Budget Control Group

The budget control group is assigned at site or responsibility profile level and can set limits for:

  • Funds check level
  • Tolerance Percent: overspend allowed on budget in percent
  • Tolerance Amount: overspend allowed on budget in amount
  • Override amount: maximum override amount per account for failed absolute level

Note: tolerances only work in an encumbrance environment.

Amount Types and Boundaries:

  • Amount Type: cumulative balance for funds checking interval
  • Boundary: end point of the interval so budget cannot be spent beyond this point
  • Some examples:
    • PTD + Period: PTD budget is checked against current period (any previous periods underspend is ignored)
    • YTD + Period: Accumulated YTD budget is checked for current period (YTD underspend can be used until current period)
    • QTD + Quarter: Accumulated QTD budget is checked for current quarter (QTD underspend can be used within current quarter)
    Boundary      
    Period Quarter Year Project
Amount Type PTD: Period-to-date OK      
  QTD: Quarter-to-date OK OK    
  YTD: Year-to-date OK OK OK  
  PJTD: Project-to-date OK OK OK OK

Funds Available Calculation:

Encumbrance Accounting:

  • Funds Available = Budget – Encumbrance – Expenditures

Budgetary Accounting:

  • Funds Available = Budget – Expenditures

Detail Budget Control

To define detailed budget control you must create a budget organisation with a specific range and assign budgetary control that range.

Any ranges not assigned to budget control will not have budgetary control enabled so care must be taken when defining ranges to ensure all accounts are covered if full budgetary control is required.

Each of the budget organisation assignments having budgetary control enabled should have a budget amount allocated.

In practice, this may become a huge task. Just imagine you have an account for "stamps", then you must assign a budget amount for "stamps" per cost centre and per company per any code combination, your account structure may have.

Therefore, detail level budgetary control assumes you have a budget amount per code combination.

To limit the number of code combinations requiring budget amounts it is recommended to switch off "dynamic insert" so only pre-defined code combinations can be used.

A simple setup would be (based on the chart of accounts in the appendix):

Purpose

Range from

Range to

Budgetary control

Insurance

10-100-6400

10-100-6499

Advisory

Travel

10-100-6500

10-100-6599

Absolute

With this setup, you are required to enter a budget amounts for:

  • 10.100.6510
  • 10.100.6520
  • 10.100.6530

And optionally amounts for:

  • 10.100.6410
  • 10.100.6420

Budgets > Define > Budget:

image

Note: "Require Budget Journals" must be enabled for budgetary control budgets.

Budgets > Define > Organization:

image

Note: Use the "Maintain" button to update code combination assignments when budget organisation ranges are changed or new accounts are added.

Click: Ranges

image

Click: Budgetary Control (line 10)

image

Click: Budgetary Control (line 20)

image

Process for adding a new account, which is inside an existing budget organisation range with budgetary control:

  1. Add new account
  2. Create code combinations manually
  3. Add budget amount for the code combination
  4. Run " Program – Maintain Budget Organization"
  5. Check if code combination exists in budget organisation assignment with correct budget control assignment

Only code combination assignments will have budget control enabled hence, it is important to run: "Program – Maintain Budget Organization". This program is run when clicking the button "Maintain" in the "Define Budget Organization" screen.

Summary Budget Control

Summary level control is managed at summary account level. Summary accounts are always assigned at parent account level.

This makes it easier to enter budget amounts as this can be entered at a higher level.

Also the budget organisations can now be arranged independently of the budgetary control.

To ensure we only budget at one account we specifically add a budget account for this purpose with "Posting = No" and "Budget = Yes" and set all other accounts to "Budget = No" and "Posting = Yes".

For example using our chart of accounts below, we define:

  • Insurance Budget: 6401
  • Travel Budget: 6501

Then we define summary accounts for 6400 and 6500 to summarise both actual and budget amounts in the parent account range.

First, we define a rollup group to control the summarisation.

Setup>Financials>Flexfields>Key>Groups:

image

Name the rollup groups the same as the segment values so you can refer to these in the same way as an account.

Setup>Financials>Flexfields>Key>Values:

image

Assign the rollup group to the parent account.

The rollup can be assigned to any level in a hierarchy so the account hierarchy is important for the level of budgeting as well as reporting.

Accounts>Summary:

image

These summary accounts will have a total for "Travel" or "Insurance" for total of company and cost centre. TBD as company should be "D".

Click: Budgetary Control (Insurance)

image

Click: Budgetary Control (Travel)

image

The budget control can be performed at these summary levels per segment:

  • D: Detail – sum per segment value
  • T: Total – sum per segment (normally applied to a 4th segment like "Analysis")
  • {rollup group}: Sum of the accounts assigned to the rollup group

In the above example the calculation will be:

D.D.6400 =

10.100.6401 +

10.100.6410 +

10.100.6420

The amounts are added for each balance type of actual, budget and encumbrance.

Funds available = Budget – Encumbrance – Actuals = 100 – 0 – (10 + 20) = 70

All made simple thanks to the summary account.

The amounts can be checked using the funds inquiry screen.

Inquiry>Funds:

image

As the budgetary control is on D.D.6400 then there is no need for a budget on 6410 and 6420 making it simpler to maintain on just one account: 6401.

When a new account/combination is added it will inherit the budget assigned to the summary account hence dynamic insert can be enabled.

So for example, we add account 6430 to the above example then there would still be a budget covered by the amount on 6401.

However, you still need to ensure the summary account template is updated by running: Program – Maintain Summary Templates

Process for adding a new account, which is inside an existing budget organisation range with budgetary control:

  1. Add new account

Simple and clean.

Optional you can run: "Program – Maintain Summary Templates" but this is automatically run during the posting process anyway.

Not if you change the hierarchy, which is part of a summary group you need to run: Program – Incremental Add/Delete Summary Templates

Chart of Accounts

Note this is not the full chart of accounts but only listing accounts relevant for the above examples.

COA structure:

Segment

Qualifier

Length

Company

Balancing

2

Cost Centre

Cost Center

3

Account

Account

4

Company values:

Value

Description

10

ACME Limited

Cost Centre values:

Value

Description

000

Balance Sheet

100

Finance

Account values:

Value

Description

Type

Parent

Child Range

6000

Operating Expenses

Expense

Yes

6000-6999

6400

Insurance Expense

Expense

Yes

6400-6499

6401

Insurance Budget

Expense

No

 

6410

Liability Insurance

Expense

No

 

6420

Property Insurance

Expense

No

 

6500

Travel

Expense

Yes

6500-6599

6501

Travel Budget

Expense

No

 

6510

Trains

Expense

No

 

6520

Planes

Expense

No

 

6530

Automobiles

Expense

No

 
Posted in: Functional Knowledge, General Ledger by Kent Willumsen Comments Off on Budgetary Control and Funds Checking Notes , , ,