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

Source: Internet
Author: User
Tags arcsight

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.