(Linux版です) 本を出版しましたので、ご紹介します。Webサーバー、データベースをつかって、WordPressをきちんと構築し、それを全力でSplunkで監視する方法についてです。 DB Connectというアドオンがありますが、これをきちんと動かしているケースは少ない。とてもいいアドオンなので使わないのはソン！ 綺麗にSplunkを構築する方法なので、Splunk極めたい人には超絶オススメです。 「Splunk CloudでSecurity/IT監視基盤を「構築」しよう: WordPress + Apache HTTP + MySQL on Linux編」 https://www.amazon.co.jp/dp/B09T735Z2J/ref=cm_sw_r_tw_dp_1MW8QMBXXWX76HW7GZVE 最後に、定番のInfoSec Appで可視化してます。 ご注意) 本書では紙面の都合上、途中で以下の別書籍のご購入&その内容の理解済み、を前提としたページ構成となっています。 Splunk Enterpriseの始め方、Splunk Cloudへの引っ越し - Linux編 本気でUnix/Linuxマシンを監視する方法: Splunk + Splunk Unix and Linux Add-on編 ログのCIM対応はもう怖くない！ぱぱっとCIM化！: InfoSec Appで可視化もやっちゃう Amazon Kindle Unlimited契約の方は無料になりますので、ぜひ合わせてお読みください。Amazon Kindle Unlimited未契約の方は、誠に恐れ入りますが、上述の本もご一緒にご購入&学習の上、本書のご購入を強くオススメいたします。 使用ソフトウェア) 構築基盤) AWS 監視対象アプリケーション) WordPress 5.9 CentOS Linux release 7.9.2009 (Core) My SQL Server Community Edition 5.7 Apache HTTP Server 2.4.52 (CentOS Linux release 7.9.2009) PHP 7.4.28 (cli) Splunkソフトウェア) Splunk Cloud Trial 8.2.2104.1 Splunk Enterprise 8.2.5 ( Heavy Forwarderとして利用 ) Splunk Universal Forwarder 8.2.5 Splunk Add-on for Apache Web Server 2.0.0 Splunk Add-on for MySQL 3.0.0 Splunk DB Connect 3.7.0 Splunk Common Information Model (CIM) 5.0.0 InfoSec App for Splunk 1.7.0 他 JDK 11 目次) ご注意 はじめに 本書で確認が取れている各種ソフトウェア・バージョン 本書の前提について AWS上での分析対象システムの構築 - WordPress on Linux AWS上に3つのインスタンス作成 6.1. ３つインスタンスの作成 6.2. マシン名の設定 6.3. 通信ポートの設定 DB) AWS上のDBサーバーの構築 7.1. マシン名の設定 7.2. 通信ポートの設定 7.3. ファイアウォールの停止 7.1. selinuxのdisable 7.2. MySQL Community Editionのダウンロードとインストール 7.3. リモートマシン(Web)からアクセスできるように 7.4. Generalログ、Slow Queryログ、Errorログの設定 7.5. WordPressデータベースの作成 WWW) AWS上のWebサーバーの構築 8.1. マシン名の設定 8.2. 通信ポートの設定 8.3. ファイアウォールの停止 8.4. selinuxのdisable 8.5. PHP7のダウンロードとインストール 8.6. Apache HTTP Serverのダウンロードとインストール 8.7. Apache - PHP のセットアップ 8.8. Apache - PHP の動作確認 8.9. WordPressのダウンロード 8.10. WordPressのセットアップ 8.11. WordPressの動作確認 8.12. WordPressのIPアドレスの変更 Splunk Cloud)Splunk Cloudの申し込みと初期設定 9.1. Splunk社サイトへアクセス 9.2. Splunk.comアカウントの作成 9.3. ログインURLのメールを受信 9.4. Splunk Cloudへログインし、Forwarder Appをダウンロード 9.5. Splunk Cloud側でのアドオンインストール 9.6. Splunk Cloud側でのインデックス作成 Splunk) AWS上でのSplunk Deployment Serverセットアップ 10.1. マシン名の設定 10.2. 通信ポートの設定 10.3. ファイアウォールの停止 10.1. selinuxのdisable 10.2. Splunk Enterpriseのダウンロード 10.3. Splunk Enterpriseのセットアップ 10.4. デプロイメントサーバーライセンスの適用 WWW) AWS上のWebサーバーのUniversal Forwarderセットアップ 11.1. Splunk Universal Forwarderのセットアップ 11.2. Apache HTTPサーバーのログフォーマットの変更 11.3. 部分の書き換え 11.4. 動作確認(変更後) DB) AWS上のDBサーバーのHeavy Forwarderセットアップ 12.1. Splunk Heavy Forwarderのセットアップ 12.2. Heavy Forwarderにもライセンスキーが必要 12.3. JDK 11のダウンロードとインストール 12.4. 重要)DB Connect はDeployment Serverで集中管理・配布ができない 12.5. DB Connectのアドオンのダウンロードとインストール 12.6. MySSQLのJDBCのダウンロードとインストール 12.7. DB Connect AppでのJDBCドライバの確認 12.8. 認証情報の設定 12.9. 接続の設定 12.10. データベースへの接続テスト 12.11. インデックス作成 Splunk) AWS上でのSplunk Deployment Serverの設定 13.1. アドオンのインストール 13.2. Forwarder Appのインストール 13.3. コピーしたForwarder Appのファイルの書き換え(書き戻し)の実施 13.4. 接続中のWebサーバ、DBサーバのForwarderの確認 13.5. 3つのアドオンを一旦deployment-appsフォルダへコピー 13.6. 配布のためのUnix Add-on設定の作成 13.7. 配布のためのApache HTTP Server Add-on設定の作成 13.8. 配布のためのMySQL Add-on設定の作成 13.9. WWW) Webサーバーへのアドオン配布 13.9.1. Webサーバークラスの作成 13.9.2. クライアントの割当とアドオンの配布 13.10. DB) DBサーバーへのアドオン配布 13.10.1. DBサーバークラスの作成 13.10.2. クライアントの割当とアドオンの配布 13.11. ForwarderAppの再起動オプションをONにする DB) DB Connect設定の仕上げ 14.1. Splunk Cloudでのデータ確認 14.2. 他のデータの取込設定を実施 14.3. エラーログの監視設定 Splunk Cloud) Splunk Cloudでの可視化 15.1. Unixマシンのイベントログの可視化 15.2. Webサーバのログの可視化 15.3. DBサーバのログの可視化 Splunk Cloud) Splunk CloudでのInfoSec Appによる可視化 16.1. Common Information Model Add-onのインストール 16.2. InfoSec Appのインストール 16.3. InfoSec Appに必要な他の可視化Appのインストール 16.3.1. Punchcard - Custom Visualization 16.3.2. Force Directed App For Splunk 16.3.3. Splunk Sankey Diagram - Custom Visualization 16.4. InfoSec Appのためのデータモデル高速化設定 InfoSec Appの画面を確認 17.1. Security Posture画面 17.2. All Authentications画面 17.3. User Investigation画面 終わりに 著者プロフィール

