Monitoring Splunk

how make this query with lot of append faster and shorter?

ashish2110
New Member
source="s3://cgsumbrella/*" Action=Allowed
Destination!=192.168.9.20.
Destination!=*in-addr.arpa*
Destination!=*fp.measure.office.com*
Destination!=*sprint.com*
Destination!=*drift.com*
Destination!=*aoltw.net*
Destination!=*yahoo.com*
Destination!=*easyhost.com*
Destination!=*aol.com*
Destination!=*igodigital.com*
Destination!=*doubleclick.net*
Destination!=*cpesmsp.apsx*
Destination!=*microsoft.com*
| eval Office=if(len(Office) > 2, Office, "_INTERNET") 
| lookup officeip.csv IP as "ExternalIP" output Office
| eval temp=split(Destination,".") | stats count as total by Office InternalIP ExternalIP Destination temp | where (temp!="" and temp!="com" and temp!="net") | stats count as abnormal by Office InternalIP ExternalIP temp | sort -count | where count>1 
| append[ search source="s3://cgsumbrella/*" Action=Allowed
Destination!=192.168.9.20.
Destination!=*in-addr.arpa*
Destination!=*fp.measure.office.com*
Destination!=*sprint.com*
Destination!=*drift.com*
Destination!=*aoltw.net*
Destination!=*yahoo.com*
Destination!=*easyhost.com*
Destination!=*aol.com*
Destination!=*igodigital.com*
Destination!=*doubleclick.net*
Destination!=*cpesmsp.apsx*
Destination!=*microsoft.com*
| eval Office=if(len(Office) > 2, Office, "_INTERNET") 
| lookup officeip.csv IP as "ExternalIP" output Office
| eval temp=split(Destination,"-") | stats count as total by Office InternalIP ExternalIP Destination temp | where (temp!="" and temp!="com" and temp!="net") | stats count as abnormal by Office InternalIP ExternalIP temp | sort -count | where count>1]
| append[ search source="s3://cgsumbrella/*" Action=Allowed
Destination!=192.168.9.20.
Destination!=*in-addr.arpa*
Destination!=*fp.measure.office.com*
Destination!=*sprint.com*
Destination!=*drift.com*
Destination!=*aoltw.net*
Destination!=*yahoo.com*
Destination!=*easyhost.com*
Destination!=*aol.com*
Destination!=*igodigital.com*
Destination!=*doubleclick.net*
Destination!=*cpesmsp.apsx*
Destination!=*microsoft.com*
| eval Office=if(len(Office) > 2, Office, "_INTERNET") 
| lookup officeip.csv IP as "ExternalIP" output Office
| eval temp=split(Destination,",") | stats count as total by Office InternalIP ExternalIP Destination temp | where (temp!="" and temp!="com" and temp!="net") | stats count as abnormal by Office InternalIP ExternalIP temp | sort -count | where count>1]
| append[ search source="s3://cgsumbrella/*" Action=Allowed
Destination!=192.168.9.20.
Destination!=*in-addr.arpa*
Destination!=*fp.measure.office.com*
Destination!=*sprint.com*
Destination!=*drift.com*
Destination!=*aoltw.net*
Destination!=*yahoo.com*
Destination!=*easyhost.com*
Destination!=*aol.com*
Destination!=*igodigital.com*
Destination!=*doubleclick.net*
Destination!=*cpesmsp.apsx*
Destination!=*microsoft.com*
| eval Office=if(len(Office) > 2, Office, "_INTERNET") 
| lookup officeip.csv IP as "ExternalIP" output Office
| eval temp=split(Destination,"_") | stats count as total by Office InternalIP ExternalIP Destination temp | where (temp!="" and temp!="com" and temp!="net") | stats count as abnormal by Office InternalIP ExternalIP temp | sort -count | where count>1]
| append[ search source="s3://cgsumbrella/*" Action=Allowed
Destination!=192.168.9.20.
Destination!=*in-addr.arpa*
Destination!=*fp.measure.office.com*
Destination!=*sprint.com*
Destination!=*drift.com*
Destination!=*aoltw.net*
Destination!=*yahoo.com*
Destination!=*easyhost.com*
Destination!=*aol.com*
Destination!=*igodigital.com*
Destination!=*doubleclick.net*
Destination!=*cpesmsp.apsx*
Destination!=*microsoft.com*
| eval Office=if(len(Office) > 2, Office, "_INTERNET") 
| lookup officeip.csv IP as "ExternalIP" output Office
| eval temp=split(Destination,"$") | stats count as total by Office InternalIP ExternalIP Destination temp | where (temp!="" and temp!="com" and temp!="net") | stats count as abnormal by Office InternalIP ExternalIP temp | sort -count | where count>1]
| append[ search source="s3://cgsumbrella/*" Action=Allowed
Destination!=192.168.9.20.
Destination!=*in-addr.arpa*
Destination!=*fp.measure.office.com*
Destination!=*sprint.com*
Destination!=*drift.com*
Destination!=*aoltw.net*
Destination!=*yahoo.com*
Destination!=*easyhost.com*
Destination!=*aol.com*
Destination!=*igodigital.com*
Destination!=*doubleclick.net*
Destination!=*cpesmsp.apsx*
Destination!=*microsoft.com*
| eval Office=if(len(Office) > 2, Office, "_INTERNET") 
| lookup officeip.csv IP as "ExternalIP" output Office
| eval temp=split(Destination,"+") | stats count as total by Office InternalIP ExternalIP Destination temp | where (temp!="" and temp!="com" and temp!="net") | stats count as abnormal by Office InternalIP ExternalIP temp | sort -count | where count>1]
| append[ search source="s3://cgsumbrella/*" Action=Allowed
Destination!=192.168.9.20.
Destination!=*in-addr.arpa*
Destination!=*fp.measure.office.com*
Destination!=*sprint.com*
Destination!=*drift.com*
Destination!=*aoltw.net*
Destination!=*yahoo.com*
Destination!=*easyhost.com*
Destination!=*aol.com*
Destination!=*igodigital.com*
Destination!=*doubleclick.net*
Destination!=*cpesmsp.apsx*
Destination!=*microsoft.com*
| eval Office=if(len(Office) > 2, Office, "_INTERNET") 
| lookup officeip.csv IP as "ExternalIP" output Office
| eval temp=split(Destination,"!") | stats count as total by Office InternalIP ExternalIP Destination temp | where (temp!="" and temp!="com" and temp!="net") | stats count as abnormal by Office InternalIP ExternalIP temp | sort -count | where count>1]
| append[ search source="s3://cgsumbrella/*" Action=Allowed
Destination!=192.168.9.20.
Destination!=*in-addr.arpa*
Destination!=*fp.measure.office.com*
Destination!=*sprint.com*
Destination!=*drift.com*
Destination!=*aoltw.net*
Destination!=*yahoo.com*
Destination!=*easyhost.com*
Destination!=*aol.com*
Destination!=*igodigital.com*
Destination!=*doubleclick.net*
Destination!=*cpesmsp.apsx*
Destination!=*microsoft.com*
| eval Office=if(len(Office) > 2, Office, "_INTERNET") 
| lookup officeip.csv IP as "ExternalIP" output Office
| eval temp=split(Destination,"*") | stats count as total by Office InternalIP ExternalIP Destination temp | where (temp!="" and temp!="com" and temp!="net") | stats count as abnormal by Office InternalIP ExternalIP temp | sort -count | where count>1]
| append[ search source="s3://cgsumbrella/*" Action=Allowed
Destination!=192.168.9.20.
Destination!=*in-addr.arpa*
Destination!=*fp.measure.office.com*
Destination!=*sprint.com*
Destination!=*drift.com*
Destination!=*aoltw.net*
Destination!=*yahoo.com*
Destination!=*easyhost.com*
Destination!=*aol.com*
Destination!=*igodigital.com*
Destination!=*doubleclick.net*
Destination!=*cpesmsp.apsx*
Destination!=*microsoft.com*
| eval Office=if(len(Office) > 2, Office, "_INTERNET") 
| lookup officeip.csv IP as "ExternalIP" output Office
| eval temp=split(Destination,"'") | stats count as total by Office InternalIP ExternalIP Destination temp | where (temp!="" and temp!="com" and temp!="net") | stats count as abnormal by Office InternalIP ExternalIP temp | sort -count | where count>1]
| append[ search source="s3://cgsumbrella/*" Action=Allowed
Destination!=192.168.9.20.
Destination!=*in-addr.arpa*
Destination!=*fp.measure.office.com*
Destination!=*sprint.com*
Destination!=*drift.com*
Destination!=*aoltw.net*
Destination!=*yahoo.com*
Destination!=*easyhost.com*
Destination!=*aol.com*
Destination!=*igodigital.com*
Destination!=*doubleclick.net*
Destination!=*cpesmsp.apsx*
Destination!=*microsoft.com*
| eval Office=if(len(Office) > 2, Office, "_INTERNET") 
| lookup officeip.csv IP as "ExternalIP" output Office
| eval temp=split(Destination,"(") | stats count as total by Office InternalIP ExternalIP Destination temp | where (temp!="" and temp!="com" and temp!="net") | stats count as abnormal by Office InternalIP ExternalIP temp | sort -count | where count>1]
| append[ search source="s3://cgsumbrella/*" Action=Allowed
Destination!=192.168.9.20.
Destination!=*in-addr.arpa*
Destination!=*fp.measure.office.com*
Destination!=*sprint.com*
Destination!=*drift.com*
Destination!=*aoltw.net*
Destination!=*yahoo.com*
Destination!=*easyhost.com*
Destination!=*aol.com*
Destination!=*igodigital.com*
Destination!=*doubleclick.net*
Destination!=*cpesmsp.apsx*
Destination!=*microsoft.com*
| eval Office=if(len(Office) > 2, Office, "_INTERNET") 
| lookup officeip.csv IP as "ExternalIP" output Office
| eval temp=split(Destination,")") | stats count as total by Office InternalIP ExternalIP Destination temp | where (temp!="" and temp!="com" and temp!="net") | stats count as abnormal by Office InternalIP ExternalIP temp | sort -count | where count>1]
Labels (1)
0 Karma

yannK
Splunk Employee
Splunk Employee

Can you make a more generic search, that could handle them all at once, instead of using appends?
It would be more efficient and faster.

Or on recent spunk, if you need to append, take a look at the alternate command union.
instead of "| append" try " | union " to run sub searches in parallel instead of serialized.

search 1 | union [ search 2 ]

https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Union

0 Karma

wmyersas
Builder

I see lots of weirdness in this search - not least of which is evaling Office and then promptly overwriting it with a lookup!

0 Karma

richgalloway
SplunkTrust
SplunkTrust

What is the search trying to do?

---
If this reply helps you, Karma would be appreciated.

ashish2110
New Member

I am trying to append different tables having same format. The only difference in each append query is the value of delimiter in split function.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

You appear to be appending the same data many times, but counting it differently.
What problem are you trying to solve with this search? Perhaps there is another way.

---
If this reply helps you, Karma would be appreciated.
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...