Output empty rows

Question

Good day everyone, I have this query that returns logs with different Event IDs that occured in the last few minutes:

SELECT LogEventID, LogMessage, LogTime
FROM Database.dbo.ApplLog
WHERE (LogEventID LIKE 14
OR LogEventID LIKE 20
OR LogEventID LIKE 27
OR LogEventID LIKE 40
OR LogEventID LIKE 41
OR LogEventID LIKE 42)
AND LogTime < CURRENT_TIMESTAMP
AND LogTime > DateADD(mi, -5, CURRENT_TIMESTAMP);

Results look like this for example:

LogEventID	LogMessage
42	Error while transmitting new Incident XYZ
42	Error while transmitting new Incident XYZ
20	Error while transmitting updated Incident XYZ

There are 4 more of EventIDs. Most of the time, the other EventIDs don't produce logs. In our monitoring solution, I have set up a sensor that executes this query regularly. Long story short I can't setup the sensor properly because not all EventIDs produced logs in the past few minutes at the same time.

My question is: How do I output fake rows with all the LogEventIDs but empty LogMessages, so I have all the LogEventIDs, that haven't produced logs in the past few minutes, in every query result along the ones that did? Something like this:

LogEventID	LogMessage
42	Error while transmitting new Incident XYZ
42	Error while transmitting new Incident XYZ
20	Error while transmitting updated Incident XYZ
14
27
40
41

I've seen this post, but I don't understand the solution and it's far complicated than (feels like) it needs to be. Thanks for the help in advance.

EDIT: Here are the table definitions as requested. I hope this is what table definitions are:

score 2 · Accepted Answer · answered Jul 08 '24 at 14:25

"Empty rows" come about through the use of "Outer Joins".
You need a "dummy table" of the IDs you're interested in, then "left join" that to the actual log table, something like this (untested).

select 
  t0.id
, lg.LogEventID
, lg.LogMessage
, lg.LogTime
from 
( 
            select 14 as id 
  union all select 20 
  union all select 27 
  union all select 40 
  union all select 41 
  union all select 42 
) t0                              --- dummy table of IDs
LEFT JOIN Database.dbo.ApplLog lg --- actual log table 
  on t0.id = lg.LogEventID 
 AND lg.LogTime between DateAdd( mi, -5, CURRENT_TIMESTAMP ) 
                    and CURRENT_TIMESTAMP ;

Note that the filter on the "outer joined" table has been moved to the join condition.
If you try to use a where clause on an field in the outer joined table, most DBMSs will "collapse" the join to a regular, "inner" one.

Output empty rows

1 Answers1