Linux: Logfiles, timestamps and datematching

I recently came across someting at work that had me bothered for a while. A customer wanted to check some logfiles, where this one specific line is stamped ever so often. If the line is NOT stamped in a timely matter, they wanted to be notified.

Seems simple, right? Well, for me it wasn’t that easy. I rarely do scripts in bash – and when I do, I usually use a lot of time on something that would have taken me five minutes to write in powershell.

Nevertheless, here is what I came up with. Note that this most definitely can be done in simpler and better ways, but this works for me.

So. The logfile is huge, but the guys that “owns” it, only wanted to be notified when a certain string wasn’t beeing stamped in a timely manner.
The string in question is beeing stamped every minute – meaning, a threshold of not beeing written to in the last 10 minutes would do the trick here.

Example of the string we are interested in:

10:24:00,002 DEBUG [] (EJB default - 4) Starting Scheduled Tasks Job

To manage this, we will be using cat, grep, tail, and awk.

cat to read the logfile
grep to grab the string we are looking for
tail to fetch the last occurcance of this string
awk to fetch the timestamp, which is in the beginning of the string, and split on comma in this case, to only fetch the actual time.
date to do some dateformat-juggeling.


# var
logcheck=$( cat $logname | grep 'Starting Scheduled Tasks Job' | tail -1 | awk -F ',' '{print $1}' )
logdate=$( date -d $logcheck +%H:%M:%S )
nowminus10=$( date -d "-10 minutes" +%H:%M:%S )
nowplus10=$( date -d "+10 minutes" +%H:%M:%S )
now=$( date +%H:%M:%S )

# construct
if [[ "$logdate" > "$nowminus10" && "$logdate" < "$nowplus10" ]];
	echo "HEALTHY: The logfile is beeing written to in a timely manner."
	echo "ERROR: The logfile is not beeing written to in a timely manner. The time now: $now. Last timestamp in log: $logdate."

This just echoes out the result – it is up to you to do something useful with the script.

In my case, I use this with SCOM – more on this in the next article.


Powershell: Create Event with parameters

This function will let you stamp events to the Windows EventLog, and feed the event with filterable parameterdata (which in the cases you use SCOM to sniff events, is pretty awesome).

I use this all the time in cases where a script should dump some kind of result to the eventlog, and using SCOM to fetch the event and trigger on special texts or values in the parameterdata returned.

function createParamEvent ()
  Function for creating events, just like create-event or eventcreate.exe - but with the added functionality to add up to 5 filterable parameters.
  The function stamps Windows Events to the Windows Eventlog by your choice, but can also be fed up to 5 different parameters, where param1 contains the basic Event Description, and param2 to param5 contains data of your choosing.
  For Information events, use eventID 220:
  CreateParamEvent -source "TestSource" -evtID 220 -param1 "The server $hostname shut down at $timestamp" -param2 $hostname -param3 $timestamp -param4 "Some generic text"
  For Warning events:
  CreateParamEvent -source "TestSource" -evtID 221 -param1 "The server $hostname shut down at $timestamp" -param2 $hostname -param3 $timestamp -param4 "some generic text"
  For Error events, with the manditory param1 set. All parameters from param2 to param5 are not manditory:
  CreateParamEvent -source "TestSource" -evtID 222 -param1 "The server $hostname shut down at $timestamp"
  Alle andre eventID'er vil logges som information events.
  Mandatory: The logic in this function is based on the principle where the sample eventIDs (222, 221, 220) will throw an error corresponding to the Event Type (error, warning, information). Other EventIDs can be used, but will then be logged as an Information Event.
  .PARAMETER param1
  Mandatory: The full description in the event.
  .PARAMETER param2-5
  Use theese parameters to add additional useful information to the mix, for example additional information that can be pulled from the Event in SCOM.
  - Param1 = Full description in the event
  - Source is mandetory
  - EventID is mandetory
  - Eventid 222 = Error event
  - Eventid 221 = Warning event
  - Eventid 220 = Information event
  - Param2 to 5 are optional.
    #Define the event log
    $evtlog = "Application"

    #Load the event source to the log if not already loaded.  This will fail if the event source is already assigned to a different log.
    if ([System.Diagnostics.EventLog]::SourceExists($source) -eq $false) {
        [System.Diagnostics.EventLog]::CreateEventSource($source, $evtlog)

    if ($evtID -eq 222){
    $id = New-Object System.Diagnostics.EventInstance($evtID,1,1); #ERROR EVENT
    elseif ($evtID -eq 221){
    $id = New-Object System.Diagnostics.EventInstance($evtID,1,2); #WARNING EVENT
    $id = New-Object System.Diagnostics.EventInstance($evtID,1); #INFORMATION EVENT
    $evtObject = New-Object System.Diagnostics.EventLog;
    $evtObject.Log = $evtlog;
    $evtObject.Source = $source;
    $evtObject.WriteEvent($id, @($param1,$param2,$param3,$param4,$param5))


Datawarehouse Database Cleanup SQL query

IMPORTANT: Always perform a FULL Backup of the database before doing anything to it !!!

This article applies to SCOM 2007, 2012 as well as 2016 (haven’t tested 1807 yet).

Somtimes you may have event storms where you end up having old entries in the Data Warehouse database i.e data that is older than the grooming threshold. This may happen because the grooming processes can’t keep up because they run on a regular interval but only delete a fixed number of rows per run.

The following SQL query may also be valuable in case you end up with the issue of SQL Timeouts from the Data Warehouse database when the StandardDataSetMaintenance stored procedure is executed by the RMS.

More on that issue here:

To check if this is the case for you, run this SQL Query on the Data Warehouse database:

DECLARE    @MaxDataAgeDays INT,    @DataSetName NVARCHAR(150) 
SET @DataSetName = 'Event' 
SELECT @MaxDataAgeDays = MAX(MaxDataAgeDays) 
FROM StandardDatasetAggregation 
WHERE DatasetId = (    
SELECT DatasetId    
FROM StandardDataset    
WHERE SchemaName = @DataSetName ) 
FROM EventCategory 
WHERE LastReceivedDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE()) 
FROM EventChannel 
WHERE LastReceivedDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE()) 
FROM EventLoggingComputer 
WHERE LastReceivedDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE()) 
FROM EventPublisher 
WHERE LastReceivedDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE()) 
FROM EventUserName 
WHERE LastReceivedDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE()) 
FROM ManagedEntityProperty 
WHERE ToDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE()) 
FROM RelationshipProperty 

