Getting Data In

determining sourcetype reporting intervals

spluzer
Communicator

Hey Splunkerinos,

Noob Here. The code below tells us what sourcetypes haven't reported in, which is great and all.. However, I need to get a little deeper analysis to determine if we actually care about that sourcetype that hasnt reported in....To do that I need to get the INTERVALS for when these sourcetypes report ...for instance, if i havent heard from a sourcetype in 9 hours, (currently filtering to over a week, but you get my drift) but it only reports every ten hours, then i dont care and then I can blacklist it to keep it from showing up.......perhaps another lookup (multi-column) with sourcetype , interval, blacklist ...Not really sure how i would implement that though ...Thanks for your help !

| tstats latest(_indextime) as lt by host sourcetype
| search NOT [inputlookup sourcetype_blacklist.csv | table sourcetype]
| eval NOW=now() 
| eval difftime=NOW-lt 
| rangemap field=difftime "0 - 60 Min"=0-3600 "1 - 24 Hours"=3601-86400 "1-7 Days"=86401-604800 "1-2 Weeks"=604801-1209600 "2-3 Weeks"=1209601-1814400 default="Greater than 3 Weeks"

| eventstats count(host) as tots_hosts by sourcetype
| eventstats dc(sourcetype) as tots_st by host
| search difftime >= 604801 
| eventstats count(host) as ghost_hosts  by sourcetype
| eval percent_ghost_host = (ghost_hosts / tots_hosts) * 100
| eval percent_ghost_host=round(percent_ghost_host,2)

| eventstats dc(sourcetype) as ghost_st by host
| eval percent_ghost_st = (ghost_st / tots_st) * 100
| eval percent_ghost_st=round(percent_ghost_st,2)
| dedup sourcetype
| rename host as Host sourcetype as Sourcetype range as "Time Missing" percent_ghost_host as "This Hosts Percentage Missing to Whole" percent_ghost_st as "This Sourcetypes Percentage Missing to Whole" tots_hosts as "Total Count of Hosts by Sourcetype" tots_st as "Total Count of Sourcetypes by Host"  ghost_hosts as "Count of Missing Hosts" ghost_st as "Count of Missing Sourcetypes"
| table Host Sourcetype "Time Missing" "Total Count of Hosts by Sourcetype" "Count of Missing Hosts" "This Hosts Percentage Missing to Whole" "Total Count of Sourcetypes by Host" "Count of Missing Sourcetypes" "This Sourcetypes Percentage Missing to Whole"
1 Solution

nahfam
Path Finder

This is what i ended up doing. (obviously, you will have to create your own lookup like the one below this paragraph) And you may or may not be referencing a host and sourcetype blacklist like mine.....if not, just remove those lines...As you can see I'm filtering on percent changes, which is a threshold you can change or remove the where command altogether..I'm still working on the math, but for the most part i think its right.

sourcetype                 interval

blah                             300 

blah1                           86400


| tstats latest(_indextime) as Latest where index=* by host sourcetype index 
| `remove_blacklisted_servers()` 
| search NOT 
    [ inputlookup sourcetype_blacklist.csv 
    | table sourcetype] 
| lookup sourcetype_interval.csv sourcetype OUTPUT interval as intervals 
| eval intervals=round(intervals/60/60,2) 
| eval intervals=coalesce(intervals,0)

| eval current=now() 
| eval Minimum_Age=round(((current-Latest)/60)/60,2) 
| eval perc_change=((Minimum_Age-intervals)/Minimum_Age*100)
| where perc_change > 90 
| rangemap field=Minimum_Age default=Critical Normal=0-0.5 Elevated=0.5-2 Warning=2-3 
| eval stIDX=tostring(index) + " -- " + tostring(sourcetype) 
| eval stINT=tostring(sourcetype) + " -- " + tostring(intervals) 
| eval stLast=tostring(sourcetype) + " -- " + tostring(Minimum_Age) 
| eval pcChange=tostring(sourcetype) + " -- " + tostring(perc_change)
| stats values(stIDX) as Index--Sourcetype list(Latest) as "Latest Event" list(Minimum_Age) as Minimum_Age list(range) as Threshold list(stINT) as Sourcetype--Interval list(stLast) as Sourcetype--HoursSinceLast list(pcChange) as Sourcetype--PercChange by host 

| convert ctime("Latest Event") timeformat="%Y/%m/%d %H:%M" 
| eventstats avg(Minimum_Age) as average by host 
| eval average=round(average,2) 
| rename Minimum_Age as "Hours Since Last Seen" average as "Avg Hours Since Last Seen" lintervals as ST_Interval 
| sort "Latest Event" 
| fields - "Avg Hours Since Last Seen" 
| table host "Latest Event" Threshold Sourcetype--Interval Sourcetype--HoursSinceLast Sourcetype--PercChange

View solution in original post

0 Karma

nahfam
Path Finder

This is what i ended up doing. (obviously, you will have to create your own lookup like the one below this paragraph) And you may or may not be referencing a host and sourcetype blacklist like mine.....if not, just remove those lines...As you can see I'm filtering on percent changes, which is a threshold you can change or remove the where command altogether..I'm still working on the math, but for the most part i think its right.

sourcetype                 interval

blah                             300 

blah1                           86400


| tstats latest(_indextime) as Latest where index=* by host sourcetype index 
| `remove_blacklisted_servers()` 
| search NOT 
    [ inputlookup sourcetype_blacklist.csv 
    | table sourcetype] 
| lookup sourcetype_interval.csv sourcetype OUTPUT interval as intervals 
| eval intervals=round(intervals/60/60,2) 
| eval intervals=coalesce(intervals,0)

| eval current=now() 
| eval Minimum_Age=round(((current-Latest)/60)/60,2) 
| eval perc_change=((Minimum_Age-intervals)/Minimum_Age*100)
| where perc_change > 90 
| rangemap field=Minimum_Age default=Critical Normal=0-0.5 Elevated=0.5-2 Warning=2-3 
| eval stIDX=tostring(index) + " -- " + tostring(sourcetype) 
| eval stINT=tostring(sourcetype) + " -- " + tostring(intervals) 
| eval stLast=tostring(sourcetype) + " -- " + tostring(Minimum_Age) 
| eval pcChange=tostring(sourcetype) + " -- " + tostring(perc_change)
| stats values(stIDX) as Index--Sourcetype list(Latest) as "Latest Event" list(Minimum_Age) as Minimum_Age list(range) as Threshold list(stINT) as Sourcetype--Interval list(stLast) as Sourcetype--HoursSinceLast list(pcChange) as Sourcetype--PercChange by host 

| convert ctime("Latest Event") timeformat="%Y/%m/%d %H:%M" 
| eventstats avg(Minimum_Age) as average by host 
| eval average=round(average,2) 
| rename Minimum_Age as "Hours Since Last Seen" average as "Avg Hours Since Last Seen" lintervals as ST_Interval 
| sort "Latest Event" 
| fields - "Avg Hours Since Last Seen" 
| table host "Latest Event" Threshold Sourcetype--Interval Sourcetype--HoursSinceLast Sourcetype--PercChange
0 Karma

woodcock
Esteemed Legend

Come back and click Accept on your answer to close the question.

0 Karma

spluzer
Communicator

I cant download any additional apps. Which leaves me with the monitoring console. Is there something in the monitoring console that addresses sourcetype intervals that I am missing?

0 Karma

woodcock
Esteemed Legend

My point is not necessarily to install the apps, although that is the easiest approach. You can download them and see how they work and copy those searches wholesale into your environment without using the apps directly.

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