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!

New Cloud Intrusion Detection System Add-on for Splunk

In July 2022 Splunk released the Cloud IDS add-on which expanded Splunk capabilities in security and data ...

Happy CX Day to our Community Superheroes!

Happy 10th Birthday CX Day!What is CX Day? It’s a global celebration recognizing innovation and success in the ...

Check out This Month’s Brand new Splunk Lantern Articles

Splunk Lantern is a customer success center providing advice from Splunk experts on valuable data insights, ...