Get server data using osquery

Get server data using osquery

Clock Icon2018.05.06

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

Osquery can get be used to retrieve device data using SQL commands. Specifically, it is possible to get data in Tables.

Downloading & Installing

It runs everywhere

Windows, macOS, CentOS, FreeBSD, and almost every Linux OS released since 2011 are supported with no dependencies.

Let's try installing it by referring to Downloading & Installing Osquery .

For example, I am installing to CentOS7 below.

$ curl -L https://pkg.osquery.io/rpm/GPG | sudo tee /etc/pki/rpm-gpg/RPM-GPG-KEY-osquery
$ sudo yum-config-manager --add-repo https://pkg.osquery.io/rpm/osquery-s3-rpm.repo
$ sudo yum-config-manager --enable osquery-s3-rpm
$ sudo yum install osquery

Execute osqueryi

Let’s now try using interactive mode as an example.

# osqueryi
Using a virtual database. Need help, type '.help'
osquery>

meta command

.tables

to list all tables

osquery> .table
  => acpi_tables
  => apt_sources
  => arp_cache
  [...]

.schema table_name

to list the schema

osquery> .schema shell_history
CREATE TABLE shell_history(`uid` BIGINT, `time` INTEGER, `command` TEXT, `history_file` TEXT);

pragma table_info(table_name);

to display table details

osquery> pragma table_info(shell_history);
+-----+--------------+---------+---------+------------+----+
| cid | name         | type    | notnull | dflt_value | pk |
+-----+--------------+---------+---------+------------+----+
| 0   | uid          | BIGINT  | 0       |            | 0  |
| 1   | time         | INTEGER | 0       |            | 0  |
| 2   | command      | TEXT    | 0       |            | 0  |
| 3   | history_file | TEXT    | 0       |            | 0  |
+-----+--------------+---------+---------+------------+----+

Execute query

load_average

to display system load average

osquery> select * from load_average;
+--------+----------+
| period | average  |
+--------+----------+
| 1m     | 0.000000 |
| 5m     | 0.010000 |
| 15m    | 0.050000 |
+--------+----------+

block_devices

to display block devices

osquery> select * from block_devices;
+------------+-----------+--------+-------+----------+------------+--------------------------------------+------+-------+
| name       | parent    | vendor | model | size     | block_size | uuid                                 | type | label |
+------------+-----------+--------+-------+----------+------------+--------------------------------------+------+-------+
| /dev/xvda  |           |        |       | 16777216 | 512        |                                      |      |       |
| /dev/xvda1 | /dev/xvda |        |       | 16775168 |            | ********-****-****-****-************ | xfs  |       |
+------------+-----------+--------+-------+----------+------------+--------------------------------------+------+-------+

last

to display login history

osquery> select * from last where username like 'centos';
+----------+-------+-------+------+------------+-----------------------------------+
| username | tty   | pid   | type | time       | host                              |
+----------+-------+-------+------+------------+-----------------------------------+
| centos   | pts/0 | 10158 | 7    | 1519072204 | ***.***.***.2                     |
| centos   | pts/0 | 1185  | 7    | 1519073094 | ***.***.***.2                     |
| centos   | pts/1 | 21429 | 7    | 1519113254 | d***-***-***-***.******.*****.net |
| centos   | pts/0 | 2815  | 7    | 1519113982 | d***-***-***-***.******.*****.net |
| centos   | pts/1 | 13023 | 7    | 1519405082 | ***.***.***.2                     |
| centos   | pts/1 | 13054 | 7    | 1519405100 | ***.***.***.2                     |
| centos   | pts/2 | 15834 | 7    | 1519415807 | ***.***.***.2                     |
| centos   | pts/2 | 16006 | 7    | 1519415868 | ***.***.***.2                     |
| centos   | pts/0 | 27795 | 7    | 1519456885 | d***-***-***-***.******.*****.net |
+----------+-------+-------+------+------------+-----------------------------------+

memory_info

to display memory information in bytes

osquery> select * from memory_info;
+--------------+-------------+---------+-----------+-------------+------------+-----------+------------+-----------+
| memory_total | memory_free | buffers | cached    | swap_cached | active     | inactive  | swap_total | swap_free |
+--------------+-------------+---------+-----------+-------------+------------+-----------+------------+-----------+
| 3973382144   | 1826029568  | 2117632 | 669937664 | 0           | 1718042624 | 256385024 | 0          | 0         |
+--------------+-------------+---------+-----------+-------------+------------+-----------+------------+-----------+

mode

We can set output mode using .mode MODE.

.mode MODE       Set output mode where MODE is one of:
                   csv      Comma-separated values
                   column   Left-aligned columns see .width
                   line     One value per line
                   list     Values delimited by .separator string
                   pretty   Pretty printed SQL results (default)

We can change the mode and get ec2_instance_metadata table data.

ec2_instance_metadata

to display EC2 instance metadata

osquery> .mode line
osquery> select * from ec2_instance_metadata;
      instance_id = i-*****************
    instance_type = t2.medium
     architecture = x86_64
           region = us-west-2
availability_zone = us-west-2c
   local_hostname = ip-***-***-***-***.us-west-2.compute.internal
       local_ipv4 = ***.***.***.***
              mac = **:**:**:**:**:**
  security_groups = defaultCentOS 7 -x86_64- - with Updates HVM-1801_01-AutogenByAWSMP-
          iam_arn = arn:aws:iam::************:instance-profile/iamrolename
           ami_id = ami-********
   reservation_id = r-********
       account_id = ********
   ssh_public_key = ssh-rsa ********

Execute pipe a query as stdin

ec2_instance_tags

to display EC2 instance tags

$ echo "select * from ec2_instance_tags;" | osqueryi --json | jq .
I0225 20:11:24.138509  5701 http_client.cpp:309] HTTP(S) request re-directed to: http://169.254.169.254/latest/meta-data/iam/security-credentials/
[
  {
    "instance_id": "i-*****************",
    "key": "Name",
    "value": "Prd"
  },
  {
    "instance_id": "i-*****************",
    "key": "Service",
    "value": "Dev"
  }
]

Conclusion

We illustrated how to get server data using osquery commands.

References

この記事をシェアする

facebook logohatena logotwitter logo

© Classmethod, Inc. All rights reserved.