sunhux
asked on
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.PayloadFilterI
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
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.PayloadFilterI
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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
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
ASKER
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'
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'.
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'
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'.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Vitor's corrected script gives the followg:
Msg 8120, Level 16, State 1, Line 2
Column 'dbo.payloadlogs.Severity'
Msg 8120, Level 16, State 1, Line 2
Column 'dbo.payloadlogs.Severity'
ASKER
If I remove all occurrences of Severity, it works.
Small issue, I can manually look up the Severity from a table for my analysis
Small issue, I can manually look up the Severity from a table for my analysis
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
payloadlogs.LogDate >= '2015-02-01' AND payloadlogs.LogDate < '2015-03-01'