IOC's cross custom tables

%3CLINGO-SUB%20id%3D%22lingo-sub-1397673%22%20slang%3D%22en-US%22%3ERe%3A%20IOC's%20cross%20custom%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1397673%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F501739%22%20target%3D%22_blank%22%3E%40MIkushOmri%3C%2FA%3E%26nbsp%3B%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20data-preserver-spaces%3D%22true%22%3EIf%20I%20understand%20Omri's%20question%20correctly%2C%20it%20is%20about%20getting%20the%20category%20and%20campaign%20associated%20once%20a%20match%20is%20found.%20The%20answer%20is%20to%20join%20the%20result%20set%20of%20the%20union%20on%20the%20IP%20address.%20A%20few%20implementation%20guidelines%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CSPAN%20data-preserver-spaces%3D%22true%22%3EYou%20need%20to%20keep%20the%20TI%20table%20and%20not%20make%20a%20list%20out%20of%20it.%20The%20%22let%20IoCs%22%20line%20should%20not%20work%20as-is%20and%20assume%20TI%20is%20still%20a%20table.%26nbsp%3B%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%3E%3CSPAN%20data-preserver-spaces%3D%22true%22%3EAs%20Clive%20mentions%2C%20assigning%20the%20relevant%20IP%20to%20a%20common%20field%20such%20as%20NormalizedIP%20is%20needed%20for%20that.%3C%2FSPAN%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%3CSPAN%20data-preserver-spaces%3D%22true%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20data-preserver-spaces%3D%22true%22%3EA%20few%20additional%20differences%20worth%20mentioning%20between%20your%20two%20versions%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CSPAN%20data-preserver-spaces%3D%22true%22%3EClive%20is%20using%20%22isfuzzy%3Dtrue%2C%22%20which%20is%20recommended%20(thanks%20Clive)%20as%20otherwise%2C%20if%20any%20of%20the%20parts%20of%20the%20union%20has%20an%20error%2C%20the%20all%20thing%20fails.%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%3E%3CSPAN%20data-preserver-spaces%3D%22true%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F501739%22%20target%3D%22_blank%22%3E%40MIkushOmri%3C%2FA%3E%26nbsp%3B%3A%20If%20your%20external%20list%20includes%20values%20that%20are%20not%20IP%20addresses%2C%20please%20filter%20them%20out%20when%20creating%20IoCs.%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%3E%3CSPAN%20data-preserver-spaces%3D%22true%22%3EThe%20difference%20between%20using%20let%20statements%20and%20or%20not%20using%20it%20should%20not%20make%20a%20difference.%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3A%20I%20wonder%20if%20you%20see%20an%20advantage%20either%20way%20apart%20from%20style%3F%3C%2FSPAN%3E%3C%2FLI%3E%0A%3C%2FUL%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1397582%22%20slang%3D%22en-US%22%3ERe%3A%20IOC's%20cross%20custom%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1397582%22%20slang%3D%22en-US%22%3E%26amp%3Bnbsp%3B%3CBR%20%2F%3E%26amp%3Bnbsp%3B%3CBR%20%2F%3EHow%20about%3F%26amp%3Bnbsp%3B%20I%20added%20a%20few%20lines%20to%20test%20this%2C%20please%20remove.%26amp%3Bnbsp%3B%20If%20you%20do%20a%20lot%20of%20union's%2C%20then%20standardizing%20(normalization)%20can%20help%2C%20I%20added%20this%2C%20but%20please%20ignore%20if%20this%20isn't%20necessary.%26amp%3Bnbsp%3B%26amp%3Bnbsp%3B%26amp%3Bnbsp%3B%3CBR%20%2F%3E%26amp%3Bnbsp%3Blet%20TI%20%3D%20toscalar(externaldata(Category%3Astring%2CIndicator%3Astring%2CCampaign%3Astring)%20%5B%20%40%22%3CA%20href%3D%22https%3A%2F%2Fxxxxxxxxxips.csv%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fxxxxxxxxxips.csv%3C%2FA%3E%22%5D%20%7C%20summarize%20make_list(Indicator))%3B%3CBR%20%2F%3Elet%20IoCs%20%3D%20TI%20%7C%20project%20Indicator%3B%3CBR%20%2F%3E%2F%2F%20test%20line%3CBR%20%2F%3Elet%20fakeLet%20%3D%20dynamic(%5B%221%22%5D)%3B%3CBR%20%2F%3Eunion%20isfuzzy%3Dtrue%3CBR%20%2F%3E(%20PaloAlto_CL%20%7C%20where%20Source_IP_s%20in%20(IoCs)%20%7C%20extend%20normalizedIP%20%3D%20Source_IP_s%20)%2C%3CBR%20%2F%3E(%20OfficeActivity%20%7C%20where%20ClientIP%20in%20(IoCs)%20%7C%20extend%20normalizedIP%20%3D%20ClientIP)%2C%3CBR%20%2F%3E(%20securityAkamai_CL%20%7C%20where%20src_s%20in%20(IoCs)%20%7C%20extend%20normalizedIP%20%3D%20src_s)%2C%3CBR%20%2F%3E(%20AWSCloudTrail%20%7C%20where%20SourceIpAddress%20in%20(IoCs)%20%7C%20extend%20normalizedIP%20%3D%20SourceIpAddress)%2C%3CBR%20%2F%3E(%20SecurityEvent%20%7C%20where%20IpAddress%20in%20(IoCs)%20%7C%20extend%20normalizedIP%20%3D%20IpAddress)%2C%3CBR%20%2F%3E(%20Fastly_CL%20%7C%20where%20client_ip_s%20in%20(IoCs)%20%7C%20extend%20normalizedIP%20%3Dclient_ip_s)%2C%3CBR%20%2F%3E%2F%2F%20test%20table%3CBR%20%2F%3E(%20SecurityEvent%20%7C%20where%20EventID%20%26amp%3Bgt%3B%207000%20%7C%20extend%20normalizedIP%20%3D%20EventID)%3CBR%20%2F%3E%2F%2F%20now%20show%20data%20you%20need%20%3CBR%20%2F%3E%7C%20project%20normalizedIP%2C%20fakeLet.%5B0%5D%3CBR%20%2F%3E%26amp%3Bnbsp%3B%3CBR%20%2F%3E%26amp%3Bnbsp%3B%3CBR%20%2F%3EIf%20you%20need%20it%2C%20change%20the%20last%20line%20to%3A%3CBR%20%2F%3E%26amp%3Bnbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%7C%20project%20normalizedIP%2C%20fakeLet.%5B0%5D%2C%20Type%3CBR%20%2F%3E%26amp%3Bnbsp%3B%3CBR%20%2F%3EThis%20would%20show%20the%20TableName%20(as%20Type%20%3D%3D%20TableName)%3CBR%20%2F%3E%26amp%3Bnbsp%3B%3CBR%20%2F%3E%26amp%3Bnbsp%3B%3CBR%20%2F%3EThanks%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1396750%22%20slang%3D%22en-US%22%3EIOC's%20cross%20custom%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1396750%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhen%20using%20the%20above%20query%20i%20am%20trying%20to%20search%20across%20multiple%20tables%20at%20once%20and%20see%20if%20there%20is%20a%20match%20for%20any%20relevant%20IOC%3C%2FP%3E%3CP%3E1.%20i%20am%20managing%20several%20lists%3A%20IP.%20Hash.%20URL%20etc%20and%20have%20created%20various%20external%20data%20containers%3C%2FP%3E%3CP%3Ewhen%20running%20it%20via%20join%20there%20is%20a%20limitation%20of%20~3-4%20tables%20to%20run%20on.%20i%20need%20to%20tun%20across%208%2B%20tables%3C%2FP%3E%3CP%3E2.%20when%20trying%20to%20use%20an%20in%20statement%20as%20described%20below%20im%20unable%20to%20output%20the%20Category%2CIndicator%2C%20Campaign%2C%20%3A%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3Elet%20TI%20%3D%20toscalar(externaldata(Category%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Estring%3C%2FSPAN%3E%3CSPAN%3E%2CIndicator%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Estring%3C%2FSPAN%3E%3CSPAN%3E%2CCampaign%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Estring%3C%2FSPAN%3E%3CSPAN%3E)%20%5B%20%40%3C%2FSPAN%3E%3CSPAN%3E%22%3CA%20href%3D%22https%3A%2F%2Fxxxxxxxxxips.csv%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fxxxxxxxxxips.csv%3C%2FA%3E%22%3C%2FSPAN%3E%3CSPAN%3E%5D%20%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%20make_list(Indicator))%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Elet%20IoCs%20%3D%20TI%20%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%20Indicator%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Elet%20security1%20%3D%20PaloAlto_CL%20%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20Source_IP_s%20in%20(IoCs)%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Elet%20security2%3D%20OfficeActivity%20%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20ClientIP%20in%20(IoCs)%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Elet%20security3%3D%20securityAkamai_CL%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20src_s%20in%20(IoCs)%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Elet%20AWSCloudTrailp%20%3D%20AWSCloudTrail%20%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20SourceIpAddress%20in%20(IoCs)%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Elet%20SecurityEventp%20%3D%20SecurityEvent%20%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20IpAddress%20in%20(IoCs)%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Elet%20security4%3D%20Custom_CL%20%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20client_ip_s%20in%20(IoCs)%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Eunion%3C%2FSPAN%3E%3CSPAN%3E%20withsource%3D%20TableName%20security1%2C%20security2%2C%20security3%2CAWSCloudTrailp%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E3.%20also%20tried%20the%20following%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3Elet%20TI%20%3D%20toscalar(externaldata(Category%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Estring%3C%2FSPAN%3E%3CSPAN%3E%2CIndicator%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Estring%3C%2FSPAN%3E%3CSPAN%3E%2CCampaign%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Estring%3C%2FSPAN%3E%3CSPAN%3E)%20%5B%20%40%3C%2FSPAN%3E%3CSPAN%3E%22%3CA%20href%3D%22https%3A%2F%2Fxxxxxxxxx.csv%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fxxxxxxxxx.csv%3C%2FA%3E%22%3C%2FSPAN%3E%3CSPAN%3E%5D%20%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%20IndicatorList%3Dmake_list(Indicator))%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Elet%20cortex_match%20%3D%20toscalar(%3CCUSTOMTABLE%3E_CL%7C%20%3C%2FCUSTOMTABLE%3E%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%20%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20Source_IP_s%20%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20Indicator%3DTI%20%7C%20mv-apply%20IndicatorList%3DTI%20%3C%2FSPAN%3E%3CSPAN%3Eon%3C%2FSPAN%3E%3CSPAN%3E%20(%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20Source_IP_s%20%3D%3D%20IndicatorList)%20%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%20Source_IP_s%20%7C%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%20makelist(Source_IP_s))%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3ECustomTable_CL%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20Source_IP_s%20in%20(cortex_match)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3Etnx%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1466803%22%20slang%3D%22en-US%22%3ERe%3A%20IOC's%20cross%20custom%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1466803%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhen%20trying%20to%20add%20to%20the%20query%20additional%20external%20data%20such%20as%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fopenphish.com%2Ffeed.txt%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fopenphish.com%2Ffeed.txt%3C%2FA%3E%3C%2FP%3E%3CP%3Eas%20there%20is%20no%20deceleration%20of%20the%20column%20names%20in%20the%20feed%20i%20am%20unable%20to%20determine%20what%20is%20the%20proper%20syntax%20of%20adding%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3Elet%20openphish%20%3D%20externaldata%20%3C%2FSPAN%3E%3CSTRONG%3E(%20domain%3Astring)%3C%2FSTRONG%3E%20%3CSPAN%3E(%5B%3C%2FSPAN%3E%3CSPAN%3E%40%3C%2FSPAN%3E%3CSPAN%3E%22%3CA%20href%3D%22https%3A%2F%2Fopenphish.com%2Ffeed.txt%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fopenphish.com%2Ffeed.txt%3C%2FA%3E%22%3C%2FSPAN%3E%3CSPAN%3E%5D%3C%2FSPAN%3E%3CSPAN%3E%20with%20%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3Eformat%3D%3C%2FSPAN%3E%3CSPAN%3E%22txt%22%3C%2FSPAN%3E%3CSPAN%3E))%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1466940%22%20slang%3D%22en-US%22%3ERe%3A%20IOC's%20cross%20custom%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1466940%22%20slang%3D%22en-US%22%3EI%20cant%20make%20that%20file%20open%20with%20externaldata%20-%20do%20you%20also%20get%20a%20Query%20Aborted%20error%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1467164%22%20slang%3D%22en-US%22%3ERe%3A%20IOC's%20cross%20custom%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1467164%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%3C%2FP%3E%3CP%3Eyes%2C%20any%20alternatives%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1473135%22%20slang%3D%22en-US%22%3ERe%3A%20IOC's%20cross%20custom%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1473135%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F501739%22%20target%3D%22_blank%22%3E%40MIkushOmri%3C%2FA%3E%26nbsp%3B%3A%20My%20guess%20is%20that%20openpish%20has%20some%20protection%20mechanism%20in%20place%20that%20blocks%20externaldata.%20As%20a%20long%20shot%2C%20you%20may%20want%20to%20check%20with%20them%2C%20however%20using%20Logic%20Apps%20to%20copy%20it%20daily%20to%20Azure%20storage%20would%20be%20easier.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi @Clive Watson 

