Windows 10 – Marketing Name via PowerShell

Background

Reading through MSFT’s documentation on installing the “Linux Subsystem on MS Windows” brought to light the need to familiarize oneself with the various marketing moniker for each Released build of MS Windows 10.

Literature

Here is the specific document…

Install the Windows Subsystem for Linux
Link

And, here are the specific texts that juiced my appetite:

Image

Falls Creator Update

linuxPlatform_FallCreatorUpdate_20180613_1150AM.png

Anniversary Update and Creators Update

linuxPlatform_AnniversaryAndCreatorsUpdate_20180613_1152AM.png

Explanation

So the question is which MS Windows 10 Update am I running ….

Referenced Work

Thanks goodness there are various credible sources doing the heavy work of lining up MSFT’s version #, Marketing Version, and Build Number  low & high bars.

Here are the sources we used for this post:

  1. Microsoft
    • Windows 10
      • Windows 10 release information
        Link
  2.  Wikipedia
    • Windows 10 version history
      Link
  3. PureInfotech
    • Mauro Huculak
      • Windows Update History information helps you know exactly what’s the latest version of Windows 10 available.
        Link

Image

Wikipedia – Windows 10 – Version History

wikipedia_Windows10VersionHistory_20170613_1225PM

Code

Text file

Text file – UpdateList.txt

Outline

  1. Here is our file’s content
    • Version
    • Marketing
    • Build Number – Min
    • Build Number – Max

1809, Windows 10 version 1809 (Late 2018), 17686.00, 17686.00
1803, Windows 10 version 1803 (April 2018 Update) history, 17133.73, 17134.83
1709, Windows 10 version 1709 (Fall Creators Update) history, 16299.15, 16299.461
1703, Windows 10 version 1703 (Creators Update) history, 15063.11, 15063.1112
1607, Windows 10 version 1607 (Anniversary Update) history, 14393, 14393.2189
1511, Windows 10 version 1511 (November Update) history, 10586.104, 10586.1176
1507, Windows 10 version 1507 (Initial Release) history, 10240.16683, 10240.17831

PowerShell

PowerShell – getWindows10MarketingName.ps1

Outline

  1. Read flat file ( updateList.txt)
  2. Get MS Windows Version via calling [Environment]::OSVersion
    • Build Number
  3. Get MS Windows by issuing “Get-WmiObject Win32_OperatingSystem
    • Returns
      • Caption
      • OSArchitecture
  4. Registry Access – Get-ItemProperty
    • Argument :- HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion
    • Returns
      • ProductName
      • ReleaseID
      • CurrentBuildNumber
      • CompositionEditionID
  5. Iterate read file
    • Condition to indicate match
      • If ReleaseID from registry Path HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion matches read record
      • If Build Number falls into range of read record
    • Save Pointer
  6. If Pointer Saved
    • Display OS Version from table

Preparatory

Registry
Registry – HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion

registry_20180614_0700AM.png

 

Actual Code


Set-StrictMode -Version 1
  

[boolean]$debug=$false;
[string] $objOSVerNumberAsString = $null;
[double] $versionNumberMinAsNumber = 0;
[double] $versionNumberMaxAsNumber = 0;
[boolean] $bConverted = $false;
[string] $strLog = $null;
#[string] $CHAR_EMPTY='';
#PowerTip: Create Empty String with PowerShell
[string] $CHAR_EMPTY=[string]::Empty;

[string] $productName =$null;
[string] $releaseID =$null;
[string] $currentBuild =$null
[string] $edition =$null;

[string] $releaseIDInList = $null;

$debug=$false;
#$debug=$true;

$updateListFilename = 'updateList.txt';

#read file updateList.txt
$updateList = Get-Content $updateListFilename;

#Display Update List
#$updateList;

#Get OS version [Environment]::OSVersion
$objOSVer =[Environment]::OSVersion;

