Help needed to combine 2 SQL scripts

SELECT PayloadFilterID,SourceIP, DestinationIP, Action, Severity, COUNT(1) from dbo.payloadlogs WHERE  (payloadlogs.LogDate >= '2015-02-01 00:00:00.0' AND payloadlogs.LogDate < '2015-03-01 00:00:00.0')  GROUP BY PayloadFilterID, SourceIP, DestinationIP, Action ORDER BY COUNT(1) DESC

The above SQL script is very close to what I needed but it's missing one more column/field,
ie payloadfilter2s.Name  found in the script below:

SELECT payloadlogs.PayloadLogID, payloadlogs.PayloadFilterID, payloadlogs.SourceIP, payloadlogs.DestinationIP, payloadlogs.action, payloadlogs.Severity, payloadfilter2s.PayloadFilter2ID, payloadfilter2s.TBUID, payloadfilter2s.Name FROM payloadlogs, payloadfilter2s WHERE payloadlogs.PayloadFilterID = payloadfilter2s.PayloadFilter2ID order by payloadlogs.PayloadLogID


I kept getting some sort of syntax error if I attempts to insert
"GROUP BY payloadfilter2s.Name, SourceIP, DestinationIP,  payloadlogs.action  ORDER BY COUNT(1) DESC"
   into the 2nd SQL (which if successfully implemented in the 2nd SQL will closely meet my requirements)