Now if you get any results here it means that you are experiencing the issue. So you might want to clean these up manually to help out SCOM.

So execute this SQL Query on the Data Warehouse database to clean the old entries

DECLARE    @MaxDataAgeDays 
INT,    @DataSetName 
SET @DataSetName = 'Event' 
SELECT @MaxDataAgeDays = MAX(MaxDataAgeDays) 
FROM StandardDatasetAggregation 
WHERE DatasetId = (    
SELECT DatasetId    
FROM StandardDataset    
WHERE SchemaName = @DataSetName ) 
DELETE EventCategory 
WHERE LastReceivedDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE()) 
DELETE EventChannel 
WHERE LastReceivedDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE()) 
DELETE EventLoggingComputer 
WHERE LastReceivedDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE()) 
DELETE EventPublisher
WHERE LastReceivedDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE()) 
DELETE EventUserName 
WHERE LastReceivedDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE()) 
DELETE ManagedEntityProperty 
WHERE ToDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE()) 
OPTION(RECOMPILE) DELETE RelationshipProperty 

After running this query you will hopefully experience better performance.

Thanks to for initially writing this article.

Update: SCOM: October 2016 patch makes your Console crash

Awesome Microsoft, way to go with QA when you make your own core products crash…


It seems that the bundled October patch for Windows Server 2008x and 2012x makes the SCOM Console crash when viewing different state views.
The patches mentioned:

Server 2008 –
Server 2012 –

After uninstalling theese in my environment, the Console started working again.

This has now been officially aknowlegded, and MS is working on a solution:

Follow the MOMTeam blog for more info on when the fix will arrive:


The product group released a hotfix for this issue:


– F

SCOM: DeltaSynchronization Error

This error appeared in out environment recently, and didn’t go away until we changed some configuration settings in a config file on all the management servers.


  • EventID 29181 in the OpsMgr eventlog.
  • Newly pushed agents show up as “Not Monitored”
  • Changes you do to a Management Pack, ex. overriding a rule, does not work.
  • Discovery of new objects is insanely slow, or does not work all together.

Example Event: 

