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)