Solution : basic starter queries for Splunk admin

[splunkreal]

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 🙂

[splunkreal · Accepted solution]

Solution provided above 😊