@vivekramrs. The below YAML works based on Event Timestamp, as well as taking the about.labels.value field and does a distinct count (distinct count based on Event ID, as opposed to count of actual "events", as in some cases there can be multiple events generated, but only 1 event ID).
The below is a YAML file, go to dashboards, import, and import the following.
lookml:
- dashboard: logging_vs_not_logging
description: ""
elements:
- col: 0
column_limit: 50
conditional_formatting_include_nulls: false
conditional_formatting_include_totals: false
defaults_version: 1
dynamic_fields:
- _kind_hint: measure
_type_hint: number
based_on: events__about__labels.value
category: measure
expression: null
label: Custom Distinct count based on About.Labels.Value (Event ID)
measure: custom_distinct_count_based_on_aboutlabelsvalue_event_id
type: count_distinct
value_format: null
value_format_name: null
- _kind_hint: measure
_type_hint: string
category: table_calculation
expression: if(${custom_distinct_count_based_on_aboutlabelsvalue_event_id} >=
1, "Yes","No")
label: Logging
table_calculation: logging
value_format: null
value_format_name: null
enable_conditional_formatting: false
explore: events_connector
fields:
- events.metadata__event_timestamp_date
- events.principal__asset__hostname
- custom_distinct_count_based_on_aboutlabelsvalue_event_id
fill_fields:
- events.metadata__event_timestamp_date
filters: {}
header_font_size: 12
header_text_alignment: left
height: 11
hidden_pivots: {}
hide_row_totals: false
hide_totals: false
label_density: 25
legend_position: center
limit: 500
limit_displayed_rows: false
listen:
Filter Based on Event Timestamp!: events.time_filter
model: scn
name: Untitled
ordering: none
pivots:
- events.metadata__event_timestamp_date
plot_size_by_field: false
point_style: none
row: 0
rows_font_size: 12
show_null_labels: false
show_row_numbers: true
show_silhouette: false
show_totals_labels: false
show_value_labels: false
show_view_names: false
show_x_axis_label: true
show_x_axis_ticks: true
show_y_axis_labels: true
show_y_axis_ticks: true
size_to_fit: true
sorts:
- events.metadata__event_timestamp_date
- custom_distinct_count_based_on_aboutlabelsvalue_event_id desc 0
stacking: ""
table_theme: white
title: Untitled
totals_color: '#808080'
transpose: false
trellis: ""
truncate_text: true
type: looker_grid
width: 24
x_axis_gridlines: false
x_axis_reversed: false
x_axis_scale: auto
y_axis_combined: true
y_axis_gridlines: true
y_axis_reversed: false
y_axis_scale_mode: linear
y_axis_tick_density: default
y_axis_tick_density_custom: 5
filters:
- allow_multiple_values: true
default_value: 7 day
explore: events_connector
field: events.time_filter
listens_to_filters: []
model: scn
name: Filter Based on Event Timestamp!
required: false
title: Filter Based on Event Timestamp!
type: field_filter
ui_config:
display: popover
options: []
type: advanced
layout: newspaper
title: Logging vs Not Logging
metadata:
exported_at: "2023-12-19T10:16:04-08:00"
file_fingerprint: "155790365035053142063705415671691895678"
looker_version: 23.18.54
version: "1"
In terms of automating and generating an alert, you can:
You can also attempt to manually download the "Table" (it sends a GET request, monitor it via network tab) and then you can create a python script that can query this HTTP request, download it a excel, go through, check the "Logging" column for "NO", extract the particular row and column, write all of these to a separate .csv (these will be the hosts that haven't logged), and push that as an alert into chronicle.
Alternatively, schedule a delivery via email as a .CSV, and grab it from the email, create a python script to go through checking the Logging column, extract the count per row and push it into an alert into chronicle - this should work.
You also have the ability to do more fancy stuff, how about create a separate table, based on the initially created table that only shows assets that have the "Logging" set to no, or count the amount of assets that haven't been logging and produce a time graph!
Hope this helped!
LinkedIn
Twitter
