Subscribe: Blog
http://jasonvogel.blogspot.com/feeds/posts/default
Preview: Blog

Blog



Jason's Rant...I mean Blog



Updated: 2018-01-23T11:17:36.639-06:00

 



Excel: Converting a number of minutes to hours and minutes

2017-03-02T16:22:29.834-06:00

This is one of those, it should be easy, but it turns out to be harder to do than I initially expected. I made some notes, and I figured I might as well share it!

Goal: Convert a Number of Minutes into actual Hours and Minutes [H:mm], e.g. 90 into 1:30 or Hours: 1, Minutes: 30.

Hours & Minutes

A B C
1 Minutes 90 Comment: Number value of 90 is in Cell B1
2
3 Hours & Minutes 2160:00 Formula: =B1. Format: Time, [h]:mm.
4 WTH?! Comment: The Value is "wacked" because "90" is not actually a valid "time".
5
6 1:30 Formula: =TIME(0,B1,0). Comment: Create an actual valid =Time value.
7
8 30 Ok, let's find the number of minutes after subtracting all the hours.
Formula: =MID(TEXT(C7,"h:mm"),FIND(":",TEXT(C7,"h:mm"))+1,2)
9 Hours: 1, Minutes: 30 Formula: =CONCATENATE("Hours: ",TEXT(C7,"H"),", Minutes: ",MID(TEXT(C7,"h:mm"),FIND(":",TEXT(C7,"h:mm"))+1,2))

Comment: You can't just use =Text(C7,"mm") because =Text pulls the full value of 90.


So like everything, there is probably a better way to do this. I got this to work.

If you find a better way, please share!



PowerShell: Argument Checking (string or object?) with GetType()

2017-01-05T13:53:49.327-06:00

I wrote a routine that performs a file search on a server via Admin access (i.e. \\servername\e$\...). Anyway, I have a couple of other routines that use an "object" that represents a server. So in some cases, meaning I need a [non-string, object] "$server" and in other cases I need [string] "$serverName". And yes, I've named the arguments appropriately to differentiate them. However, I didn't have any enforcement [i.e. argument validation] on the type. And sure enough, I criss-crossed them at one point. For the purists in the room, I could have used PSCustomObject,but I'm not for simplicity sake.

Declarations...
$serverName = "server1"
$server = [ordered]@{"Name"="server1","ipAddress"="127.0.0.1"}

$serverName.GetType().FullName
Gives
System.String 
And
server.GetType().FullName
Gives
System.Collections.Specialized.OrderedDictionary
So a simple "if" comparison between $.GetType().FullName against System.String, allows me a simple sanity check that I using the appropriate argument.




So, I've started doing a bunch of PowerShell...

2016-11-08T10:13:55.681-06:00

Lately, I've been using PowerShell to handle some ad-hoc admin-ish activities. I thought that I would document some of the pain and progress as well as just serving as a general notepad of simple how-to's.



Get Oracle Version and NLS_LANG via SQL

2015-09-10T06:32:06.842-05:00

For one of the applications I support, we have to pull the appropriate Oracle client. So I need the Oracle version. Second, we have to set the Oracle Client NLS_LANG value to match the database's settings. So here is a simple query to get that information.
select DECODE(parameter, 'NLS_CHARACTERSET', 'CHARACTER SET',
'NLS_LANGUAGE', 'LANGUAGE',
'NLS_TERRITORY', 'TERRITORY') name,
value from v$nls_parameters
WHERE parameter IN ( 'NLS_CHARACTERSET', 'NLS_LANGUAGE', 'NLS_TERRITORY')
NAMEVALUE
LANGUAGEAMERICAN
TERRITORYAMERICA
CHARACTER SETUTF8
VERSION11.2.0.4.0



Query latest updates in Linux via RPM (YUM)

2015-01-09T10:58:41.132-06:00

rpm -qa --qf '%{INSTALLTIME} %{INSTALLTIME:date}: %-40{NAME} %{VERSION}-%{RELEASE}.%{ARCH}\n' | sort -n  | cut -d' ' -f2-
{InstallTime:Date}               {NAME}                                   {VERSION}-{RELEASE}.{ARCH}
...
Fri 09 Jan 2015 11:08:56 AM EST: ntp                                      4.2.6p5-2.el6_6.x86_64
Fri 09 Jan 2015 11:09:00 AM EST: mailx                                    12.4-8.el6_6.x86_64
Fri 09 Jan 2015 11:09:06 AM EST: jasper-libs                              1.900.1-16.el6_6.2.x86_64

