Best layout for inventory Data

Hi,
I am realatively new to the topic of Elasticsearch and Kibana. I have set up a small infrastructure with Logstash, Elastisearch and Kibana. Currently, files are successfully routed to my custom index via Logsatash and are available in Elasticsearch and Kibana. So far so good. However, queries and displaying data is failing in some cases.

In principle, I want to index two types of data. Inventory and security scan data in the form of findings.

For both, JSON data is provided. I would like to know from the experienced users an opinion about an optimal data structure.

The structure I use does not seem to be optimal. For example, if I search for an installed product and its version, I get the product and host back, but all versions of all installed products.

I would appreciate tips and help.
Thanks

Eric

Search Query

    GET /security_customer/_search
    {
      "query": {
        "wildcard": {
          "Inventory.Product.Description.keyword": "7-Zip*"
        }
      },
      "fields": ["@timestamp","Hostname","Inventory.Product.Version.keyword"],
      "_source": [ "@timestamp","Hostname" ]
    }

Search output:

    {
      "took" : 152,
      "timed_out" : false,
      "_shards" : {
        "total" : 1,
        "successful" : 1,
        "skipped" : 0,
        "failed" : 0
      },
      "hits" : {
        "total" : {
          "value" : 1,
          "relation" : "eq"
        },
        "max_score" : 1.0,
        "hits" : [
          {
            "_index" : "security_customer",
            "_type" : "_doc",
            "_id" : "aM4TAngB-niHIzJf1HMI",
            "_score" : 1.0,
            "_source" : {
              "@timestamp" : "2021-03-05T11:09:17.004Z",
              "Hostname" : "MYHOST"
            },
            "fields" : {
              "@timestamp" : [
                "2021-03-05T11:09:17.004Z"
              ],
              "Inventory.Product.Version.keyword" : [
                "1.0.1",
                "1.0.0.9",
                "1.1.2",
                "1.0.9",
                "1.1.9",
                "1.1.4",
                "1.1.7",
                "1.0.0",
    ....
         ],
              "Hostname" : [
                "MyHost"
              ]
            }
          }
        ]
      }
    }

Inventory Data

    {
         "User": "MYHOST\\Adminisitrator",
         "Inventory": {
              "Installed-Programs": [
                   {
                        "ProgramId": "00001595524d8fbc93b466797401af4508ed0000ffff",
                        "Name": "GooGet - google-compute-engine-driver-gvnic",
                        "MsiPackageCode": "",
                        "MsiProductCode": "",
                        "Language": "65535",
                        "Version": "1.0.0@5",
                        "Vendor": ""
                   },
                   {
                        "ProgramId": "00002b842f47051e90b726193292a687fe380000ffff",
                        "Vendor": "",
                        "Version": "3.14.0@1",
                        "Language": "65535",
                        "MsiProductCode": "",
                        "MsiPackageCode": "",
                        "Name": "GooGet - google-compute-engine-sysprep"
                   },
                   {
                        "MsiProductCode": "",
                        "MsiPackageCode": "",
                        "Name": "Google Cloud SDK",
                        "Vendor": "Google Inc.",
                        "Version": "",
                        "Language": "65535",
                        "ProgramId": "00002e8338a7a095d31c6a3c49e8f75545a80000ffff"
                   },
                   {
                        "ProgramId": "0000694981f7b52a2372f4daf23c968d315d0000ffff",
                        "Name": "GooGet - google-compute-engine-driver-balloon",
                        "MsiPackageCode": "",
                        "MsiProductCode": "",
                        "Language": "65535",
                        "Version": "16.1.3@18",
                        "Vendor": ""
                   },
                   {
                        "ProgramId": "000080ccbf5f307a4d68711d70aa23cdcca90000ffff",
                        "Language": "65535",
                        "Version": "1.1.1@18",
                        "Vendor": "",
                        "Name": "GooGet - google-compute-engine-driver-gga",
                        "MsiPackageCode": "",
                        "MsiProductCode": ""
                   },
                   {
                        "Version": "20201229.01.0+win@1",
                        "Language": "65535",
                        "Vendor": "",
                        "Name": "GooGet - google-osconfig-agent",
                        "MsiPackageCode": "",
                        "MsiProductCode": "",
                        "ProgramId": "00008559899a641aeb711bbc0814971182f90000ffff"
                   },
                   {
                        "ProgramId": "000080ccbf5f307a4d68711d70aa23cdcca90000ffff",
                        "Language": "65535",
                        "Version": "1.1.1@18",
                        "Vendor": "",
                        "Name": "GooGet - google-compute-engine-driver-gga",
                        "MsiPackageCode": "",
                        "MsiProductCode": ""
                   },
                   {
                        "Version": "19.0",
                        "Language": "65535",
                        "Vendor": "",
                        "Name": "7-Zip Agent",
                        "MsiPackageCode": "",
                        "MsiProductCode": "",
                        "ProgramId": "00008559899a641aeb711bbc0814971182f90000ffff"
                   }
              ],
              "BIOS": [
                   {
                        "ListOfLanguages": null,
                        "Description": "Google",
                        "BIOSVersion": [
                             "Google - 1",
                             "Google",
                             "EDK II - 10000"
                        ],
                        "InstallableLanguages": null,
                        "EmbeddedControllerMajorVersion": 255,
                        "EmbeddedControllerMinorVersion": 255,
                        "Name": "Google",
                        "Caption": "Google",
                        "BiosCharacteristics": [
                             3,
                             42
                        ],
                        "Manufacturer": "Google",
                        "CurrentLanguage": null
                   }
              ]
         },
         "Hostname": "MYHOST",
         "ScanTime": "2021-03-05 13:34:11",
         "FQDN": "MYHOST",
         "GlobalUUID": "1634BD3236-B433C-3z57-4752-06369C163FA8"
    }

hi @Eric007 ,

since this is somewhat of a general question, the answer will be a little general too. So YMMV.

In my experience, you will get the most value out of Elasticsearch if you denormalize your data. In other words, instead of creating nested data structures, index as a single document your most granular entity, and have the fields acts as metadata about this .

Necessarily, this will end up duplicated some data. But the Elasticsearch-aggregation framework is very useful to query and pivot on this data in any way you would like.

So e.g., for the inventory data, assing a single document to every user-BIOS-Installed-program in your inventory, and duplicate the user meta-data on each.

1 Like

Hi @thomasneirynck,

thanks for your reply. I had already thought so, but I was afraid of the redundant and amount of data. If you take the number of CIs to be scanned (> 30,000) the number of individual data blocks and a daily data set per CI, you quickly arrive at a large set of documents. But I will give it a try.

Thanks for your support.

Eric

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.