Metabase Query

Use this forum to get assistance with understanding, editing, or using the IIS metabase including automating with ADSI or WMI Latest: Nov 07, 2019 06:08 AM Re: IIS6 Virtual STMP server and Content-Transfer-Encoding by Jalpa Panchal. + 018b068 satellite/metabase: different queries for deleteexpired + 6a553ec web/satellite: change banner for beta satellites with URLs + 36bc17e satellite/metainfo/metabase: add etag column to segments table. You can write SQL (or your database’s native querying language) directly into the editor that appears. To try it out, select the Sample Dataset, then type the command select sum (subtotal), createdat from orders group by createdat. Don’t worry if you don’t understand this just yet. But with Metabase’s SQL interface, designers can write their own custom queries and share the results with their teams. And even beginners can create their own shareable dashboards. When you add the Panoply Metabase integration, the entire ETL process is managed securely and automatically. ETL is the flow that powers any data operations.

v0.39.0.1 / Users Guide / Writing Sql

Asking more advanced questions in the SQL/native query editor

If you ever need to ask questions that can’t be expressed using the question builder, you can use SQL instead.

What’s SQL?

SQL (pronounced “sequel”) stands for Structured Query Language, and is a widely used standard for getting data from databases. We won’t try to teach you all about SQL in this guide, but to learn more about it, check out this SQL Tutorial.

But so is Metabase’s query builder and notebook editor. You can compose queries using Metabase’s graphical interface to join tables, filter and summarize data, create custom columns, and more. And with custom expressions, you can handle the vast majority of analytical use cases, without ever needing to reach for SQL.

Metabase Query

Even if you don’t understand SQL or how to use it, it’s worthwhile to understand how works inside Metabase because sometimes other people will share SQL-based questions that might be useful to you.

Starting a new SQL query

If you have the permissions to use the SQL editor, when you click the Ask a Question button you’ll see an option to start a new SQL query. There’s also a shortcut to the editor in the top nav bar; it’s the little console icon.

You can write SQL (or your database’s native querying language) directly into the editor that appears.

To try it out, select the Sample Dataset, then type the command select sum(subtotal), created_at from orders group by created_at. Don’t worry if you don’t understand this just yet. Click the blue run button and note the table that comes back is the same as if you had asked for the sum of Subtotal in the Orders table, grouped by Created At.

Tip: You can run your SQL query by pressing ctrl + enter on Windows and Linux, or ⌘ + return on a Mac. You can also run only the selected section of a query by highlighting the part you’d like to run before clicking the run button or using the run shortcut key.

Questions asked using SQL can be saved, downloaded, or added to a dashboard just like questions asked using the question builder.

Using SQL filters

Metabase Query Interview

If you or someone else wrote a SQL query that includes variables, your question might have filter widgets at the top of the screen. Filter widgets let you modify the SQL query before it’s run, changing the results you might get.

Writing SQL queries that use variables or parameters can be very powerful, but it’s also a bit more advanced, so that topic has its own page if you’d like to learn more.

SQL snippets

You can use SQL snippets to save, reuse, and share SQL code across multiple questions that are composed using the SQL editor.

Metabase Query Example

Learn more

  • Best practices for writing SQL queries.

Next: Creating charts

Now that you have an answer to your question, you can learn about visualizing answers.

-->

IIS server variables provide information about the server, the connection with the client, and the current request on the connection. IIS server variables are not the same as environment variables.

Security

Metabase Query Json

Some server variables get their information from HTTP headers. It is recommended that you distrust information in HTTP headers because this data can be falsified by malicious users. For more detailed information, see Writing More Secure IIS Applications.

Obtaining Server Variables

Metabase Query Api

Server variables can be obtained in one of the following ways:

  • In ASP, use the Request.ServerVariables collection.

  • In ASP.NET, use the HttpRequest.ServerVariables property. HttpRequest.ServerVariables returns some additional server variables that are only relevant to ASP.NET.

  • In COM+ components for ASP, use the IRequest::get_ServerVariables method.

  • In ISAPI applications, use the GetServerVariable callback function. In ISAPI, unicode versions of server variables are available by pre-pending 'UNICODE_' to the front of server variable names. For more information about unicode server variables, see the 'Remarks' section of the GetServerVariable topic.

Metabase Query Format

Variable

Description

ALL_HTTP

All HTTP headers sent by the client.

ALL_RAW

Retrieves all headers in raw form. The difference between ALL_RAW and ALL_HTTP is that ALL_HTTP places an HTTP_ prefix before the header name and the header name is always capitalized. In ALL_RAW the header name and values appear as they are sent by the client.

APP_POOL_ID

IIS 5.1 and earlier: This server variable is not available.

Returns the name of the application pool that is running in the IIS worker process that is handling the request.

There is also an APP_POOL_ID environment variable that is available to applications that are running in the IIS worker process.

APPL_MD_PATH

Retrieves the metabase path of the application.

APPL_PHYSICAL_PATH

Retrieves the physical path corresponding to the metabase path in APPL_MD_PATH.

