-->
🏠 🔍
SHAREOLITE

SQL useful select query examples



 
SQL select query example to convert rows to column :

select sum(case when EMP_ID=1 then 1 else 0 END) "Pantry" ,sum (case when EMP_ID=2 then 1 else 0 END) "Security"  from EMP_TABLE where to_char(JOIN_DATE,'DD-MM-YY')='10-11-12';

SQL select case query example :

select case when EMP_ID=1 then 'Pantry' when EMP_ID in (2,3) then 'Security' END  from EMP_TABLE where to_char(JOIN_DATE,'DD-MM-YY')='10-11-12';

SQL select statement example comma seperated delimiter display 

select EMP_ID||','||JOIN_DATE from EMP_TABLE;

SQL select statement example to combine data from two different tables

select * from EMP_TABLE_1 union all select * from EMP_TABLE_2;
 

VMware NIC port View Down Up command line - How to - SOLVED

In this post , we cover a practically working command line option in VMware esxi on how to View VM host server NIC port status , enable or disable it.


Command to view the network NIC ports list

/bin # esxcli network  nic list
Name    PCI Device     Driver     Link  Speed  Duplex  MAC Address         MTU  Description
------  -------------  ---------  ----  -----  ------  -----------------  ----  -------------------------------------------------
vmnic0  0000:006:00.0  igb        Up     1000  Full    1c:a1:82:18:7c:31  1500  Intel Corporation I350 Gigabit Network Connection
vmnic1  0000:006:00.1  igb        Up     1000  Full    1c:a1:82:18:7c:12  1500  Intel Corporation I350 Gigabit Network Connection
vmnic2  0000:006:00.2  igb        Down      0  Half    1c:a1:82:18:7c:22  1500  Intel Corporation I350 Gigabit Network Connection
vmnic3  0000:006:00.3  igb        Down      0  Half    1c:a1:82:18:7c:75  1500  Intel Corporation I350 Gigabit Network Connection
vusb0   Pseudo         cdc_ether  Up       10  Half    1e:a1:81:31:1c:71  1500  Unknown Unknown


Command to view the details of a specific port

/bin # esxcli network  nic get -n vmnic0
   Advertised Auto Negotiation: true
   Advertised Link Modes: 10baseT/Half, 10baseT/Full, 100baseT/Half, 100baseT/Full, 1000baseT/Full
   Auto Negotiation: true
   Cable Type: Twisted Pair
   Current Message Level: 7
   Driver Info:
         Bus Info: 0000:06:00.0
         Driver: igb
         Firmware Version: 1.61, 0x8000090e
         Version: 5.0.5.1
   Link Detected: true
   Link Status: Up
   Name: vmnic0
   PHYAddress: 1
   Pause Autonegotiate: true
   Pause RX: false
   Pause TX: false
   Supported Ports: TP
   Supports Auto Negotiation: true
   Supports Pause: true
   Supports Wakeon: true
   Transceiver: internal
   Wakeon: MagicPacket(tm)

Command to bring down a specific network interface

/bin # esxcli network  nic  down  -n vmnic3

Command to bring back a specific network interface

/bin # esxcli network  nic  up -n vmnic3

Hope this is useful to some VMware command geeks.
 

Cisco switch capture packet trace - Howto - SOLVED

In this post , we present practically verified and working - Cisco switch command line option for capturing packet traces for a specific interface port , which may be analyzed using Wireshark.


Enabling and capturing trace on a cisco switch includes below points.

  • Defining an access list to restrict any specific hosts .
  • Defining a capture trace buffer
  • Attaching an access list filter to this trace buffer
  • Defining any specific interfaces for capture
  • Start the capture 
  • Stop the capture and download the pcap file for analysis
Command line for the above are as below -

Creating an Access list

SW1# config terminal

SW1(config)#ip  access-list  standard acl1
SW1(config-std-nacl)#permit any
SW1(config-std-nacl)#exit
SW1#

Defining a trace capture buffer

SW1#monitor capture trace1 buffer size 1

Attaching an access list filter to this trace buffer

SW1#monitor capture trace1 access-list acl1


Defining any specific interfaces for capture

SW1#monitor capture trace1 interface GigabitEthernet 1/0/2 both

Start the capture for a duration and stop 

SW1#monitor capture trace1 start
SW1#
SW1#monitor capture trace1 stop


