Subscribe: Blog
http://jasonvogel.blogspot.com/feeds/posts/default
Added By: Feedage Forager
Language: English
Tags:
audit  find  getdirectoryentry properties  oracle oracle  oracle sleep  oracle  result getdirectoryentry  sleep oracle  string  user
Rate this Feed

Feed Details and Statistics
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"
```

```\$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')```
 NAME VALUE LANGUAGE AMERICAN TERRITORY AMERICA CHARACTER SET UTF8 VERSION 11.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;

// 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

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 `UPDATE`s, 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...

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
```

`ORA-02287: sequence number not allowed here`
```DBMS_REPUTIL.REPLICATION_OFF;