when using the above query i am trying to search across multiple tables at once and see if there is a match for any relevant IOC

1. i am managing several lists: IP. Hash. URL etc and have created various external data containers

when running it via join there is a limitation of ~3-4 tables to run on. i need to tun across 8+ tables

2. when trying to use an in statement as described below im unable to output the Category,Indicator, Campaign, :

let TI = toscalar(externaldata(Category:string,Indicator:string,Campaign:string) [ @"https://xxxxxxxxxips.csv"] | summarize make_list(Indicator));
let IoCs = TI | project Indicator;
let security1 = PaloAlto_CL | where Source_IP_s in (IoCs);
let security2= OfficeActivity | where ClientIP in (IoCs);
let security3= securityAkamai_CL| where src_s in (IoCs);
let AWSCloudTrailp = AWSCloudTrail | where SourceIpAddress in (IoCs);
let SecurityEventp = SecurityEvent | where IpAddress in (IoCs);
let security4= Custom_CL | where client_ip_s in (IoCs);
union withsource= TableName security1, security2, security3,AWSCloudTrailp

 

3. also tried the following

let TI = toscalar(externaldata(Category:string,Indicator:string,Campaign:string) [ @"https://xxxxxxxxx.csv"] | summarize IndicatorList=make_list(Indicator));
let cortex_match = toscalar(<CustomTable>_CL| summarize by Source_IP_s | extend Indicator=TI | mv-apply IndicatorList=TI on (where Source_IP_s == IndicatorList) | project Source_IP_s |summarize makelist(Source_IP_s));
CustomTable_CL
| where Source_IP_s in (cortex_match)
 
