Splunk Search

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

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

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

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

SplunkTrust
SplunkTrust

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

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

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

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