SQL server query lines output

Question

When I execute the following sql query:

select nodename, message from messages, where messages like '%hit%'

I get the following output: (string field):

oracle01 file system /apl/oracleasm hit 93% usage
         file system /apl/oraclears hit 96% usage
         file system /apl/oracleadv hit 97% usage

Is there any way to change my query to get output below ?

oracle01 file system /apl/oracleasm hit 93% usage
oracle01 file system /apl/oraclears hit 96% usage
oracle01 file system /apl/oracleadv hit 97% usage

@AbeMiessler Yeah, I think that I misunderstood the question and those are the results from his query, not an error message — Lamak
– Lamak, Commented May 13, 2013 at 22:30
Do you care sharing your specific query? Based on the details provided no one will be able to help you. — Amit Rai Sharma
– Amit Rai Sharma, Commented May 13, 2013 at 22:30
I´m using sql server. This script will monitoring file systems usage. That´s not an error message: select nodename, message from messages, where messages like '%hit%' — user1658549
– user1658549, Commented May 13, 2013 at 22:36
I believe the message column contains multi-line data. So the original output shown in your question is actually a single row rather than 3 rows. — Amit Rai Sharma
– Amit Rai Sharma, Commented May 13, 2013 at 22:45

Devart · Accepted Answer · 2013-05-15 17:38:18Z

2

Try this one -

Query:

DECLARE @messages TABLE
(
      nodename VARCHAR(50)
    , [message] VARCHAR(100)
)

INSERT INTO @messages (nodename, [message])
VALUES 
('oracle01', 'file system /apl/oracleasm hit 93% usage'),
('',         'file system /apl/oraclears hit 96% usage'),
('',         'file system /apl/oracleadv hit 97% usage'),
('oracle02', 'file system /apl/oracleadv hit 99% usage'),
('',         'file system /apl/oracleadv hit 80% usage')

;WITH cte AS 
(
    SELECT 
          m.nodename
        , m.[message]
        , RowID = ROW_NUMBER() OVER (ORDER BY (SELECT 1))
    FROM @messages m
    WHERE m.[message] LIKE '%hit%' 
)
SELECT
      nodename = 
        CASE WHEN ISNULL(m.nodename, '') = '' 
            THEN (
                SELECT TOP 1 m2.nodename 
                FROM cte m2
                WHERE m2.RowID - 1 < m.RowID
                    AND ISNULL(m2.nodename, '') != ''
                ORDER BY m2.RowID DESC
            ) 
            ELSE m.nodename 
        END
    , [message]
FROM cte m

Output:

nodename    message
----------- ------------------------------------------
oracle01    file system /apl/oracleasm hit 93% usage
oracle01    file system /apl/oraclears hit 96% usage
oracle01    file system /apl/oracleadv hit 97% usage
oracle02    file system /apl/oracleadv hit 99% usage
oracle02    file system /apl/oracleadv hit 80% usage

Update:

DECLARE @messages TABLE
(
      nodename VARCHAR(50)
    , [message] VARCHAR(500)
)

INSERT INTO @messages (nodename, [message])
SELECT 'oracle01', 
'file system /apl/oracleasm hit 93% usage
file system /apl/oraclears hit 96% usage
file system /apl/oracleadv hit 97% usage'

SELECT nodename + ' ' + REPLACE([message], CHAR(10), nodename + ' ')
FROM @messages

Output for update:

oracle01 file system /apl/oracleasm hit 93% usage
oracle01 file system /apl/oraclears hit 96% usage
oracle01 file system /apl/oracleadv hit 97% usage

Update for comment:

SELECT errormessage = 

    n.caption + ' ' + 
    n.ambiente + ' ' + 
    REPLACE(ccs.errormessage, CHAR(10), n.caption + ' ' + n.ambiente + ' ')

FROM dbo.APM_CurrentComponentStatus ccs WITH (NOLOCK) 
JOIN dbo.APM_Application a WITH (NOLOCK) ON ccs.ApplicationID = a.ID 
JOIN dbo.Nodes n WITH (NOLOCK) ON a.NodeID = n.NodeID 
WHERE ccs.ErrorMessage LIKE '%hit%' 
    AND n.ambiente IN ('homologação', 'desenvolvimento')

Is there any way to sort output by MB available ascending ?

DECLARE @table TABLE 
(
      nodename VARCHAR(50)
    , [message] VARCHAR(500)
)

INSERT INTO @table (nodename, [message])
SELECT 'oracle01', 
'FS /bd1/devsdata/logs1 hit 93% usage -> 742 MB available 
FS /bd1/devsdata/logs1 hit 98% usage -> 542 MB available 
FS /bd1/devsdata/tmp hit 99% usage -> 793 MB available'

SELECT *
FROM (
    SELECT 
          d.nodename
        , [message] = p.value('(.)[1]', 'VARCHAR(500)')
    FROM (
        SELECT 
              t.nodename
            , kxml = CAST('<r><s>' + REPLACE(t.[message], CHAR(10), '</s>' + '<s>') + '</s></r>' AS XML)  
        FROM @table t
    ) d
    CROSS APPLY kxml.nodes('/r/s') t(p)
) d
ORDER BY d.nodename, SUBSTRING([message], CHARINDEX('-> ', [message]) + 3, CHARINDEX('MB', [message]) - CHARINDEX('-> ', [message]) - 3)

Output:

nodename    message
----------- ---------------------------------------------------------
oracle01    FS /bd1/devsdata/logs1 hit 98% usage -> 542 MB available 
oracle01    FS /bd1/devsdata/logs1 hit 93% usage -> 742 MB available 
oracle01    FS /bd1/devsdata/tmp hit 99% usage -> 793 MB available

edited May 15, 2013 at 17:38

answered May 14, 2013 at 5:57

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

Amit Rai Sharma Over a year ago

You sample code assume that nodetext is empty for some message. However that's not the case. Message Column can have multi-line data. I did update the question to include this information but somehow it was rejected by someone. Please check the comment list in the question which states this.

user1658549 Over a year ago

Hi Masters, Follow query that I´m using: Select n.caption as server, n.ambiente as environment,ccs.errormessage FROM APM_CurrentComponentStatus ccs WITH (NOLOCK) INNER JOIN APM_Application a WITH (NOLOCK) ON ccs.ApplicationID = a.ID INNER JOIN Nodes n WITH (NOLOCK) ON a.NodeID = n.NodeID where ccs.ErrorMessage LIKE '%hit%' and (n.ambiente='homologação' or n.ambiente='desenvolvimento') server environment errormessage server01 Development FS /bd1/devsdata/logs1 hit 93% usage -> 742 MB available FS /bd1/devsdata/tmp hit 99% usage -> 793 MB available

user1658549 Over a year ago

Thanks so much for your job ! Is there any way to change this query to show me data like a table ouput ? Server FS %used MB Available server01 /bd1/teindata/logs1 93 742

Devart Over a year ago

SSMS do not correct supported line breaks in results window. As alternatively can try our product in which implemented this feature - devart.com/dbforge/sql/studio

user1658549 Over a year ago

Is there any way to sort output by MB available ascending ?

|

Collectives™ on Stack Overflow

SQL server query lines output

1 Answer 1

14 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Related