Viewing the captured file details


SW1#show monitor capture trace1 buffer brief
  0.000000   1.1.1.2 -> 224.0.0.5    OSPF Hello Packet
  0.376979   1.1.1.2 -> 224.0.0.18   VRRP Announcement (v2)
  1.214985   1.1.1.2 -> 224.0.0.2    HSRP Hello (state Active)

View the capture rules & parameters

SW1#show monitor capture  trace1 parameter
   monitor capture trace1 interface GigabitEthernet1/0/2 both
   monitor capture trace1 access-list acl1
   monitor capture trace1 buffer size 1

SW1#show monitor capture  trace1

Status Information for Capture trace1
  Target Type:
   Interface: GigabitEthernet1/0/2, Direction: both
   Status : Inactive
  Filter Details:
   Access-list: acl1
  Buffer Details:
   Buffer Type: LINEAR (default)
   Buffer Size (in MB): 1
  File Details:
   File not associated
  Limit Details:
   Number of Packets to capture: 0 (no limit)
   Packet Capture duration: 0 (no limit)
   Packet Size to capture: 0 (no limit)
   Packets per second: 0 (no limit)
   Packet sampling rate: 0 (no sampling)

Copying the buffer content to a pcap file for analysis

SW1#monitor capture trace1 export flash:/trace1.pcap
Exported Successfully


Downloading the file from switch

SW1(config)#
SW1(config)#ip ftp username backup
SW1(config)#ip ftp password backup
SW1(config)#
SW1#copy flash:/trace1.pcap ftp:
Address or name of remote host []? 192.168.40.100
Destination filename [trace1.pcap]?
Writing trace1.pcap !
2511 bytes copied in 2.156 secs (1165 bytes/sec)

To disable the capture rules , follow the standard option to execute the same commands with a 'no' prefix.

Hope its useful to some beginners.

SOLVED - Oracle procedure , trigger , function source code using command line

If you are finding short of a Oracle schema browser such as TOAD , SQL work bench etc , below SQL statements may be handy to get the source code of a object which could be a procedure , trigger , function etc.
 



  • Connect as oracle sysdba or dba user
  • The type of oracle objects which source code may be extracted 

SQL> select distinct(type) from all_source;

PROCEDURE
PACKAGE
PACKAGE BODY
LIBRARY
TYPE BODY
TRIGGER
FUNCTION
JAVA SOURCE
TYPE

  • Sample Query to extract source code of a oracle procedure
SQL> set linesize 120
SQL> set pagesize 0

SQL> select text from all_source where owner='SYSTEM' and type='PROCEDURE' and NAME='ORA$_SYS_REP_AUTH' order by LINE;

procedure        ora$_sys_rep_auth as
begin
  EXECUTE IMMEDIATE 'GRANT SELECT ON SYSTEM.repcat$_repschema TO SYS ' ||
                 'WITH GRANT OPTION';
  EXECUTE IMMEDIATE 'GRANT SELECT ON SYSTEM.repcat$_repprop TO SYS ' ||
                 'WITH GRANT OPTION';
  EXECUTE IMMEDIATE 'GRANT SELECT ON SYSTEM.def$_aqcall TO SYS ' ||
                 'WITH GRANT OPTION';
  EXECUTE IMMEDIATE 'GRANT SELECT ON SYSTEM.def$_calldest TO SYS ' ||
                 'WITH GRANT OPTION';
  EXECUTE IMMEDIATE 'GRANT SELECT ON SYSTEM.def$_error TO SYS ' ||
                 'WITH GRANT OPTION';
  EXECUTE IMMEDIATE 'GRANT SELECT ON SYSTEM.def$_destination TO SYS ' ||
                 'WITH GRANT OPTION';
end;


  • Example to extract a oracle trigger source code

SQL> select text from all_source where owner='SYSTEM' and type='TRIGGER' and NAME='REPCATLOGTRIG' order by LINE;

TRIGGER system.repcatlogtrig
AFTER UPDATE OR DELETE ON system.repcat$_repcatlog
BEGIN
  sys.dbms_alert.signal('repcatlog_alert', '');
END;

Hope this is useful to some SQL beginners

Diameter Credit Control - Session control - Abort Session Answer

