You realize the drill: The phrases you might be on the lookout for could be in Slack, or GitHub, or Google Drive, or Google Sheets, or Zendesk, or … the listing goes on. Looking out throughout these silos is a standard frustration. It ought to be frictionless, and this Steampipe dashboard makes it so.
This wasn’t my first rodeo. I began this journey in 1996 and have revisited the thought periodically. In 2018 I wrote a few model that was the traditional instance of The Easiest Factor That May Presumably Work: a net web page that corrals the search URLs for numerous companies and visits every in its personal tab. As foolish as that sounds it was useful sufficient to get used a bit, and never simply by me.
In fact I needed to make use of the underlying APIs, normalize the outcomes, and merge them into a standard view. However the effort required to wrangle all of the APIs made that challenge extra hassle than it was value. For those who’ve accomplished this sort of factor earlier than you realize that almost all companies present search APIs together with adapters on your most popular programming language. However every service can have its personal manner of calling the API, paginating outcomes, and formatting them. These variations create friction you want to overcome as a way to work with the leads to a constant manner.
When API wrangling turns into frictionless, although, many issues grow to be attainable. Efficient metasearch is certainly one of them. Steampipe will get you out of the enterprise of calling APIs, paginating outcomes, and unpacking JSON objects. It calls the APIs for you and streams the outcomes into database tables so you may focus solely on working with the info. That solves the most important drawback you face when constructing a metasearch dashboard.
Converging on a schema
The following problem is to bind search outcomes to a standard schema. SQL is a good setting wherein to do this. The question that drives the dashboard proven within the screencast consists of three stanzas that you simply don’t need to be a SQL wizard to put in writing. All of them observe the identical sample as this one for looking GitHub points.
choose 'github_issue' as kind, repository_full_name || ' ' || title as supply, to_char(created_at, 'YYYY-MM-DD') as date, html_url as hyperlink, substring(physique from 1 for 200) || '...' as content material from github_search_issue the place $1 ~ 'github_issue' and question = 'in:physique in:feedback org:github ' || $2 restrict $3
Gadgets in blue are the names of columns in a database desk—on this case github_search_issue, one of many tables made by Steampipe’s GitHub plugin. The Steampipe hub makes it straightforward to examine the names and descriptions of the columns within the desk, and exhibits you examples of how you can use the knowledge within the desk.
As a result of fetching the info doesn’t require calling APIs and unpacking JSON, you may give attention to higher-order search syntax, which is a lot to consider, together with the attention-grabbing (and enjoyable!) problem of mapping supply columns to a standard schema.
Gadgets in purple are the names of the columns that present up within the dashboard. For this dashboard we’ve determined every search end result will map to those 5 columns: kind, supply, date, hyperlink, and content material. SQL’s AS clause makes it straightforward for every stanza to rename its columns to match the schema.
The complete question
Right here’s the total question that drives the dashboard. There are three stanzas just like the one above, every written as a CTE (widespread desk expression) with parameters akin to enter variables. And there’s nearly nothing else! Every stanza queries an API-based desk (slack_search, github_search_issue, googleworkspace_drive_my_file), selects (and perhaps transforms) columns, then aliases the outcomes to match the schema. All that’s left is to UNION the three CTEs, which act like short-term tables, and order the outcomes.
with slack as (
choose
'slack' as kind,
user_name || ' in #' || (channel ->> 'identify')::textual content as supply,
to_char(timestamp, 'YYYY-MM-DD') as date,
permalink as hyperlink,
substring(textual content from 1 for 200) as content material
from
slack_search
the place
$1 ~ 'slack'
and question = 'in:#steampipe after:${native.config.slack_date} ' || $2
restrict $3
),
github_issue as (
choose
'github_issue' as kind,
repository_full_name || ' ' || title as supply,
to_char(created_at, 'YYYY-MM-DD') as date,
html_url as hyperlink,
substring(physique from 1 for 200) || '...' as content material
from
github_search_issue
the place
$1 ~ 'github_issue'
and question = ' in:physique in:feedback org:${native.config.github_org} ' || $2
restrict $3
),
gdrive as (
choose
'gdrive' as kind,
substitute(mime_type,'utility/vnd.google-apps.','') as supply,
to_char(created_time, 'YYYY-MM-DD') as date,
'https://docs.google.com/doc/d/' || id as hyperlink,
identify as content material
from
googleworkspace_drive_my_file
the place
$1 ~ 'gdrive'
and question = 'fullText comprises ' || '''' || $2 || ''''
restrict $3
)
choose * from slack
union
choose * from github_issue
union
choose * from gdrive
order by
date desc
Dashboards as code
Many dashboard methods can work with this question. You possibly can, for instance, join Metabase, or Tableau, or one other Postgres shopper to Steampipe and construct the identical form of interactive dashboard as proven right here. You’d do this work in a low-code setting the place widgets and settings are dealt with in a person interface. Steampipe’s dashboard subsystem takes a unique strategy knowledgeable by its infrastructure-as-code (IaC) roots. Queries in opposition to APIs ought to be expressed in SQL code that’s managed, like all different code, in version-controlled repositories. The dashboard widgets that show the outcomes of these queries ought to likewise be expressed in code, and on this case the language is Terraform’s HCL.
Right here’s the HCL definition of the metasearch dashboard. It declares three sorts of enter block: sources (multi-select), search_term (textual content), and max_per_source (single-select, which is the default). You are able to do rather more with the enter block—notably, you may fill it with outcomes from a SQL question, as proven in the documentation. That’s not wanted right here, although.
The desk block makes use of the question outlined above, and defines the parameters handed to it. The wrap argument ensures that columns with a number of textual content will probably be readable.
dashboard "metasearch" {
enter "sources" {
title = "sources"
kind = "multiselect"
width = 2
possibility "slack" {}
possibility "github_issue" {}
possibility "gdrive" {}
}
enter "search_term" {
kind = "textual content"
width = 2
title = "search time period"
}
enter "max_per_source" {
title = "max per supply"
width = 2
possibility "2" {}
possibility "5" {}
possibility "10" {}
possibility "20" {}
}
desk {
title = "search slack + github + gdrive"
question = question.metasearch
args = [
self.input.sources,
self.input.search_term,
self.input.max_per_source
]
column "supply" {
wrap = "all"
}
column "hyperlink" {
wrap = "all"
}
column "content material" {
wrap = "all"
}
}
}
Once more there’s not a lot else to see right here, nor ought to there be. Constructing dashboards as code shouldn’t require a number of advanced code, and it doesn’t.
No wizardry required
Simply as you don’t must be a SQL wizard to create new subqueries, you additionally don’t must be an HCL wizard so as to add them to the dashboard. Would you want so as to add sources? There are dozens of different plugins to select from, with extra added every month. They don’t all supply search however many do, and it’s straightforward to search out them with (in fact!) a Steampipe question.
choose identify html_url from github_search_code the place question = 'search org:turbot org:francois2metz org:ellisvalentiner org:theapsgroup' and identify ~ 'desk' and identify ~ 'search' order by identify
Within the steampipe-samples repo we’ve included the code for the dashboard proven right here, together with an additional search stanza for Zendesk that we eliminated when our trial account expired. Have enjoyable extending this dashboard! If a search API you want isn’t already accessible, drop by our Slack group and tell us. Someone may already be writing the plugin you want—or perhaps you’d wish to sort out that your self. Each new plugin makes it attainable for anybody who can work with fundamental HCL plus SQL to wield APIs like a professional and remedy actual issues.
Copyright © 2022 IDG Communications, Inc.
