Substring Charindex Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Substring Charindex Help

Author

Topic

dimepop
Starting Member

33 Posts

Posted - 2012-08-21 : 07:13:27

Hi, i have a query that returns a server name and a message.
Server Message
------ -------------
Server1 Failed events: Error due to 5 pings errors. Destination unreachable
Server2 can not be resolved due to DNS error. IP Address not resolving.
Server3 Failed events: Error due to 8 pings errors. Destination unreachable

I have amended the query so it returns only "x pings error" using substring and charindex.
The problem is that with Server 2 the conditions are not met and the query fails


Select
     UPPER(displayName) AS Devices, MAX(SUBSTRING(message, LEN('to') + 28, CHARINDEX(' failures ', message) - LEN('to') - 19)) AS Pings
From
  eventlogevents
Where
  eventlogevents.type = 2
GROUP BY UPPER(displayName)

How can i do so if the message is different it selects only the text "DNS error" ?
Can i add another substring??
Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-21 : 07:29:52

>> I have amended the query so it returns only "x pings error" using substring and charindex
Have you?
It looks like that will return the max value starting from character 30 - which will depend on the error message.
note len('to') = 2 and I don't see the text ' failures ' in your data.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dimepop
Starting Member

33 Posts

Posted - 2012-08-21 : 07:33:42

Hi, thanks for your reply.
The message i wrote it is an example.
The message actual is this:
"Failed events: Error due to 10 pings failures (threshold is 5). 10 pings failed because the destination was unreachable. "
The problem is that i have rows with a different message and i need a different substring for that.
"Failed events: Server1 could not be resolved to an IP address. "

Regards

quote:
Originally posted by nigelrivett

>> I have amended the query so it returns only "x pings error" using substring and charindex
Have you?
It looks like that will return the max value starting from character 30 - which will depend on the error message.
note len('to') = 2 and I don't see the text ' failures ' in your data.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-21 : 07:37:18

What is the text that you want out of the strings?
And do you just want one per server?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dimepop
Starting Member

33 Posts

Posted - 2012-08-21 : 07:51:48

Hi,
Each server can have a different message depending on the error state they are:
So for Example for:
Server Message
------ --------
Server1 "Failed events: Error due to 10 pings failures (threshold is 5). 10 pings failed because the destination was unreachable. "

I want to return ( which i already have working with the query i sent you):

Server1 10 pings failures

The problem is that if the server message contains the text:
"Failed events: Server2 could not be resolved to an IP address. "

I want to return:

Server2 not resolved
Or it could be replaced by the text "DNS Failure"
Whatever is easier

Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-21 : 09:29:26

Something like

select Server,
case when message like 'Failed events: Error due to % pings failures %'
then substring(message, charindex(' to ',message) + 4), charindex(' pings ',message) - charindex(' to ',message)-4) + ' pings failures'
case when message like 'can not be resolved due to DNS error %'
then 'not resolved'
else 'unknkown failure'
end
from eventlogevents

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dimepop
Starting Member

33 Posts

Posted - 2012-08-21 : 10:50:54

Hi nigelrivett, i didn't know abou the 'CASE WHEN'
I will try to amend my query to see if it works.
thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-21 : 11:37:46

It's like an if in excel (or iif).

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dimepop
Starting Member

33 Posts

Posted - 2012-08-22 : 09:05:20

I got it working now.
Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources