def _repr_html_(self):
self.html_body = []
self.html_head = []
if self.display_info:
Display.showInfoMessage(self.conn_info)
# msg_html = Display.getInfoMessageHtml(self.conn_info)
# self.html_body.append(msg_html.get("body", ""))
# self.html_head.append(msg_html.get("head", ""))
if not self.suppress_result:
if self.is_chart():
self.show_chart(**self.options)
# char_html = self._getChartHtml()
# self.html_body.append(char_html.get("body", ""))
# self.html_head.append(char_html.get("head", ""))
else:
self.show_table(**self.options)
# table_html = self._getTableHtml()
# self.html_body.append(table_html.get("body", ""))
# self.html_head.append(table_html.get("head", ""))
if self.display_info:
Display.showInfoMessage(self.info)
# msg_html = Display.getInfoMessageHtml(self.info)
# b = msg_html.get("body", "")
# if not self.suppress_result and len(b) > 0 and not self.is_chart():
# b = "<br>" + b
# self.html_body.append(b)
# self.html_head.append(msg_html.get("head", ""))
self.display_info = False
self.suppress_result = False
# if len(self.html_body) > 0:
# html_body_str = ''.join(self.html_body)
# html_head_str = ''.join(self.html_head) if len(self.html_head) > 0 else ''
# Display.show(Display.toHtml(body = html_body_str, head = html_head_str))
return ''
def show_table(self, **kwargs):
"display the table"
options = {**self.options, **kwargs}
if options.get("table_package", "").upper() == "PANDAS":
t = self.to_dataframe()._repr_html_()
html = Display.toHtml(body=t)
else:
t = self._getTableHtml()
html = Display.toHtml(**t)
if options.get("popup_window") and not options.get("botton_text"):
options["botton_text"] = 'popup ' + 'table' + (
(' - ' + self.title) if self.title else '') + ' '
Display.show(html, **options)
return None
def show_chart(self, **kwargs):
"display the chart that was specified in the query"
options = {**self.options, **kwargs}
window_mode = options is not None and options.get("popup_window")
if window_mode and not options.get("botton_text"):
options["botton_text"] = 'popup ' + self.visualization + (
(' - ' + self.title) if self.title else '') + ' '
c = self._getChartHtml(window_mode)
if c is not None:
html = Display.toHtml(**c)
Display.show(html, **options)
return None
else:
return self.show_table(**kwargs)
def _repr_html_(self):
if not self.suppress_result:
if self.display_info:
Display.showInfoMessage(self.metadata.get("conn_info"))
if self.is_chart():
self.show_chart(**self.options)
else:
self.show_table(**self.options)
if self.display_info:
Display.showInfoMessage(self.feedback_info)
# display info only once
self.display_info = False
# suppress results info only once
self.suppress_result = False
return ""
def show_chart(self, **kwargs):
"display the chart that was specified in the query"
options = {**self.options, **kwargs}
window_mode = options is not None and options.get("popup_window")
if window_mode and not options.get("button_text"):
options["button_text"] = "popup " + self.visualization + (
(" - " + self.title) if self.title else "") + " "
c = self._getChartHtml(window_mode)
if c.get("body") or c.get("head"):
html = Display.toHtml(**c)
Display.show(html, **options)
elif c.get("fig"):
if Display.notebooks_host or options.get(
"notebook_app") == "jupyterlab":
plotly.offline.init_notebook_mode(connected=True)
plotly.offline.iplot(c.get("fig"), filename="plotlychart")
else:
Display.show(c.get("fig"), **options)
else:
return self.show_table(**kwargs)
def popup_schema(conn):
if isinstance(conn, KustoEngine) or isinstance(conn,
AppinsightsEngine):
database_name = conn.get_database()
conn_name = conn.get_name()
if isinstance(conn, KustoEngine):
query = '.show schema'
raw_table = conn.execute(query)
database_metadata_tree = Database_html._create_database_metadata_tree(
raw_table.fetchall(), database_name)
elif isinstance(conn, AppinsightsEngine):
database_metadata_tree = {}
for table_name in Database_html.application_insights_tables:
query = table_name + " | getschema"
try:
raw_table = conn.execute(query)
rows = raw_table.fetchall()
if (raw_table.returns_rows()):
database_metadata_tree[table_name] = {}
for row in rows:
column_name = row['ColumnName']
column_type = row['ColumnType']
if column_name and len(
column_name
) > 0 and column_type and len(column_type) > 0:
database_metadata_tree.get(
table_name)[column_name] = column_type
except:
pass
html_str = Database_html.convert_database_metadata_to_html(
database_metadata_tree, conn_name)
window_name = conn_name.replace('@', '_at_') + '_schema'
file_path = Display._html_to_file_path(html_str, window_name)
botton_text = 'popup schema ' + conn_name
Help_html.add_menu_item(conn_name, file_path)
Display.show_window(window_name, file_path, botton_text)
else:
return None
def _show_connection_info(self, **options):
msg = self._get_connection_info(**options)
if len(msg) > 0:
Display.showInfoMessage(msg)
def execute_query(self, parsed, user_ns, result_set=None):
if Help_html.showfiles_base_url is None:
window_location = user_ns.get("NOTEBOOK_URL")
if window_location is not None:
Help_html.flush(window_location,
notebook_app=self.notebook_app)
else:
self.submit_get_notebook_url()
query = parsed["kql"].strip()
options = parsed["options"]
suppress_results = options.get(
"suppress_results", False) and options.get(
"enable_suppress_result", self.enable_suppress_result)
connection_string = parsed["connection"]
special_info = False
if options.get("version"):
print("Kqlmagic version: " + VERSION)
special_info = True
if options.get("palette"):
palette = Palette(
palette_name=options.get("palette_name", self.palette_name),
n_colors=options.get("palette_colors", self.palette_colors),
desaturation=options.get("palette_desaturation",
self.palette_desaturation),
to_reverse=options.get("palette_reverse", False),
)
html_str = palette._repr_html_()
Display.show_html(html_str)
special_info = True
if options.get("popup_palettes"):
n_colors = options.get("palette_colors", self.palette_colors)
desaturation = options.get("palette_desaturation",
self.palette_desaturation)
palettes = Palettes(n_colors=n_colors, desaturation=desaturation)
html_str = palettes._repr_html_()
button_text = "popup {0} colors palettes".format(n_colors)
file_name = "{0}_colors_palettes".format(n_colors)
if desaturation is not None and desaturation != 1.0 and desaturation != 0:
file_name += "_desaturation{0}".format(str(desaturation))
button_text += " (desaturation {0})".format(str(desaturation))
file_path = Display._html_to_file_path(html_str, file_name,
**options)
Display.show_window(file_name,
file_path,
button_text=button_text,
onclick_visibility="visible")
special_info = True
if options.get("popup_help"):
help_url = "http://aka.ms/kdocs"
# 'https://docs.loganalytics.io/docs/Language-Reference/Tabular-operators'
# 'http://aka.ms/kdocs'
# 'https://kusdoc2.azurewebsites.net/docs/queryLanguage/query-essentials/readme.html'
# import requests
# f = requests.get(help_url)
# html = f.text.replace('width=device-width','width=500')
# Display.show(html, **{"popup_window" : True, 'name': 'KustoQueryLanguage'})
button_text = "popup kql help "
Display.show_window("KustoQueryLanguage",
help_url,
button_text,
onclick_visibility="visible")
special_info = True
if special_info and not query and not connection_string:
return None
try:
#
# set connection
#
conn = Connection.get_connection(connection_string, **options)
# parse error
except KqlEngineError as e:
if options.get("short_errors", self.short_errors):
msg = Connection.tell_format(connect_str)
Display.showDangerMessage(str(e))
Display.showInfoMessage(msg)
return None
else:
raise
# parse error
except ConnectionError as e:
if options.get("short_errors", self.short_errors):
Display.showDangerMessage(str(e))
self._show_connection_info(show_conn_info="list")
return None
else:
raise
try:
# validate connection
if not conn.options.get(
"validate_connection_string_done") and options.get(
"validate_connection_string",
self.validate_connection_string):
retry_with_code = False
try:
conn.validate(**options)
conn.set_validation_result(True)
except Exception as e:
msg = str(e)
if msg.find("AADSTS50079") > 0 and msg.find(
"multi-factor authentication") > 0 and isinstance(
conn, KustoEngine):
Display.showDangerMessage(str(e))
retry_with_code = True
else:
raise e
if retry_with_code:
Display.showInfoMessage(
"replaced connection with code authentication")
database_name = conn.get_database()
cluster_name = conn.get_cluster()
connection_string = "kusto://code().cluster('" + cluster_name + "').database('" + database_name + "')"
conn = Connection.get_connection(connection_string,
**options)
conn.validate(**options)
conn.set_validation_result(True)
conn.options["validate_connection_string_done"] = True
schema_file_path = None
if options.get("popup_schema") or (
not conn.options.get("auto_popup_schema_done") and
options.get("auto_popup_schema", self.auto_popup_schema)):
schema_file_path = Database_html.get_schema_file_path(
conn, **options)
Database_html.popup_schema(schema_file_path, conn)
conn.options["auto_popup_schema_done"] = True
if not conn.options.get("add_schema_to_help_done") and options.get(
"add_schema_to_help"):
schema_file_path = schema_file_path or Database_html.get_schema_file_path(
conn, **options)
Help_html.add_menu_item(conn.get_conn_name(), schema_file_path,
**options)
conn.options["add_schema_to_help_done"] = True
if not query:
#
# If NO kql query, just return the current connection
#
if not connection_string and Connection.connections and not suppress_results:
self._show_connection_info(**options)
return None
#
# submit query
#
start_time = time.time()
params_dict_name = options.get('params_dict')
dictionary = user_ns.get(
params_dict_name) if params_dict_name is not None and len(
params_dict_name) > 0 else user_ns
parametrized_query = Parameterizer(dictionary).expand(
query) if result_set is None else result_set.parametrized_query
raw_query_result = conn.execute(parametrized_query, user_ns,
**options)
end_time = time.time()
#
# model query results
#
if result_set is None:
fork_table_id = 0
saved_result = ResultSet(raw_query_result,
parametrized_query,
fork_table_id=0,
fork_table_resultSets={},
metadata={},
options=options)
saved_result.metadata["magic"] = self
saved_result.metadata["parsed"] = parsed
saved_result.metadata["connection"] = conn.get_conn_name()
else:
fork_table_id = result_set.fork_table_id
saved_result = result_set.fork_result(0)
saved_result.feedback_info = []
saved_result._update(raw_query_result)
result = saved_result
if not connection_string and Connection.connections:
saved_result.metadata["conn_info"] = self._get_connection_info(
**options)
else:
saved_result.metadata["conn_info"] = []
saved_result.metadata["start_time"] = start_time
saved_result.metadata["end_time"] = end_time
if options.get("feedback", self.feedback):
minutes, seconds = divmod(end_time - start_time, 60)
saved_result.feedback_info.append(
"Done ({:0>2}:{:06.3f}): {} records".format(
int(minutes), seconds, saved_result.records_count))
if options.get("columns_to_local_vars",
self.columns_to_local_vars):
# Instead of returning values, set variables directly in the
# users namespace. Variable names given by column names
if options.get("feedback", self.feedback):
saved_result.feedback_info.append(
"Returning raw data to local variables")
self.shell.user_ns.update(saved_result.to_dict())
result = None
if options.get("auto_dataframe", self.auto_dataframe):
if options.get("feedback", self.feedback):
saved_result.feedback_info.append(
"Returning data converted to pandas dataframe")
result = saved_result.to_dataframe()
if options.get("result_var") and result_set is None:
result_var = options["result_var"]
if options.get("feedback", self.feedback):
saved_result.feedback_info.append(
"Returning data to local variable {}".format(
result_var))
self.shell.user_ns.update({
result_var:
result if result is not None else saved_result
})
result = None
if options.get('cache') and not options.get(
'use_cache') and not isinstance(conn, CacheEngine):
file_path = CacheClient().save(raw_query_result,
conn.get_database(),
conn.get_cluster(),
parametrized_query, **options)
if options.get("feedback", self.feedback):
saved_result.feedback_info.append("query results cached")
if options.get('save_as') is not None:
file_path = CacheClient().save(raw_query_result,
conn.get_database(),
conn.get_cluster(),
parametrized_query,
filepath=options.get('save_as'),
**options)
if options.get("feedback", self.feedback):
saved_result.feedback_info.append(
"query results saved as {0}".format(file_path))
saved_result.suppress_result = False
saved_result.display_info = False
if result is not None:
if suppress_results:
saved_result.suppress_result = True
elif options.get("auto_dataframe", self.auto_dataframe):
Display.showSuccessMessage(saved_result.feedback_info)
else:
saved_result.display_info = True
if result_set is None:
saved_result._create_fork_results()
else:
saved_result._update_fork_results()
# Return results into the default ipython _ variable
self.shell.user_ns.update({
options.get("last_raw_result_var", self.last_raw_result_var):
saved_result
})
if result == saved_result:
result = saved_result.fork_result(fork_table_id)
return result
except Exception as e:
if not connection_string and Connection.connections and not suppress_results:
# display list of all connections
self._show_connection_info(**options)
if options.get("short_errors", self.short_errors):
Display.showDangerMessage(e)
return None
else:
raise e
def __init__(self, shell):
# constants
Configurable.__init__(self, config=shell.config)
Magics.__init__(self, shell=shell)
set_logger(Logger())
get_ipython().magic("matplotlib inline")
# Add ourself to the list of module configurable via %config
self.shell.configurables.append(self)
ip = get_ipython()
kql_magic_load_mode = _get_kql_magic_load_mode()
if kql_magic_load_mode != "silent":
html_str = """<html>
<head>
<style>
.kqlmagic-banner {
display: flex;
background-color: #d9edf7;
}
.kqlmagic-banner > div {
margin: 10px;
padding: 20px;
color: #3a87ad;
font-size: 13px;
}
</style>
</head>
<body>
<div class='kqlmagic-banner'>
<div><img src='data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAH8AAAB9CAIAAAFzEBvZAAAABGdBTUEAALGPC/xhBQAAAAZiS0dEAC8ALABpv+tl0gAAAAlwSFlzAAAOwwAADsMBx2+oZAAAAAd0SU1FB+AHBRQ2KY/vn7UAAAk5SURBVHja7V3bbxxXGT/fuc9tdz22MW7t5KFxyANRrUQ8IPFQqQihSLxERBQhVUU0qDZ1xKVJmiCBuTcpVdMkbUFFRQIJRYrUB4r6CHIRpU1DaQl/AH9BFYsGbO/MOTxMPGz2MjuzO7M7sz7f0+zszJzv+32X8507PPjJFZSFMMpI3V945sLX3vzLxa5/0fjq/VsvpSmBJv/d9pXlw6upZFg+vLp8eLWLDNHd+L+26yAIugi9fHi1qzBaq9u3b3d54f1bL7V+NS4EAM/MzPSEte2dnihFzCTjmw1WhBC02tK16+cOHJinlCYwBmMyvgQaF0u//d3pXtq4i+A7Ny8JwTP4Q9enO50hrQytGsSdjhL/3fpcGIY9he4q7ubmptaqv/HFhfi+D4BTOVCSHob1h65v3mNLf3rzQqPhAsCE+0PhHGWlnmp7/OTnP/u5o4uL05bFMcbpI2mfAlLWWn2fjDmgeUERf7GtYJymDmy9zk0Hbax1AtL1vtZ6c3MzDEOtVeT9NH3sSvMAANi2rbWO/RX31eQfNy5kMhvGGOccIegDUSy773vpTasEjtZshghpxujw9tq9gE8dWev15su/PHVg6eO+XyME76VgV3gBBqIS12iddPnFlcWF2YXFacbY4DVaTM8+9/iRIwccV0gpcpPg7XcvMUYIIUVBJCVP+VrKCrlSVtSr3h6fBGPOKnqlGlrrMAwR0v3r5KwpYkTb29t37txRKsCYZdBB+kpfKRWGoUYaIZ1D6tiZLgohCCEYaAxR5qZjMhFChBBRTpc28RpMGRn8YJisK1VmN2QZe6pGS1ZMnz6U2E2aTcU5ibP74Q33ngKOPPhkfP36G+uzsw3OaWcTMx+IvnBsve3O62+sT0/XLYv3lc9kdqaAirUPKo+QEaCYyiATPfbYw584tH/p4H1fPP7jMgpw5uyX9u/35+b9et1zXS4E1xoBIADIFNQLEeD0mROWLRYXfd+vC4lrNU8IIoSohgkNmc3l/s3xNM5MFCpBFBrGTvqaHB2mgNavZy24XBoomnutdYEC9NLJ8A8jhIIgCIIgDEMA0Foh1F630HIDr7a3t7e2tprNJsZYqQBjghCOuybydOIBuO+M620fAQDGmNaaUgoAABHrkFsYbXPigXtIErJ9zrnjOJ7nua6LMW3tuMmnHujad5ezEAAY417Nc5yL8XCxVbAqCq6Jb9x8dQSqyCeMJjjryCovkwsVGW2zqrHyGujTrXL5yuqd//zXq9kLCzNzc1NSsmFaiUV4dh8TOrXWX6G/eOWUY0vbFpbFbYe7rkMIRPG7Gj7wxMnLPb9Oqdbq8tUnGlPu3NzUGEzINCmNAEaAitcDBn7DveHecG+4H2nb5akzxw8uLTywdP/DD50tO/c/+NGjritcz2o03HrdqdVs2xYlxX7lG8f27ZtfWJyaatS8muW61m6qDxhD6Szn9NkTBw8uzM9POa4QQlCKOacltfuz505M+bX9+2alxW1LeDVHiJznYBbF/V9vPE8IGSO0Q3FvWfl728C9WhM49mi4N9yXN1MYxjWTvdxYTlUsJ2FgdCxD7bgIe63SLIFqTxEYTNSUQiqllFKRDJ397LTMwGutowkOWmuElNbQNjpNy23uemdnZ2dnR2utVIgxadPAOKc29GUdIR2GYRAESqld7KGQiRnFEERzAqLrtikZY+a+n+EBQpoxtuuyGAC3OS4uiJW8kGeMSSmllACkE/6yWw4hJLKczrkwKMf5PKiic2GKFqDAPGcsc0fyxP7G314YF/w5cM85e++DF8ciAB7YTlqvR9BlmU+O2cvQzeQpw73hviel32ZgRO3aTPT2u5cSHH1vTbib3N6oMAyDQAMgQjDG+awly7caTsL+6PLaxsY/NjZu/fPWvz788N9hqKqEPULozHd+1Xbn+mvf9TzL8yzGKCE4UkpJue+kE8d/0vrzytUVr25bknHBbYs7rrRtOZolizlEzLUnX267s/7DR5eWFqZnbCm540hKSXGS5B/v17/3m+iCEAKAlFKvvPpN36/NztbzbzeaeWmGe8O94d5wb7g33BvuJzRTqDphA4FB36BvyKBv0Ddk0N8DRKvI9Je/8pBty5pneTWn5tn+jOO5luNYli0opUJgQsjR5TWD/iD09PlHap5Vb1j1umc73LIoIQQAU4IBY0qjbnhECCEEl2dRTDXQv3jxpO1JwRnnmDEuJHEcKQRjDDPGACAad4pmQ4xxsKN66H995ZjrSMvmluSua9mOaNRd14vWxRHOUbSRlNZ6dwbaJINbFPq//8P3m0GolaaUMC4sybggUjKlVDwvLj7WzFDO6C/u+1glLHf0c6EyDbMPmHGObKy2cpRJ3ybfN60tg74hg77JeQylTmCGzKmM7U+07Xc1n/QHto09f69w3O+FY8L9vQN9uSJPcpCdPOhLVOtW1+SjDQoStikoNfqFmvwAtU4m5MMw1C2EENo9jAHtdoNBedGvcpTX0XmfESmlWtAPo4XQ0ZFLCQqgBvqBoUe7549Eu0REu4xgjLVWCABpBIC11gkKoGWDvlK16/9jTox+dB9pQKBbj8GtQFu3OtDfxZQQ0hJw9G6wx/ceBAPVQL/Xicol7EIAAK0RpRRjHBl+jD7GZBfxPqMguIRmPuLNNAa3fwBCCKWUMcY5F0IIITjnse33HYDC5YwzIz7WZxgFYIzJvdS2PVN527rv3HxhmPhQKjXEVJmeBiHYzb9fSVZAhXRQvX4eSsl7H1y9dv38ZDhBxdCPWiiHDi38+a1n95oCStTH6XlO337/CdNBsfn+AJn7RPYkV8D29yAZ9A36Bn1Dk1brjp2G3Oex6BTA2L6JPAZ9Q7lQpvbggHH/o4+2giDY2moGQaiUphQ4Z4RQIQjnLFpTWIblFSVvGw+I/mc+/e2u93/2zFfvu3/Gn3ZrNZtzChAdo4AJuasPs+ilwJzn3NO/7vvMtevnpaRCMAAkBKOUAGDGCEKodT/MaMVor73pDfoD0iMnfpr8wLeeOu7YTErputL33XqjJgSzbSqliLQCgAEmQTFlzPef//lryQ88d+mkY0vblp5nSYtJyaNF6wCIMRIN9VUC/cnZGYwQjDFBSDWbobH9UVMYqhLuDG3yfYO+IYO+Qd+QQd+gb8igb9A36BsaPf0PJmoM1QL6Q/4AAAAASUVORK5CYII='></div>
<div>
<p>Kusto is a log analytics cloud platform optimized for ad-hoc big data queries. Read more about it here: http://aka.ms/kdocs</p>
<p> • kql language reference: Click on 'Help' tab > and Select 'kql referece'<br>
• Kqlmagic configuarion: Run in cell '%config kqlmagic'<br>
• Kqlmagic syntax: Run in cell '%kql?'<br>
• Kqlmagic upgrate syntax: Run 'pip install git+git://github.com/Microsoft/jupyter-Kqlmagic.git --upgrade'<br>
</div>
</div>
</body>
</html>"""
Display.show_html(html_str)
Display.showInfoMessage(
"""Kqlmagic package is updated frequently. Run pip install Kqlmagic --upgrade to use the latest version.<br>Kqlmagic version: """
+ VERSION +
""", source: https://github.com/Microsoft/jupyter-Kqlmagic""")
# <div><img src='https://az818438.vo.msecnd.net/icons/kusto.png'></div>
_override_default_configuration(ip, kql_magic_load_mode)
root_path = get_ipython().starting_dir.replace("\\", "/")
folder_name = ip.run_line_magic("config", "Kqlmagic.temp_folder_name")
showfiles_folder_Full_name = root_path + "/" + folder_name
if not os.path.exists(showfiles_folder_Full_name):
os.makedirs(showfiles_folder_Full_name)
# ipython will removed folder at shutdown or by restart
ip.tempdirs.append(showfiles_folder_Full_name)
Display.showfiles_base_path = root_path
Display.showfiles_folder_name = folder_name
Display.notebooks_host = Help_html.notebooks_host = os.getenv(
"AZURE_NOTEBOOKS_HOST")
app = ip.run_line_magic("config", "Kqlmagic.notebook_app")
# add help link
add_kql_ref_to_help = ip.run_line_magic(
"config", "Kqlmagic.add_kql_ref_to_help")
if add_kql_ref_to_help:
Help_html.add_menu_item("kql Reference",
"http://aka.ms/kdocs",
notebook_app=app)
if app is None or app != "jupyterlab":
display(
Javascript(
"""IPython.notebook.kernel.execute("NOTEBOOK_URL = '" + window.location + "'");"""
))
time.sleep(5)
def execute(self, line, cell="", local_ns={}):
"""Query Kusto or ApplicationInsights using kusto query language (kql). Repository specified by a connect string.
Magic Syntax::
%%kql <connection-string>
<KQL statement>
# Note: establish connection and query.
%%kql <established-connection-reference>
<KQL statemnt>
# Note: query using an established connection.
%%kql
<KQL statement>
# Note: query using current established connection.
%kql <KQL statment>
# Note: single line query using current established connection.
%kql <connection-string>
# Note: established connection only.
Connection string Syntax::
kusto://username('<username>).password(<password>).cluster(<cluster>).database(<database>')
appinsights://appid(<appid>).appkey(<appkey>)
loganalytics://workspace(<workspaceid>).appkey(<appkey>)
%<connectionStringVariable>%
# Note: connection string is taken from the environment variable.
[<sectionName>]
# Note: connection string is built from the dsn file settings, section <sectionName>.
# The dsn filename value is taken from configuartion value Kqlmagic.dsn_filename.
# Note: if password or appkey component is missing, user will be prompted.
# Note: connection string doesn't have to include all components, see examples below.
# Note: substring of the form $name or ${name} in windows also %name%, are replaced by environment variables if exist.
Examples::
%%kql kusto://username('myName').password('myPassword').cluster('myCluster').database('myDatabase')
<KQL statement>
# Note: establish connection to kusto and submit query.
%%kql myDatabase@myCluster
<KQL statement>
# Note: submit query using using an established kusto connection to myDatabase database at cluster myCluster.
%%kql appinsights://appid('myAppid').appkey('myAppkey')
<KQL statement>
# Note: establish connection to ApplicationInsights and submit query.
%%kql myAppid@appinsights
<KQL statement>
# Note: submit query using established ApplicationInsights connection to myAppid.
%%kql loganalytics://workspace('myWorkspaceid').appkey('myAppkey')
<KQL statement>
# Note: establish connection to LogAnalytics and submit query.
%%kql myWorkspaceid@loganalytics
<KQL statement>
# Note: submit query using established LogAnalytics connection to myWorkspaceid.
%%kql
<KQL statement>
# Note: submit query using current established connection.
%kql <KQL statement>
# Note: submit single line query using current established connection.
%%kql kusto://cluster('myCluster').database('myDatabase')
<KQL statement>
# Note: establish connection to kusto using current username and password to form the full connection string and submit query.
%%kql kusto://database('myDatabase')
<KQL statement>
# Note: establish connection to kusto using current username, password and cluster to form the full connection string and submit query.
%kql kusto://username('myName').password('myPassword').cluster('myCluster')
# Note: set current (default) username, passsword and cluster to kusto.
%kql kusto://username('myName').password('myPassword')
# Note: set current (default) username and password to kusto.
%kql kusto://cluster('myCluster')
# Note set current (default) cluster to kusto.
"""
set_logger(Logger(None, create_log_context()))
# save globals and locals so they can be referenced in bind vars
user_ns = self.shell.user_ns.copy()
user_ns.update(local_ns)
logger().debug("To Parsed: \n\rline: {}\n\rcell:\n\r{}".format(
line, cell))
try:
parsed = None
parsed_queries = Parser.parse("%s\n%s" % (line, cell), self)
logger().debug("Parsed: {}".format(parsed_queries))
result = None
for parsed in parsed_queries:
result = self.execute_query(parsed, user_ns)
return result
except Exception as e:
if parsed:
if parsed["options"].get("short_errors", self.short_errors):
Display.showDangerMessage(str(e))
return None
elif self.short_errors:
Display.showDangerMessage(str(e))
return None
raise
def acquire_token(self):
"""Acquire tokens from AAD."""
token = self._adal_context.acquire_token(self._kusto_cluster,
self._username,
self._client_id)
if token is not None:
expiration_date = dateutil.parser.parse(
token[TokenResponseFields.EXPIRES_ON])
if expiration_date > datetime.now() + timedelta(minutes=1):
return self._get_header(token)
if TokenResponseFields.REFRESH_TOKEN in token:
token = self._adal_context.acquire_token_with_refresh_token(
token[TokenResponseFields.REFRESH_TOKEN], self._client_id,
self._kusto_cluster)
if token is not None:
return self._get_header(token)
if self._authentication_method is AuthenticationMethod.aad_username_password:
token = self._adal_context.acquire_token_with_username_password(
self._kusto_cluster, self._username, self._password,
self._client_id)
elif self._authentication_method is AuthenticationMethod.aad_application_key:
token = self._adal_context.acquire_token_with_client_credentials(
self._kusto_cluster, self._client_id, self._client_secret)
elif self._authentication_method is AuthenticationMethod.aad_device_login:
# print(code[OAuth2DeviceCodeResponseParameters.MESSAGE])
# webbrowser.open(code[OAuth2DeviceCodeResponseParameters.VERIFICATION_URL])
# token = self._adal_context.acquire_token_with_device_code(
# self._kusto_cluster, code, self._client_id
# )
code = self._adal_context.acquire_user_code(
self._kusto_cluster, self._client_id)
url = code[OAuth2DeviceCodeResponseParameters.VERIFICATION_URL]
device_code = code[
OAuth2DeviceCodeResponseParameters.USER_CODE].strip()
html_str = ("""<!DOCTYPE html>
<html><body>
<!-- h1 id="user_code_p"><b>""" + device_code +
"""</b><br></h1-->
<input id="kqlMagicCodeAuthInput" type="text" readonly style="font-weight: bold; border: none;" size = '"""
+ str(len(device_code)) + """' value='""" +
device_code + """'>
<button id='kqlMagicCodeAuth_button', onclick="this.style.visibility='hidden';kqlMagicCodeAuthFunction()">Copy code to clipboard and authenticate</button>
<script>
var kqlMagicUserCodeAuthWindow = null
function kqlMagicCodeAuthFunction() {
/* Get the text field */
var copyText = document.getElementById("kqlMagicCodeAuthInput");
/* Select the text field */
copyText.select();
/* Copy the text inside the text field */
document.execCommand("copy");
/* Alert the copied text */
// alert("Copied the text: " + copyText.value);
var w = screen.width / 2;
var h = screen.height / 2;
params = 'width='+w+',height='+h
kqlMagicUserCodeAuthWindow = window.open('""" + url +
"""', 'kqlMagicUserCodeAuthWindow', params);
// TODO: save selected cell index, so that the clear will be done on the lince cell
}
</script>
</body></html>""")
Display.show_html(html_str)
# webbrowser.open(code['verification_url'])
try:
token = self._adal_context.acquire_token_with_device_code(
self._kusto_cluster, code, self._client_id)
finally:
html_str = """<!DOCTYPE html>
<html><body><script>
// close authentication window
if (kqlMagicUserCodeAuthWindow && kqlMagicUserCodeAuthWindow.opener != null && !kqlMagicUserCodeAuthWindow.closed) {
kqlMagicUserCodeAuthWindow.close()
}
// TODO: make sure, you clear the right cell. BTW, not sure it is a must to do any clearing
// clear output cell
Jupyter.notebook.clear_output(Jupyter.notebook.get_selected_index())
// TODO: if in run all mode, move to last cell, otherwise move to next cell
// move to next cell
</script></body></html>"""
Display.show_html(html_str)
elif self._authentication_method is AuthenticationMethod.aad_application_certificate:
token = self._adal_context.acquire_token_with_client_certificate(
self._kusto_cluster, self._client_id, self._certificate,
self._thumbprint)
else:
raise KustoClientError(
"Please choose authentication method from azure.kusto.data.security.AuthenticationMethod"
)
return self._get_header(token)
def __getitem__(self, key):
if isinstance(key, slice):
s = self.row[key]
return KqlRow(s, len(s), **self.kwargs)
else:
return Display.to_styled_class(self.row[key], **self.kwargs)
def acquire_token(self):
""" A method to acquire tokens from AAD. """
# print("my_aad_helper_acquire_token")
token_response = self.adal_context.acquire_token(
self.kusto_cluster, self.username, self.client_id)
if token_response is not None:
expiration_date = dateutil.parser.parse(
token_response['expiresOn'])
if expiration_date > datetime.utcnow() + timedelta(minutes=5):
return token_response['accessToken']
if self.client_secret is not None and self.client_id is not None:
token_response = self.adal_context.acquire_token_with_client_credentials(
self.kusto_cluster, self.client_id, self.client_secret)
elif self.username is not None and self.password is not None:
token_response = self.adal_context.acquire_token_with_username_password(
self.kusto_cluster, self.username, self.password,
self.client_id)
else:
code = self.adal_context.acquire_user_code(self.kusto_cluster,
self.client_id)
url = code['verification_url']
device_code = code["user_code"].strip()
html_str = """<!DOCTYPE html>
<html><body>
<!-- h1 id="user_code_p"><b>""" + device_code + """</b><br></h1-->
<input id="kqlMagicCodeAuthInput" type="text" readonly style="font-weight: bold; border: none;" size = '""" + str(
len(device_code)) + """' value='""" + device_code + """'>
<button id='kqlMagicCodeAuth_button', onclick="this.style.visibility='hidden';kqlMagicCodeAuthFunction()">Copy code to clipboard and authenticate</button>
<script>
var kqlMagicUserCodeAuthWindow = null
function kqlMagicCodeAuthFunction() {
/* Get the text field */
var copyText = document.getElementById("kqlMagicCodeAuthInput");
/* Select the text field */
copyText.select();
/* Copy the text inside the text field */
document.execCommand("copy");
/* Alert the copied text */
// alert("Copied the text: " + copyText.value);
var w = screen.width / 2;
var h = screen.height / 2;
params = 'width='+w+',height='+h
kqlMagicUserCodeAuthWindow = window.open('""" + url + """', 'kqlMagicUserCodeAuthWindow', params);
}
</script>
</body></html>"""
Display.show(html_str)
# webbrowser.open(code['verification_url'])
try:
token_response = self.adal_context.acquire_token_with_device_code(
self.kusto_cluster, code, self.client_id)
finally:
html_str = """<!DOCTYPE html>
<html><body><script>
// close authentication window
if (kqlMagicUserCodeAuthWindow && kqlMagicUserCodeAuthWindow.opener != null && !kqlMagicUserCodeAuthWindow.closed) {
kqlMagicUserCodeAuthWindow.close()
}
// clear output cell
Jupyter.notebook.clear_output(Jupyter.notebook.get_selected_index())
// move to next cell
</script></body></html>"""
Display.show(html_str)
return token_response['accessToken']
def raw_json(self):
return Display.to_styled_class(self._json_response, **self.options)
def execute_query(self, parsed, user_ns, result_set=None):
if Help_html.showfiles_base_url is None:
# display(Javascript("""IPython.notebook.kernel.execute("NOTEBOOK_URL = '" + window.location + "'")"""))
window_location = user_ns.get("NOTEBOOK_URL")
if window_location is not None:
Help_html.flush(window_location)
else:
display(
Javascript(
"""IPython.notebook.kernel.execute("NOTEBOOK_URL = '" + window.location + "'");"""
))
query = parsed['kql'].strip()
options = parsed['options']
suppress_results = options.get(
'suppress_results', False) and options.get(
'enable_suppress_result', self.enable_suppress_result)
connection_string = parsed['connection']
if options.get('version'):
print('Kqlmagic version: ' + VERSION)
if options.get('popup_help'):
help_url = 'http://aka.ms/kdocs'
# 'https://docs.loganalytics.io/docs/Language-Reference/Tabular-operators'
# 'http://aka.ms/kdocs'
# 'https://kusdoc2.azurewebsites.net/docs/queryLanguage/query-essentials/readme.html'
# import requests
# f = requests.get(help_url)
# html = f.text.replace('width=device-width','width=500')
# Display.show(html, **{"popup_window" : True, 'name': 'KustoQueryLanguage'})
button_text = 'popup kql help '
Display.show_window('KustoQueryLanguage', help_url, button_text)
try:
#
# set connection
#
conn = Connection.get_connection(connection_string)
# parse error
except KqlEngineError as e:
if options.get('short_errors', self.short_errors):
msg = Connection.tell_format(connect_str)
Display.showDangerMessage(str(e))
Display.showInfoMessage(msg)
return None
else:
raise
# parse error
except ConnectionError as e:
if options.get('short_errors', self.short_errors):
Display.showDangerMessage(str(e))
list = Connection.get_connection_list_formatted()
if len(list) > 0:
Display.showInfoMessage(list)
return None
else:
raise
try:
# validate connection
retry_with_code = False
if options.get(
'validate_connection_string',
self.validate_connection_string
) and not conn.options.get('validate_connection_string'):
validation_query = 'range c from 1 to 10 step 1 | count'
try:
raw_table = conn.execute(validation_query)
conn.set_validation_result(True)
except Exception as e:
msg = str(e)
if msg.find('AADSTS50079') > 0 and msg.find(
'multi-factor authentication') > 0 and isinstance(
conn, KustoEngine):
Display.showDangerMessage(str(e))
retry_with_code = True
else:
raise e
if retry_with_code:
Display.showInfoMessage(
'replaced connection with code authentication')
database_name = conn.get_database()
cluster_name = conn.get_cluster()
connection_string = "kusto://code().cluster('" + cluster_name + "').database('" + database_name + "')"
conn = Connection.get_connection(connection_string)
raw_table = conn.execute(validation_query)
conn.set_validation_result(True)
conn.options['validate_connection_string'] = True
if options.get('popup_schema') or (
options.get('auto_popup_schema', self.auto_popup_schema)
and not conn.options.get('auto_popup_schema')):
Database_html.popup_schema(conn)
conn.options['auto_popup_schema'] = True
if not query:
#
# If NO kql query, just return the current connection
#
if not connection_string and Connection.connections and options.get(
'show_conn_list',
self.show_conn_list) and not suppress_results:
Display.showInfoMessage(
Connection.get_connection_list_formatted())
return None
#
# submit query
#
start_time = time.time()
raw_table = conn.execute(query, user_ns)
end_time = time.time()
elapsed_timespan = end_time - start_time
#
# model query results
#
if result_set is None:
saved_result = ResultSet(raw_table, query, options)
saved_result.magic = self
saved_result.parsed = parsed
saved_result.connection = conn.get_name()
else:
saved_result = result_set
saved_result._update(raw_table)
if not connection_string and Connection.connections and options.get(
'show_conn_list', self.show_conn_list):
saved_result.conn_info = Connection.get_connection_list_formatted(
)
saved_result.start_time = start_time
saved_result.end_time = end_time
saved_result.elapsed_timespan = elapsed_timespan
self.shell.user_ns.update({
options.get('last_raw_result_var', self.last_raw_result_var):
saved_result
})
result = saved_result
if options.get('feedback', self.feedback):
minutes, seconds = divmod(elapsed_timespan, 60)
saved_result.info.append(
'Done ({:0>2}:{:06.3f}): {} records'.format(
int(minutes), seconds, saved_result.records_count))
logger().debug("Results: {} x {}".format(
len(saved_result), len(saved_result.columns_name)))
if options.get('columns_to_local_vars',
self.columns_to_local_vars):
#Instead of returning values, set variables directly in the
#users namespace. Variable names given by column names
if options.get('feedback', self.feedback):
saved_result.info.append(
'Returning raw data to local variables [{}]'.format(
', '.join(saved_result.columns_name)))
self.shell.user_ns.update(saved_result.to_dict())
result = None
if options.get('auto_dataframe', self.auto_dataframe):
if options.get('feedback', self.feedback):
saved_result.info.append(
'Returning data converted to pandas dataframe')
result = saved_result.to_dataframe()
if options.get('result_var') and result_set is None:
result_var = options['result_var']
if options.get('feedback', self.feedback):
saved_result.info.append(
'Returning data to local variable {}'.format(
result_var))
self.shell.user_ns.update({
result_var:
result if result is not None else saved_result
})
result = None
if result is None:
return None
if not suppress_results:
if options.get('auto_dataframe', self.auto_dataframe):
Display.showSuccessMessage(saved_result.info)
else:
saved_result.display_info = True
else:
saved_result.suppress_result = True
# Return results into the default ipython _ variable
return result
except Exception as e:
if not connection_string and Connection.connections and options.get(
'show_conn_list',
self.show_conn_list) and not suppress_results:
# display list of all connections
Display.showInfoMessage(
Connection.get_connection_list_formatted())
if options.get('short_errors', self.short_errors):
Display.showDangerMessage(e)
return None
else:
raise e
def completion_query_info(self):
return Display.to_styled_class(self._completion_query_info,
**self.options)
def completion_query_resource_consumption(self):
return Display.to_styled_class(
self._completion_query_resource_consumption, **self.options)
