Splunk Search

Search top 4 destinations based on usage by IP and put remaining in others for each IP

ahmadsaadwarrai
Explorer

I am bit new to splunk.

I want to search top 4 destinations downloads and total ‘Other’ traffic for each source ip.

site    src_ip  destination           download
ALSTP   10.98.66.5  Youtube          150
ALSTP   10.98.66.5  Google            103
ALSTP   10.98.66.5  Microsoft_Updates   85
ALSTP   10.98.66.5  Dropbox          25
ALSTP   10.98.66.5  Other              430
ALSTP   10.98.70.10   Google              12
ALSTP   10.98.70.10   Youtube            650
ALSTP   10.98.70.10   Apple_Updates    502
ALSTP   10.98.70.10   iTunes              300
ALSTP   10.98.70.10   Other            4040

I tried below search but it is not giving the desired result.

| ess eaddr=172.16.9.60:9200 index=nuage_dpi_flowstats-* tsfield=timestamp query="EnterpriseName=Lismore Diocese" 
| eval _time=strftime(_time/1000, "%Y-%m-%d %H:%M:%S") 
| stats sum(TotalMB) as "Total(MB)" by DstIp, L7ClassEnhanced, DestinationNSG 
| sort 5 -"Total(MB)"
| addcoltotals labelfield=L7ClassEnhanced label=Others 
| rename L7ClassEnhanced as Destination, DstIp as SrcIp, DestinationNSG as SourceNSG 
| search NOT SourceNSG=ULT1_NSGX1

.

SrcIp             Destination             SourceNSG              Total(MB)
10.102.57.21       HTTP                  PMAC_NSG1      395.26492023463334
10.101.144.193   GOOGLE                CASH_NSG1        88.199470520036470
10.101.148.114   YOUTUBE                  CASH_NSG1     201.709753036428500
10.102.81.44       SSL                    PMREG_NSG1       163.37909603115
                                           Others           5072.701299667548000
0 Karma
1 Solution

ahmadsaadwarrai
Explorer

Below search gives the desired result:

| ess eaddr=172.16.9.60:9200 index=nuage_dpi_flowstats-* tsfield=timestamp query="EnterpriseName=Lismore Diocese" 
| eval _time=strftime(_time/1000, "%Y-%m-%d %H:%M:%S") 
| stats sum(TotalMB) as "Total(MB)" by DstIp, L7ClassEnhanced, DestinationNSG 
| sort DstIp -"Total(MB)" 
| eval counter = 1 
| streamstats sum(counter) as seqNo by DstIp 
| eval L7ClassEnhanced=case(seqNo < 5, L7ClassEnhanced, seqNo > 4, "Others") 
| rename "Total(MB)" as TotalMB 
| stats sum(TotalMB) as "Total(MB)" by DstIp, L7ClassEnhanced, DestinationNSG 
| fields - counter seqNo 
| sort DstIp -"Total(MB)" 
| search NOT DestinationNSG=ULT1_NSGX1

View solution in original post

0 Karma

ahmadsaadwarrai
Explorer

Below search gives the desired result:

| ess eaddr=172.16.9.60:9200 index=nuage_dpi_flowstats-* tsfield=timestamp query="EnterpriseName=Lismore Diocese" 
| eval _time=strftime(_time/1000, "%Y-%m-%d %H:%M:%S") 
| stats sum(TotalMB) as "Total(MB)" by DstIp, L7ClassEnhanced, DestinationNSG 
| sort DstIp -"Total(MB)" 
| eval counter = 1 
| streamstats sum(counter) as seqNo by DstIp 
| eval L7ClassEnhanced=case(seqNo < 5, L7ClassEnhanced, seqNo > 4, "Others") 
| rename "Total(MB)" as TotalMB 
| stats sum(TotalMB) as "Total(MB)" by DstIp, L7ClassEnhanced, DestinationNSG 
| fields - counter seqNo 
| sort DstIp -"Total(MB)" 
| search NOT DestinationNSG=ULT1_NSGX1
0 Karma

richgalloway
SplunkTrust
SplunkTrust

@ahmadsaadwarraich If your problem is resolved, please accept an answer to help future readers.

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

pkeenan87
Communicator

You will need to use stats in combination with eval to calculate a sum of the bytes for download traffic and non-download traffic. Hopefully there is a key in your data that will help you differentiate. Here is an example

index=foo
| stats sum(eval(if(action="download", TotalMB, NULL))) AS download_traffic sum(eval(if(action!="download", TotalMB, NULL))) AS other_traffic by src_ip

For reference: https://docs.splunk.com/Documentation/Splunk/7.2.6/Search/Usestatswithevalexpressionsandfunctions

0 Karma

ahmadsaadwarrai
Explorer

Thanks for the reply, I don't want to differentiate download and non-download traffic,

I want to list top 4 destination based on usage and all others destinations as "Others" for each ip, like below:

  1. site src_ip destination download(MB)
  2. ALSTP 10.98.66.5 Youtube 150
  3. ALSTP 10.98.66.5 Google 103
  4. ALSTP 10.98.66.5 Microsoft_Updates 85
  5. ALSTP 10.98.66.5 Dropbox 25
  6. ALSTP 10.98.66.5 Other 430
  7. ALSTP 10.98.70.10 Google 12
  8. ALSTP 10.98.70.10 Youtube 650
  9. ALSTP 10.98.70.10 Apple_Updates 502
  10. ALSTP 10.98.70.10 iTunes 300
  11. ALSTP 10.98.70.10 Other 4040
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...