1#@!#!123s

: / usr / share / grafana / public / build /
Filename : mssqlPlugin.621e49efd40661f1f794.js
back
(self.webpackChunkgrafana=self.webpackChunkgrafana||[]).push([[5032],{52709:(fe,y,r)=>{"use strict";r.r(y),r.d(y,{plugin:()=>oe});var D=r(68246),w=r(25789),S=r(42844),e=r(27702),T=r(66310);function $(){const t=(0,T.wW)(F);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 F(t){return{ulPadding:(0,S.css)({margin:t.spacing(1,0),paddingLeft:t.spacing(5)})}}var u=r(17312),b=r(5229),c=r(48024),h=r(1128),C=r(28580),N=r(71107),I=r(43656),R=r(79510),x=r(42074),M=r(63439),P=r(9848),W=r(55093),O=r(12308),B=r(77553),Q=r(10467),g=(t=>(t.sqlAuth="SQL Server Authentication",t.windowsAuth="Windows Authentication",t))(g||{}),f=(t=>(t.disable="disable",t.false="false",t.true="true",t))(f||{});const U=t=>{const{options:a,onOptionsChange:i}=t,n=(0,T.wW)(G),l=a.jsonData;(0,Q.D)(t);const s=()=>{(0,u.Mf)(t,"password")},o=m=>he=>{i({...a,[m]:he.currentTarget.value})},E=m=>{(0,u.tp)(t,"tlsSkipVerify",m.currentTarget.checked)},v=m=>{(0,u.tp)(t,"encrypt",m.value)},ce=m=>{i({...a,jsonData:{...l,authenticationType:m.value},secureJsonData:{...a.secureJsonData,password:""},secureJsonFields:{...a.secureJsonFields,password:!1},user:""})},ue=m=>{(0,u.tp)(t,"connectionTimeout",m??0)},me=[{value:g.sqlAuth,label:"SQL Server Authentication"},{value:g.windowsAuth,label:"Windows Authentication"}],de=[{value:f.disable,label:"disable"},{value:f.false,label:"false"},{value:f.true,label:"true"}],d=15,A=46,p=25,L=20;return e.createElement(e.Fragment,null,e.createElement(b.C,{label:"MS SQL Connection",width:400},e.createElement(c._,{labelWidth:d,label:"Host"},e.createElement(h.I,{width:A,name:"host",type:"text",value:a.url||"",placeholder:"localhost:1433",onChange:o("url")})),e.createElement(c._,{labelWidth:d,label:"Database"},e.createElement(h.I,{width:A,name:"database",value:l.database||"",placeholder:"database name",onChange:(0,u._R)(t,"database")})),e.createElement(c._,{label:"Authentication",labelWidth:d,htmlFor:"authenticationType",tooltip:e.createElement("ul",{className:n.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."))},e.createElement(C.Ph,{value:l.authenticationType||g.sqlAuth,inputId:"authenticationType",options:me,onChange:ce})),l.authenticationType===g.windowsAuth?null:e.createElement(N.Z,null,e.createElement(c._,{labelWidth:d,label:"User"},e.createElement(h.I,{width:d,value:a.user||"",placeholder:"user",onChange:o("user")})),e.createElement(c._,{label:"Password",labelWidth:d},e.createElement(I.m4,{width:d,placeholder:"Password",isConfigured:a.secureJsonFields&&a.secureJsonFields.password,onReset:s,onBlur:(0,u.fi)(t,"password")})))),O.config.secureSocksDSProxyEnabled&&e.createElement(R.i,{options:a,onOptionsChange:i}),e.createElement(b.C,{label:"TLS/SSL Auth"},e.createElement(c._,{labelWidth:p,htmlFor:"encrypt",tooltip: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:n.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(C.Ph,{options:de,value:l.encrypt||f.false,inputId:"encrypt",onChange:v})),l.encrypt===f.true?e.createElement(e.Fragment,null,e.createElement(c._,{labelWidth:p,htmlFor:"skipTlsVerify",label:"Skip TLS Verify"},e.createElement(x.x,{id:"skipTlsVerify",onChange:E,value:l.tlsSkipVerify||!1})),l.tlsSkipVerify?null:e.createElement(e.Fragment,null,e.createElement(c._,{labelWidth:p,tooltip: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(h.I,{value:l.sslRootCertFile||"",onChange:(0,u._R)(t,"sslRootCertFile"),placeholder:"TLS/SSL root certificate file path"})),e.createElement(c._,{labelWidth:p,label:"Hostname in server certificate"},e.createElement(h.I,{placeholder:"Common Name (CN) in server certificate",value:l.serverName||"",onChange:(0,u._R)(t,"serverName")})))):null),e.createElement(B.K,{labelWidth:d,options:a,onOptionsChange:i}),e.createElement(b.C,{label:"MS SQL details"},e.createElement(c._,{tooltip: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",labelWidth:L},e.createElement(h.I,{placeholder:"1m",value:l.timeInterval||"",onChange:(0,u._R)(t,"timeInterval")})),e.createElement(c._,{tooltip: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",labelWidth:L},e.createElement(W.Y,{placeholder:"60",min:0,value:l.connectionTimeout,onChange:ue}))),e.createElement(M.b,{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(P.r,{rel:"noreferrer",target:"_blank",href:"http://docs.grafana.org/features/datasources/mssql/"},"Microsoft SQL Server Data Source Docs")," ","for more information."))};function G(t){return{ulPadding:(0,S.css)({margin:t.spacing(1,0),paddingLeft:t.spacing(5)})}}var H=r(53085),V=r(1814);function Y(){return"SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');"}function j(t){return`SELECT TABLE_SCHEMA + '.' + TABLE_NAME as schemaAndName
    FROM [${t}].INFORMATION_SCHEMA.TABLES`}function J(t,a){return`
   USE ${t}
   SELECT COLUMN_NAME as 'column',DATA_TYPE as 'type'
   FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='${a}';`}var Z=r(97997);class K{constructor(a,i,n){this.target=(0,Z.Y)(a||{refId:"A"}),this.templateSrv=i,this.scopedVars=n}quoteLiteral(a){return"'"+a.replace(/'/g,"''")+"'"}}var z=r(60429),X=r(6411);const k=({getColumns:t,getTables:a})=>(i,n)=>({...n&&(0,z.h)(i,n),tables:{resolve:async l=>await a.current(l?.table),parseName:l=>{if(!l)return{table:""};let s=l,o=s.value;for(;s.next&&s.next.type!==X.iv.Whitespace;)o+=s.next.value,s=s.next;return s.value.endsWith(".")&&(o=s.value.slice(0,s.value.length-1)),{table:o}}},columns:{resolve:async l=>{if(!l?.table)return[];const[s,o,E]=l.table.split(".");return await t.current({table:`${o}.${E}`,dataset:s,refId:"A"})}}});async function q(t,a){const i=await t.fields(a);return i.length>0?i.map(n=>({name:n.value,type:n.value,description:n.value})):[]}async function _(t,a){return await t.lookup?.(a)||[]}var ee=r(89949),te=r(60244);function ae(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 ne(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 le({sql:t,dataset:a,table:i}){let n="";if(!t||!(0,te.IC)(t.columns))return n;if(n+=re(t.columns,t.limit),a&&i&&(n+=`FROM ${a}.${i} `),t.whereString&&(n+=`WHERE ${t.whereString} `),t.groupBy?.[0]?.property.name){const l=t.groupBy.map(s=>s.property.name).filter(s=>!(0,ee.isEmpty)(s));n+=`GROUP BY ${l.join(", ")} `}return t.orderBy?.property.name&&(n+=`ORDER BY ${t.orderBy.property.name} `),t.orderBy?.property.name&&t.orderByDirection&&(n+=`${t.orderByDirection} `),n}function re(t,a){const i=t.map(n=>{let l="";return n.name&&n.alias?l+=`${n.name}(${n.parameters?.map(s=>`${s.name}`)}) AS ${n.alias}`:n.name?l+=`${n.name}(${n.parameters?.map(s=>`${s.name}`)})`:n.alias?l+=`${n.parameters?.map(s=>`${s.name}`)} AS ${n.alias}`:l+=`${n.parameters?.map(s=>`${s.name}`)}`,l});return`SELECT ${ie(a)?"TOP("+a+")":""} ${i.join(", ")} `}const ie=t=>t!==void 0&&t>=0;class se extends H.D{constructor(a){super(a),this.sqlLanguageDefinition=void 0}getQueryModel(a,i,n){return new K(a,i,n)}async fetchDatasets(){return(await this.runSql(Y(),{refId:"datasets"})).fields.name?.values.flat()??[]}async fetchTables(a){return(await this.runSql(j(a),{refId:"tables"})).fields.schemaAndName?.values.flat()??[]}async fetchFields(a){if(!a.table)return[];const[i,n]=a.table.split("."),l=await this.runSql(J(a.dataset,n),{refId:"columns"}),s=[];for(let o=0;o<l.length;o++){const E=l.fields.column.values[o],v=l.fields.type.values[o];s.push({label:E,value:E,type:v,icon:ae(v),raqbFieldType:ne(v)})}return s}getSqlLanguageDefinition(a){if(this.sqlLanguageDefinition!==void 0)return this.sqlLanguageDefinition;const i={getColumns:{current:n=>q(a,n)},getTables:{current:n=>_(a,n)}};return this.sqlLanguageDefinition={id:"sql",completionProvider:k(i),formatter:V._},this.sqlLanguageDefinition}getDB(){return this.db!==void 0?this.db:{init:()=>Promise.resolve(!0),datasets:()=>this.fetchDatasets(),tables:a=>this.fetchTables(a),getEditorLanguageDefinition:()=>this.getSqlLanguageDefinition(this.db),fields:async a=>!a?.dataset||!a?.table?[]:this.fetchFields(a),validateQuery:a=>Promise.resolve({isError:!1,isValid:!0,query:a,error:"",rawSql:a.rawSql}),dsID:()=>this.id,dispose:a=>{},toRawSql:le,lookup:async a=>{if(a){const i=a.split(".").filter(n=>n);return i.length>2?[]:i.length===1?(await this.fetchTables(i[0])).map(l=>({name:l,completion:l})):[]}else return(await this.fetchDatasets()).map(n=>({name:n,completion:`${n}.`}))}}}}const oe=new D.hf(se).setQueryEditor(w.M).setQueryEditorHelp($).setConfigEditor(U)},89818:()=>{},45399:()=>{}}]);

//# sourceMappingURL=mssqlPlugin.621e49efd40661f1f794.js.map