https://devcafe.co.kr/w/index.php?action=history&feed=atom&title=BigQuery-LookerSutdio_Audit_Log_SQL 2026-05-19T12:22:34Z 이 문서의 편집 역사 MediaWiki 1.42.1 https://devcafe.co.kr/w/index.php?title=BigQuery-LookerSutdio_Audit_Log_SQL&diff=639&oldid=prev 2024-10-22T08:19:52Z

<p>설명 수정</p> <table style="background-color: #fff; color: #202122;" data-mw="interface"> <col class="diff-marker" /> <col class="diff-content" /> <col class="diff-marker" /> <col class="diff-content" /> <tr class="diff-title" lang="ko"> <td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">← 이전 판</td> <td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">2024년 10월 22일 (화) 17:19 판</td> </tr><tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l1">1번째 줄:</td> <td colspan="2" class="diff-lineno">1번째 줄:</td></tr> <tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>* LookerStudio Audit <del style="font-weight: bold; text-decoration: none;">Log를 위해 </del>BigQuery Export <del style="font-weight: bold; text-decoration: none;">시 </del>Workspace 목록 추출</div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>* LookerStudio <ins style="font-weight: bold; text-decoration: none;">Pro 사용시 </ins>Audit <ins style="font-weight: bold; text-decoration: none;">Log To </ins>BigQuery Export <ins style="font-weight: bold; text-decoration: none;">에서 </ins>Workspace 목록 추출</div></td></tr> <tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br></td></tr> <tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>&lt;pre&gt;</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>&lt;pre&gt;</div></td></tr> </table>

Kokoksh https://devcafe.co.kr/w/index.php?title=BigQuery-LookerSutdio_Audit_Log_SQL&diff=638&oldid=prev 2024-10-22T08:17:27Z

<p>새 문서: * LookerStudio Audit Log를 위해 BigQuery Export 시 Workspace 목록 추출 &lt;pre&gt; SELECT FORMAT_TIMESTAMP((&#039;%Y-%m-%d %H:%M:%S&#039;), TIMESTAMP_MICROS(log.time_usec), &#039;Asia/Seoul&#039;) AS inq_dttm, gwslog.event_name, gwslog.email, gwslog.event_type, gwslog.data_studio.asset_id, gwslog.data_studio.asset_name, gwslog.data_studio.parent_workspace_id, rs.asset_name latest_asset_name, gwslog.data_studio.owner_email, gwslog.data_studio.visibility, rs.owner_email lates...</p> <p><b>새 문서</b></p><div>* LookerStudio Audit Log를 위해 BigQuery Export 시 Workspace 목록 추출<br /> <br /> &lt;pre&gt;<br /> SELECT<br /> FORMAT_TIMESTAMP((&#039;%Y-%m-%d %H:%M:%S&#039;), TIMESTAMP_MICROS(log.time_usec), &#039;Asia/Seoul&#039;) AS inq_dttm,<br /> gwslog.event_name,<br /> gwslog.email,<br /> gwslog.event_type,<br /> gwslog.data_studio.asset_id,<br /> gwslog.data_studio.asset_name,<br /> gwslog.data_studio.parent_workspace_id,<br /> rs.asset_name latest_asset_name,<br /> gwslog.data_studio.owner_email,<br /> gwslog.data_studio.visibility,<br /> rs.owner_email latest_owner_email,<br /> rs.visibility latest_visibility,<br /> rs.parent_workspace_id latest_parent_workspace_id<br /> FROM<br /> `[Project_ID].[DataSet].activity` gwslog <br /> LEFT JOIN ( -- 가장 최근에 변경된 보고서명을 가져오기 위한 서브쿼리 조인<br /> SELECT<br /> data_studio.asset_id,<br /> data_studio.asset_name,<br /> data_studio.visibility,<br /> data_studio.owner_email,<br /> data_studio.parent_workspace_id,<br /> ROW_NUMBER() OVER (PARTITION BY data_studio.asset_id ORDER BY time_usec DESC) AS rn<br /> FROM<br /> `[Project_ID].[DataSet].activity`<br /> WHERE<br /> record_type =&#039;data_studio&#039;<br /> AND data_studio.asset_type=&#039;WORKSPACE&#039;<br /> QUALIFY<br /> rn=1 ) rs <br /> ON<br /> gwslog.data_studio.asset_id = rs.asset_id<br /> WHERE<br /> gwslog.record_type = &#039;data_studio&#039;<br /> AND event_name=&#039;CREATE&#039; -- 생성보고서 기준으로 필터링 조건<br /> AND data_studio.asset_type=&#039;WORKSPACE&#039; -- LookerStudio Pro에서 Workspace 생성 목록 필터링 조건 <br /> AND NOT EXISTS ( -- 현재 삭제된 Workspace는 제외<br /> SELECT<br /> x.data_studio.asset_id<br /> FROM<br /> `[Project_ID].[DataSet].activity` x<br /> WHERE<br /> 1=1<br /> AND event_name=&#039;TRASH&#039; -- LookerStudio Pro에서 삭제된 리스트 필터링 조건<br /> AND x.data_studio.asset_id = gwslog.data_studio.asset_id<br /> AND data_studio.asset_type=&#039;WORKSPACE&#039;<br /> AND x.data_studio.asset_id = gwslog.data_studio.asset_id)<br /> &lt;/pre&gt;</div>

Kokoksh