One Hat Cyber Team

Edit File: mssqlPlugin.88775f59ced74c2283c5.js

"use strict";(self.webpackChunkgrafana=self.webpackChunkgrafana||[]).push([[9040],{47371:(Te,D,s)=>{s.r(D),s.d(D,{plugin:()=>ve});var z=s(40187),v=s(71928),I=s(32196),e=s(96540),w=s(40845);function $(){const t=(0,w.of)(R);return e.createElement("div",null,e.createElement("h2",null,"MSSQL cheat sheet"),"Time series:",e.createElement("ul",{className:t.ulPadding},e.createElement("li",null,"return column named time (in UTC), as a unix time stamp or any sql native date data type. You can use the macros below."),e.createElement("li",null,"any other columns returned will be the time point values.")),"Optional:",e.createElement("ul",{className:t.ulPadding},e.createElement("li",null,"return column named ",e.createElement("i",null,"metric")," to represent the series name."),e.createElement("li",null,"If multiple value columns are returned the metric column is used as prefix."),e.createElement("li",null,"If no column named metric is found the column name of the value column is used as series name")),e.createElement("p",null,"Resultsets of time series queries need to be sorted by time."),"Table:",e.createElement("ul",{className:t.ulPadding},e.createElement("li",null,"return any set of columns")),"Macros:",e.createElement("ul",{className:t.ulPadding},e.createElement("li",null,"$__time(column) -> column AS time"),e.createElement("li",null,"$__timeEpoch(column) -> DATEDIFF(second, '1970-01-01', column) AS time"),e.createElement("li",null,"$__timeFilter(column) -> column BETWEEN '2017-04-21T05:01:17Z' AND '2017-04-21T05:01:17Z'"),e.createElement("li",null,"$__unixEpochFilter(column) -> column >= 1492750877 AND column <= 1492750877"),e.createElement("li",null,"$__unixEpochNanoFilter(column) -> column >= 1494410783152415214 AND column <= 1494497183142514872"),e.createElement("li",null,"$__timeGroup(column, '5m'[, fillvalue]) -> CAST(ROUND(DATEDIFF(second, '1970-01-01', column)/300.0, 0) as bigint)*300 by setting fillvalue grafana will fill in missing values according to the interval fillvalue can be either a literal value, NULL or previous; previous will fill in the previous seen value or NULL if none has been seen yet"),e.createElement("li",null,"$__timeGroupAlias(column, '5m'[, fillvalue]) -> CAST(ROUND(DATEDIFF(second, '1970-01-01', column)/300.0, 0) as bigint)*300 AS [time]"),e.createElement("li",null,"$__unixEpochGroup(column,'5m') -> FLOOR(column/300)*300"),e.createElement("li",null,"$__unixEpochGroupAlias(column,'5m') -> FLOOR(column/300)*300 AS [time]")),e.createElement("p",null,"Example of group by and order by with $__timeGroup:"),e.createElement("pre",null,e.createElement("code",null,"SELECT $__timeGroup(date_time_col, '1h') AS time, sum(value) as value ",e.createElement("br",null),"FROM yourtable",e.createElement("br",null),"GROUP BY $__timeGroup(date_time_col, '1h')",e.createElement("br",null),"ORDER BY 1",e.createElement("br",null))),"Or build your own conditionals using these macros which just return the values:",e.createElement("ul",{className:t.ulPadding},e.createElement("li",null,"$__timeFrom() -> '2017-04-21T05:01:17Z'"),e.createElement("li",null,"$__timeTo() -> '2017-04-21T05:01:17Z'"),e.createElement("li",null,"$__unixEpochFrom() -> 1492750877"),e.createElement("li",null,"$__unixEpochTo() -> 1492750877"),e.createElement("li",null,"$__unixEpochNanoFrom() -> 1494410783152415214"),e.createElement("li",null,"$__unixEpochNanoTo() -> 1494497183142514872")))}function R(t){return{ulPadding:(0,I.css)({margin:t.spacing(1,0),paddingLeft:t.spacing(5)})}}var g=s(22391),O=s(68704),C=s(91062),P=s(29020),M=s(42418),x=s(50720),L=s(25994),u=s(88575),p=s(10354),b=s(88323),B=s(15292),Q=s(9226),U=s(84167),G=s(17081),W=s(60188),N=s(2913),j=s(94701),A=s(32264),y=(t=>(t.sqlAuth="SQL Server Authentication",t.windowsAuth="Windows Authentication",t.azureAuth="Azure AD Authentication",t))(y||{}),S=(t=>(t.disable="disable",t.false="false",t.true="true",t))(S||{}),T=(t=>(t.Public="AzureCloud",t.None="",t))(T||{}),d=(t=>(t.MSI="msi",t.CLIENT_SECRET="clientsecret",t))(d||{}),J=(t=>(t.Public="AzureCloud",t.None="",t))(J||{});const H=[{value:"AzureCloud",label:"Azure"}];function Xe(t){switch(t.authType){case AzureAuthType.MSI:return!0;case AzureAuthType.CLIENT_SECRET:return!!(t.azureCloud&&t.tenantId&&t.clientId&&t.clientSecret)}}const V=(t,n)=>t?{authType:d.MSI}:{authType:d.CLIENT_SECRET,azureCloud:n},Y=(t,n)=>{const a=Symbol("Concealed client secret");return t?a:typeof n=="string"&&n.length>0?n:void 0},q=(t,n)=>{const a=t.jsonData?.azureCredentials,l=t.secureJsonFields?.azureClientSecret,r=t.secureJsonData?.azureClientSecret,i=!!n.azure?.managedIdentityEnabled,c=n.azure?.cloud||T.Public;if(!a)return V(i,c);switch(a.authType){case d.MSI:return i?{authType:d.MSI}:{authType:d.CLIENT_SECRET,azureCloud:c};case d.CLIENT_SECRET:return{authType:d.CLIENT_SECRET,azureCloud:a.azureCloud||c,tenantId:a.tenantId,clientId:a.clientId,clientSecret:Y(l,r)}}},Z=(t,n,a)=>{const l=!!n.azure?.managedIdentityEnabled,r=n.azure?.cloud||T.Public;switch(a.authType){case d.MSI:if(!l)throw new Error("Managed Identity authentication is not enabled in Grafana config.");return t={...t,jsonData:{...t.jsonData,azureCredentials:{authType:d.MSI}}},t;case d.CLIENT_SECRET:return t={...t,jsonData:{...t.jsonData,azureCredentials:{authType:d.CLIENT_SECRET,azureCloud:a.azureCloud||r,tenantId:a.tenantId,clientId:a.clientId}},secureJsonData:{...t.secureJsonData,azureClientSecret:typeof a.clientSecret=="string"&&a.clientSecret.length>0?a.clientSecret:void 0},secureJsonFields:{...t.secureJsonFields,azureClientSecret:typeof a.clientSecret=="symbol"}},t}};var K=s(55852);const F=[{value:d.MSI,label:"Managed Identity"},{value:d.CLIENT_SECRET,label:"App Registration"}],k=t=>{const{managedIdentityEnabled:n,credentials:a,azureCloudOptions:l,onCredentialsChange:r,disabled:i}=t,c=o=>{if(r){const m={...a,authType:o.value||d.MSI};r(m)}},h=({property:o,value:m})=>{if(r&&a.authType==="clientsecret"){const X={...a,[o]:m};r(X)}};return e.createElement("div",null,n&&e.createElement(u.D,{label:"Authentication",description:"Choose the type of authentication to Azure services",htmlFor:"authentication-type"},e.createElement(b.l6,{width:20,value:F.find(o=>o.value===a.authType),options:F,onChange:c,disabled:i})),a.authType==="clientsecret"&&e.createElement(e.Fragment,null,l&&e.createElement(u.D,{label:"Azure Cloud",htmlFor:"azure-cloud-type",disabled:i},e.createElement(b.l6,{value:l.find(o=>o.value===a.azureCloud),options:l,onChange:o=>{const m=o.value||"";h({property:"azureCloud",value:m})},isDisabled:i,inputId:"azure-cloud-type","aria-label":"Azure Cloud",width:20})),e.createElement(u.D,{label:"Directory (tenant) ID",required:!0,htmlFor:"tenant-id",invalid:!a.tenantId,error:"Tenant ID is required"},e.createElement(p.p,{width:45,placeholder:"XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",value:a.tenantId||"",onChange:o=>{const m=o.target.value;h({property:"tenantId",value:m})},disabled:i,"aria-label":"Tenant ID"})),e.createElement(u.D,{label:"Application (client) ID",required:!0,htmlFor:"client-id",invalid:!a.clientId,error:"Client ID is required"},e.createElement(p.p,{width:45,placeholder:"XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",value:a.clientId||"",onChange:o=>{const m=o.target.value;h({property:"clientId",value:m})},disabled:i,"aria-label":"Client ID"})),!i&&(typeof a.clientSecret=="symbol"?e.createElement(u.D,{label:"Client Secret",htmlFor:"client-secret",required:!0},e.createElement("div",{className:"width-30",style:{display:"flex",gap:"4px"}},e.createElement(p.p,{"aria-label":"Client Secret",placeholder:"configured",disabled:!0,"data-testid":"client-secret",width:45}),e.createElement(K.$n,{variant:"secondary",type:"button",onClick:()=>{h({property:"clientSecret",value:""})},disabled:i},"Reset"))):e.createElement(u.D,{label:"Client Secret",required:!0,htmlFor:"client-secret",invalid:!a.clientSecret,error:"Client secret is required"},e.createElement(p.p,{width:45,"aria-label":"Client Secret",placeholder:"XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",value:a.clientSecret||"",onChange:o=>{const m=o.target.value;h({property:"clientSecret",value:m})},id:"client-secret",disabled:i})))))},De=null,_=t=>{const{dataSourceConfig:n,onChange:a}=t,l=A.$.azure.managedIdentityEnabled,r=(0,e.useMemo)(()=>q(n,A.$),[n]),i=c=>{a(Z(n,A.$,c))};return(0,j.A)(()=>{n.jsonData.authType||i(r)}),e.createElement(k,{managedIdentityEnabled:l,credentials:r,azureCloudOptions:H,onCredentialsChange:i,disabled:n.readOnly})},Ie=null,f=40,ee=t=>{(0,v.nj)(t);const{options:n,onOptionsChange:a}=t,l=(0,w.of)(te),r=n.jsonData,i=N.$W.azureAuthEnabled,c={azureAuthIsSupported:i,azureAuthSettingsUI:_},h=()=>{(0,g.QP)(t,"password")},o=E=>Ae=>{a({...n,[E]:Ae.currentTarget.value})},m=E=>{(0,g.lO)(t,"tlsSkipVerify",E.currentTarget.checked)},X=E=>{(0,g.lO)(t,"encrypt",E.value)},ye=E=>{a({...n,jsonData:{...r,authenticationType:E.value,azureCredentials:void 0},secureJsonData:{...n.secureJsonData,password:""},secureJsonFields:{...n.secureJsonFields,password:!1},user:""})},Se=E=>{(0,g.lO)(t,"connectionTimeout",E??0)},Ce=()=>{const E=[{value:y.sqlAuth,label:"SQL Server Authentication"},{value:y.windowsAuth,label:"Windows Authentication"}];return i?[...E,{value:y.azureAuth,label:"Azure AD Authentication"}]:E},be=[{value:S.disable,label:"disable"},{value:S.false,label:"false"},{value:S.true,label:"true"}];return e.createElement(e.Fragment,null,e.createElement(O.I,{dataSourceName:"Microsoft SQL Server",docsLink:"https://grafana.com/docs/grafana/latest/datasources/mssql/",hasRequiredFields:!0}),e.createElement(M.F,{title:"User Permission",severity:"info"},"The database user should only be granted SELECT permissions on the specified database and tables you want to query. Grafana does not validate that queries are safe so queries can contain any SQL statement. For example, statements like ",e.createElement("code",null,"USE otherdb;")," and ",e.createElement("code",null,"DROP TABLE user;")," would be executed. To protect against this we ",e.createElement("em",null,"highly")," recommend you create a specific MS SQL user with restricted permissions. Check out the"," ",e.createElement(x.N,{rel:"noreferrer",target:"_blank",href:"http://docs.grafana.org/features/datasources/mssql/"},"Microsoft SQL Server Data Source Docs")," ","for more information."),e.createElement(L.c,null),e.createElement(C.A,{title:"Connection"},e.createElement(u.D,{label:"Host",required:!0,invalid:!n.url,error:"Host is required"},e.createElement(p.p,{width:f,name:"host",type:"text",value:n.url||"",placeholder:"localhost:1433",onChange:o("url")})),e.createElement(u.D,{label:"Database",required:!0,invalid:!r.database,error:"Database is required"},e.createElement(p.p,{width:f,name:"database",value:r.database||"",placeholder:"database name",onChange:(0,g.PG)(t,"database")}))),e.createElement(C.A,{title:"TLS/SSL Auth"},e.createElement(u.D,{htmlFor:"encrypt",description:e.createElement(e.Fragment,null,"Determines whether or to which extent a secure SSL TCP/IP connection will be negotiated with the server.",e.createElement("ul",{className:l.ulPadding},e.createElement("li",null,e.createElement("i",null,"disable")," - Data sent between client and server is not encrypted."),e.createElement("li",null,e.createElement("i",null,"false")," - Data sent between client and server is not encrypted beyond the login packet. (default)"),e.createElement("li",null,e.createElement("i",null,"true")," - Data sent between client and server is encrypted.")),"If you're using an older version of Microsoft SQL Server like 2008 and 2008R2 you may need to disable encryption to be able to connect."),label:"Encrypt"},e.createElement(b.l6,{options:be,value:r.encrypt||S.false,inputId:"encrypt",onChange:X,width:f})),r.encrypt===S.true?e.createElement(e.Fragment,null,e.createElement(u.D,{htmlFor:"skipTlsVerify",label:"Skip TLS Verify"},e.createElement(B.d,{id:"skipTlsVerify",onChange:m,value:r.tlsSkipVerify||!1})),r.tlsSkipVerify?null:e.createElement(e.Fragment,null,e.createElement(u.D,{description:e.createElement("span",null,"Path to file containing the public key certificate of the CA that signed the SQL Server certificate. Needed when the server certificate is self signed."),label:"TLS/SSL Root Certificate"},e.createElement(p.p,{value:r.sslRootCertFile||"",onChange:(0,g.PG)(t,"sslRootCertFile"),placeholder:"TLS/SSL root certificate file path",width:f})),e.createElement(u.D,{label:"Hostname in server certificate"},e.createElement(p.p,{placeholder:"Common Name (CN) in server certificate",value:r.serverName||"",onChange:(0,g.PG)(t,"serverName"),width:f})))):null),e.createElement(C.A,{title:"Authentication"},e.createElement(u.D,{label:"Authentication Type",htmlFor:"authenticationType",description:e.createElement("ul",{className:l.ulPadding},e.createElement("li",null,e.createElement("i",null,"SQL Server Authentication")," This is the default mechanism to connect to MS SQL Server. Enter the SQL Server Authentication login or the Windows Authentication login in the DOMAIN\\User format."),e.createElement("li",null,e.createElement("i",null,"Windows Authentication")," Windows Integrated Security - single sign on for users who are already logged onto Windows and have enabled this option for MS SQL Server."),i&&e.createElement("li",null,e.createElement("i",null,"Azure Authentication")," Securely authenticate and access Azure resources and applications using Azure AD credentials - Managed Service Identity and Client Secret Credentials are supported."))},e.createElement(b.l6,{value:r.authenticationType||y.sqlAuth,inputId:"authenticationType",options:Ce(),onChange:ye,width:f})),(r.authenticationType===y.sqlAuth||!r.authenticationType)&&e.createElement(e.Fragment,null,e.createElement(u.D,{label:"Username",required:!0,invalid:!n.user,error:"Username is required"},e.createElement(p.p,{value:n.user||"",placeholder:"user",onChange:o("user"),width:f})),e.createElement(u.D,{label:"Password",required:!0,invalid:!n.secureJsonFields.password&&!n.secureJsonData?.password,error:"Password is required"},e.createElement(Q.L4,{width:f,placeholder:"Password",isConfigured:n.secureJsonFields&&n.secureJsonFields.password,onReset:h,onChange:(0,g.mn)(t,"password"),required:!0}))),i&&r.authenticationType===y.azureAuth&&e.createElement(U.n,{label:"Azure Authentication Settings"},e.createElement(c.azureAuthSettingsUI,{dataSourceConfig:n,onChange:a}))),e.createElement(L.c,null),e.createElement(C.A,{title:"Additional settings",description:"Additional settings are optional settings that can be configured for more control over your data source. This includes connection limits, connection timeout, group-by time interval, and Secure Socks Proxy.",isCollapsible:!0,isInitiallyOpen:!0},e.createElement(v.jN,{options:n,onOptionsChange:a}),e.createElement(P.I,{title:"Connection details"},e.createElement(u.D,{description:e.createElement("span",null,"A lower limit for the auto group by time interval. Recommended to be set to write frequency, for example",e.createElement("code",null,"1m")," if your data is written every minute."),label:"Min time interval"},e.createElement(p.p,{width:f,placeholder:"1m",value:r.timeInterval||"",onChange:(0,g.PG)(t,"timeInterval")})),e.createElement(u.D,{description:e.createElement("span",null,"The number of seconds to wait before canceling the request when connecting to the database. The default is ",e.createElement("code",null,"0"),", meaning no timeout."),label:"Connection timeout"},e.createElement(W.Q,{width:f,placeholder:"60",min:0,value:r.connectionTimeout,onChange:Se}))),N.$W.secureSocksDSProxyEnabled&&e.createElement(G.Y,{options:n,onOptionsChange:a})))};function te(t){return{ulPadding:(0,I.css)({margin:t.spacing(1,0),paddingLeft:t.spacing(5)})}}function ne(){return"SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');"}function ae(t){return`SELECT TABLE_SCHEMA + '.' + TABLE_NAME as schemaAndName
    FROM [${t}].INFORMATION_SCHEMA.TABLES`}function le(t,n){return`
   USE ${t}
   SELECT COLUMN_NAME as 'column',DATA_TYPE as 'type'
   FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='${n}';`}class re{constructor(n,a,l){this.target=(0,v.To)(n||{refId:"A"}),this.templateSrv=a,this.scopedVars=l}quoteLiteral(n){return"'"+n.replace(/'/g,"''")+"'"}}var ie=s(33125),se=s(98765);const oe=({getColumns:t,getTables:n})=>(a,l)=>({...l&&(0,ie.N)(a,l),tables:{resolve:async r=>await n.current(r?.table),parseName:r=>{if(!r)return{table:""};let i=r,c=i.value;for(;i.next&&i.next.type!==se.ks.Whitespace;)c+=i.next.value,i=i.next;return i.value.endsWith(".")&&(c=i.value.slice(0,i.value.length-1)),{table:c}}},columns:{resolve:async r=>{if(!r?.table)return[];const[i,c,h]=r.table.split(".");return await t.current({table:`${c}.${h}`,dataset:i,refId:"A"})}}});async function ce(t,n){const a=await t.fields(n);return a.length>0?a.map(l=>({name:l.value,type:l.value,description:l.value})):[]}async function ue(t,n){return await t.lookup?.(n)||[]}var de=s(2543);function me(t){switch(t){case"datetimeoffset":case"date":case"datetime2":case"smalldatetime":case"datetime":case"time":return"clock-nine";case"bit":return"toggle-off";case"tinyint":case"smallint":case"int":case"bigint":case"decimal":case"numeric":case"real":case"float":case"money":case"smallmoney":return"calculator-alt";case"char":case"varchar":case"text":case"nchar":case"nvarchar":case"ntext":case"binary":case"varbinary":case"image":return"text";default:return}}function he(t){switch(t){case"datetimeoffset":case"datetime2":case"smalldatetime":case"datetime":return"datetime";case"time":return"time";case"date":return"date";case"bit":return"boolean";case"tinyint":case"smallint":case"int":case"bigint":case"decimal":case"numeric":case"real":case"float":case"money":case"smallmoney":return"number";case"char":case"varchar":case"text":case"nchar":case"nvarchar":case"ntext":case"binary":case"varbinary":case"image":return"text";default:return"text"}}function Ee({sql:t,dataset:n,table:a}){let l="";if(!t||!(0,v.YW)(t.columns))return l;if(l+=pe(t.columns,t.limit),n&&a&&(l+=`FROM ${n}.${a} `),t.whereString&&(l+=`WHERE ${t.whereString} `),t.groupBy?.[0]?.property.name){const r=t.groupBy.map(i=>i.property.name).filter(i=>!(0,de.isEmpty)(i));l+=`GROUP BY ${r.join(", ")} `}return t.orderBy?.property.name&&(l+=`ORDER BY ${t.orderBy.property.name} `),t.orderBy?.property.name&&t.orderByDirection&&(l+=`${t.orderByDirection} `),l}function pe(t,n){const a=t.map(l=>{let r="";return l.name&&l.alias?r+=`${l.name}(${l.parameters?.map(i=>`${i.name}`)}) AS ${l.alias}`:l.name?r+=`${l.name}(${l.parameters?.map(i=>`${i.name}`)})`:l.alias?r+=`${l.parameters?.map(i=>`${i.name}`)} AS ${l.alias}`:r+=`${l.parameters?.map(i=>`${i.name}`)}`,r});return`SELECT ${fe(n)?"TOP("+n+")":""} ${a.join(", ")} `}const fe=t=>t!==void 0&&t>=0;class ge extends v.oK{constructor(n){super(n),this.sqlLanguageDefinition=void 0}getQueryModel(n,a,l){return new re(n,a,l)}async fetchDatasets(){return(await this.runSql(ne(),{refId:"datasets"})).fields.name?.values.flat()??[]}async fetchTables(n){return(await this.runSql(ae(n),{refId:"tables"})).fields.schemaAndName?.values.flat()??[]}async fetchFields(n){if(!n.table)return[];const[a,l]=n.table.split("."),r=await this.runSql(le(n.dataset,l),{refId:"columns"}),i=[];for(let c=0;c<r.length;c++){const h=r.fields.column.values[c],o=r.fields.type.values[c];i.push({label:h,value:h,type:o,icon:me(o),raqbFieldType:he(o)})}return i}getSqlLanguageDefinition(n){if(this.sqlLanguageDefinition!==void 0)return this.sqlLanguageDefinition;const a={getColumns:{current:l=>ce(n,l)},getTables:{current:l=>ue(n,l)}};return this.sqlLanguageDefinition={id:"sql",completionProvider:oe(a),formatter:v.se},this.sqlLanguageDefinition}getDB(){return this.db!==void 0?this.db:{init:()=>Promise.resolve(!0),datasets:()=>this.fetchDatasets(),tables:n=>this.fetchTables(n),getEditorLanguageDefinition:()=>this.getSqlLanguageDefinition(this.db),fields:async n=>!n?.dataset||!n?.table?[]:this.fetchFields(n),validateQuery:n=>Promise.resolve({isError:!1,isValid:!0,query:n,error:"",rawSql:n.rawSql}),dsID:()=>this.id,dispose:n=>{},toRawSql:Ee,lookup:async n=>{if(n){const a=n.split(".").filter(l=>l);return a.length>2?[]:a.length===1?(await this.fetchTables(a[0])).map(r=>({name:r,completion:r})):[]}else return(await this.fetchDatasets()).map(l=>({name:l,completion:`${l}.`}))}}}}const ve=new z.tD(ge).setQueryEditor(v.e_).setQueryEditorHelp($).setConfigEditor(ee)}}]);

//# sourceMappingURL=mssqlPlugin.88775f59ced74c2283c5.js.map