AUTH_PASSWORD

The value entered in the client's authentication dialog. This variable is available only if Basic authentication is used.

AUTH_TYPE

The authentication method that the server uses to validate users when they attempt to access a protected script.

It does not mean that the user was authenticated if AUTH_TYPE contains a value and the authentication scheme is not Basic or integrated Windows authentication. The server allows authentication schemes it does not natively support because an ISAPI filter may be able to handle that particular scheme.

AUTH_USER

The name of the user as it is derived from the authorization header sent by the client, before the user name is mapped to a Windows account. This variable is no different from REMOTE_USER. If you have an authentication filter installed on your Web server that maps incoming users to accounts, use LOGON_USER to view the mapped user name.

CACHE_URL

IIS 5.1 and earlier: This server variable is not available.

For use in ISAPI applications only. Returns the unambiguous name for the current URL. It is necessary to use the Unicode version of this variable in conjunction with the kernel mode cache invalidation function to evict entries placed in the cache by HSE_REQ_VECTOR_SEND.

Note

The server variable 'UNICODE_CACHE_URL' is used in conjunction with the cache invalidation function retrieved by the HSE_REQ_GET_CACHE_INVALIDATION_CALLBACK function. This function invalidates responses cached in HTTP.SYS, whether those responses are produced by requests or by ISAPIs calling HSE_REQ_VECTOR_SEND.

CERT_COOKIE

Unique ID for the client certificate, returned as a string. This can be used as a signature for the whole client certificate.

CERT_FLAGS

bit0 is set to 1 if the client certificate is present.

bit1 is set to 1 if the certification authority of the client certificate is invalid (that is, it is not in the list of recognized certification authorities on the server).

If bit 1 of CERT_FLAGS is set to 1, indicating that the certificate is invalid, IIS version 4.0 and later will reject the certificate. Earlier versions of IIS will not reject the certificate.

CERT_ISSUER

Issuer field of the client certificate (O=MS, OU=IAS, CN=user name, C=USA).

CERT_KEYSIZE

Number of bits in the Secure Sockets Layer (SSL) connection key size. For example, 128.

CERT_SECRETKEYSIZE

Number of bits in server certificate private key. For example, 1024.

CERT_SERIALNUMBER

Serial number field of the client certificate.

CERT_SERVER_ISSUER

Issuer field of the server certificate.

CERT_SERVER_SUBJECT

Subject field of the server certificate.

CERT_SUBJECT

Subject field of the client certificate.

CONTENT_LENGTH

The length of the content as given by the client.

CONTENT_TYPE

The data type of the content. Used with queries that have attached information, such as the HTTP queries GET, POST, and PUT.

GATEWAY_INTERFACE

The revision of the CGI specification used by the server. The format is CGI/revision.

HEADER_<HeaderName>

IIS 5.1 and earlier: This server variable is not available.

The value stored in the header <HeaderName>. Any header other than those listed in this table must be preceded by 'HEADER_' in order for the ServerVariables collection to retrieve its value. This is useful for retrieving custom headers.

Note

Unlike HTTP_<HeaderName>, all characters in HEADER_<HeaderName> are interpreted as-is. For example, if you specify HEADER_MY_HEADER, the server searches for a request header named MY_HEADER.

HTTP_<HeaderName>

The value stored in the header <HeaderName>. Any header other than those listed in this table must be preceded by 'HTTP_' in order for the ServerVariables collection to retrieve its value. This is useful for retrieving custom headers.

Note

The server interprets any underscore (_) characters in <HeaderName> as dashes in the actual header. For example, if you specify HTTP_MY_HEADER, the server searches for a request header named MY-HEADER.

HTTP_ACCEPT

Returns the value of the Accept header that contains a list of accepted formats, for example, 'image/gif, image/x-xbitmap, image/jpeg, image/pjpeg, application/vnd.ms-excel'.

The values of the fields for the HTTP_ACCEPT variable are concatenated, and separated by a comma (,).

HTTP_ACCEPT_ENCODING

Returns a list of accepted encoding types, for example, 'gzip, deflate'.

HTTP_ACCEPT_LANGUAGE

Returns a string describing the language to use for displaying content.

HTTP_CONNECTION

Returns a string describing the connection type, for example, 'Keep-Alive'.

HTTP_COOKIE

Returns the cookie string that was included with the request.

HTTP_HOST

Returns the name of the Web server. This may or may not be the same as SERVER_NAME depending on type of name resolution you are using on your Web server (IP address, host header).

HTTP_METHOD

The method used to make the request (same as REQUEST_METHOD).

HTTP_REFERER

Returns a string that contains the URL of the page that referred the request to the current page using an HTML <A> tag. Note that the URL is the one that the user typed into the browser address bar, which may not include the name of a default document.

If the page is redirected, HTTP_REFERER is empty.

HTTP_REFERER is not a mandatory member of the HTTP specification.

HTTP_URL

Returns the raw, encoded URL, for example, '/vdir/default.asp?querystring'.

HTTP_USER_AGENT