#Display OS version
Write-Host('OS Version');
Write-Host('----------');
Write-Host($objOSVer);


$objOSVerPlatform = $objOSVer.Platform;
$objOSVerServicePack = $objOSVer.ServicePack;
$objOSVerNumber = $objOSVer.Version;
$objOSVerString = $objOSVer.VersionString;


$objOSVerNumberAsString = [system.String]::Join($CHAR_EMPTY, $objOSVerNumber);


$objOSVerNumber0 = $objOSVerNumberAsString.split('.')[0];
$objOSVerNumber1 = $objOSVerNumberAsString.split('.')[1];
$objOSVerNumber2 = $objOSVerNumberAsString.split('.')[2];
$objOSVerNumber3 = $objOSVerNumberAsString.split('.')[3];


$objOSVerNumberBase = $objOSVerNumber0;
$objOSVerNumberSP = $objOSVerNumber1;
$objOSVerNumberBuild = $objOSVerNumber2;
$objOSVerNumberMinor = $objOSVerNumber3;

if ( $debug)
{

    Write-Host('');

    Write-Host('OS Version ( broken into pieces)');
    Write-Host('--------------------------------')  

    Write-Host('OSVersionPlatform      :- {0}' -f $objOSVerPlatform);
    Write-Host('OSVersionServicePack   :- {0}' -f $objOSVerServicePack);
    Write-Host('OSVersionNumberAsArray :- {0}' -f $objOSVerNumber);
    Write-Host('objOSVerNumberAsString :- {0}' -f $objOSVerNumberAsString);
    Write-Host('OSVersionString        :- {0}' -f $objOSVerString);

    Write-Host('objOSVerNumberSP       :- {0}' -f $objOSVerNumberSP);
    Write-Host('objOSVerNumberBuild    :- {0}' -f $objOSVerNumberBuild);

    Write-Host('');
}

  

$objWMIOS = (Get-WmiObject Win32_OperatingSystem);

if ($objWMIOS -ne $null)
{
	Write-Host('');

    Write-Host("WMI - Win32_OperatingSystem");
	#$objWMIOS;

	$WMIOSCaption = $objWMIOS.caption;
	$WMIOSArchitecture = $objWMIOS.OSArchitecture;

	$strLog = "`t WMI OS - Caption        :- {0}" -f $WMIOSCaption;
	Write-Host $strLog;

	$strLog = "`t WMI OS - OSArchitecture :- {0}" -f $WMIOSArchitecture;
	Write-Host $strLog;	

	Write-Host('');
}


$strRegPath = "HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion";
$objRegWinOS = (Get-ItemProperty $strRegPath);

if ($objRegWinOS -ne $null)
{

	Write-Host('');
    Write-Host("Registry Path $strRegPath");
	#$objRegWinOS;

	$productName = $objRegWinOS.ProductName;
	$releaseID = $objRegWinOS.ReleaseID;
	$currentBuild = $objRegWinOS.CurrentBuildNumber;
	$edition = $objRegWinOS.CompositionEditionID;

	$strLog = "`t WinOS Registry - productName :- {0}"`
				-f $productName;
	Write-Host $strLog;

	$strLog = "`t WinOS Registry - ReleaseID :- {0}" `
				-f $releaseID;
	Write-Host $strLog;

	$strLog = "`t WinOS Registry - Current Build :- {0}" `
				-f $currentBuild;
	Write-Host $strLog;

	$strLog = "`t WinOS Registry - Edition :- {0}" `
				-f $edition;
	Write-Host $strLog;

	Write-Host('');
}


$i = 0;
$entry = $null;
$entrySaved = $null;


$iNumberofUpdates = $updateList.Length;

Write-Host '';
Write-Host 'Iterating Build List looking to match OS Build Number ... ';
Write-Host '';