はじめに AWS上にあるMySQLデータベースをOCIのMySQL DBシステムに移行する際に、S3上にあるバックアップデータを移動することなく、直接MySQL DBシステムにリストアする手順を検証しました。 今回は、Oracle Autonomous Linux 7.9のComputeインスタンスに、S3をファイルシステムとしてマウントできる「goofys」をインストールして使用しました。 1. goofysのインストール goofysはGoとfuseを使用するため、yumコマンドでgolangとfuseをインストールします。 [opc@compute1 ~]$ sudo yum install -y golang fuse Loaded plugins: langpacks, ulninfo Resolving Dependencies --> Running transaction check ---> Package fuse.x86_64 0:2.9.4-1.0.9.el7 will be installed ---> Package golang.x86_64 0:1.9.4-1.el7 will be installed --> Processing Dependency: golang-bin = 1.9.4-1.el7 for package: golang-1.9.4-1.el7.x86_64 --> Processing Dependency: golang-src = 1.9.4-1.el7 for package: golang-1.9.4-1.el7.x86_64 --> Running transaction check ---> Package golang-bin.x86_64 0:1.9.4-1.el7 will be installed ---> Package golang-src.noarch 0:1.9.4-1.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved =================================================================================================================== Package Arch Version Repository Size =================================================================================================================== Installing: fuse x86_64 2.9.4-1.0.9.el7 ol7_latest 88 k golang x86_64 1.9.4-1.el7 ol7_optional_latest 610 k Installing for dependencies: golang-bin x86_64 1.9.4-1.el7 ol7_optional_latest 48 M golang-src noarch 1.9.4-1.el7 ol7_optional_latest 5.1 M Transaction Summary =================================================================================================================== Install 2 Packages (+2 Dependent packages) Total download size: 54 M Installed size: 222 M Downloading packages: (1/4): fuse-2.9.4-1.0.9.el7.x86_64.rpm | 88 kB 00:00:01 (2/4): golang-1.9.4-1.el7.x86_64.rpm | 610 kB 00:00:01 (3/4): golang-src-1.9.4-1.el7.noarch.rpm | 5.1 MB 00:00:00 (4/4): golang-bin-1.9.4-1.el7.x86_64.rpm | 48 MB 00:00:02 ------------------------------------------------------------------------------------------------------------------- Total 20 MB/s | 54 MB 00:00:02 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : golang-src-1.9.4-1.el7.noarch 1/4 Installing : golang-1.9.4-1.el7.x86_64 2/4 Installing : golang-bin-1.9.4-1.el7.x86_64 3/4 Installing : fuse-2.9.4-1.0.9.el7.x86_64 4/4 Verifying : golang-bin-1.9.4-1.el7.x86_64 1/4 Verifying : golang-1.9.4-1.el7.x86_64 2/4 Verifying : fuse-2.9.4-1.0.9.el7.x86_64 3/4 Verifying : golang-src-1.9.4-1.el7.noarch 4/4 Installed: fuse.x86_64 0:2.9.4-1.0.9.el7 golang.x86_64 0:1.9.4-1.el7 Dependency Installed: golang-bin.x86_64 0:1.9.4-1.el7 golang-src.noarch 0:1.9.4-1.el7 Complete! [opc@compute1 ~]$ wgetでGitHubからgoofysをダウンロードします。 [opc@compute1 ~]$ wget https://github.com/kahing/goofys/releases/latest/download/goofys --2022-02-24 01:01:15-- https://github.com/kahing/goofys/releases/latest/download/goofys Resolving github.com (github.com)... 13.114.40.48 Connecting to github.com (github.com)|13.114.40.48|:443... connected. HTTP request sent, awaiting response... 302 Found Location: https://github.com/kahing/goofys/releases/download/v0.24.0/goofys [following] --2022-02-24 01:01:15-- https://github.com/kahing/goofys/releases/download/v0.24.0/goofys Reusing existing connection to github.com:443. HTTP request sent, awaiting response... 302 Found Location: https://objects.githubusercontent.com/github-production-release-asset-2e65be/42475296/92e1ec00-768c-11ea-82dd-825b7e124210?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAIWNJYAX4CSVEH53A%2F20220224%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20220224T010115Z&X-Amz-Expires=300&X-Amz-Signature=8b8605da0670a0357a9d53da81ee05fbb010ae9b74465bf64dbfe51cef9b5f53&X-Amz-SignedHeaders=host&actor_id=0&key_id=0&repo_id=42475296&response-content-disposition=attachment%3B%20filename%3Dgoofys&response-content-type=application%2Foctet-stream [following] --2022-02-24 01:01:15-- https://objects.githubusercontent.com/github-production-release-asset-2e65be/42475296/92e1ec00-768c-11ea-82dd-825b7e124210?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAIWNJYAX4CSVEH53A%2F20220224%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20220224T010115Z&X-Amz-Expires=300&X-Amz-Signature=8b8605da0670a0357a9d53da81ee05fbb010ae9b74465bf64dbfe51cef9b5f53&X-Amz-SignedHeaders=host&actor_id=0&key_id=0&repo_id=42475296&response-content-disposition=attachment%3B%20filename%3Dgoofys&response-content-type=application%2Foctet-stream Resolving objects.githubusercontent.com (objects.githubusercontent.com)... 185.199.108.133, 185.199.109.133, 185.199.110.133, ... Connecting to objects.githubusercontent.com (objects.githubusercontent.com)|185.199.108.133|:443... connected. HTTP request sent, awaiting response... 200 OK Length: 23296633 (22M) [application/octet-stream] Saving to: ‘goofys’ 100%[=========================================================================>] 23,296,633 7.01MB/s in 3.2s 2022-02-24 01:01:19 (7.01 MB/s) - ‘goofys’ saved [23296633/23296633] [opc@compute1 ~]$ ls -l total 22752 -rw-rw-r--. 1 opc opc 23296633 Dec 8 02:48 goofys ダウンロードしたgoofysをパスの通っているディレクトリ（今回は/usr/local/bin）に移動そ、オーナーとパーミッションを変更します。 [opc@compute1 ~]$ sudo mv goofys /usr/local/bin [opc@compute1 ~]$ sudo chown root:root /usr/local/bin/goofys [opc@compute1 ~]$ sudo chmod 775 /usr/local/bin/goofys 2. S3アクセス用のクレデンシャルファイルの作成 S3アクセス用のクレデンシャルファイル"/root/.aws/credentials"を作成します。 [opc@compute1 ~]$ sudo vi /root/.aws/credentials /root/.aws/credentials [aws] aws_access_key_id = S3バケットにアクセス可能なユーザのアクセスキー aws_secret_access_key = S3バケットにアクセス可能なユーザのシークレットキー 3. S3バケットのマウント S3バケットをファイルシステムとしてマウントするためのマウントポイントを作成します。 今回な"/mnt_s3"としました。 [opc@compute1 ~]$ sudo mkdir -p /mnt_s3 /etc/fstabに、goofysでS3バケットをマウントするためのエントリを追加します。 [opc@compute1 ~]$ sudo vi /etc/fstab 以下の内容を追加します。 今回は"s3bucketformds"という名前のS3バケットを"/mnt_s3"にマウントします。 goofys#s3bucketformds /mnt_s3 fuse _netdev,allow_other,--file-mode=0644,--uid=0,--gid=0,--region=ap-northeast-1,--profile=aws 0 0 mount -aでファイルシステムをマウントします。 [opc@compute1 ~]$ sudo mount -a S3のマウントポイントにファイルが作成できるか確認します。 [opc@compute1 mnt]$ cd /mnt_s3 [opc@compute1 mnt_s3]$ sudo touch test.txt [opc@compute1 mnt_s3]$ ls -l total 0 -rw-r--r--. 1 root root 0 Feb 24 01:18 test.txt AWSのコンソールでバケットの内容を確認します。 ファイルシステム上で作成したファイルが、S3バケット内にオブジェクトとして格納されていることが確認できました。 4. MySQLクライアントのインストール yumコマンドでリポジトリをセットアップします。 [opc@compute1 ~]$ sudo yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-5.noarch.rpm yumコマンドでMySQLクライアントをインストールします。 [opc@compute1 ~]$ sudo yum install -y mysql-community-client 5. MySQLクライアントからMySQL DBシステムに接続 mysqlコマンドでadminユーザとしてMySQL DB システムに接続します。 [opc@compute1 ~]$ mysql -u admin -p -h mysqlds1.subnet1.vcn1.oraclevcn.com Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 8.0.28-u1-cloud MySQL Enterprise - Cloud Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> データベースemployeesを作成します。 mysql> CREATE DATABASE employees; Query OK, 1 row affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | employees | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> mysqlを終了します。 mysql> exit Bye [opc@compute1 ~]$ 6. バックアップファイルの準備 mysqldumpで取得したバックアップファイル"employees.dmp"をS3バケットにアップロードします。 こちらのバックアップファイルは、こちらで公開されているサンプルデータを一度MySQLに取り込み、mysqldumpでデータベースのバックアップとして取得したものです。 S3のマウントポイントの内容を確認します。 [opc@compute1 ~]$ ls -l /mnt_s3/ total 164430 -rw-r--r--. 1 root root 168375940 Feb 24 05:32 employees.dmp -rw-r--r--. 1 root root 0 Feb 24 01:18 test.txt [opc@compute1 ~]$ employees.dmpがあることが確認できました。 7. S3バケットにあるバックアップファイルのリストア S3バケットのマウントポイント/mnt_s3にあるemployees.dmpをmysqlコマンドでMySQL DBシステムにリストアします。 [opc@compute1 ~]$ time mysql -u admin -p -h mysqlds1.subnet1.vcn1.oraclevcn.com -t employees < /mnt_s3/employees.dmp Enter password: real 1m9.092s user 0m2.026s sys 0m0.167s [opc@compute1 ~]$ mysqlコマンドでadminユーザとしてMySQL DBシステムに接続して、バックアップファイルが正しくリストアされているか確認します。 [opc@compute1 ~]$ mysql -u admin -p -h mysqlds1.subnet1.vcn1.oraclevcn.com Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 8.0.28-u1-cloud MySQL Enterprise - Cloud Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> データベースをemployeesに切り替えます。 mysql> use employees; Database changed mysql> データベースemployees内のテーブルを確認します。 mysql> show tables; +----------------------+ | Tables_in_employees | +----------------------+ | current_dept_emp | | departments | | dept_emp | | dept_emp_latest_date | | dept_manager | | employees | | salaries | | titles | +----------------------+ 8 rows in set (0.00 sec) mysql> テーブル内のデータを確認します。 mysql> SELECT COUNT(*) FROM employees; +----------+ | COUNT(*) | +----------+ | 300024 | +----------+ 1 row in set (0.02 sec) mysql> SELECT COUNT(*) FROM departments; +----------+ | COUNT(*) | +----------+ | 9 | +----------+ 1 row in set (0.01 sec) mysql> mysqlを終了します。 mysql> exit Bye [opc@compute1 ~]$ goofysを使用してS3バケットをファイルシステムにマウントし、S3バケット内のバックアップファイルを直接MySQL DBシステムにリストアすることができました。 参考情報 goofys mysql クライアントコマンド mysqldump — データベースバックアッププログラム