A sample Diameter credit control - Session Control - Abort Session Answer (ASA) wireshark capture which may help Diameter base protocol beginners.

Diameter Protocol
    Version: 0x01
    Length: 128
    Flags: 0x40
        0... .... = Request: Not set
        .1.. .... = Proxyable: Set
        ..0. .... = Error: Not set
        ...0 .... = T(Potentially re-transmitted message): Not set
        .... 0... = Reserved: Not set
        .... .0.. = Reserved: Not set
        .... ..0. = Reserved: Not set
        .... ...0 = Reserved: Not set
    Command Code: 274 Abort-Session
    ApplicationId: Diameter Credit Control Application (4)
    Hop-by-Hop Identifier: 0x88f628db
    End-to-End Identifier: 0xc3ef6b84
   
    AVP: Session-Id(263) l=33 f=-M- val=shareo;3640208403;642;9328
        AVP Code: 263 Session-Id
        AVP Flags: 0x40
            0... .... = Vendor-Specific: Not set
            .1.. .... = Mandatory: Set
            ..0. .... = Protected: Not set
            ...0 .... = Reserved: Not set
            .... 0... = Reserved: Not set
            .... .0.. = Reserved: Not set
            .... ..0. = Reserved: Not set
            .... ...0 = Reserved: Not set
        AVP Length: 33
        Session-Id: shareo;3640208403;642;9328
        Padding: 000000
    AVP: Auth-Application-Id(258) l=12 f=-M- val=Diameter Credit Control (4)
        AVP Code: 258 Auth-Application-Id
        AVP Flags: 0x40
            0... .... = Vendor-Specific: Not set
            .1.. .... = Mandatory: Set
            ..0. .... = Protected: Not set
            ...0 .... = Reserved: Not set
            .... 0... = Reserved: Not set
            .... .0.. = Reserved: Not set
            .... ..0. = Reserved: Not set
            .... ...0 = Reserved: Not set
        AVP Length: 12
        Auth-Application-Id: Diameter Credit Control (4)
    AVP: Origin-Host(264) l=13 f=-M- val=shareo
        AVP Code: 264 Origin-Host
        AVP Flags: 0x40
            0... .... = Vendor-Specific: Not set
            .1.. .... = Mandatory: Set
            ..0. .... = Protected: Not set
            ...0 .... = Reserved: Not set
            .... 0... = Reserved: Not set
            .... .0.. = Reserved: Not set
            .... ..0. = Reserved: Not set
            .... ...0 = Reserved: Not set
        AVP Length: 13
        Origin-Host: shareo
        Padding: 000000
    AVP: Origin-Realm(296) l=20 f=-M- val=shareolite
        AVP Code: 296 Origin-Realm
        AVP Flags: 0x40
            0... .... = Vendor-Specific: Not set
            .1.. .... = Mandatory: Set
            ..0. .... = Protected: Not set
            ...0 .... = Reserved: Not set
            .... 0... = Reserved: Not set
            .... .0.. = Reserved: Not set
            .... ..0. = Reserved: Not set
            .... ...0 = Reserved: Not set
        AVP Length: 20
        Origin-Realm: shareolite
    AVP: Result-Code(268) l=12 f=-M- val=DIAMETER_SUCCESS (2001)
        AVP Code: 268 Result-Code
        AVP Flags: 0x40
            0... .... = Vendor-Specific: Not set
            .1.. .... = Mandatory: Set
            ..0. .... = Protected: Not set
            ...0 .... = Reserved: Not set
            .... 0... = Reserved: Not set
            .... .0.. = Reserved: Not set
            .... ..0. = Reserved: Not set
            .... ...0 = Reserved: Not set
        AVP Length: 12
        Result-Code: DIAMETER_SUCCESS (2001)
    AVP: Origin-State-Id(278) l=12 f=-M- val=3626120984
        AVP Code: 278 Origin-State-Id
        AVP Flags: 0x40
            0... .... = Vendor-Specific: Not set
            .1.. .... = Mandatory: Set
            ..0. .... = Protected: Not set
            ...0 .... = Reserved: Not set
            .... 0... = Reserved: Not set
            .... .0.. = Reserved: Not set
            .... ..0. = Reserved: Not set
            .... ...0 = Reserved: Not set
        AVP Length: 12
        Origin-State-Id: 3626120984

Diameter Credit control - Session Control - Abort Session Request

