How to gather anything you want from a system and write it to Log Analytics

A colleague of mine recently reached out to me to ask for a query which would display key performance counters for each known server (items like CPU, free disk space, free memory, total memory). In the first blog post of this series we created a pretty simple query to make that happen but the query did not include the total amount of memory on a system on the total amount of disk space. In the second blog post we showed how we could develop approximate memory totals and disk space totals with a little math. In this blog post we’ll show how we can gather this type of information (and pretty much anything else you can imagine) with a simple script.

This blog post will show examples of how this script can be used to:

  1. Gather total disk space for a computer via WMI
  2. Gather total memory on a computer via WMI
  3. Gather count of CPU’s on a computer via WMI
  4. Gather Exchange queue information
  5. Gather Active Directory user information
  6. Gather properties from SQL

With no further introduction let’s see how we can gather total disk space via a computer using this script and a properly formatted WMI query!

Gathering total disk space for a computer via WMI:

For the first example we are going to gather up the total amount of disk space on a system (since as we discussed in previous blog post this isn’t natively available as a performance counter we can collect). To do this we run the script with the following parameters. For a production environment this would most likely be run once a day or once a week and it would need to occur for all servers which would need this particular performance counter.

.\Write-LogAnalytics.ps1 -InformationType “WMI” -LogName “win32_logicaldisk” -Command “Get-WmiObject -Class Win32_LogicalDisk | Select-Object Name, VolumeName, FileSystem, Description, FreeSpace, Size”

Results:

And the data adds itself nicely to Log Analytics under the custom class name “win32_logicaldisk_CL” which is the LogName specified above with _CL appended to it.

Gathering total memory for a computer via WMI:

For the second example we are going to gather up the total amount memory in a system (since as we discussed in previous blog post this isn’t natively available as a performance counter we can collect). To do this we run the script with the following parameters. For a production environment this would most likely be run once a day or once a week and it would need to occur for all servers which would need this particular performance counter.

$Command = @’

Get-WmiObject CIM_PhysicalMemory | Measure-Object -Property capacity -sum | % {[math]::round(($_.sum / 1GB),2)}

‘@

.\Write-LogAnalytics.ps1 -InformationType “WMI” -LogName “PhysicalMemory” -Command $Command

(The above WMI command was gathered from this blog post)

Results:

And the data adds itself nicely to Log Analytics under the custom class name “PhysicalMemory_CL” which is the LogName specified above with _CL appended to it.

Gathering CPU count for a computer via WMI:

For the third example in this blog post we are going to gather the count of CPU’s and other detailed CPU information for a system. To do this we run the script with the following parameters. For a production environment this would most likely be run once a week in the environment.

.\Write-LogAnalytics.ps1 -InformationType “WMI” -LogName “win32_processor” -Command “Get-WmiObject –class Win32_processor | Select-Object Name,DeviceID,NumberOfCores,NumberOfLogicalProcessors, Addresswidth”

(The above command was gathered from this blog post)

Results:

And the data adds to Log Analytics under the custom class name “win32_processor_CL” which is the LogName specified above with _CL appended to it.

Gathering Exchange queue information

For the fourth example in this blog post we are going to Exchange queue information which also is not natively available as a performance counter we can collect. To do this we run the script with the following parameters. For a production environment this would most likely be run several a day at least during business hours for the Exchange servers in the environment.

.\Write-LogAnalytics.ps1 -InformationType “Exchange” -LogName “ExchangeQueues” -Command “Get-ExchangeServer | ? serverrole -Like *Mailbox* | Get-Queue | select Identity,DeliveryType,Status,MessageCount,NextHopDomain | Sort -Descending MessageCount”

Results:

And the data adds itself to Log Analytics under the custom class name “ExchangeQueues_CL” which is the LogName specified above with _CL appended to it.

 

Gathering Active Directory user information

For the fifth example in this blog post we are going to gather user information from Active Directory. To do this we run the script with the following parameters. For a production environment this would most likely be run once a day or once a week and it would only need to be run from one server in the environment.

$Command = @’

Get-ADUser -SearchBase ‘OU=OUNAME,DC=CONTOSO,DC=COM’ -SearchScope ‘Subtree’ -properties “Created”,”Department”,”Displayname”,”EmailAddress”,”Manager”,”Modified”,”Name”,”Office”,”SamAccountName”,”Title”,”UserPrincipalName” -filter *

‘@

.\Write-LogAnalytics.ps1 -InformationType “AD” -LogName “ActiveDirectory” -Command $Command

Results:

And the data adds itself to Log Analytics under the custom class name “ActiveDirectory_CL” which is the LogName specified above with _CL appended to it.


Gathering properties from SQL

For the final example in this blog post we are going to gather up the properties of a SQL server (this approach can also be used to run any SQL queries would you want). To do this we run the script with the following parameters. For a production environment this would most likely be run once a day or once a week and it would need to occur for all the SQL servers which would need this particular information.

$Command = @’

SELECT SERVERPROPERTY(‘MachineName’) AS [MachineName]

, physical_memory_in_use_kb / 1024 AS [SQLServerMemoryUsageMB]

, locked_page_allocations_kb / 1024 AS [SQLServerLockedPagesAllocationMB]

, large_page_allocations_kb / 1024 AS [SQLServerLargePagesAllocationMB]

, page_fault_count

, memory_utilization_percentage

, available_commit_limit_kb

, process_physical_memory_low

, process_virtual_memory_low

FROM sys.dm_os_process_memory WITH (NOLOCK)

OPTION (RECOMPILE);

‘@

.\Write-LogAnalytics.ps1 -InformationType “SQL” -LogName “SQLProcessSpaceInfo” -Command $Command -Computer “SQL1”

Results:

write it to Log Analytics

And the data adds itself to Log Analytics under the custom class name “SQLProcessSpaceInfo_CL” which is the LogName specified above with _CL appended to it.


Summary: The script provided gives a solid base to gather data which may not be easy to add to Log Analytics in other methods (examples include WMI, SQL, Exchange and AD). This script could definitely gain some improvements including better support for gathering data from remote computers but it should provide a good base for the community to build from. I owe huge thanks to Matt Dowst, Michael Soliz and of course the documentation team who put together the original PowerShell source to write to the Log Analytics API. Feel free to make enhancements or add more samples and post them here or via TechNet galleries. The script is directly available from download here: https://gallery.technet.microsoft.com/PowerShell-script-to-0823e09d

Leave a Reply

x

We use cookies to ensure the best possible experience on our website. Detailed information on the use of cookies on this site is provided in our Privacy and Cookie Policy. Further instruction on how to disable our cookies can be found there.