Getting Data In

Solution : basic starter queries for Splunk admin

splunkreal
Motivator

Hello,

sharing my experience for beginners, especially new Splunk customers 😊

 

Connected UF / forwarders :

 

 

index=_internal source=*metrics.log group=tcpin_connections | eval sourceHost=if(isnull(hostname), sourceHost,hostname) | rename connectionType as connectType | eval connectType=case(fwdType=="uf","univ fwder", fwdType=="lwf", "lightwt fwder",fwdType=="full", "heavy fwder", connectType=="cooked" or connectType=="cookedSSL","Splunk fwder", connectType=="raw" or connectType=="rawSSL","legacy fwder") | eval version=if(isnull(version),"pre 4.2",version) | rename version as Ver | fields connectType sourceIp sourceHost destPort kb tcp_eps tcp_Kprocessed tcp_KBps splunk_server Ver | eval Indexer= splunk_server | eval Hour=relative_time(_time,"@h") | stats avg(tcp_KBps) as average_kbps sum(tcp_eps) sum(tcp_Kprocessed) sum(kb) by Hour connectType sourceIp sourceHost destPort Indexer Ver | dedup sourceHost | sort - avg(tcp_KBps) | search connectType="univ fwder"
| stats dc(sourceHost) as nb_hosts

 

 

Current license usage:

 

 

index=_internal source=*license_usage.log type=Usage | fields h, b | rename h as host_name | timechart span=1h sum(eval(round(b/1024,2))) AS Total_KB | streamstats sum(Total_KB) as Cumul | fields - Total_KB | tail 1 | eval etatlic=round(Cumul/1024,0) | table etatlic

 

 

Chart over last days:

 

 

index=_internal source=*license_usage.log type=Usage earliest=-0d@d latest=now | timechart span=15m sum(eval(round(b/1024/1024,2))) AS Total_MB | eval ReportKey="today" | streamstats sum(Total_MB) as cumul
| append [search index=_internal source=*license_usage.log type=Usage earliest=-1d@d latest=-0d@d | timechart span=15m sum(eval(round(b/1024/1024,2))) AS Total_MB | eval ReportKey="yesterday" | streamstats sum(Total_MB) as cumul| eval _time=_time+86400 ]
| append [search index=_internal source=*license_usage.log type=Usage earliest=-2d@d latest=-1d@d | timechart span=15m sum(eval(round(b/1024/1024,2))) AS Total_MB | eval ReportKey="2 days ago" | streamstats sum(Total_MB) as cumul| eval _time=_time+172800]
| append [search index=_internal source=*license_usage.log type=Usage earliest=-3d@d latest=-2d@d | timechart span=15m sum(eval(round(b/1024/1024,2))) AS Total_MB | eval ReportKey="3 days ago" | streamstats sum(Total_MB) as cumul| eval _time=_time+259200]
| timechart span=15m avg(cumul) by ReportKey

 

 


Predictive license usage for today:

 

 

index=_internal source=*license_usage.log type=Usage
| eval MB = round(b/1024,2) | timechart span=1h sum(MB) as totalkb | eval hour = strftime(_time,"%H") |streamstats sum(totalkb) as totalCumulativeMB reset_before="("hour==0")"
| eval htilmnight=24-hour | predict totalCumulativeMB future_timespan=24
| where _time=relative_time(now(),"+1d@d")
| rename prediction(totalCumulativeMB) as midprediction
| eval midprediction=round((midprediction/1024),0)
| table midprediction

 

 

 

Most consuming sources today:

 

 

index=_* source=*license* | eval h = lower(replace(h,"myFQDN","")) | eval h = lower(replace(h,".myotherFQDN.fr",""))
| eval mb=round((b/1024),2)
| stats sum(mb) as totalkb by s,h,idx
| sort - totalkb
| search s!=""
| eval totalkb=round(totalkb/1024)
| rename totalkb as totalmb
| search totalmb>100

 

 

 

Yesterday:

 

 

index=_* source=*license* | eval h = lower(replace(h,"myFQDN","")) | eval h = lower(replace(h,".myotherFQDN.fr",""))
| eval mb=round((b/1024),2)
| stats sum(mb) as totalkb by s,h,idx
| sort - totalkb
| search s!=""
| eval totalkb=round(totalkb/1024)
| rename totalkb as totalmb
| search totalmb>100

 

 


Diff license per host:

 

 

index=_internal source=*license_usage.log type=Usage earliest=@d latest=@h | stats sum(eval(round(b/1024,2))) AS Total_KB by h,s | join h,s [search index=_internal source=*license_usage.log type=Usage earliest=-1d@d latest=-1d@h | rename b as b_y | stats sum(eval(round(b_y/1024))) AS Total_KB_y by h,s] | eval diff_Total_KB=Total_KB-Total_KB_y | fields - Total_KB* | where (diff_Total_KB<-1000 OR diff_Total_KB>1000) | sort - diff_Total_KB
| eval diff_Total_KB=round(diff_Total_KB/1024)
| rename diff_Total_KB as diff_Total_MB
| eval h = lower(replace(h,"myFQDN","")) | eval h = lower(replace(h,".myotherFQDN.fr",""))
| search s!=""

 

 


Missing sources :

 

 

index=_* source="*license_usage.log" earliest=-1d@d latest=@d
| eval h = lower(replace(h,".myFQDN.fr","")) | eval h = lower(replace(h,".myotherFQDN.fr",""))
| eval indexname=idx
| eval hostname=h
| eval sourcename=s
| stats sum(b) as sumByesterday by indexname hostname sourcename
| eval sumByesterday=round(sumByesterday/1024,0)
| search sumByesterday>0
| join indexname hostname sourcename type=left
[search index=_* source="*license_usage.log" earliest=@d latest=now
| eval h = lower(replace(h,".myFQDN.fr","")) | eval h = lower(replace(h,".myotherFQDN.fr",""))
| eval indexname=idx
| eval hostname=h
| eval sourcename=s
| stats sum(b) as sumBtoday by indexname hostname sourcename
| eval sumBtoday=round(sumBtoday/1024,0)]
| search sumBtoday=0
| sort indexname

 

 

 

More may come later or don't hesitate to reply.

Have a nice day 🙂

 

 

* If this helps, please upvote or accept solution 🙂 *
Tags (1)
1 Solution

splunkreal
Motivator

Solution provided above 😊

* If this helps, please upvote or accept solution 🙂 *

View solution in original post

0 Karma

splunkreal
Motivator

Solution provided above 😊

* If this helps, please upvote or accept solution 🙂 *
0 Karma
Get Updates on the Splunk Community!

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 ...

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, ...