tnx
6 Replies
&nbsp;
&nbsp;
How about?&nbsp; I added a few lines to test this, please remove.&nbsp; If you do a lot of union's, then standardizing (normalization) can help, I added this, but please ignore if this isn't necessary.&nbsp;&nbsp;&nbsp;
&nbsp;let TI = toscalar(externaldata(Category:string,Indicator:string,Campaign:string) [ @"https://xxxxxxxxxips.csv"] | summarize make_list(Indicator));
let IoCs = TI | project Indicator;
// test line
let fakeLet = dynamic(["1"]);
union isfuzzy=true
( PaloAlto_CL | where Source_IP_s in (IoCs) | extend normalizedIP = Source_IP_s ),
( OfficeActivity | where ClientIP in (IoCs) | extend normalizedIP = ClientIP),
( securityAkamai_CL | where src_s in (IoCs) | extend normalizedIP = src_s),
( AWSCloudTrail | where SourceIpAddress in (IoCs) | extend normalizedIP = SourceIpAddress),
( SecurityEvent | where IpAddress in (IoCs) | extend normalizedIP = IpAddress),
( Fastly_CL | where client_ip_s in (IoCs) | extend normalizedIP =client_ip_s),
// test table
( SecurityEvent | where EventID &gt; 7000 | extend normalizedIP = EventID)
// now show data you need
| project normalizedIP, fakeLet.[0]
&nbsp;
&nbsp;
If you need it, change the last line to:
&nbsp;