Returns a string describing the browser that sent the request.

HTTP_VERSION

The name and version of the request protocol (the raw form of SERVER_PROTOCOL).

HTTPS

Returns ON if the request came in through a secure channel (for example, SSL); or it returns OFF, if the request is for an insecure channel.

HTTPS_KEYSIZE

Number of bits in the SSL connection key size. For example, 128.

HTTPS_SECRETKEYSIZE

Number of bits in the server certificate private key. For example, 1024.

HTTPS_SERVER_ISSUER

Issuer field of the server certificate.

HTTPS_SERVER_SUBJECT

Subject field of the server certificate.

INSTANCE_ID

The ID for the IIS instance in textual format. If the instance ID is 1, it appears as a string. You can use this variable to retrieve the ID of the Web server instance (in the metabase) to which the request belongs.

INSTANCE_META_PATH

The metabase path for the instance of IIS that responds to the request.

LOCAL_ADDR

Returns the server address on which the request came in. This is important on computers where there can be multiple IP addresses bound to the computer, and you want to find out which address the request used.

LOGON_USER

The Windows account that the user is impersonating while connected to your Web server. Use REMOTE_USER, UNMAPPED_REMOTE_USER, or AUTH_USER to view the raw user name that is contained in the request header. The only time LOGON_USER holds a different value than these other variables is if you have an authentication filter installed.

PATH_INFO

Path information, as given by the client, for example, '/vdir/myisapi.dll/zip'. If this information comes from a URL, it is decoded by the server before it is passed to the CGI script or ISAPI filter.

If the AllowPathInfoForScriptMappings metabase property is set to true (to support exclusive CGI functionality), PATH_INFO will only contain '/zip' and ISAPI applications such as ASP will break.

PATH_TRANSLATED

The physical path that maps to the virtual path in PATH_INFO, for example, 'c:inetpubwwwrootvdirmyisapi.dll'. This variable is used by IIS during the processing of ISAPI applications.

If the AllowPathInfoForScriptMappings metabase property is set to true (to support exclusive CGI functionality), PATH_INFO will only contain '/zip' and ISAPI applications such as ASP will break.

QUERY_STRING

Query information stored in the string following the question mark (?) in the HTTP request.

REMOTE_ADDR

The IP address of the remote host that is making the request.

REMOTE_HOST

The name of the host that is making the request. If the server does not have this information, it will set REMOTE_ADDR and leave this empty.

REMOTE_PORT

The client port number of the TCP connection.

REMOTE_USER

The name of the user as it is derived from the authorization header sent by the client, before the user name is mapped to a Windows account. If you have an authentication filter installed on your Web server that maps incoming users to accounts, use LOGON_USER to view the mapped user name.

REQUEST_METHOD

The method used to make the request. For HTTP, this can be GET, HEAD, POST, and so on.

SCRIPT_NAME

A virtual path to the script being executed, for example, '/vdir/default.asp'. This is used for self-referencing URLs.

SCRIPT_TRANSLATED

IIS 5.1 and earlier: This server variable is not available.

The canonical physical path to the script listed in SCRIPT_NAME, for example, '?c:inetpubwwwrootvdirdefault.asp'.

SERVER_NAME

The server's host name, DNS alias, or IP address as it would appear in self-referencing URLs.

SERVER_PORT

The server port number to which the request was sent.

SERVER_PORT_SECURE

A string that contains either 0 or 1. If the request is being handled on the secure port, then this is 1. Otherwise, it is 0.

SERVER_PROTOCOL

The name and revision of the request information protocol. The format is protocol/revision. (The canonicalized form of HTTP_VERSION.)

SERVER_SOFTWARE

The name and version of the server software that answers the request and runs the gateway. The format is name/version.

SSI_EXEC_DISABLED

IIS 5.1 and earlier: This server variable is not available.

Returns a 1 if the server-side include directive, #exec, is disabled. Otherwise, SSI_EXE_DISABLED returns a 0. To enable or disable #exec, use the SSIExecDisablemetabase property.

UNENCODED_URL

IIS 4.0 and earlier: This server variable is not available.

Returns the raw, unencoded URL, for example, '/vdir/default.asp?querystring'.

UNICODE_<ServerVariable Name>

IIS 5.1 and earlier: This server variable is not available.

In unicode ISAPI applications only, it is possible to retrieve server variable values as unicode values by prepending 'UNICODE_' to the name of the server variable, unless the variable starts with 'HTTP_' or 'HEADER_'.

UNMAPPED_REMOTE_USER

The name of the user as it is derived from the authorization header sent by the client, before the user name is mapped to a Windows account (same as REMOTE_USER). If you have an authentication filter installed on your Web server that maps incoming users to accounts, use LOGON_USER to view the mapped user name.

URL

Gives the base portion of the URL, without any querystring or extra path information, for example, '/vdir/default.asp'.

For the raw URL, use HTTP_URL or UNENCODED_URL.

URL_PATH_INFO

Note: This server variable is only available on IIS 5.0.

Use PATH_INFO instead.