Log Name:      Operations Manager
Source:        OpsMgr Management Configuration
Event ID:      29181
Level:         Error
User:          N/A
OpsMgr Management Configuration Service failed to execute ‘DeltaSynchronization’ engine work item due to the following exception
Microsoft.EnterpriseManagement.ManagementConfiguration.DataAccessLayer.DataAccessOperationTimeoutException: Exception of type ‘Microsoft.EnterpriseManagement.ManagementConfiguration.DataAccessLayer.DataAccessOperationTimeoutException’ was thrown.
at Microsoft.EnterpriseManagement.ManagementConfiguration.DataAccessLayer.DataAccessOperation.ExecuteSynchronously(Int32 timeoutSeconds, WaitHandle stopWaitHandle)
at Microsoft.EnterpriseManagement.ManagementConfiguration.SqlConfigurationStore.ConfigurationStore.ExecuteOperationSynchronously(IDataAccessConnectedOperation operation, String operationName)
at Microsoft.EnterpriseManagement.ManagementConfiguration.SqlConfigurationStore.ConfigurationStore.WriteConfigurationDelta(IConfigurationDeltaDataSet dataSet)
at Microsoft.EnterpriseManagement.ManagementConfiguration.Engine.DeltaSynchronizationWorkItem.TransferData(String watermark)
at Microsoft.EnterpriseManagement.ManagementConfiguration.Engine.DeltaSynchronizationWorkItem.ExecuteSharedWorkItem()
at Microsoft.EnterpriseManagement.ManagementConfiguration.Interop.SharedWorkItem.ExecuteWorkItem()
at Microsoft.EnterpriseManagement.ManagementConfiguration.Interop.ConfigServiceEngineWorkItem.Execute()


We went as far as opening a support case with Microsoft.
In the end, we managed to fix the problem, changing some timeoutvalues in the Config Service configfile on each and every Management Server.

Check workitem duration:

First of all, run this query against the OperationsManager database to check if you see errors in the WorkItemState table;

SELECT * FROM cs.WorkItem WHERE WorkItemName = ‘DeltaSynchronization’

If you see alot of 10’s and not all 20’s, you have a problem with syncronization.
The states are:
WorkItemStateId WorkItemStateName
1 Running
10 Failed
12 Abandoned
15 Timed out
20 Succeeded

How to fix the issue:

You may stumble upon this support tip if you did what we did – google’d the crap out of the problem:

This provides most people with a fix to the problem, but we had to change another parameter to get stuff working again.
In addition to thanging the <Category Name=”Cmdb”> TimeoutSeconds values, we needed to change the <Category Name=”ConfigStore”> TimeoutSeconds values.

Our config now looks like this:

<Category Name=”Cmdb”>
<OperationTimeout DefaultTimeoutSeconds=”300″>
<Operation Name=”GetEntityChangeDeltaList” TimeoutSeconds=”300″ />

<Category Name=”ConfigStore”>
<OperationTimeout DefaultTimeoutSeconds=”300″>

After changig this on EVERY management server, and restarting the Config Service, things started to work again.

SCOM: Get Subscriber in Subscription

This little thing will search through, and get all subscribers containing your searchword in any subscription you may have set up in SCOM.


$sub = Get-SCOMNotificationSubscription | select displayname,torecipients

