Monitoring Splunk

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

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

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

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

SplunkTrust
SplunkTrust

What is the search trying to do?

---
If this reply helps you, an upvote would be appreciated.

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

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, an upvote would be appreciated.
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes
and swag!