asked on SQL Query return results for non-existant entries
Hello,
I'm trying to write a query that will do a couple things, first it will pull results from the database and format it by adding all the rows together (for a certain time frame). Second if a certain Category doesn't exist it will label it as 0. These entries won't be there at all if an event didn't happen (so no 0 or null entries, just non-existant). For example, it will search for critical events, if none exist it won't make any entry. If one does exist it will make an entries with a number.
Here's a slightly better scenerio, it's for Symantec Critical System Protection. It makes an entries for a "Warning, Major, Information, Critical" events. I want to grab all those from the SQL data base to show the following for the last 7 days in table format:
Type of Event | Number of Occurances
If there is no entries for a type of event (say no critical happened) it will enter Critical | 0, I have to have it show this for audit reasons saying "it's not there because none happened" isn't good enough. I will be doing this in MSSQL Management Studio (since I can't seem to do it inside the program itself for those events).
I would like to export this to a excel / pdf any type of common format. I hope this makes sense.
I'm trying to write a query that will do a couple things, first it will pull results from the database and format it by adding all the rows together (for a certain time frame). Second if a certain Category doesn't exist it will label it as 0. These entries won't be there at all if an event didn't happen (so no 0 or null entries, just non-existant). For example, it will search for critical events, if none exist it won't make any entry. If one does exist it will make an entries with a number.
Here's a slightly better scenerio, it's for Symantec Critical System Protection. It makes an entries for a "Warning, Major, Information, Critical" events. I want to grab all those from the SQL data base to show the following for the last 7 days in table format:
Type of Event | Number of Occurances
If there is no entries for a type of event (say no critical happened) it will enter Critical | 0, I have to have it show this for audit reasons saying "it's not there because none happened" isn't good enough. I will be doing this in MSSQL Management Studio (since I can't seem to do it inside the program itself for those events).
I would like to export this to a excel / pdf any type of common format. I hope this makes sense.
Microsoft SQL ServerWindows Server 2012SQL
Last Comment
Seth_zin
ASKER
Basically that shows Time, the even count and the severity.
What I would like is for there to be one entry for each type of severity, for example W (Warning) would have Event_cnt added for the last 7 days so there is one entry and the count would be like 60,000. I would have 10 etc. The table format would be the same (a date column not needed just for example). So like:
Event_Severity | Event_cnt
Warning | 62,405
Information | 38
Major | 0
Critical | 0
Warning is W, Critical is C (which most the time won't have an entry as they don't occur often) etc (first letter of each severity). Also the select top 200 is just for example, I want it to pull all of them for the last 7 days.
sqlq.jpg
What I would like is for there to be one entry for each type of severity, for example W (Warning) would have Event_cnt added for the last 7 days so there is one entry and the count would be like 60,000. I would have 10 etc. The table format would be the same (a date column not needed just for example). So like:
Event_Severity | Event_cnt
Warning | 62,405
Information | 38
Major | 0
Critical | 0
Warning is W, Critical is C (which most the time won't have an entry as they don't occur often) etc (first letter of each severity). Also the select top 200 is just for example, I want it to pull all of them for the last 7 days.
sqlq.jpg
Basically you need to LEFT JOIN your data to your category table.
ASKER
It's all in one table, but I don't understand SQL syntax that well, but isn't join for two different tables (I know I wasn't exactly clear, I haven't had to do SQL queries that weren't built-in for years).
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
I'm able to use that code to grab the events that do exist. The one biggest issue we have is I also need for it to check for "C" (Critical) and if it is not there to show it as 0. In this case the event will not be in the database as all as no critical events happened so there is no entry, not even null. That's where my biggest hurdle comes into play.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
E.g.
btw, I have a bad feeling about your mix between talking about 'category', 'severity' and 'type' of event. These are often not the same..
p.s. and PLEASE post code using the code tag or embed code block feature and not as image.
WITH Category ( Category )
AS ( SELECT DISTINCT
EVENT_SEVERITY
FROM CSPEVENT
)
SELECT Cat.Category ,
COUNT(CE.Event_cnt)
FROM Category Cat
LEFT JOIN CSPEVENT CE ON Cat.Category = CE.Event_Severity
WHERE E.Event_dt > GETDATE() - 7
GROUP BY Cat.Category;
btw, I have a bad feeling about your mix between talking about 'category', 'severity' and 'type' of event. These are often not the same..
p.s. and PLEASE post code using the code tag or embed code block feature and not as image.
SELECT DISTINCTI would prefer to use a table instead of this select. CSPEvent can store dozens or hundreds of thousand records.
EVENT_SEVERITY
FROM CSPEVENT
Or..
SELECT Cat.Category ,
COUNT(CE.Event_cnt)
FROM ( VALUES ( 'C', 'I', 'M', 'W' ) ) Cat ( Category )
LEFT JOIN CSPEVENT CE ON Cat.Category = CE.Event_Severity
WHERE E.Event_dt > GETDATE() - 7
GROUP BY Cat.Category;
Better :)
But don't forget that COUNT doesn't count null values and that's why in my version I used COUNT(ISNULL(E.Event_cnt,0
But don't forget that COUNT doesn't count null values and that's why in my version I used COUNT(ISNULL(E.Event_cnt,0
Saying that a non-existing row equals 0 is a violation of the ternary logic, imho. I don't like that. It's a formatting (front-end) issue.
Well, this is a well known trick to achieve this kind of requirements.
How would you do it (count null values)?
How would you do it (count null values)?
And don't forget that isn't stored in the table. It's only for reporting. Zero here means no record with that severity has found.
hmm, just a short test:
NULL is autmatically count'd as zero... using
yields a 1 instead of 0.
SELECT T.name ,
COUNT(C.object_id)
FROM sys.types T
LEFT JOIN sys.columns C ON C.user_type_id = T.user_type_id
WHERE T.name LIKE 'i%'
GROUP BY T.name;
NULL is autmatically count'd as zero... using
SELECT T.name ,
COUNT(ISNULL(C.object_id, 0))
FROM sys.types T
LEFT JOIN sys.columns C ON C.user_type_id = T.user_type_id
WHERE T.name LIKE 'i%'
GROUP BY T.name;
yields a 1 instead of 0.
ASKER
Thanks I was able to modify what you provided me to get some aditional results I needed!
How the actual rows looks alike and the expected result.