cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Josip
Engaged Sweeper II
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
1 ACCEPTED SOLUTION
brandon_jones
Champion Sweeper III
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


View solution in original post

2 REPLIES 2
Josip
Engaged Sweeper II
I love you Brandon.

This script is awesome and it's exactly what i was looking for.
brandon_jones
Champion Sweeper III
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