Multi-table and view union query, implement MVM Scan Task Detail SOC case----de-weight, Union, stuff, trigger empowerment

SELECT Distinct ([faultline].[ Mvmlive]. [Vwscan]. [JobID])
, [faultline]. [dbo]. [Users]. Name
, [faultline]. [Mvmlive]. [Vwscan]. [ConfigurationName]
, [faultline]. [Mvmlive]. [Vwscan]. [StartTime]
, [faultline]. [Mvmlive]. [Vwscan]. [EndTime]
       , STUFF ((select ', ' + (startipaddressipv4+ '-' +endipaddressipv4) from [faultline].[ Mvmlive]. [Vwscannediprange]where jobid=5 for XML Path (")), ("), ")
From [faultline]. [Mvmlive]. [Vwscan]
Left join [faultline]. [dbo]. [Jobs]on [Faultline]. [dbo]. [Jobs]. jobid= [Faultline]. [Mvmlive]. [Vwscan]. JobID
Left join [faultline]. [dbo]. [Users]on [Faultline]. [dbo]. [Jobs]. Userid=[faultline]. [dbo]. [Users]. UserID
Left join [faultline]. [Mvmlive]. [Vwscannediprange] on [faultline]. [Mvmlive]. [Vwscannediprange]. Jobid=[faultline]. [Mvmlive]. [Vwscan]. [JobID]
where [faultline]. [Mvmlive]. [Vwscan]. Jobid= 5

The above statement relates to the knowledge points:

1.distinct query Results de-weight function.

2. Joint query of the LEFT JOIN, the federated condition does not necessarily have to be the primary table, such as [faultline]. [dbo]. [Jobs]. Userid=[faultline]. [dbo]. [Users]. Userid. That is to say, 1, 2, 3, not necessarily, 1-2 and 1-3 of the way union, can also be, 1-2 and 2-3 of the Union.

3. Blending of views and tables, [faultline]. [Mvmlive]. [Vwscannediprange] is the view, which is actually used to discover that the execution of the view requires additional permissions----The permissions of the associated trigger, FSLONG2IP,

4. Statement empowering for a particular view, table, trigger, stored procedure grant Execute/select on Fslong2ip to ArcSight

The use of 5.stuff functions, character substitution.

Concepts and relationships for views and triggers:

The view is the result of the query, and the result is presented in a table Form. Triggers are additional actions that need to be performed when performing additions and deletions to tables. Guess the upper fslong2ip is probably a function that converts 16 of the stored IP into a string, and this function is not explicitly executed.

The execution is called only when the IP string needs to be obtained, which is why the ArcSight account does not have permission to execute, and it needs to be individually empowered for this trigger.

---------------------2015/2/10 14:14 The implementation after several modifications and optimizations---------------------------------------

Use Faultline
Go
CREATE View Jobdesiprange as
SELECT JobID,
Cast ((select StartIPAddressIPv4 + '-' +endipaddressipv4+ ', ' from [faultline].[ Mvmlive]. [Vwscannediprange] WHERE JobID = A.jobid for XML PATH (")) as varchar (+)) as IPRange
from [Faultline]. [Mvmlive]. [Vwscannediprange] A
GROUP BY Jobid

cast--is converted to a string, and the length of the string is specified, otherwise the default is only 30 and the query result is missing.

XML Path (") implements a conditional concatenation of the uniform fields of different entries. Useful to a technique, self-connecting condition,JobID = a.jobid.

Use Faultline
Go
CREATE View Forsoccase as
SELECT [Faultline]. [Mvmlive]. [Vwscan]. [JobID]
, [Faultline]. [dbo]. [Users]. Name as UserName
, [Faultline]. [Mvmlive]. [Vwscan]. [ConfigurationName] as TaskName
, [Faultline]. [Mvmlive]. [Vwscan]. [StartTime]
, [Faultline]. [Mvmlive]. [Vwscan]. [EndTime]
, [Faultline]. [Dbo].engines.serveraddress as ScanSource
, [Faultline]. [Dbo].jobdesiprange.iprange as Scandestination
from [Faultline]. [Mvmlive]. [Vwscan]
Left join [faultline]. [dbo]. [Jobs]on [Faultline]. [dbo]. [Jobs]. jobid= [Faultline]. [Mvmlive]. [Vwscan]. JobID
Left join [faultline]. [dbo]. [Users]on [Faultline]. [dbo]. [Jobs]. Userid=[faultline]. [dbo]. [Users]. Userid
Left join [faultline]. [Dbo].jobdesiprange on [faultline]. [Dbo].jobdesiprange.jobid=[faultline]. [Mvmlive]. [Vwscan]. [JobID]
Left join [faultline]. [Dbo].engines on [faultline]. [Mvmlive]. [Vwscan]. [Enginename]=[faultline]. [Dbo].engines.name
where [faultline]. [Mvmlive]. [Vwscan]. JobID > 0

Use Faultline
Go

Grant execute on Fslong2ip to ArcSight

-------------------2015/2/13 11:26-----------------------

Because the system time is stored in the database in the form of UTC 0, the displayed time is 8 hours less than the normal time, and the corresponding fields are modified.

, DateAdd (Hour,8,[faultline]. [Mvmlive]. [Vwscan]. [StartTime]) StartTime
, DateAdd (hour,8,[faultline].[ Mvmlive]. [Vwscan]. [EndTime]) EndTime

This article from "Struggling rookie" blog, declined reprint!

Multi-table and view union query, implement MVM Scan Task Detail SOC case----de-weight, Union, stuff, trigger empowerment