Let me know any further inputs/info needed from me to implement requirements indicated above
sunhuxAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
This is the problem with not testing :(
Just add 'Severity' in the GROUP BY clause:
WITH CTE_Logs (PayloadFilterID, SourceIP, DestinationIP, Action, Severity, CountPayLoad)
AS (SELECT PayloadFilterID,SourceIP, DestinationIP, Action, Severity, COUNT(1) 
	FROM dbo.payloadlogs 
	WHERE  (payloadlogs.LogDate >= '2015-02-01 00:00:00.0' AND payloadlogs.LogDate < '2015-03-01 00:00:00.0')  
	GROUP BY PayloadFilterID, SourceIP, DestinationIP, Action, Severity)

SELECT CTE_Logs.CountPayLoad, CTE_Logs.PayloadFilterID, CTE_Logs.SourceIP, CTE_Logs.DestinationIP, CTE_Logs.action, 
		CTE_Logs.Severity, payloadfilter2s.PayloadFilter2ID, payloadfilter2s.TBUID, payloadfilter2s.Name 
FROM CTE_Logs
	INNER JOIN payloadfilter2s ON CTE_Logs.PayloadFilterID = payloadfilter2s.PayloadFilter2ID 
ORDER BY CTE_Logs.CountPayLoad DESC

Open in new window

0
 
Leo TorresSQL DeveloperCommented:
Try
SELECT PayloadFilterID,SourceIP, DestinationIP, Action, Severity, pl.Name ,COUNT(1) 
from dbo.payloadlogs pl	
	join payloadfilter2s pf
		on pl.PayloadFilterID = pf.PayloadFilter2ID
WHERE  (payloadlogs.LogDate >= '2015-02-01 00:00:00.0' 
AND payloadlogs.LogDate < '2015-03-01 00:00:00.0')  
GROUP BY PayloadFilterID, SourceIP, DestinationIP,pl.Name, Action 
ORDER BY COUNT(1) DESC

Open in new window

0
 
ManjuIT - Project ManagerCommented:
Any reason why not using Join query?

from your above statement, i believe u try to pull columns from 2 tables with a id common between both.

Ex:
SELECT payloadlogs.PayloadLogID, payloadlogs.PayloadFilterID, payloadfilter2s.PayloadFilter2ID,
payloadfilter2s.TBUID, payloadfilter2s.Name
FROM payloadlogs
INNER JOIN payloadfilter2s
ON payloadlogs.PayloadFilterID=payloadfilter2s.PayloadFilter2ID;

use Left / Right / Inner Join's - however you want your data to be seen..,

Cheers
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Mike EghtebasDatabase and Application DeveloperCommented:
no points, consider having:

payloadlogs.LogDate >= '2015-02-01' AND payloadlogs.LogDate < '2015-03-01'
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can use CTE:
WITH CTE_Logs (PayloadFilterID, SourceIP, DestinationIP, Action, Severity, CountPayLoad)
AS (SELECT PayloadFilterID,SourceIP, DestinationIP, Action, Severity, COUNT(1) 
	FROM dbo.payloadlogs 
	WHERE  (payloadlogs.LogDate >= '2015-02-01 00:00:00.0' AND payloadlogs.LogDate < '2015-03-01 00:00:00.0')  
	GROUP BY PayloadFilterID, SourceIP, DestinationIP, Action)

SELECT CTE_Logs.CountPayLoad, CTE_Logs.PayloadFilterID, CTE_Logs.SourceIP, CTE_Logs.DestinationIP, CTE_Logs.action, 
		CTE_Logs.Severity, payloadfilter2s.PayloadFilter2ID, payloadfilter2s.TBUID, payloadfilter2s.Name 
FROM CTE_Logs
	INNER JOIN payloadfilter2s WHERE CTE_Logs.PayloadFilterID = payloadfilter2s.PayloadFilter2ID 
ORDER BY CTE_Logs.CountPayLoad DESC

Open in new window

0
 
Scott PletcherSenior DBACommented:
Do the name lookup last by making the first query a derived table:

SELECT ppl2.*, plf2.PayloadFilter2ID, plf2.TBUID, plf2.Name
FROM (
    SELECT pll.PayloadFilterID, pll.SourceIP, pll.DestinationIP, pll.Action, pll.Severity, COUNT(1) AS [Count]
    FROM dbo.payloadlogs AS pll
    WHERE  (pll.LogDate >= '20150201' AND pll.LogDate < '20150301')
    GROUP BY pll.PayloadFilterID, pll.SourceIP, pll.DestinationIP, pll.Action
) AS pll2
INNER JOIN dbo.payloadfilter2s AS plf2 ON
    pll2.PayloadFilterID = plf2.PayloadFilter2ID
ORDER BY pll2.PayloadLogID
0
 
sunhuxAuthor Commented:
thanks very much, will test it out tmr

Yes eghtebas, will need to bound the start & end dates.

Suppose it's now Apr '15,  can anyone amend it such that the
  start date is  1st_of_Last_Month:00:00hrs  &
  end date is 1st_of_This_Month:00:00hrs
0
 
sunhuxAuthor Commented:
With Leo's script, I got the following errors:
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "payloadlogs.LogDate" could not be bound.
Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "payloadlogs.LogDate" could not be bound.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Name'.
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Severity'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Name'.

==================================================

With Vitor's script, got the following error:

Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'WHERE'.

===================================================

With Scott's script, got the followg:

Msg 8120, Level 16, State 1, Line 3
Column 'dbo.payloadlogs.Severity' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 107, Level 15, State 1, Line 1
The column prefix 'ppl2' does not match with a table name or alias name used in the query.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'PayloadLogID'.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry. Should be ON and not WHERE. Corrected script:
WITH CTE_Logs (PayloadFilterID, SourceIP, DestinationIP, Action, Severity, CountPayLoad)
AS (SELECT PayloadFilterID,SourceIP, DestinationIP, Action, Severity, COUNT(1) 
	FROM dbo.payloadlogs 
	WHERE  (payloadlogs.LogDate >= '2015-02-01 00:00:00.0' AND payloadlogs.LogDate < '2015-03-01 00:00:00.0')  
	GROUP BY PayloadFilterID, SourceIP, DestinationIP, Action)

SELECT CTE_Logs.CountPayLoad, CTE_Logs.PayloadFilterID, CTE_Logs.SourceIP, CTE_Logs.DestinationIP, CTE_Logs.action, 
		CTE_Logs.Severity, payloadfilter2s.PayloadFilter2ID, payloadfilter2s.TBUID, payloadfilter2s.Name 
FROM CTE_Logs
	INNER JOIN payloadfilter2s ON CTE_Logs.PayloadFilterID = payloadfilter2s.PayloadFilter2ID 
ORDER BY CTE_Logs.CountPayLoad DESC

Open in new window

0
 
sunhuxAuthor Commented:
Vitor's corrected script gives the followg:

Msg 8120, Level 16, State 1, Line 2
Column 'dbo.payloadlogs.Severity' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
0
 
sunhuxAuthor Commented:
If I remove all occurrences of Severity, it works.

Small issue, I can manually look up the Severity from a table for my analysis
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.