SWQL/ SQL group and combining multiple result in one field into single result

Question

Is there any way to achieve such things in the expectation result below ?

saw this example , but not sure to applied since there are many "JOIN" here.

Current original Statement:

SELECT TOP 1000 n.NodeID,t.Name as tagName ,le.LogEntryID,  le.DateTime, MessageDateTime
FROM Orion.OLM.LogEntry le
JOIN Orion.OLM.LogEntryTagAssignment leta on leta.LogEntryID=le.LogEntryID
JOIN Orion.OLM.Tags t on t.LogEntryTagID=leta.LogEntryTagID
JOIN Orion.Nodes n on n.NodeID = le.NodeID

GROUP by t.Name, n.NodeID, le.DateTime

Result:

NodeID  tagName         LogEntryID          DateTime                    MessageDateTime
210     ToBeDiscarded   1559852514889690000 2020-12-12T02:14:15.0530000 2020-12-12T10:14:15.0520000
210     Login Failure   1559852514889690000 2020-12-12T02:14:15.0530000 2020-12-12T10:14:15.0520000
210     MediaServer     1559852514889690000 2020-12-12T02:14:15.0530000 2020-12-12T10:14:15.0520000
210     RealTime        1559852514889690000 2020-12-12T02:14:15.0530000 2020-12-12T10:14:15.0520000
210     EscalateTo-L1   1560053462887030000 2020-12-12T05:33:52.4870000 2020-12-12T13:33:52.2710000
210     MediaServer     1560053462887030000 2020-12-12T05:33:52.4870000 2020-12-12T13:33:52.2710000
210     RealTime        1560053462887030000 2020-12-12T05:33:52.4870000 2020-12-12T13:33:52.2710000
210     EscalateTo-L2   1560053490082900000 2020-12-12T05:33:54.1070000 2020-12-12T13:33:54.1080000
210     MediaServer     1560053490082900000 2020-12-12T05:33:54.1070000 2020-12-12T13:33:54.1080000
210     RealTime        1560053490082900000 2020-12-12T05:33:54.1070000 2020-12-12T13:33:54.1080000

Expectation:

NodeID  tagName                                                 LogEntryID          DateTime                    MessageDateTime
210     ToBeDiscarded, Login Failure, MediaServer, RealTime     1559852514889690000 2020-12-12T02:14:15.0530000 2020-12-12T10:14:15.0520000
210     EscalateTo-L1, MediaServer, RealTime                    1560053462887030000 2020-12-12T05:33:52.4870000 2020-12-12T13:33:52.2710000
210     EscalateTo-L2, MediaServer, RealTime                    1560053490082900000 2020-12-12T05:33:54.1070000 2020-12-12T13:33:54.1080000

score 0 · Answer 1 · answered Dec 12 '20 at 13:27

Solarwinds is a suite of tools built (often) on top of SQL Server. SQL Server now offers string_agg() to do what you want:

SELECT n.NodeID, le.DateTime, 
       STRING_AGG(t.Name, ', ') as tagNames,
       le.LogEntryID, MessageDateTime
FROM Orion.OLM.LogEntry le JOIN
     Orion.OLM.LogEntryTagAssignment leta
     ON leta.LogEntryID=le.LogEntryID JOIN
     Orion.OLM.Tags t 
     ON t.LogEntryTagID=leta.LogEntryTagID JOIN
     Orion.Nodes n on n.NodeID = le.NodeID
GROUP by t.Name, n.NodeID, le.DateTime, e.LogEntryID;

This is possible in older versions of SQL Server but the syntax is more convoluted.

SWQL/ SQL group and combining multiple result in one field into single result

1 Answers1