今回やること 1. MySQLのサンプルデータを取得 2. サンプルデータをJOINして取得できるようにする 3. 取得したいデータをドロップダウンリストで選択できる 4. 取得したデータをASP.NETのDataGridに表示 5. DataGridをソートできるようにする SQL文を定数として設定するだけでなく、条件に応じて変更させる。 ASP.NETのコントロールを使ってみる サンプルデータを取得 公式からZIPファイルをダウンロード 今回はworld databaseを使用します。 Zipを解凍して、MySQLにインストール コマンドプロンプト mysql> SOURCE 解凍したworld.sqlまでの絶対パス; データベースを確認 コマンドプロンプト # worldが追加されている mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sakila | | sys | | test | | world | +--------------------+ 7 rows in set (0.01 sec) mysql> use world; Database changed # テーブルが追加されている mysql> show tables; +-----------------+ | Tables_in_world | +-----------------+ | city | | country | | countrylanguage | +-----------------+ 3 rows in set (0.03 sec) テーブルを確認 コマンドプロンプト # cityテーブル mysql> select * from city limit 10; +----+----------------+-------------+---------------+------------+ | ID | Name | CountryCode | District | Population | +----+----------------+-------------+---------------+------------+ | 1 | Kabul | AFG | Kabol | 1780000 | | 2 | Qandahar | AFG | Qandahar | 237500 | | 3 | Herat | AFG | Herat | 186800 | | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 | | 5 | Amsterdam | NLD | Noord-Holland | 731200 | | 6 | Rotterdam | NLD | Zuid-Holland | 593321 | | 7 | Haag | NLD | Zuid-Holland | 440900 | | 8 | Utrecht | NLD | Utrecht | 234323 | | 9 | Eindhoven | NLD | Noord-Brabant | 201843 | | 10 | Tilburg | NLD | Noord-Brabant | 193238 | +----+----------------+-------------+---------------+------------+ 10 rows in set (0.02 sec) # countryテーブル mysql> select * from country limit 10; +------+----------------------+---------------+---------------------------+-------------+-----------+------------+----------------+-----------+-----------+-------------------------------------+----------------------------------------------+-----------------------------+---------+-------+ | Code | Name | Continent | Region | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP | GNPOld | LocalName | GovernmentForm | HeadOfState | Capital | Code2 | +------+----------------------+---------------+---------------------------+-------------+-----------+------------+----------------+-----------+-----------+-------------------------------------+----------------------------------------------+-----------------------------+---------+-------+ | ABW | Aruba | North America | Caribbean | 193.00 | NULL | 103000 | 78.4 | 828.00 | 793.00 | Aruba | Nonmetropolitan Territory of The Netherlands | Beatrix | 129 | AW | | AFG | Afghanistan | Asia | Southern and Central Asia | 652090.00 | 1919 | 22720000 | 45.9 | 5976.00 | NULL | Afganistan/Afqanestan | Islamic Emirate | Mohammad Omar | 1 | AF | | AGO | Angola | Africa | Central Africa | 1246700.00 | 1975 | 12878000 | 38.3 | 6648.00 | 7984.00 | Angola | Republic | Jos? Eduardo dos Santos | 56 | AO | | AIA | Anguilla | North America | Caribbean | 96.00 | NULL | 8000 | 76.1 | 63.20 | NULL | Anguilla | Dependent Territory of the UK | Elisabeth II | 62 | AI | | ALB | Albania | Europe | Southern Europe | 28748.00 | 1912 | 3401200 | 71.6 | 3205.00 | 2500.00 | Shqip?ria | Republic | Rexhep Mejdani | 34 | AL | | AND | Andorra | Europe | Southern Europe | 468.00 | 1278 | 78000 | 83.5 | 1630.00 | NULL | Andorra | Parliamentary Coprincipality | | 55 | AD | | ANT | Netherlands Antilles | North America | Caribbean | 800.00 | NULL | 217000 | 74.7 | 1941.00 | NULL | Nederlandse Antillen | Nonmetropolitan Territory of The Netherlands | Beatrix | 33 | AN | | ARE | United Arab Emirates | Asia | Middle East | 83600.00 | 1971 | 2441000 | 74.1 | 37966.00 | 36846.00 | Al-Imarat al-´Arabiya al-Muttahida | Emirate Federation | Zayid bin Sultan al-Nahayan | 65 | AE | | ARG | Argentina | South America | South America | 2780400.00 | 1816 | 37032000 | 75.1 | 340238.00 | 323310.00 | Argentina | Federal Republic | Fernando de la R?a | 69 | AR | | ARM | Armenia | Asia | Middle East | 29800.00 | 1991 | 3520000 | 66.4 | 1813.00 | 1627.00 | Hajastan | Republic | Robert Kot?arjan | 126 | AM | +------+----------------------+---------------+---------------------------+-------------+-----------+------------+----------------+-----------+-----------+-------------------------------------+----------------------------------------------+-----------------------------+---------+-------+ 10 rows in set (0.03 sec) # countrylanguageテーブル mysql> select * from countrylanguage limit 10; +-------------+------------+------------+------------+ | CountryCode | Language | IsOfficial | Percentage | +-------------+------------+------------+------------+ | ABW | Dutch | T | 5.3 | | ABW | English | F | 9.5 | | ABW | Papiamento | F | 76.7 | | ABW | Spanish | F | 7.4 | | AFG | Balochi | F | 0.9 | | AFG | Dari | T | 32.1 | | AFG | Pashto | T | 52.4 | | AFG | Turkmenian | F | 1.9 | | AFG | Uzbek | F | 8.8 | | AGO | Ambo | F | 2.4 | +-------------+------------+------------+------------+ 10 rows in set (0.00 sec) カラム情報を確認 コマンドプロンプト mysql> show columns from city; +-------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------+------+-----+---------+----------------+ | ID | int | NO | PRI | NULL | auto_increment | | Name | char(35) | NO | | | | | CountryCode | char(3) | NO | MUL | | | | District | char(20) | NO | | | | | Population | int | NO | | 0 | | +-------------+----------+------+-----+---------+----------------+ 5 rows in set (0.03 sec) mysql> show columns from country; +----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+ | Code | char(3) | NO | PRI | | | | Name | char(52) | NO | | | | | Continent | enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') | NO | | Asia | | | Region | char(26) | NO | | | | | SurfaceArea | decimal(10,2) | NO | | 0.00 | | | IndepYear | smallint | YES | | NULL | | | Population | int | NO | | 0 | | | LifeExpectancy | decimal(3,1) | YES | | NULL | | | GNP | decimal(10,2) | YES | | NULL | | | GNPOld | decimal(10,2) | YES | | NULL | | | LocalName | char(45) | NO | | | | | GovernmentForm | char(45) | NO | | | | | HeadOfState | char(60) | YES | | NULL | | | Capital | int | YES | | NULL | | | Code2 | char(2) | NO | | | | +----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+ 15 rows in set (0.00 sec) mysql> show columns from countrylanguage; +-------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------+------+-----+---------+-------+ | CountryCode | char(3) | NO | PRI | | | | Language | char(30) | NO | PRI | | | | IsOfficial | enum('T','F') | NO | | F | | | Percentage | decimal(4,1) | NO | | 0.0 | | +-------------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) サンプルデータをJOINして取得 コマンドプロンプト mysql> select cou.code, lan.language, city.name from country as cou inner join countrylanguage as lan on cou.code = lan.countrycode inner join city on cou.code = city.countrycode where cou.code = 'AFG'; +------+------------+----------------+ | code | language | name | +------+------------+----------------+ | AFG | Balochi | Kabul | | AFG | Dari | Kabul | | AFG | Pashto | Kabul | | AFG | Turkmenian | Kabul | | AFG | Uzbek | Kabul | | AFG | Balochi | Qandahar | | AFG | Dari | Qandahar | | AFG | Pashto | Qandahar | | AFG | Turkmenian | Qandahar | | AFG | Uzbek | Qandahar | | AFG | Balochi | Herat | | AFG | Dari | Herat | | AFG | Pashto | Herat | | AFG | Turkmenian | Herat | | AFG | Uzbek | Herat | | AFG | Balochi | Mazar-e-Sharif | | AFG | Dari | Mazar-e-Sharif | | AFG | Pashto | Mazar-e-Sharif | | AFG | Turkmenian | Mazar-e-Sharif | | AFG | Uzbek | Mazar-e-Sharif | +------+------------+----------------+ 20 rows in set (0.00 sec) 取得したいデータをドロップダウンリストで選択 ドロップダウンリストで大陸と地域を選択できるように設定 『完成イメージ』 ドロップダウンリストへデータを設定 ' 大陸データを取得するSQL文 Private Const Set_SQL_ddlcont As String = "" & " SELECT " & " DISTINCT " & " Continent " & " FROM " & " Country " & " ORDER BY " & " Continent " '地域データを取得するSQL文 Private Const Set_SQL_ddlregi As String = "" & " SELECT " & " DISTINCT " & " Region " & " FROM " & " Country " & " ORDER BY " & " Region " '大陸のドロップダウンリストにデータをセット Protected Sub set_cont() Dim ssql As String Dim dsCont As New DataSet Dim i As Integer Dim dsrow As Integer Dim Item As New System.Web.UI.WebControls.ListItem ssql = Set_SQL_ddlcont dsCont = SelectFromWorld(ssql) ddlCont.Items.Clear() Item.Text = "すべて" Item.Value = "0" ddlCont.Items.Add(Item) dsrow = dsCont.Tables(0).Rows.Count If dsrow > 0 Then For i = 0 To dsrow - 1 Item = New System.Web.UI.WebControls.ListItem Item.Text = dsCont.Tables(0).Rows(i).Item("Continent") Item.Value = i + 1 ddlCont.Items.Add(Item) Next Else lblErr.Text = "失敗しました" End If End Sub ' 地域のドロップダウンリストにデータをセット Protected Sub set_regi() Dim ssql As String Dim dsRegi As New DataSet Dim i As Integer Dim dsrow As Integer Dim Item As New System.Web.UI.WebControls.ListItem ssql = Set_SQL_ddlregi dsRegi = SelectFromWorld(ssql) ddlRegi.Items.Clear() Item.Text = "すべて" Item.Value = "0" ddlRegi.Items.Add(Item) dsrow = dsRegi.Tables(0).Rows.Count If dsrow > 0 Then For i = 0 To dsrow - 1 Item = New System.Web.UI.WebControls.ListItem Item.Text = dsRegi.Tables(0).Rows(i).Item("Region") Item.Value = i + 1 ddlregi.Items.Add(Item) Next Else lblErr.Text = "失敗しました" End If End Sub ラジオボタンで選択するとドロップダウンリストが操作できるようにする ラジオボタン操作 Protected Sub radBtn_CheckedChanged(sender As Object, e As EventArgs) Handles radRegi.CheckedChanged, radCont.CheckedChanged If radRegi.Checked = True Then ddlRegi.Enabled = True ddlCont.Enabled = False lblErr.Text = "地域を選択してください" ElseIf radCont.Checked = True Then ddlCont.Enabled = True ddlRegi.Enabled = False lblErr.Text = "大陸を選択してください" End If End Sub 大陸のドロップダウンリストと地域のドロップダウンリストを対応させる。 => 大陸でAsiaを選択したら地域にはAsiaに属する地域のみ表示 地域でMiddle Eastを選択したら大陸にはMiddle Eastに対応する大陸のみ表示 すべてを選択したら全件表示される ドロップダウンリスト操作 ' 大陸ドロップダウンリストの選択 Protected Sub ddlCont_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ddlCont.SelectedIndexChanged Dim ssql As New System.Text.StringBuilder Dim dsRegi As New DataSet Dim dsrow As Integer Dim Item As New System.Web.UI.WebControls.ListItem ssql.Append(" SELECT ") ssql.Append(" DISTINCT ") ssql.Append(" Region ") ssql.Append(" FROM ") ssql.Append(" Country ") 'ddlCont.SelectedValueが0(すべて)でない場合、対応する地域のみ取得 If ddlCont.SelectedValue <> 0 Then ssql.Append(" WHERE ") ssql.Append(" Continent = '" & ddlCont.SelectedItem.Text & "'") ssql.Append(" ORDER BY ") ssql.Append(" Region ") End If dsRegi = SelectFromWorld(ssql.ToString) ddlRegi.Items.Clear() Item.Text = "すべて" Item.Value = "0" ddlRegi.Items.Add(Item) dsrow = dsRegi.Tables(0).Rows.Count If dsrow > 0 Then For i = 0 To dsrow - 1 Item = New System.Web.UI.WebControls.ListItem Item.Text = dsRegi.Tables(0).Rows(i).Item("Region") Item.Value = i + 1 ddlRegi.Items.Add(Item) Next Else lblErr.Text = "地域の取得に失敗しました" End If 'ddlCont.SelectedValueが0のときは大陸も全件表示させる If ddlCont.SelectedValue = 0 Then Call set_cont() End If End Sub ' 地域ドロップダウンリストの選択 Private Sub ddlRegi_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ddlRegi.SelectedIndexChanged Dim ssql As New System.Text.StringBuilder Dim dsCont As New DataSet Dim dsrow As Integer Dim Item As New System.Web.UI.WebControls.ListItem ssql.Append(" SELECT ") ssql.Append(" DISTINCT ") ssql.Append(" Continent ") ssql.Append(" FROM ") ssql.Append(" Country ") 'ddlRegi.SelectedValueが0(すべて)でない場合、対応する大陸のみ取得 If ddlRegi.SelectedValue <> 0 Then ssql.Append(" WHERE ") ssql.Append(" Region = '" & ddlRegi.SelectedItem.Text & "'") ssql.Append(" ORDER BY ") ssql.Append(" Continent ") End If dsCont = SelectFromWorld(ssql.ToString) ddlCont.Items.Clear() Item.Text = "すべて" Item.Value = "0" ddlCont.Items.Add(Item) dsrow = dsCont.Tables(0).Rows.Count If dsrow > 0 Then For i = 0 To dsrow - 1 Item = New System.Web.UI.WebControls.ListItem Item.Text = dsCont.Tables(0).Rows(i).Item("Continent") Item.Value = i + 1 ddlCont.Items.Add(Item) Next Else lblErr.Text = "大陸の取得に失敗しました" End If 'ddlRegi.SelectedValueが0(すべて)のときは大陸もすべてを、すべて以外のときは対応する大陸を選択する If ddlRegi.SelectedValue = 0 Then ddlCont.SelectedValue = 0 Else ddlCont.SelectedValue = dsrow End If 'ddlRegi.SelectedValueが0のとき、地域も全件表示 If ddlRegi.SelectedValue = 0 Then Call set_regi() End If End Sub 取得したデータをASP.NETのDataGridに表示 検索ボタンをクリックすると上位100件までデータが表示される 『完成イメージ』 DataGridの操作 Protected Sub btnSerch_Click(sender As Object, e As EventArgs) Handles btnSerch.Click Dim ssql As New System.Text.StringBuilder Dim dsGrid As New DataSet Dim dt As New DataTable Dim i As Integer For i = 0 To gridM.Columns.Count - 1 gridM.Columns(i).HeaderText = gridM.Columns(i).HeaderText.Trim(AscCap).Trim(DescCap) Next lblErr.Text = "" ssql.Append(" SELECT ") ssql.Append(" ROW_NUMBER() OVER() AS Num, ") ssql.Append(" ctn.Name AS Name, ") ssql.Append(" ctn.Code AS Code, ") ssql.Append(" city.District AS District, ") ssql.Append(" city.Name AS City, ") ssql.Append(" city.Population AS Population, ") ssql.Append(" lang.Language AS Language") ssql.Append(" FROM ") ssql.Append(" country AS ctn") ssql.Append(" INNER JOIN ") ssql.Append(" city ") ssql.Append(" ON ") ssql.Append(" ctn.Code = city.CountryCode ") ssql.Append(" INNER JOIN ") ssql.Append(" countrylanguage AS lang ") ssql.Append(" ON ") ssql.Append(" ctn.Code = lang.CountryCode ") ssql.Append(" Where ") ssql.Append(" lang.IsOfficial = 'T' ") If ddlCont.SelectedValue <> 0 Then ssql.Append(" AND ") ssql.Append(" ctn.Continent = '" & ddlCont.SelectedItem.Text & "'") End If If ddlRegi.SelectedValue <> 0 Then ssql.Append(" And ") ssql.Append(" ctn.Region ='" & ddlRegi.SelectedItem.Text & "'") End If ssql.Append(" LIMIT ") ssql.Append(" 100 ") dsGrid = SelectFromWorld(ssql.ToString) dt = dsGrid.Tables(0) If dt.Rows.Count < 1 Then lblErr.Text = "データがありません" Session("GridData") = Nothing gridM.DataSource = Session("GridData") gridM.DataBind() Exit Sub End If Session("GridData") = dt gridM.DataSource = Session("GridData") gridM.CurrentPageIndex = 0 gridM.DataBind() End Sub ' グリッドビューのページ移動処理 Private Sub gridM_PageIndexChanged(source As Object, e As DataGridPageChangedEventArgs) Handles gridM.PageIndexChanged Dim dt As New DataTable dt = CType(Session("GridData"), DataTable) gridM.CurrentPageIndex = e.NewPageIndex gridM.DataSource = dt gridM.DataBind() End Sub DataGridをソートできるようにする 『完成イメージ』 人口でソート ソート機能 'char型は1文字+c　▲(昇順)　▼(降順) Private Const AscCap As Char = "▲"c Private Const DescCap As Char = "▼"c Public Sub gridM_Sort(sender As Object, e As DataGridSortCommandEventArgs) Handles gridM.SortCommand Dim ds As New DataSet Dim dt As New DataTable Dim dv As New DataView Dim curSortOrder As String Dim newSortOrder As String Dim sortColumn As New System.Text.StringBuilder Dim k As Integer '昇順か降順かを変数にセット、Sessionに記録する curSortOrder = CType(Session("viewstate"), String) If curSortOrder Is Nothing Or curSortOrder = "DESC" Then newSortOrder = "ASC" Else newSortOrder = "DESC" End If Session("viewstate") = newSortOrder ' 選択したカラムに並び順をセット sortColumn.Append(e.SortExpression) sortColumn.Append(" ") sortColumn.Append(newSortOrder) ' カラムの▲と▼を除く処理 For k = 0 To gridM.Columns.Count - 1 gridM.Columns(k).HeaderText = gridM.Columns(k).HeaderText.TrimEnd(AscCap).TrimEnd(DescCap) '選択したカラムとデータグリッドのカラムが等しい場合▲か▼を付ける If e.SortExpression.Equals(gridM.Columns(k).SortExpression) Then If newSortOrder = "ASC" Then gridM.Columns(k).HeaderText = String.Concat(gridM.Columns(k).HeaderText, AscCap) Else gridM.Columns(k).HeaderText = String.Concat(gridM.Columns(k).HeaderText, DescCap) End If End If Next dt = CType(Session("GridData"), DataTable) dv = dt.DefaultView dv.Sort = sortColumn.ToString gridM.DataSource = dv gridM.DataBind() If gridM.Visible = False Then lblErr.Text = "処理に失敗しました" Exit Sub End If End Sub ページ初期化処理 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Me.IsPostBack Then Exit Sub End If radCont.Checked = True ddlRegi.Enabled = False Session("GridData") = Nothing Session("viewstate") = Nothing Call set_cont() Call set_regi() End Sub 参考資料 MySQLのちゃんとしたサンプルデータ 【SQL】MySQL公式サンプルデータベースを使う ［ASP.NET］DataGridコントロールにソート機能を追加するには？ ［ASP.NET］DataGridコントロールのソートを双方向にするには？