I'm trying to rectify a search where the chart should represent a Trend but is actually just adding the last active user and the current active user and showing it in the Trend Column :
eg :
2016-08-10 11:10:00 17 17
2016-08-10 11:15:00 20 37
2016-08-10 11:20:00 0 37
search :
index="X" sourcetype="Y" DOMAIN="ACTIVE_USER" | lookup host_lookup HOSTNAME OUTPUT SYSTEM | dedup HOSTNAME,ACTIVE_USER | where ACTIVE_USER!="NO_USER" | timechart span=5m dc(ACTIVE_USER) as "Active User Count" | addtotals | streamstats sum(Total) as "Trend" | fields - Total
Try using trendline:
index="X" sourcetype="Y" DOMAIN="ACTIVE_USER" | lookup host_lookup HOSTNAME OUTPUT SYSTEM | dedup HOSTNAME,ACTIVE_USER | where ACTIVE_USER!="NO_USER" | timechart span=5m dc(ACTIVE_USER) as "active_users" | trendline sma5(active_users)
https://docs.splunk.com/Documentation/Splunk/6.4.2/SearchReference/Trendline
Also two comments on your search, first, you don't seem to be using the value from the lookup (SYSTEM). And second, it doesn't seem necessary to dedup by hostname,active_user when you are using dc (which give you a distinct count of active users).
Thanks for your help
The lookup file OUTPUT isn't a field in the csv (HOSTNAME and SERVER exist) but as we aren't using those values not sure why we are using it ?
If dc(ACTIVE_USER) is used then no need to dedup right ? so i will remove that.
So my search looks like so ..
index="X" sourcetype="Y" DOMAIN=ACTIVE_USER | lookup host_lookup HOSTNAME OUTPUT SYSTEM | where ACTIVE_USER!="NO_USER" | timechart span=5m dc(ACTIVE_USER) as "Active User Count" | trendline sma5("Active User Count") AS Trend