How do I show comparison trends?

[hmohta]

Hello All

I have been asked to show trends for business requirement with the dataset I have. Possible past, present and 'possible' predict for 3\4 months. the only _time dataset I have is the "WeekStarting": where events have occurred. To make it more relatable I need to show trends in login sharing. 

Due to the magnitude of data, to make more sense out of the values I have selected 3 quarters over 3 years i.e (WeekStarting="2020-07-20" OR WeekStarting="2020-08-24" OR WeekStarting="2020-09-28" OR WeekStarting="2021-07-26" OR WeekStarting="2021-08-23" OR WeekStarting="2021-09-20" OR WeekStarting="2022-06-20" OR WeekStarting="2022-07-18" OR WeekStarting="2022-08-22" ).

Now I don't have any day or any time series data which makes it difficult for me to make timechart or timewrap commands. 

What I have used so far and many others:

index="AB" sourcetype="AB" | spath
| search (WeekStarting="2020-07-20" OR WeekStarting="2020-08-24" OR WeekStarting="2020-09-28" OR WeekStarting="2021-07-26" OR WeekStarting="2021-08-23" OR WeekStarting="2021-09-20" OR WeekStarting="2022-06-20" OR WeekStarting="2022-07-18" OR WeekStarting="2022-08-22" )

| stats values(TotalUsers) , values(DeviceTypes{}), values(WeekStarting), sum(Newbrowsertypes) as Aggregate_Logins by AccountID
| where Aggregate_Logins >=5

I do know these are not trend commands. But I am really lost as to how I can incorporate trends with the dataset. Please help!!

[hmohta]

yes I will still use spath and WeekStarting only because I want to filter out the quarters. So yes Week Starting is the only event I have in relation to _time. How can I create timechart with 'span' with these? as my selected dates are like 3 months in a year over 3 years.

Also do I have to create a makeresults or eval to generate events for the predict command?

[bowesmana]

You already have the ability to filter out quarters based on _time without searching 3 years of data, using spath to then search for the quarters. Use the earliest=X latest=Y syntax - that will result in extracting a much smaller data set and will perform much faster.

You can use something like this for timechart

| timechart span=1w count
| timewrap 1y

which will wrap each year's weekly figures onto the same x-axis

As for predict, I've not used it - I suspect you need to use it before the timechart

[hmohta]

thanks @bowesmana  for your reply. Wont using the earliest=x latest=y syntax only give you the number of events that fall within that period. I am really confused as to how to include the specific events occurred during that time which  the business would really like to see.

[bowesmana]

All searches done in Splunk require a time range and this range is then matched against the _time field, so if you specify a time range window in the time picker of 3 years, ALL events in 3 years will be returned.

In your example, you are first fetching data for 3 years, then parsing the JSON then searching for the WeekStarting field. This is totally unnecessary.

You can also use the 'earliest=' and 'latest=' search parameters to control what events are returned, which override the time picker. This is simply a filter to the events returned by Splunk, in the same way that 'index=X' is a filter to only return events from index X.

So, to use fixed _time ranges in the search parameter use the syntax described here

https://docs.splunk.com/Documentation/Splunk/9.0.1/Search/Specifytimemodifiersinyoursearch#Specify_a...

to specify absolute time ranges. You can therefore combine multiple time ranges with () OR () OR () in your search. In that way, you will only return events for those time ranges you want, rather than all 3 years worth of data.

Just try this is part of your original search

index="AB" sourcetype="AB" (earliest="07/20/2020:00:00:00" latest="07/27/2020:00:00:00")

and you will see it only returns events where _time is between those dates.

[bowesmana]

As you have a date, you can convert that to _time

| eval _time=strptime(WeekStarting, "%F")

then you can do time based calculations on that. Unless you have any other day/hour indicator, your trends will have at best day granularity. 

What does your existing _time value represent?

As  for trends/predictions, you can look at trendline/predict commands to see if they can serve a purpose, or you can roll your own calculations and make new 'events' for predicted future values.

You could always just do your stats and add by StartingWeek as an additional split by clause

or use chart with AccountID and Starting week. (over/by)

[hmohta]

thanks for your reply @bowesmana . at the moment my existing _time value is the Week Starting, that's all.

I do not understand the need to use the command: 

| eval _time=strptime(WeekStarting, "%F")

Correct me  if I am wrong, it will still give me the existing format which already exists?

[bowesmana]

so when you said you have no time series data, it seems you do have. If _time is representing WeekStarting, then you can use earliest/latest criteria in your search, e.g.

(earliest="07/20/2020:00:00:00" latest="07/27/2020:00:00:00") OR... 

 I assumed that because you were doing spath + the search for WeekStarting=XX that it was different to just using _time directly.

So, what can't you do?