Project

General

Profile

query.sql

Brandon Weeks, 07/21/2016 02:11 PM

 
1
SELECT COUNT(DISTINCT "hosts"."id")
2
FROM "hosts"
3
LEFT OUTER JOIN "models" ON "models"."id" = "hosts"."model_id"
4
LEFT OUTER JOIN "hostgroups" ON "hostgroups"."id" = "hosts"."hostgroup_id"
5
LEFT OUTER JOIN "nics" ON "nics"."host_id" = "hosts"."id"
6
AND "nics"."primary" = 't'
7
LEFT OUTER JOIN "domains" ON "domains"."id" = "nics"."domain_id"
8
LEFT OUTER JOIN "realms" ON "realms"."id" = "hosts"."realm_id"
9
LEFT OUTER JOIN "environments" ON "environments"."id" = "hosts"."environment_id"
10
LEFT OUTER JOIN "architectures" ON "architectures"."id" = "hosts"."architecture_id"
11
LEFT OUTER JOIN "compute_resources" ON "compute_resources"."id" = "hosts"."compute_resource_id"
12
LEFT OUTER JOIN "images" ON "images"."id" = "hosts"."image_id"
13
LEFT OUTER JOIN "operatingsystems" ON "operatingsystems"."id" = "hosts"."operatingsystem_id"
14
LEFT OUTER JOIN "nics" "primary_interfaces_hosts" ON "primary_interfaces_hosts"."host_id" = "hosts"."id"
15
AND "primary_interfaces_hosts"."primary" = 't'
16
LEFT OUTER JOIN "nics" "interfaces_hosts" ON "interfaces_hosts"."host_id" = "hosts"."id"
17
LEFT OUTER JOIN "nics" "primary_interfaces_hosts_join" ON "primary_interfaces_hosts_join"."host_id" = "hosts"."id"
18
AND "primary_interfaces_hosts_join"."primary" = 't'
19
LEFT OUTER JOIN "subnets" ON "subnets"."id" = "primary_interfaces_hosts_join"."subnet_id"
20
LEFT OUTER JOIN "nics" "provision_interfaces_hosts" ON "provision_interfaces_hosts"."host_id" = "hosts"."id"
21
AND "provision_interfaces_hosts"."provision" = 't'
22
LEFT OUTER JOIN "discovery_rules" ON "discovery_rules"."id" = "hosts"."discovery_rule_id"
23
WHERE "hosts"."type" IN ('Host::Managed')
24
    AND (("hosts"."name" ILIKE '%asdf%'
25
          OR "hosts"."comment" ILIKE '%asdf%'
26
          OR "models"."name" ILIKE '%asdf%'
27
          OR "hostgroups"."name" ILIKE '%asdf%'
28
          OR "hostgroups"."title" ILIKE '%asdf%'
29
          OR "hostgroups"."title" ILIKE '%asdf%'
30
          OR "domains"."name" ILIKE '%asdf%'
31
          OR "realms"."name" ILIKE '%asdf%'
32
          OR "environments"."name" ILIKE '%asdf%'
33
          OR "architectures"."name" ILIKE '%asdf%'
34
          OR "compute_resources"."name" ILIKE '%asdf%'
35
          OR "images"."name" ILIKE '%asdf%'
36
          OR "operatingsystems"."name" ILIKE '%asdf%'
37
          OR "operatingsystems"."description" ILIKE '%asdf%'
38
          OR "operatingsystems"."title" ILIKE '%asdf%'
39
          OR "operatingsystems"."major" ILIKE '%asdf%'
40
          OR "operatingsystems"."minor" ILIKE '%asdf%'
41
          OR "nics"."ip" ILIKE '%asdf%'
42
          OR "nics"."ip" ILIKE '%asdf%'
43
          OR "nics"."mac" ILIKE '%asdf%'
44
          OR "subnets"."network" ILIKE '%asdf%'
45
          OR "subnets"."name" ILIKE '%asdf%'
46
          OR "hosts"."uuid" ILIKE '%asdf%'
47
          OR "nics"."mac" ILIKE '%asdf%'
48
          OR "operatingsystems"."name" ILIKE '%asdf%'
49
          OR "operatingsystems"."description" ILIKE '%asdf%'
50
          OR "operatingsystems"."title" ILIKE '%asdf%'
51
          OR "operatingsystems"."major" ILIKE '%asdf%'
52
          OR "operatingsystems"."minor" ILIKE '%asdf%'
53
          OR "discovery_rules"."name" ILIKE '%asdf%'))
54
    AND (last_report < '2016-07-21 16:55:26.923487'
55
         AND enabled != 'f');