| project normalizedIP, fakeLet.[0], Type
&nbsp;
This would show the TableName (as Type == TableName)
&nbsp;
&nbsp;
Thanks

@Clive Watson , @MIkushOmri :

 

If I understand Omri's question correctly, it is about getting the category and campaign associated once a match is found. The answer is to join the result set of the union on the IP address. A few implementation guidelines:

  • You need to keep the TI table and not make a list out of it. The "let IoCs" line should not work as-is and assume TI is still a table. 
  • As Clive mentions, assigning the relevant IP to a common field such as NormalizedIP is needed for that.

 

A few additional differences worth mentioning between your two versions:

  • Clive is using "isfuzzy=true," which is recommended (thanks Clive) as otherwise, if any of the parts of the union has an error, the all thing fails.
  • @MIkushOmri : If your external list includes values that are not IP addresses, please filter them out when creating IoCs.
  • The difference between using let statements and or not using it should not make a difference. @Clive Watson : I wonder if you see an advantage either way apart from style?

Hi @Clive Watson 

when trying to add to the query additional external data such as https://openphish.com/feed.txt

as there is no deceleration of the column names in the feed i am unable to determine what is the proper syntax of adding this.

 

let openphish = externaldata ( domain:string) ([@"https://openphish.com/feed.txt"] with (format="txt"));
 
I cant make that file open with externaldata - do you also get a Query Aborted error?

 

@Clive Watson

yes, any alternatives?

 

@MIkushOmri : My guess is that openpish has some protection mechanism in place that blocks externaldata. As a long shot, you may want to check with them, however using Logic Apps to copy it daily to Azure storage would be easier.