This queries all your installed RPM packages (rpm -qa) and prints them using a format (--qf) with the time of installation first in seconds since the epoch(%{INSTALLTIME}). The result is sorted by the first time field, and then that time field is removed.

%-40{NAME} Package Name (40 characters, left justified)
%{VERSION} Version
%{RELEASE} Release
%{ARCH} Architecture (e.g. 64 bit or 32 bit)




Creating a "watermark" version label in your ASP.Net masterpage...

2014-11-17T23:15:06.692-06:00

It's not uncommon to receive a screenshot from a user of a webpage with an issue. How do you know which version the printout came from?

I've used this simple piece of code to help document every page. Basically, create a simple readonly text/label control [ctlIssueDate] and assign the Text label dynamically.

protected override void OnLoad(EventArgs e)
{
   try
   {
      // Declarations
      System.Reflection.AssemblyName assemblyName;

      base.OnLoad(e);

      // Set page title to include version information.
      assemblyName = System.Reflection.Assembly.GetExecutingAssembly().GetName();
      versionPanel.InnerHtml = "Version " + assemblyName.Version.Major.ToString() + "." + assemblyName.Version.Minor.ToString() +"." + assemblyName.Version.Revision.ToString();

      // Set default IssueDate
      ctlIssueDate.Text = DateTime.Now.ToShortDateString();
   }
   catch (Exception ex)
   {
       Utilities.HandleError(ex);
   }
}



Active Directory - Getting a user's Contact Info from AD

2014-04-14T09:18:10.268-05:00

