Solved

Need to search the eventid in the column and need to give a appropriate name

Posted on 2012-03-10
20
585 Views
Last Modified: 2012-06-27
I have column called ticketdesc where it contain may information, I tried using charindex function, the problem using this function is ,I am not able to find proper start place.There are around 1 millions types of ticket description,so it's hard to analysis.
I like to have the SQL query,where it will search for a keyword of event id's alone and need to group as per the events Id's and need to give proper name.

Expected Output:
Problem               total
Back up Issue	50
Mirroring 		10
.
.
.
.
.
.
so on
Sample-colum.txt
0
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 7
20 Comments
 
LVL 42

Expert Comment

by:sedgwick
ID: 37706388
i didn't quite understand your requirement.
you want to loop through the information in this column and group all the entries based on keyword event id.

for instance, all the entries in the column which has the word "database" in it, group them under proper name and return it back.

is that what u mean?
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 37706390
do u have mapping table which maps the keyqord to look and the proper name you with to apply?

for example:
Table: MappingNames
Keyword | Title
-----------------------------
database | DB Issues
network | networking Issues

and so on.
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 37706666
No we don't have mapping table.

If the eventid Event1479 then it's mirroring issue.
Keyword will be given by us

Like from tale if i get the o/p like
column            count
eventid 1479     2
evebtid 3041   10

the final output will be

column                count           ---------------mirroring&backup.. so on will be given by us.
mirroring issue     2
backe up failed    10

I will analysis the eventid's and will give own caption for that .
0
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
LVL 42

Expert Comment

by:sedgwick
ID: 37706676
can we assume that "Event+number" keyword exists only once in each description in ticketdesc table?

cause i was thinking to use regex to find the event and id and use it to group them.
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 37706927
Yes it will occur only once in the ticket
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 37734322
Hello Experts,
Can anyone look into this question?
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 37749558
@admin:I don't see any improvement in this question
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 37754502
Here is an example, using PATINDEX(). Relies on '.' following event ID number.

;WITH tickets(Ticketdesc) AS (
    SELECT 'ShortDesc: Event1479.MSSQLServer.SQLServer.Themirroringconneconh...' UNION ALL
    SELECT 'Event1454.MSSQLSERVER.Applicaon..MSSQLSERVER...' UNION ALL 
    SELECT 'Event1454.MSSQLSERVER.Applicaon.SQLServer2K8.DBMPAGEU...' UNION ALL
    SELECT 'Event17063.MSSQLSERVER.Applicaon..17063Gener...' UNION ALL
    SELECT 'Control error.'
)
SELECT EventID=STUFF(LEFT(Eventdesc, PATINDEX('%[.]%', Eventdesc)-1), 6, 0, ' ')
     , CountOfEvents=COUNT(*)
FROM (
    SELECT Eventdesc=SUBSTRING(Ticketdesc, PATINDEX('%Event[0-9]%', Ticketdesc), 15)
    FROM tickets
    WHERE PATINDEX('%Event[0-9]%', Ticketdesc) > 0
) derived
GROUP BY LEFT(Eventdesc, PATINDEX('%[.]%', Eventdesc)-1)
;

Open in new window


Once you understand the concept, you can probably make this more optimized and less dependent on specific content; however, it may get complicated with nested parsing or derived queries unless you build a function that that grabs the string starting from PATINDEX('%Event[0-9]%', Ticketdesc)+5 (to skip past 'Event') and takes one character at a time until it reaches an instance of a character that is LIKE '[^0-9]' or ISNUMERIC({the character}) = 0. Once this is reached, the function can exit, returning the Event ID.

I hope that helps!

PATINDEX MSDN: http://msdn.microsoft.com/en-us/library/ms188395.aspx
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 37816203
i am getting the result as below

Event 1200      3
Event 1204      2
Event 1216      6
Event 17063      29
Event 18204      15
Event 208      18
Event 3041      24
Event 9002      3

now how can i give the substitute name instead of event id's ?
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 37817560
do u have a table which maps event id to issue name or something like that?
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 37818532
nope, I have to give them a name in a hard code. If none of the code matches then it should go to unknown caption.
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 37820674
so why not create another table in which u map the event id to issue name or description?
and in your sql query get the issue name via the event id.
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 37820778
No this production Database, I won't be able to modify any chances :(

Okay , is it possible to get issues instead of Eventid?
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 37820963
where are the issues names are located?
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 37821054
It's present in the ticketdesc table

eg : ShortDesc: Event1479.MSSQLServer.SQLServer.Themirroringconneconh

You can see the attachment.

Insteadof getting eventid which you posted on ID: 37754502

It could be better if get "MSSQLServer.SQLServer.Themirroringconneconh..."

and group them by title name
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 37821239
can u post the sql query here?
i'll make the change on top of that.
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 37822416
;WITH tickets(Ticketdesc) AS (
    SELECT 'ShortDesc: Event1479.MSSQLServer.SQLServer.Themirroringconneconh...' UNION ALL
    SELECT 'Event1454.MSSQLSERVER.Applicaon..MSSQLSERVER...' UNION ALL
    SELECT 'Event1454.MSSQLSERVER.Applicaon.SQLServer2K8.DBMPAGEU...' UNION ALL
    SELECT 'Event17063.MSSQLSERVER.Applicaon..17063Gener...' UNION ALL
    SELECT 'Control error.'
)
SELECT EventID=STUFF(LEFT(Eventdesc, PATINDEX('%[.]%', Eventdesc)-1), 6, 0, ' ')
     , CountOfEvents=COUNT(*)
FROM (
    SELECT Eventdesc=SUBSTRING(Ticketdesc, PATINDEX('%Event[0-9]%', Ticketdesc), 15)
    FROM tickets
    WHERE PATINDEX('%Event[0-9]%', Ticketdesc) > 0
) derived
GROUP BY LEFT(Eventdesc, PATINDEX('%[.]%', Eventdesc)-1)
;
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 37831424
Kindly Look into this problem
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question