New User Looking for help comparing values

[chorn3567]

Hi All! First post, super new user to Splunk. 

Have a search that i modified from a one a team member previously created, im trying to take the output of ClientVersion and compare the 6wkAvg count to the Today count for same timespan and see what the percentage -/+ is. Ultimately building towards alerting when below a certain threshold. 

| fields _time ClientVersion 
| eval DoW=strftime(_time, "%A")
| eval TodayDoW=strftime(now(), "%A")
| where DoW=TodayDoW
| search ClientVersion=FAPI*
| eval ClientVersion=if((like("ClientVersion=FAPI*","%OR%") OR false()) AND false(), "Combined", ClientVersion)
| bin _time span=5m 
| eval tempTime=strftime(_time,"%m/%d")
| where (tempTime!="null") 
| eval tempTime=if(true() AND _time < relative_time(now(), "@d"), "6wkAvg", "Today")
| stats count by ClientVersion _time tempTime
| eval _time=round(strptime(strftime(now(),"%Y-%m-%d").strftime(_time,"%H:%M:%S"),"%Y-%m-%d%H:%M:%S"),0)
| stats avg(count) as count by ClientVersion _time tempTime
| eval ClientVersion=ClientVersion."-".tempTime 
| eval count=round(count,0)

[yuanliu · Accepted solution]

Thank you for updating to text as @gcusello suggested.  It would be better if you can illustrate mock data in text tables as well.

It is hard to see how ClientVersion in 6wkAvg could be useful, but I'll just ignore this point.  Because the only numeric field is Count, I assume that you want percentage change on this field.  Splunk provides a convenient command xyseries to swap fields into row values.  You can do something like this:

| xyseries _time tempTime ClientVersion Count
| eval percentChange = round(('Count: Today' - 'Count: 6wkAvg') / 'Count: 6wkAvg' * 100, 2)

Your mock data will give

_time	ClientVersion: 6wkAvg	ClientVersion: Today	Count: 6wkAvg	Count: Today	percentChange
2024-06-26 00:00:00	FAPI-6wkAvg	FAPI-today	1582	2123	34.20
2024-06-26 00:05:00	FAPI-6wkAvg	FAPI-today	1491	1925	29.11
2024-06-26 00:10:00	FAPI-6wkAvg	FAPI-today	1888	2867	51.85
2024-06-26 00:15:00	FAPI-6wkAvg	FAPI-today	1983	2593	30.76
2024-06-26 00:20:00	FAPI-6wkAvg	FAPI-today	2882	3291	14.19

Is this something you are looking for?  Here is an emulation you can play with and compare with real data

| makeresults format=csv data="ClientVersion,        _time, tempTime, Count
FAPI-6wkAvg,    2024-06-26 00:00:00, 6wkAvg, 1582
FAPI-today,    2024-06-26 00:00:00, Today, 2123
FAPI-6wkAvg,    2024-06-26 00:05:00, 6wkAvg, 1491
FAPI-today,    2024-06-26 00:05:00, Today, 1925
FAPI-6wkAvg,    2024-06-26 00:10:00, 6wkAvg, 1888
FAPI-today,    2024-06-26 00:10:00, Today, 2867
FAPI-6wkAvg,    2024-06-26 00:15:00, 6wkAvg, 1983
FAPI-today,    2024-06-26 00:15:00, Today, 2593
FAPI-6wkAvg,    2024-06-26 00:20:00, 6wkAvg, 2485
FAPI-today,    2024-06-26 00:20:00, Today, 2939
FAPI-6wkAvg,    2024-06-26 00:20:00, 6wkAvg, 2882
FAPI-today,    2024-06-26 00:20:00, Today, 3291"
``` the above emulates
...
| stats avg(count) as count by ClientVersion _time tempTime
| eval ClientVersion=ClientVersion."-".tempTime 
| eval count=round(count,0)
```

[chorn3567]

simple as that, thank you! worked for me. 

[gcusello]

Hi @chorn3567 ,

please share your search in text mode (using theInsert/Edit code sample button), otherwise it's realy difficoult to help you.

Ciao.

Giuseppe

[chorn3567]

updated post, thank you for the tip!