Saving a snippet of code... [EnvironmentPermissionAttribute(SecurityAction.LinkDemand, Unrestricted = true)] private void accessAdEmpDetails() { string[] TempUserId = new string[2]; // Username/Password with rights to query AD string activeDirUsername = ConfigurationManager.AppSettings["adUserName"]; string activeDirPassword = ConfigurationManager.AppSettings["adPassword"]; string[] activeDirServerNames = new string[] { ConfigurationManager.AppSettings["adLink1"], ConfigurationManager.AppSettings["adLink2"], ConfigurationManager.AppSettings["adLink3"], ConfigurationManager.AppSettings["adLink4"], ConfigurationManager.AppSettings["adLink5"] }; WindowsIdentity WindowsIdentity = WindowsIdentity.GetCurrent(); WindowsPrincipal WindowsPrincipal = new WindowsPrincipal(WindowsIdentity); char[] SeparatorSlash = { '\\' }; TempUserId = Page.User.Identity.Name.Split(SeparatorSlash, 2); // Keeping user Id into the Session variable to furthur use. Session["USER_ID"] = TempUserId[1]; if (WindowsIdentity.AuthenticationType.ToUpper().Equals("KERBEROS")) { // Loop through AD servers foreach (string serverName in activeDirServerNames) { // Get a DirectoryEntry from each AD server using (DirectoryEntry entry = new DirectoryEntry(serverName)) { entry.Username = activeDirUsername; //User to access AD entry.Password = activeDirPassword; //Pwd to access AD using (DirectorySearcher searcher = new DirectorySearcher(entry)) { searcher.Filter = "(&(objectClass=user)(samaccountname=" + TempUserId[1] + "))"; SearchResult Result = searcher.FindOne(); if (Result != null) { if (Result.GetDirectoryEntry().Properties["departmentNumber"].Value != null) { userDept = Result.GetDirectoryEntry().Properties["departmentNumber"].Value.ToString(); } if (!string.IsNullOrEmpty(Result.GetDirectoryEntry().Properties["name"].Value.ToString())) { userName = Result.GetDirectoryEntry().Properties["name"].Value.ToString(); } if (Result.GetDirectoryEntry().Properties["physicalDeliveryOfficeName"].Value != null) { userLocation = Result.GetDirectoryEntry().Properties["physicalDeliveryOfficeName"].Value.ToString(); } if (Result.GetDirectoryEntry().Properties["mail"].Value != null) { userMail = Result.GetDirectoryEntry().Properties["mail"].Value.ToString(); } if (Result.GetDirectoryEntry().Properties["telephoneNumber"].Value != null) { userPhone = Result.GetDirectoryEntry().Properties["telephoneNumber"].Value.ToString(); } } } } } } } [...]



Verifying what fonts are on your RHEL6 system

2012-09-14T21:57:17.424-05:00

Tried to run xlsfonts but got error Install package 'xorg-x11-xfs-utils' to provid....

Run yum install xorg-x11-xfs-utils to fix this issue.

Successfully ran xlsfonts | more

Now let's check for the Microsoft Web Fonts... xlsfonts | grep ^-microsoft.

Sigh, no luck.




Installing the MS Core TrueType Fonts into RHEL6

2012-09-14T21:56:06.832-05:00

Followed http://www.geeksite.in/how-tos/linux/centos-how-tos/how-to-install-microsoft-truetype-fonts-in-centos-6-rhel-6.html on how to install the Microsoft TrueType fonts into RHEL6.

Found the fonts in... /usr/share/fonts/msttcore/.

Second Attempt
Followed http://oimon.wordpress.com/2011/09/05/msttcorefonts-on-rhel6-centos-6-sl6/.




Welcome to Red Hat 6 (RHEL6)?!

2012-09-12T10:24:55.035-05:00

Well, I am apparently back to the world of Linux after 20 years. Wow, that makes me feel old. I haven't used it since college, so needless to say I'm out of practice.

Going forward, I will be posting a number of noob discoveries as I work on re-learning Linux.

First one... how do I determine what version I'm running?
cat /etc/issue

Returns:
Red Hat Enterprise Linux Workstation release 6.3 (Santiago)

Another way...
cat /etc/*-release

Returns:
Linux Client for e-business (RHEL) 6.3 (Gold Master)
Open Client RHEL 64 3.30 (Gold Master)
Red Hat Enterprise Linux Workstation release 6.3 (Santiago)



Oracle - Change Password

2012-01-09T13:36:49.076-06:00

alter user jvogel identified by "jason.123" replace "jason.456"



Oracle - SQL - Evenly dividing a range of Data using NTILE, MIN, and MAX

2012-01-05T11:27:29.198-06:00

Problem: Over a widely dispersed set of IDs, create 10 evenly divided ranges each in terms of count per group. Determine the ID range for each group.
Practical: We needed to scrub a bunch of data in parallel streams. The easist answer was to create 10 simultaneous UPDATEs, but we didn't want the updates stepping on each other.
Solution: Use NTILE to create 10 evenly "divided" temporary groups [from a 'rowcount' standpoint]. Use MIN and MAX against each temporary group to determine the starting and ending points. Query:
SELECT 
   Quartile,
   MIN("ID") "StartingID",
   MAX("ID") "EndingID",
   COUNT(*) "CountPerGroup"
FROM
   ( 
      SELECT 
         a.contract_payment_id "ID",
         NTILE(10) OVER(ORDER BY a.contract_payment_id) AS quartile
      FROM   
         contract_payment a 
      WHERE
         a.transaction_type_code = 'CC' and 
         a.credit_card_type_code = 'VISA'
         and rownum <= 10000  -- Reduce resultset
 ) 
GROUP BY 
   quartile
ORDER BY 
   1;
Results:
QUARTILE  StartingID  EndingID  CountPerGroup
    1        59261      93271       1000
    2        93281     110492       1000
    3       110512     122382       1000
    4       122391     134062       1000
    5       134072     144371       1000
    6       144372     154481       1000
    7       154482     162741       1000
    8       162752     169163       1000
    9       169173     176913       1000
   10       176921     183833       1000



SameTime - Recovering your SameTime BuddyList

2011-12-12T11:56:09.651-06:00

Under Windows XP...
C:\...\\My Documents\SametimeFileTransfers\buddylist.xml 
Under Windows 7...
C:\Users\\AppData\Roaming\Lotus\Sametime\.metadata\.plugins\com.ibm.collaboration.realtime.imhub\buddylist.xml



Oracle - Performance - Timing a Statement

2011-09-07T15:25:10.338-05:00

Always, start with a fresh database session to remove any caching from previous runs.
connect user/password@database;

set timing on;

select 
	PKG_ACCT_DECRYPTION_FUNCTIONS.f_secure_decrypt(PKG_ACCT_ENCRYPTION_FUNCTIONS.f_secure_encrypt(to_char(sysdate,'mm/dd/yyyy hh24:mi:ss')))
from dual;
Also see Explain Plan and SysTimeStamp with Milliseconds.



Oracle - Sessions

2011-09-01T17:29:27.772-05:00

SELECT
  'Currently, ' 
  || (SELECT COUNT(*) FROM V$SESSION)
  || ' out of ' 
  || VP.VALUE 
  || ' connections are used.' AS "Session Usage"
FROM 
  V$PARAMETER VP
WHERE VP.NAME = 'sessions'
Currently, 1552 out of 6000 connections are used.



Unix Notes - System / Kernel Parameters

2011-09-01T17:26:18.758-05:00

sysdef -i
pending



Unix Notes - iostat

2011-08-31T12:58:43.265-05:00

iostat -x 5 100
                 extended device statistics
device    r/s    w/s   kr/s   kw/s wait actv  svc_t  %w  %b
md10      1.2   15.2    9.6   19.7  0.1  0.5   35.9   8   8
md11      0.6   15.2    4.8   19.7  0.0  0.5   29.9   0   7
md12      0.6   15.2    4.8   19.7  0.0  0.4   28.1   0   7
md20      0.0    0.0    0.0    0.0  0.0  0.0    0.0   0   0
md21      0.0    0.0    0.0    0.0  0.0  0.0    0.0   0   0



Compiling PL/SQL and Recompiling Dependent Objects

2011-08-17T07:25:09.265-05:00

set echo on;
SELECT * FROM user_objects WHERE status = 'INVALID';
set echo off;

Prompt 
Prompt Compiling c:\dispatch_data_obj_body.sql
@c:\dispatch_data_obj_body.sql

Prompt 
Prompt Attempting to recompile any invalid objects 
exec sys.UTL_RECOMP.recomp_serial(schema => user);

set echo on;
Prompt 
SELECT * FROM user_objects WHERE status = 'INVALID';
set echo off;



Unix Notes - Server Performance Monitoring with TOP

2011-08-31T12:58:54.049-05:00

/usr/local/bin/top
load averages:  1.49,  1.43,  1.52;                    up 0+03:4211:08:29
344 processes: 341 sleeping, 3 on cpu
CPU: 93.9% idle,  5.6% user,  0.5% kernel,  0.0% iowait,  0.0% swap
Memory: 128G phys mem, 33G free mem, 63G swap, 63G free swap

   PID USERNAME LWP PRI NICE  SIZE   RES STATE    TIME    CPU COMMAND
 26167 oracle     1   0    0   64G   64G cpu      0:02 52.42% oracle
 26175 oracle     1   0    0   64G   64G cpu      0:01 24.22% oracle
  4884 oracle     1  59    0 1157M 1153M sleep    4:56 17.69% oracle
 14457 oracle    11  59    0   64G   64G sleep    0:06  7.65% oracle
 14329 oracle    11  59    0   64G   64G sleep    0:12  6.31% oracle
 12036 oracle     1  59    0   64G   64G sleep    0:24  2.34% oracle
  4093 oracle     1  59    0   64G   64G sleep    2:20  1.47% oracle
 12034 oracle    11  59    0   64G   64G sleep    0:05  1.24% oracle
 24352 oracle     1  59    0   64G   64G sleep    0:00  1.08% oracle
 10318 oracle     1  59    0   64G   64G sleep    0:12  1.06% oracle
  4995 oracle     3  43    0   46M   34M sleep    0:33  0.71% tnslsnr
  4527 oracle     1  59    0   64G   64G sleep    0:21  0.55% oracle
  5939 oracle     1  59    0   64G   64G sleep    0:00  0.47% oracle
  8863 oracle     1  59    0   64G   64G sleep    0:07  0.42% oracle
 23943 jvogel     1  59    0 3816K 2832K cpu      0:02  0.38% top



Unit Notes - vmstat (virtual memory statistics)

2011-08-31T12:59:01.546-05:00

Reference: http://en.wikipedia.org/wiki/Vmstat

I've formatted the following example to make it a little easier to read.
$ vmstat 5
  kthr      memory            page             disk            faults        cpu
 r  b w   swap    free   re   mf   pi  po fr de sr m0 m2 m3 m4  in    sy     cs  us sy id
 3  6 0 9901864 4988152 1312 1293 2295 22 47  0  9  1  0  9  1  831    877    90 49 11 40
 0 10 0 6819504 2078880 1153 5777 4383  5  3  0  0  0  0  1  1 4642 188881 18478 32 12 57
 0  9 0 6813976 2075208 1350 7623 4380  6  6  0  0  0  0  2 18 5293 171861 18412 32 15 53
 0 10 0 6793096 2060464 1587 5025 4361 16 14  0  0  0  0  2  0 5773 192402 19898 31  9 59
 0 12 0 6781504 2046376 1865 8465 4436 16 16  0  0  0  0  7  0 6014 185604 17696 48 11 41
 0  6 0 6786576 2049600 1269 5981 4369  8  8  0  0  2  0  2  0 5592 203470 21790 35 10 55 
In this specific example, we are having issues with blocking.



Unix Notes - Find a File

2011-06-23T11:40:43.686-05:00

Related Posting: To find a string within a file list, see this post.

Find all the SQL (*.sql) files in the current directory:

find . -name '*.sql'

To hide any [access] errors, include 2>/dev/null at the end of the line.

find . -name '*.sql' 2>/dev/null

To list only files and avoid all directories:

find . -type f -name '*.sql' 2>/dev/null

Search all directories ('/' is the top level directory reference):

find / -type f -name '*.sql' 2>/dev/null



Unix Notes - Find Text / String in Files

2011-06-23T11:33:15.120-05:00

From the current directory and down, search for "ldapDelete" explicitly. Hide any read/access errors (2>/dev/null).
find . -exec grep "ldapDelete" '{}' \; -print 2>/dev/null
From the current directory and down, search for "ldapdelete" WITHOUT case sensitivity.
find . -exec grep -i "ldapdelete" '{}' \; -print 2>/dev/null



Resetting / unexpiring an Oracle user

2012-01-09T13:36:59.569-06:00

If you need to reset a user's password, here is a simple sample.

Please note the double quotes around the password.

It also requires the user to change their password on first login.

select username,account_status from dba_users where username = 'JOEUSER';

alter user JOEUSER identified by "expired.567";
alter user JOEUSER password expire;
alter user JOEUSER account unlock;

select username,account_status from dba_users where username = 'JOEUSER';



Burning an Oracle Sequence value to raise it above Table Max

2011-06-09T13:04:08.419-05:00

We have a log table that we use to track miscellaneous behaviors via some triggers for "audit" purposes. The design is fairly simple... on the occurrence of a "monitoring" behavior, INSERT a row into the AUDIT_LOG table. The INSERT grabs a new sequence value and poof, we're good.

Well, not always. Periodically, the MAX table value exceeds the sequence value. I don't know why/how it happens, but it causes our applications to start throwing errors [as expected]. I wrote a simple query to get the sequence value back above the table ID maximum.

Note: you may need to establish the "currval" of the session by burning a "nextval".

select
	-- audit_seq.nextval "Sequence NextVal",
	audit_seq.currval "Sequence CurrVal",
	(select max(AUDIT_LOG.SEQ_NO) from AUDIT_LOG) "Table Max ID",
	case
		when audit_seq.currval >= (select max(AUDIT_LOG.SEQ_NO) from AUDIT_LOG)
			then 'Sequence good'

		when audit_seq.currval < (select max(AUDIT_LOG.SEQ_NO) from AUDIT_LOG)
			then 'Sequence bad, Table Higher by '||( (select max(AUDIT_LOG.SEQ_NO) from AUDIT_LOG) - audit_seq.nextval)
		else
			'wtf'
	end "Sequence Status"
from
	dual

You may want to reference: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns002.htm#sthref806

ORA-02287: sequence number not allowed here



Oracle Replication - Disabling it a for a session

2011-06-07T13:01:57.006-05:00

DBMS_REPUTIL.REPLICATION_OFF;