A sample Diameter credit control (DCC) - Session control - Abort session request (ASR) wireshark capture which may help Diameter base protocol beginners.

Diameter Protocol
    Version: 0x01
    Length: 148
    Flags: 0xc0
        1... .... = Request: Set
        .1.. .... = Proxyable: Set
        ..0. .... = Error: Not set
        ...0 .... = T(Potentially re-transmitted message): Not set
        .... 0... = Reserved: Not set
        .... .0.. = Reserved: Not set
        .... ..0. = Reserved: Not set
        .... ...0 = Reserved: Not set
    Command Code: 274 Abort-Session
    ApplicationId: Diameter Credit Control Application (4)
    Hop-by-Hop Identifier: 0x88f628db
    End-to-End Identifier: 0xc3ef6b84
    AVP: Session-Id(263) l=33 f=-M- val=shareo;3640208403;642;9328
        AVP Code: 263 Session-Id
        AVP Flags: 0x40
            0... .... = Vendor-Specific: Not set
            .1.. .... = Mandatory: Set
            ..0. .... = Protected: Not set
            ...0 .... = Reserved: Not set
            .... 0... = Reserved: Not set
            .... .0.. = Reserved: Not set
            .... ..0. = Reserved: Not set
            .... ...0 = Reserved: Not set
        AVP Length: 33
        Session-Id: shareo;3640208403;642;9328
        Padding: 000000
    AVP: Destination-Realm(283) l=20 f=-M- val=shareolite
        AVP Code: 283 Destination-Realm
        AVP Flags: 0x40
            0... .... = Vendor-Specific: Not set
            .1.. .... = Mandatory: Set
            ..0. .... = Protected: Not set
            ...0 .... = Reserved: Not set
            .... 0... = Reserved: Not set
            .... .0.. = Reserved: Not set
            .... ..0. = Reserved: Not set
            .... ...0 = Reserved: Not set
        AVP Length: 20
        Destination-Realm: shareolite
    AVP: Destination-Host(293) l=13 f=-M- val=shareo
        AVP Code: 293 Destination-Host
        AVP Flags: 0x40
            0... .... = Vendor-Specific: Not set
            .1.. .... = Mandatory: Set
            ..0. .... = Protected: Not set
            ...0 .... = Reserved: Not set
            .... 0... = Reserved: Not set
            .... .0.. = Reserved: Not set
            .... ..0. = Reserved: Not set
            .... ...0 = Reserved: Not set
        AVP Length: 13
        Destination-Host: shareo
        Padding: 000000
    AVP: Origin-Host(264) l=23 f=-M- val=shareo1.com
        AVP Code: 264 Origin-Host
        AVP Flags: 0x40
            0... .... = Vendor-Specific: Not set
            .1.. .... = Mandatory: Set
            ..0. .... = Protected: Not set
            ...0 .... = Reserved: Not set
            .... 0... = Reserved: Not set
            .... .0.. = Reserved: Not set
            .... ..0. = Reserved: Not set
            .... ...0 = Reserved: Not set
        AVP Length: 23
        Origin-Host: shareo1.com
        Padding: 00
    AVP: Origin-Realm(296) l=20 f=-M- val=shareolite
        AVP Code: 296 Origin-Realm
        AVP Flags: 0x40
            0... .... = Vendor-Specific: Not set
            .1.. .... = Mandatory: Set
            ..0. .... = Protected: Not set
            ...0 .... = Reserved: Not set
            .... 0... = Reserved: Not set
            .... .0.. = Reserved: Not set
            .... ..0. = Reserved: Not set
            .... ...0 = Reserved: Not set
        AVP Length: 20
        Origin-Realm: shareolite
    AVP: Auth-Application-Id(258) l=12 f=-M- val=Diameter Credit Control (4)
        AVP Code: 258 Auth-Application-Id
        AVP Flags: 0x40
            0... .... = Vendor-Specific: Not set
            .1.. .... = Mandatory: Set
            ..0. .... = Protected: Not set
            ...0 .... = Reserved: Not set
            .... 0... = Reserved: Not set
            .... .0.. = Reserved: Not set
            .... ..0. = Reserved: Not set
            .... ...0 = Reserved: Not set
        AVP Length: 12
        Auth-Application-Id: Diameter Credit Control (4)

–>