Crashed Apps

[Josip]

Hello guys,

I am trying to add asset id in this script to see what computer is having chrashes most often, but it gives me an error every time.

Anyone willing to help?

Select Top (100000) tblCrash.CrashedApp,
Count(tblCrash.TimeGenerated) As CrashCount
From (Select SubString(tblNtlogMessage.Message, 28, CharIndex(',',
tblNtlogMessage.Message) - 28) As CrashedApp,
tblNtlog.TimeGenerated
From tblNtlog
Inner Join tblNtlogFile On tblNtlog.LogfileID = tblNtlogFile.LogfileID
Inner Join tblNtlogMessage On
tblNtlog.MessageID = tblNtlogMessage.MessageID
Inner Join tblNtlogSource On tblNtlog.SourcenameID =
tblNtlogSource.SourcenameID
Where tblNtlog.TimeGenerated > DateAdd(day, -14, GetDate()) And
tblNtlogSource.Sourcename = N'Application Error') As tblCrash
Group By tblCrash.CrashedApp
Order By CrashCount Desc

[Josip]

I love you Brandon.

This script is awesome and it's exactly what i was looking for.

[brandon_jones · Accepted solution]

You would be better off querying the event viewer table. Below is a query that will show application crashes within the last 24 hours.

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblNtlog.Eventcode,
tblNtlog.TimeGenerated,
tblNtlogSource.Sourcename,
tblNtlogFile.Logfile,
tblNtlogUser.Loguser,
tblNtlogMessage.Message
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblNtlog On tblNtlog.AssetID = tblAssets.AssetID
Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
tblNtlog.SourcenameID
Inner Join tblNtlogUser On tblNtlogUser.LoguserID = tblNtlog.LoguserID
Inner Join tblNtlogFile On tblNtlogFile.LogfileID = tblNtlog.LogfileID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblNtlog.Eventcode = 1000 And tblNtlog.TimeGenerated > GetDate() - 1 And
tblState.Statename = 'Active'
Order By tblNtlog.TimeGenerated Desc,
tblAssets.Domain,
tblAssets.AssetName

DeviantTomato wrote:
Hello guys,

I am trying to add asset id in this script to see what computer is having chrashes most often, but it gives me an error every time.

Anyone willing to help?

Select Top (100000) tblCrash.CrashedApp,
Count(tblCrash.TimeGenerated) As CrashCount
From (Select SubString(tblNtlogMessage.Message, 28, CharIndex(',',
tblNtlogMessage.Message) - 28) As CrashedApp,
tblNtlog.TimeGenerated
From tblNtlog
Inner Join tblNtlogFile On tblNtlog.LogfileID = tblNtlogFile.LogfileID
Inner Join tblNtlogMessage On
tblNtlog.MessageID = tblNtlogMessage.MessageID
Inner Join tblNtlogSource On tblNtlog.SourcenameID =
tblNtlogSource.SourcenameID
Where tblNtlog.TimeGenerated > DateAdd(day, -14, GetDate()) And
tblNtlogSource.Sourcename = N'Application Error') As tblCrash
Group By tblCrash.CrashedApp
Order By CrashCount Desc