foreach ( $entry in $updateList )
{

  $releaseIDInList =$entry.split(',')[0];
  $update=$entry.split(',')[1];
  $versionNumberMin=$entry.split(',')[2];
  $versionNumberMax=$entry.split(',')[3];

  
  $bConverted = [double]::TryParse(`
                      $versionNumberMin`
                    , [ref] $versionNumberMinAsNumber);

  $bConverted = [double]::TryParse(`
                    $versionNumberMax`
                      , [ref] $versionNumberMaxAsNumber);                               

  if ($debug)
  {

    Write-Host('Build {0} -f $objOSVerNumberBuild ');

    $strLog = 'versionNumberMin {0}' `
				-f $versionNumberMinAsNumber;
	Write-Host($strLog);

    $strLog = 'versionNumberMax {0}' `
				-f $versionNumberMaxAsNumber;
	Write-Host($strLog);	

  }     

  if (
			( $releaseID -eq $releaseIDInList) `
		-or `
			( `
				     ( $objOSVerNumberBuild -ge $versionNumberMinAsNumber) `
				-and ( $objOSVerNumberBuild -le $versionNumberMaxAsNumber) `
			)
	 )
    {
        # save entry
        $entrySaved = $entry;

        break;
    }

  $i = $i + 1;  

} # foreach ( $entry in $updateList )   


if ($entrySaved -ne $null)
{

    # Get fields
    $matchVersion = $entrySaved.split(',')[0];
    $matchMarketing = $entrySaved.split(',')[1];
    $matchBuildMin = $entrySaved.split(',')[2];
    $matchBuildMax = $entrySaved.split(',')[3];

    Write-Host("Version Matched");

    Write-Host("`t Version   :-  $matchVersion");

    $strLog = "`t Build     :-  {0} through {1}" `
                -f $matchBuildMin.Trim() `
				 , $matchBuildMax.Trim();

    Write-Host($strLog);

    Write-Host("`t Marketing :- $matchMarketing");

}
else
{

    $strLog = 'Version Number ({0}) not found!' -f `
                $objOSVerNumberBuild;

    Write-Host($strLog);            

}

Invoke

Syntax

powershell -file ./getWindows10MarketingName.ps1

Output

getMSWindows10UpdateName_Work_20180613_0742PM.png

Source Code Control

GitHub

DanielAdeniji/WinOSMarketingNameUsingPS
Link

Listening

Listening to the song I first heard during yesterday’s parade:

Drake – Nice for What
Link

Lyrics

You got to be nice for what to these …..

Windows 10 – Installing Linux SubSystem

Background

Availing Linux on MS Windows 10.

Here is the guide that we will be using:

Windows 10 Installation Guide
Install the Windows Subsystem for Linux
Link

Outline

  1. Enable Windows Feature
    • Microsoft Windows Subsystem Linux
  2. Review OS Version
    • Issue SystemInfo and get OS Version and Build Number
  3. Choose Install Location, based on Build Number
    • If Build Number > 16215
      • We can install from Windows Store
    • Else
      • Enable Windows Developer Mode
      • Start Bash or use “lxrun /install
  4. Register Product
  5. Create Linux User
  6. Set root user’s password

 

Install Steps

Enable Windows Optional Features

Microsoft-Windows-Subsystem-Linux

Code

powershell -C "Enable-WindowsOptionalFeature -Online -FeatureName Microsoft-Windows-Subsystem-Linux"

System Info

Code


powershell -C "systeminfo | Select-String 'OS' "

Output

version_20180610_0902PM

Explanation

As our Build Number ( 17134 ) is greater than 16215, we can install via “Microsoft Store“.

Microsoft Store

Visit Microsoft Store and search for Linux.

https://www.microsoft.com/en-us/search/result.aspx?q=linux

Here is the result of that search:

MicrosoftStore_Linux.PNG

 

SUSE Linux Enterprise Server 12

We chose to go with SE Linux.

SUSE Linux Enterprise Server 12 – Get

SUSELinuxEnterprise_20180610_0905PM.PNG

Use across your devices

UseAcrossYourDevices_20180610_0906PM.PNG

Add your Microsoft account to Microsoft Store

AddYourMicrosoftAccountToMicrosoftStore_20180610_0921PM [brushedup].PNG

Enter Password

AddYourMicrosoftAccountToMicrosoftStore_EnterPassword_20180610_0923PM.PNG

Installing

Installing_20180610_0911PM

SUSE Linux Enterprise Server 12 – Registration +User

registration_20180610_0926PM.PNG

SUSE Linux Enterprise Server 12 – Registration +User +Password

registrationUserAndPassword_20180610_0927PM.PNG

Validation

Bash

uname

Get Linux Version

Code


uname -r

Output

bash_uname_20180610_0938PM

List Users

Code


cat /etc/passwd

Output

security_users_20180610_0947PM.PNG

References

  1. Microsoft
    • Install the Windows Subsystem for Linux
      • Install the Windows Subsystem for Linux
        Link
    • Rich Turner
      • Bash on Ubuntu on Windows – Download Now!
        Link

Transact SQL – STRING_AGG

Background

In our post on MySQL – Information_schema.statistics we spoke glowingly of the GROUP_CONCAT Function.

I really could not find a way around using it in MySQL.

I ran into some difficulties using it and as with problems one just googles for workarounds.

BTW, the MySQL post is here.

SQL Server

Introduction

It seems that in version 2017, MSFT played catch up.

And, added a similar function.

String_Agg

Code


select
        [object]
            = quoteName(tblSS.[name])
              + '.'
              + quoteName(tblSO.[name])

        , [indexID]
            = tblSI.[index_id]

        , [index]
            = tblSI.[name]

        , [indexType]
            = tblSI.[type_desc]

        , [columnList]
            = 
                STRING_AGG 
                (
                      concat
                      (
                          tblSC.[name]
                        , ''
                      )	

                    , ', '
                ) 
                WITHIN GROUP 
                (
                    ORDER BY 
                        tblSIC.[key_ordinal]
                )
            

from   sys.schemas tblSS

inner join sys.objects tblSO

        on   tblSS.[schema_id] = tblSO.[schema_id]
 
inner join sys.indexes tblSI

        on   tblSO.[object_id] = tblSI.[object_id]

inner join sys.index_columns tblSIC

        on   tblSI.[object_id] = tblSIC.[object_id]
        and  tblSI.[index_id]  = tblSIC.[index_id]

inner join sys.columns tblSC

        on   tblSIC.[object_id] = tblSC.[object_id]
        and  tblSIC.column_id = tblSC.column_id

/*
    Skip MS Shipped Objects
*/
where tblSO.is_ms_shipped = 0

/*
    Exclude Included Columns
    Only Include actual Key Columns
*/
and   tblSIC.[key_ordinal] > 0

group by

                quoteName(tblSS.[name])
              + '.'
              + quoteName(tblSO.[name])

        , tblSI.[index_id]

        , tblSI.[name]

        , tblSI.[type_desc]

order by

                quoteName(tblSS.[name])
              + '.'
              + quoteName(tblSO.[name])

        , tblSI.[index_id]

        , tblSI.[name]



Output

 

Crediting

Crediting Dan M for asking the question.

And, Martin Smith for ably.

Simulating group_concat MySQL function in Microsoft SQL Server 2005?
Link

 

References

  1. Microsoft
    • String_Agg
    • sys.index_columns
  2. Stack Overflow
    • Simulating group_concat MySQL function in Microsoft SQL Server 2005?
      Link

SQL Server – Identify Objects with dependencies outside of current database

Background

Needed a quick way to identify referenced objects that are not contained in my current database either to having being dropped, located in a different database, or located on a linked server.

 

DevioBlog

DevioBlog has a good concise query.

A write-up is available here.

And, here it is in it’s entirety.


select o.name, d.referenced_entity_name, *
from sys.sql_expression_dependencies  d

inner join sys.objects o 
       on d.referencing_id = o.object_id

where referenced_id is null


 

Code

 


/*

    sys.sql_expression_dependencies (Transact-SQL)
    v2008
    https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-sql-expression-dependencies-transact-sql?view=sql-server-2017

    ID of the referenced entity. 


    a) The value of this column is never NULL for schema-bound references. 

    b) The value of this column is always NULL for cross-server and cross-database references.

    c) NULL for references within the database if the ID cannot be determined. 

        Objects dropped

    
    d) For non-schema-bound references, the ID cannot be resolved in the following cases:

        The referenced entity does not exist in the database.

*/
select 
          
        [object]
        = quoteName
            (
                isNull
                (
                    tblSS.[name]
                    , ''
                )
            )

            + '.'

            + quoteName
            (
                isNull
                (
                    tblSO.name
                    , ''
                )
            )

        , [objectType]
            = tblSO.[type_desc]

        , [referencedDatabase]
            = tblSED.referenced_database_name

        , [referencedObject]
            = quoteName
                (
                    isNull
                        (
                            tblSED.referenced_schema_name
                            , ''
                        )
                )
                + '.'
                + quoteName
                    (
                        isNull(tblSED.referenced_entity_name, '')
                    )

        , [classofReferencedObject]
            = tblSED.referenced_class_desc

from sys.objects tblSO

inner join sys.schemas tblSS

        on tblSO.schema_id = tblSS.schema_id

inner join sys.sql_expression_dependencies  tblSED

        on tblSO.object_id = tblSED.referencing_id

where (

        (
            ( tblSED.[referenced_id] is null )
        )

    )

Summary

Thank goodness information about referenced objects are cataloged in the sys.sql_expression_dependencies system table.

If the referenced object is not in the contextual database, the referenced_id is logged as null.

Also as part of clean-up effort the referenced_id column is nulled out whenever an object is dropped.

 

SQL Server – Operator – Top – Top Expression (0)

Background

Earlier today I found myself pressing to make sure I had done right by a query.

 

Query Plan

Query Plan – 01

Here is the original query Plan

Image

queryPlan_computeScaler_HashMatch_20180517_1139AM [clipped]

Explanation

  1. I know that I don’t really want a Hash Match
    • Took care of the Hash Match by reducing the query from two tables to a single table
    • There are a few novel ways to do so, and will cover that later

 

Query Plan – 02

Here is the query Plan once we got rid of the secondary table

Image

queryPlan_computeScaler_HashMatch_20180517_1143AM [clipped]

 

Top ?

I was stuck at the Top Operator for a very long time

Explanation

  1. Rowcount
    • Do I have a set rowcount somewhere
    • Is my environment introducing a constraint for maximum number of records to “touch
    • Is my edition of SQL Server throttling performance
  2. Top
    • Do I have a top N clause somewhere

 

Operator – Top – Default

Overview

Here is what our Top Operators looks like when we do not have “set rowcount” set and we do not have an actual TOP Clause.

Image

Explanation

  1. Actual Number of Rows :- 65
  2. Output List :- sysschobjs.id & sysschobjs.nsid
  3. Top Expression :- (0)

 

Operator – Top – “Set rowcount”

Overview

What if we add an actual set rowcount

Image

 

Explanation

  1. Actual Number of Rows :- 2
  2. Output List :- sysschobjs.id & sysschobjs.nsid
  3. Top Expression :- (0)

Operator – Top – “Select TOP N”

Overview

Here is what we see when we add a “Top 1” Clause.

Image

Explanation

  1. Actual Number of Rows :- 1
  2. Output List :- sysschobjs.id & sysschobjs.nsid
  3. Top Expression :- (1)

 

Other Things

Overview

I was stuck and so tried other things; such as

  1. Newer version of SQL Server ; v2017 to be exact
  2. Took out the insert into and performed a simple select

Could not reproduce…

 

Craig Freedman ( MSFT )

Finally goggled on the right terms and read what Craig Freedman has to say.

The particular post that I will be quoting is:

ROWCOUNT Top
Link

  1. TOP Operator
    • If you’ve looked at any insert, update, or delete plans, including those used in some of my posts, you’ve probably noticed that nearly all such plans include a top operator.
  2. SET ROWCOUNT
    •  It is a ROWCOUNT top. It is used to implement SET ROWCOUNT functionality.
  3. Why doesn’t SQL Server add a ROWCOUNT top to select statements?
    • SQL Server implements SET ROWCOUNT for select statements by simply counting and returning the correct number of rows from the root of the plan.  Although this strategy might work for a really trivial update plan such as the one above, it would not work for more complex update plans.  For instance, if we add a unique index to our table, the update plan becomes substantially more complex
  4. Placement
    • By placing the ROWCOUNT top above the table scan, the optimizer can ensure that the server updates exactly the correct number of rows regardless of the complexity of the remainder of the plan.

 

Martin Smith

The good thing about blogging and allowing comments is that people can come back and provide helpful feedback.

Here is one from Martin Smith:

Martin Smith
December 29, 2012 at 8:15 am

In 2012 it looks like this operator is only added to plans run under “SET ROWCOUNT” of other than zero. As far as I can discern it is added in to the set_options used as a plan cache key.

SQL Server Versions

Here are the versions of SQL Server where you will be able to reproduce the Top (0) Operator preceding data effecting operators :-

  1. 2005
  2. 2008-R2

Dedication

Thankfully I have a far better grasp courtesy of two able men, Craig Freedman & Martin Smith.

Tristan Harris

 

Videos

  1. YouTube
    • Ted
      • How a handful of tech companies control billions of minds every day | Tristan Harris
      • Profile :-
        • A handful of people working at a handful of tech companies steer the thoughts of billions of people every day, says design thinker Tristan Harris. From Facebook notifications to Snapstreaks to YouTube autoplays, they’re all competing for one thing: your attention. Harris shares how these companies prey on our psychology for their own profit and calls for a design renaissance in which our tech instead encourages us to live out the timeline we want.
      • Videos
        • Video #1
          Channel :- TED
          Published :- 2017-July-28th
          Link
    • The Ethics and Tricks of Technology (Tristan Harris Pt. 1)
      Tristan Harris (Ex-Design Ethicist at Google) joins Dave Rubin to discuss how technology really affects our lives, why design needs ethics, how apps like Snapchat and Twitter are addicting because of their use of ethical and unethical tricks, and much more
      Published On :- 2017-May-22nd
      Link
    • Bill Maher & Tristan Harris – The Attention Economy
      Tristan and Bill talk about the race to the bottom for attention, and why we desperately need ethical persuasion in technology
      Published On :- 2017-June-6th
      Link

 

Quotes

 

How a handful of tech companies control billions of minds every day

  1. Agree that we are persuadable
  2. Accountable
    • Goals with the persuaded are inline with those that are persuaded
  3. Closing
    • Human Architecture is limited and we have certain boundaries or dimensions in our lives that we want to be honored and respected
    • Naive Nature of Human Preference
    • Nuanced view of human design
    • Our biggest competitor
  4. Persaudability
    • Studied at Persuasive Lab @ Stanford
  5. Tech People
    • No shortage of good intent
    • Classic race for the bottom
    • Lower into emotion

 

Bill Maher & Tristan Harris – The Attention Economy

  1. Attention Economy
    • We need you to use it
    • There is only so much
    • When information gets abundant, the only thing left is time
    • They don’t read anymore
    • I have to go lower on the brain stem
    • I have to confirm your biases
    • We are not their customers; the advertisers are their customers
    • They all need more attention
  2. Myth
    • There is a myth that technology is neutral

 

 

 

SQL Server Audit – Trigger – Metadata – Day 1

Background

Evaluating appropriateness of using Server Triggers.  And, so created one and now trying to make sense of what was captured.

 

Metadata

Definition

In this first post we will look at only a couple of objects.

And, those are:

  1. sys.fn_get_audit_file
    • Link
      Link
    • Columns
      • class_id
      • action_id
  2. sys.dm_audit_class_type_map
    • Returns a table that maps the class_type field in the audit log to the class_desc field in sys.dm_audit_actions
      Link
    • Columns
      • class_type
      • class_type_desc
  3. sys.dm_audit_actions
    • Returns a row for every audit action that can be reported in the audit log and every audit action group that can be configured as part of SQL Server Audit.
      Link
    • Columns
      • action_id
      • class_desc

 

Sample Contents

sys.dm_audit_class_type_map

Query


select *

from   sys.dm_audit_class_type_map tblDACTM

order by

       tblDACTM.class_type

Output

sys.dm_audit_class_type_map

Query

Query- Object

select  *

from   sys.dm_audit_actions tblDAA

where  tblDAA.class_desc in 
	(
	  'OBJECT'
	)

order by
	  tblDAA.[action_id]
	, tblDAA.[name]

 

Output

Query – SERVER AUDIT

select  *

from   sys.dm_audit_actions tblDAA

where  tblDAA.class_desc in 
	(
	  'SERVER AUDIT'
	)

order by
	  tblDAA.[action_id]
	, tblDAA.[name]

Output

 

Sample Query

Query


declare @filename sysname

--please change
set @filename = 'E:\Audit\*.sqlaudit'

SELECT 

      [eventTimeInGMT]
        = event_time

    , [actionID]
		= itvfAF.action_id

	, [actionType]
		= tblDAA.[name]

    , [classType]
		= itvfAF.class_type

	, [classType]
		= tblDACTM.class_type_desc

	, [securableClass]
		= tblDACTM.securable_class_desc

    , [principal]
        = itvfAF.session_server_principal_name 

    , [database]
		= itvfAF.[database_name]

    , [schema]
		= itvfAF.[schema_name]

    , [object]
		= itvfAF.[object_name]

    , [statement]
		= itvfAF.[statement]

    , [additionalInformation]
		= itvfAF.additional_information


	, [bucket]
		= 
			case
				when (tblDAA.[class_desc] = tblDACTM.[securable_class_desc]) 
					then 'securable'

				when (tblDAA.[class_desc] = tblDACTM.[class_type_desc]) 
					then 'class'

				else 'Unknown'

			end

		--, tblDAA.*

FROM sys.fn_get_audit_file 
    (
          @filename
        , default
        , default
    ) itvfAF


INNER JOIN sys.dm_audit_class_type_map tblDACTM

    ON itvfAF.class_type = tblDACTM.class_type

INNER JOIN sys.dm_audit_actions tblDAA

    ON
		(

			-- class type
			(
		 
				(
						itvfAF.[action_id] = tblDAA.[action_id]
					AND tblDAA.[class_desc] = tblDACTM.[securable_class_desc]
				)
				and not exists
				(
					select * 
					
					from   sys.dm_audit_actions tblDAA_Inner 
					
					where  tblDAA_Inner.action_id =  itvfAF.action_id 
				
					and    tblDAA_Inner.class_desc = tblDACTM.class_type_desc
				) 

			)
		
			-- securable class
			or
			(

				(
						itvfAF.[action_id] = tblDAA.[action_id]
					AND tblDAA.[class_desc] = tblDACTM.class_type_desc
				)

			) -- or

		)

order by
	 itvfAF.event_time desc

GO
  

Output

 

 

References

  1. Microsoft
    • SQL Server Security Blog
      • Configuring SQL Audit using the Audit Dynamic Management Views