Playbook is not running

[akshay250692]

Hi Team,

Please help me to resolve this issue. We have created one playbook for outbound traffic to ThreatIntel. But after sometime it is giving blank excel sheet. Before it provide 2-3 times result in a week but now we are not getting any output from this playbook from long time. If we run this query then msg will show " The query couldn’t be processed in less than 10 minutes, which might happen when large volumes of old data are retrieved.
Try running the query again". This is scheduled on daily basis.

Please find query for this.

let deviceIP = (_GetWatchlist('manufacturingFirewalls') | project SearchKey);
ThreatIntelligenceIndicator
| where ExpirationDateTime > now()
| where Active == true
| where isnotempty(NetworkIP) or isnotempty(NetworkSourceIP) or isnotempty(NetworkDestinationIP)
| extend entity_threat_IP = iff(isnotempty(NetworkIP), NetworkIP, NetworkDestinationIP)
| extend entity_threat_IP = iff(isnotempty(entity_threat_IP) and isnotempty(NetworkSourceIP), NetworkSourceIP, entity_threat_IP)
| join(
workspace(".....").CommonSecurityLog
|union workspace("....").CommonSecurityLog, workspace("..........").CommonSecurityLog, workspace("...........").CommonSecurityLog, workspace("..........").CommonSecurityLog
| where TimeGenerated > now()-7d
| where DeviceVendor =~ "Palo Alto Networks" and DeviceProduct =~ "PAN-OS" and Activity =~ "traffic"
| where DeviceAction !in ("reset-both", "deny", "reset-server", "reset-client")
| where DeviceCustomString5 in~ ("outside","Outside","Outside-ISP2", "untrust", "PRISMA_INSIDE")
| where Computer !in (deviceIP)
| extend CommonSecurityLog_TimeGenerated = TimeGenerated
) on $left.entity_threat_IP == $right.DestinationIP
| where CommonSecurityLog_TimeGenerated > TimeGenerated and CommonSecurityLog_TimeGenerated < ExpirationDateTime
| project TrafficTimestamp = CommonSecurityLog_TimeGenerated, SourceIP, SourceTranslatedAddress, Source_Port=strcat(SourcePort), SourceUserName, DestinationIP, Destination_port=strcat(DestinationPort), ApplicationProtocol, Firewall_Action = DeviceAction, Packets= DeviceCustomNumber2, Rule= DeviceCustomString1, Firewall =Computer, IOC_Tag = Tags, IOC_Expiration = ExpirationDateTime, IOC_Source = Description

[#2]

I take it you are trying to do a union with 5 different Microsoft Sentinel instances? Those unions are not cheap in terms of processing and will take a long to time run since you are getting all the information and then doing a filter. I would suggest filtering the data on each union command so that only the information you need is actually being sent with the union command.

[#3]

may you re write my query ?so i can understand easily and i will apply to get result

[#4]

As you dont summarize the results returned, you could be getting 100-30k rows per workspace, that takes time and isn't easily human readable.

Maybe reduce the results using a bin or arg_max (you may in the investigation have to get the specific time range) but for the Alert this should be good enough to get focus on the rough time and details.

e.g. Add one of these lines

..
| where Computer !in (deviceIP)
| extend CommonSecurityLog_TimeGenerated = TimeGenerated
// use this
| summarize count(), make_set(SourceIP) by Computer, DeviceAction, bin(CommonSecurityLog_TimeGenerated,1d)
//or maybe this
//| summarize count(), make_set(SourceIP), arg_max(CommonSecurityLog_TimeGenerated, Computer) by DeviceAction

[#5]

where we hv to add these line because expression getting failed

[#6]

@akshay250692 

Here you can see the two lines above the arrow that match you query, then you can add either of my suggestions where the arrow is.  Later lines will fail as the columns needed wont be there so, you should removed for testing lines after the join 

I'd do some testing with a reduce set of KQL, something like this to get this section optimized

let deviceIP = dynamic (['fakeComputer']);
CommonSecurityLog
| where TimeGenerated > ago(7d)
| where DeviceVendor =~ "Palo Alto Networks" and DeviceProduct =~ "PAN-OS" //and Activity =~ "traffic"
| where DeviceAction !in ("reset-both", "deny", "reset-server", "reset-client")
//| where DeviceCustomString5 in~ ("outside","Outside","Outside-ISP2", "untrust", "PRISMA_INSIDE")
| where Computer !in (deviceIP)
| extend CommonSecurityLog_TimeGenerated = TimeGenerated
// use this
//| summarize count(), make_set(SourceIP) by Computer, DeviceAction, bin(CommonSecurityLog_TimeGenerated,1d)
//or maybe this
| summarize count(), make_set(SourceIP), arg_max(CommonSecurityLog_TimeGenerated, Computer) by DeviceAction

[#7]

my requirement is match the destination ip which is common in watchlist fw and ThreatIntelligenceIndicator. If i remove the after the join operator then how it will match. i hv to match common security log with ThreatIntelligenceIndicator table. Playbook name is "OutboundTraffictoThreatIntelIPsReport"

[#8]

I was only suggesting to remove this for "testing" / short term, as when you summarize the columns needed by any extend or project will be removed or renamed, so you need to know what to add back

e.g. just remove this until you are happy its working then slowly add it back (often you dont need to project if you have previously used summarize)

| project TrafficTimestamp = CommonSecurityLog_TimeGenerated, SourceIP, SourceTranslatedAddress, Source_Port=strcat(SourcePort), SourceUserName, DestinationIP, Destination_port=strcat(DestinationPort), ApplicationProtocol, Firewall_Action = DeviceAction, Packets= DeviceCustomNumber2, Rule= DeviceCustomString1, Firewall =Computer, IOC_Tag = Tags, IOC_Expiration = ExpirationDateTime, IOC_Source = Description

[#9]

the query is running only for single workspace not for all simultaneously but still we r not getting any result. So i think i have to create this playbook for all region separately as already gary told.