Generate a working OPAL query with SRE?

[barnao]

Hi all, I want a worksheet query that I can run to produce some data I use for analysis. I’m open to any alternatives but worksheets seem like the best way to do what is effectively similar to a CloudWatch query.

I want to use AI to help me generate an OPAL query. I see it’s able to generate these query cards that I can see in the chat, and this gives me the data I want, but the query used here is not the same as the query that will work in OPAL.

Maybe there is something I’m missing but it would be nice for there to be some built in functionality to help generate queries I can run ad-hoc.

TIA

[nikhil.dua]

Hey barnao,

Welcome to the Observe Community!

Would you mind sharing an example here of your use case with OPAL? Generally, AI SRE can produce OPAL such as the following

Nikhil

[barnao]

I guess I was thinking of a more complex query that I could copy and paste into the worksheet. this is one example:

@crawler_failures ← filter (app_environment = "production") and (app_project = "core_app") and (match_regex(logger_event_name, regex(".(sftp-crawler-failed|source-processing-failed|sftp-connection-failed)."), "i"))
timechart 1d, crawler_failures:count(), group_by()
fill crawler_failures:0

@file_failures ← filter (app_environment = "production") and (app_project = "core_app") and (match_regex(logger_event_name, regex(".(file-ingestion-failed|file-download-failed|file-listing-failed)."), "i"))
timechart 1d, file_failures:count(), group_by()
fill file_failures:0

@row_failures ← filter (app_environment = "production") and (app_project = "core_app") and (match_regex(logger_event_name, regex(".(row-validation-failed|intra-batch-duplicate|cross-file-duplicate|batch-processing-failed)."), "i"))
timechart 1d, row_failures:count(), group_by()
fill row_failures:0

@crawler_successes ← filter (app_environment = "production") and (app_project = "core_app") and (match_regex(logger_event_name, regex(".(sftp-crawler-complete)."), "i"))
timechart 1d, crawler_successes:count(), group_by()
fill crawler_successes:0

@row_successes ← filter (app_environment = "production") and (app_project = "core_app") and (match_regex(logger_event_name, regex(".(ingestion-completed|upload-completed)."), "i"))
extract_regex string(log), regex("\"successful\":\\s*(?P<successful_rows::int64>\\d+)", "c")
timechart 1d, row_successes:sum(successful_rows), group_by()
fill row_successes:0

leftjoin on(same(_c_valid_from, @file_failures._c_valid_from)), file_failures: @file_failures.file_failures
leftjoin on(same(_c_valid_from, @row_failures._c_valid_from)), row_failures: @row_failures.row_failures
leftjoin on(same(_c_valid_from, @crawler_successes._c_valid_from)), crawler_successes: @crawler_successes.crawler_successes
leftjoin on(same(_c_valid_from, @row_successes._c_valid_from)), row_successes: @row_successes.row_successes

The AI SRE can generate the right results for me, but in a "query card” format, which doesn’t seem to transfer over to worksheet.

[nikhil.dua]

Thanks. As I’m unable to see the dataset this was generated from, what’s the exact error you are seeing in the worksheet when you transfer over the OPAL?

Are you able to get something like this following screen? (Queries can be run from AI SRE)

[barnao]

There’s a few different syntax errors. I guess the card query syntax is different than OPAL syntax.

[nikhil.dua]

Was curious to see what the syntax generated from the query card was. If you can open up the query card from AI SRE in a worksheet, the syntax should be correct there to run the OPAL.