write-host "Searchword: ""$searchword"" exists in the following subscriptions:"
foreach ($s in $sub){
    $recipient = $
    if ($recipient -like "$searchword"){
    write-host ""$s.displayname""

– F

SCOM: Get Maintenance Mode History

I use this query whenever I need to investigate Maintenace Mode history for SCOM agents.

USE OperationsManagerDW
SELECT ManagedEntity.DisplayName, MaintenanceModeHistory.*
FROM ManagedEntity WITH (NOLOCK) 
MaintenanceMode ON ManagedEntity.ManagedEntityRowId = MaintenanceMode.ManagedEntityRowId 
MaintenanceModeHistory ON MaintenanceMode.MaintenanceModeRowId = MaintenanceModeHistory.MaintenanceModeRowId
where DisplayName Like '%SERVERNAME%'
order by ScheduledEndDateTime

– F

Useful SQL queries for OpsMgr DB

Here are some of the SQL scripts I usually use in case of… whatever.
Many of these must be credited to Kevin Holman

Set ALL agents to Remotely Managable

UPDATE MT_HealthService 
SET IsManuallyInstalled=0 
WHERE IsManuallyInstalled=1

Get Agents not Remotely Managable

select bme.DisplayName from MT_HealthService mths 
INNER JOIN BaseManagedEntity bme on bme.BaseManagedEntityId = mths.BaseManagedEntityId 
where IsManuallyInstalled = 1

Set Agent Remotely Managable

UPDATE MT_HealthService 
SET IsManuallyInstalled=0 
WHERE IsManuallyInstalled=1 
AND BaseManagedEntityId IN 
(select BaseManagedEntityID from BaseManagedEntity 
where BaseManagedTypeId = 'AB4C891F-3359-3FB6-0704-075FBFE36710' 
AND DisplayName = '-- Servername Here --')

Get Unix Duplicates (Run if the *Nix agent view in console fails)

DECLARE @NeededTypeName NVARCHAR(256)
DECLARE @ManagedTypeIdForManagedEntitiesByManagedTypeAndDerived UNIQUEIDENTIFIER
SET @NeededTypeName = 'Microsoft.Unix.OperatingSystem'
SET @ManagedTypeIdForManagedEntitiesByManagedTypeAndDerived = (
SELECT ManagedTypeId
FROM ManagedType
WHERE TypeName = @NeededTypeName

NULL AS SourceEntityId

FROM dbo.ManagedEntityGenericView
SELECT DISTINCT [BaseManagedEntityId]
FROM dbo.[TypedManagedEntity] TME WITH(NOLOCK)
JOIN [dbo].[DerivedManagedTypes] DT
ON DT.[DerivedTypeId] = TME.[ManagedTypeId]
DT.[BaseTypeId] = @ManagedTypeIdForManagedEntitiesByManagedTypeAndDerived AND
TME.IsDeleted = 0
) AS ManagedTypeIdForManagedEntitiesByManagedTypeAndDerived
ON ManagedTypeIdForManagedEntitiesByManagedTypeAndDerived.[BaseManagedEntityId] = [Id]
[IsDeleted] = 0 AND
[TypedMonitoringObjectIsDeleted] = 0 AND
[ManagedEntityGenericView].[Path] IN (
SELECT [BaseManagedEntity].[Path]
FROM [BaseManagedEntity]
GROUP BY [BaseManagedEntity].[Path]
HAVING COUNT([BaseManagedEntity].[Path]) > 1

ORDER BY [ManagedEntityGenericView].[Path]

Remove *NIX Duplicates

DECLARE @TypedManagedEntityId uniqueidentifier
SET @TimeGenerated = GETUTCDATE()
SELECT TME.TypedManagedEntityId
FROM TypedManagedEntity TME

OPEN EntitiesToBeRemovedCursor
FETCH NEXT FROM EntitiesToBeRemovedCursor
INTO @TypedManagedEntityId
EXEC @LastErr = [p_TypedManagedEntityDelete] @TypedManagedEntityId, @TimeGenerated

IF @LastErr <> 0
FETCH NEXT FROM EntitiesToBeRemovedCursor
INTO @TypedManagedEntityId
CLOSE EntitiesToBeRemovedCursor

DEALLOCATE EntitiesToBeRemovedCursor


Get Alerts where CustomField1 is used, and ResolutionState is 1

select  alertname, alertdescription, statesetbyuserid, resolutionstate, statesetdatetime, severity, repeatcount, ars.alertguid, customfield1
from Alert.vAlertResolutionState ars 
join alert.valert alt on ars.alertguid = alt.alertguid
join alert.valertdetail on ars.AlertGuid = vAlertDetail.AlertGuid
where ResolutionState like '1' AND customfield1 IS NOT NULL
order by statesetdatetime

Get the reason why your agent was gray

HSO.StartDateTime AS OutageStartDateTime,
DATEDIFF (DD, hso.StartDateTime, GETDATE()) AS OutageDays,
DS.Name AS ReasonString
FROM  vManagedEntity AS ME
INNER JOIN     vHealthServiceOutage AS HSO ON HSO.ManagedEntityRowId = ME.ManagedEntityRowId
INNER JOIN     vStringResource AS SR ON HSO.ReasonCode =
REPLACE(LEFT(SR.StringResourceSystemName, LEN(SR.StringResourceSystemName)
- CHARINDEX('.', REVERSE(SR.StringResourceSystemName))), 'System.Availability.StateData.Reasons.', '')
INNER JOIN     vDisplayString AS DS ON DS.ElementGuid = SR.StringResourceGuid
WHERE (SR.StringResourceSystemName LIKE 'System.Availability.StateData.Reasons.[0-9]%')
AND DS.LanguageCode = 'ENU'
AND ME.FullName like '% SERVERNAME HERE %'
ORDER BY OutageDays

– F

SCOM: Command Channel Script

This little script is what I use in cooperation with the SCOM Command Channel to parse and send SCOM alerts to a logfile.
The script will take the SCOM alert parameters and put them neatly in a .log file, one file for each alert I want. This, of course, is customizable – I just like to see the amount of files being generated.

The Channel:


The above channel parameters:

Path: C:\windows\system32\WindowsPowershell\v1.0\powershell.exe
Cmd: -file “C\:Script\AlertExport.ps1” “$Data[Default=’Not Present’]/Context/DataItem/AlertId$##$Data[Default=’Not Present’]/Context/DataItem/AlertName$##$Data[Default=’Not Present’]/Context/DataItem/AlertDescription$##$Data[Default=’Not Present’]/Context/DataItem/EntityPath$##$Data[Default=’Not Present’]/Context/DataItem/EntityDisplayName$”
Startdir: c:\

This will get you the following (of MANY) Alert details: AlertID, Alert Name, Alert Description, Path and Displayname.

The Script:

# Alert Params
$params = $parameters.split('##') | ? {$_ -ne ''}

# Build params (added commented blocks of Alert parameter data for your convenience)
$AlertID = $params[0] # $Data/Context/DataItem/AlertID$
$AlertName = $params[1] # $Data/Context/DataItem/AlertName$
$AlertDesc = $params[2]# $Data/Context/DataItem/AlertDescription$
$Path = $params[3] # $Data/Context/DataItem/ManagedEntityPath$
$DisplayName = $params[4] # $Data/Context/DataItem/ManagedEntityDisplayName$

# Sharestuff
$date = get-date
$Alertfile = "SCOM Alert - $(get-date -Format "dd.MM.yyyy").log"

# Format AlertMessage
$AlertMessage = @()

$AlertMessage += "ID: $AlertID"
$AlertMessage += "Date: $date"
$AlertMessage += "DisplayName: $DisplayName"
$AlertMessage += "AlertName: $AlertName"
$alertMessage += "Path: $Path"
$AlertMessage += "Description: $AlertDesc"

# Output alert to textfile
$AlertMessage >> $share$alertfile

Put the script on all your Management Servers, in this case in the C:\script folder, and suddenly, if all goes well, your selected Alerts will start pumping out to this share.


– F

Request Template Certificates using CertReq and Powershell

Installing SCOM agents on non-domain servers can be a real time-consuming affair, especially the Create template-request-export-import-certificates procedure.
I decided to use my basic certificate and CertReq knowlegde to create this little script that helps me automate the whole thing.

NOTE: This is meant for inspiration only. If will for the most part not work in your environment unless you heavily modify it.

Basically it requests a new certificate from your CA server, based on a predefined Template.
It then works some magic, and you are left with the *.pfx certificate file with private key, necessary for the SCOM agent on the non-domain server to communicate with the rest of the SCOM environment.

In the end, the certificate can be copied to the non-domain server, and import it using MOMCertImport found in the SCOM Agent Support Tools folder.

#$password = Read-Host -Prompt "Enter Password" -AsSecureString
$server = read-host -Prompt "Enter FQDN for DMZ Server"

$CERTPATH = "Location you want to save Certificate to"
$CAFQDN = ""
$CASERVER = "CAserver.domaint.netIssuing CA1 example"

write-host "Variables set. Continue to create .inf"  -foregroundcolor green

write-host "Generating Certificate INF File..."
$certinf = @"
ProviderName="Your Provider"
CertificateTemplate=CA Template Name

$certinf >> "$CERTPATH$server.inf"

write-host ".inf created. Continue to create .req file"  -foregroundcolor green

CertReq.exe -new "$CERTPATH$server.inf" "$CERTPATH$server.req"

write-host ".req created. Checking to see of files exist"  -foregroundcolor green

$testinf = Test-Path "$CERTPATH$server.inf"
$testreq = Test-Path "$CERTPATH$server.req"

if ($testinf -eq $true){
write-host "$CERTPATH$server.inf successfully generated." -foregroundcolor green
else {
write-host "$CERTPATH$server.inf could not be found. Check for errors." -ForegroundColor Red
if ($testreq -eq $true){
write-host "$CERTPATH$server.req successfully generated." -foregroundcolor green
else {
write-host "$CERTPATH$server.req could not be found. Check for errors." -ForegroundColor Red

write-host "Submitting new Certificate for $server"

CertReq -Submit -config "CAserver.domaint.netIssuing CA1 example" "$CERTPATH$server.req" "$CERTPATH$server.cer"

write-host "Importing .cer"

certreq -accept "$CERTPATH$server.cer"
write-host "All OK. Continue"  -foregroundcolor green

#Exporting certificate with Private Key
write-host "exporting shit with private key"
certutil -exportpfx -p "YOUR CERTIFICATE PASSWORD" my "$server" "$certpath$server.pfx" "nochain" 

Move-Item -Path "$CERTPATH*cer","$CERTPATH*inf","$CERTPATH*req" -Destination "$CERTPATHold"

And there you have it. The task that normally was done in 10-20 minutes is now done in 10-20 seconds.
I also have a script that copies SCOM agent files to the non-domain server, installs the SCOM agent based on bit-architechture, imports the certificate using MOMCertImport.exe and restarts the Microsoft Monitoring Agent, but this was not written by me, and I don’t remember where I found it (or who to credit) – so if you want it, hit me up in the comment section, and I’ll send it to you.

– F