Compare todays data with yesterdays for results from a custom command

[Harishma]

I have a custom command that returns results in tabular format with a _time column as well.

Its something like below

| dyntapplications dynt_server=psg-dynatrace-qa.abc.com | search name=* | map search="| dyntmetricdata dynt_server=psg-dynatrace-qa.abc.com application=$name$ metric_path=\"Application Infrastructure Performance|*|Agent|Agent Channels|App Config Poll\" earliest=-1d@d latest=@d "

time application dynt_server count current frequency sum
4/28/2020 0:00 PBM-Analytics-UAT psg-dynatrace-qa.abc.com 20 11 TEN_MIN 7
.
.
4/28/2020 23:00 PBM-Analytics-UAT psg-dynatrace-qa.abc.com 20 11 TEN_MIN 78
4/28/2020 0:10 APM-Analytics psg-dynatrace-qa.abc.com 23 14 TEN_MIN 77
.
.
.
4/28/2020 23:10 APM-Analytics psg-dynatrace-qa.abc.com 26 19 TEN_MIN 73

I need to calculate the total of sum column and compare it with the total of today's

I get the total for yesterdays using this command

| dyntapplications dynt_server=psg-dynatrace-qa.abc.com | search name=* | map search="|dyntmetricdata dynt_server=psg-dynatrace-qa.abc.com application=$name$ metric_path=\"Application Infrastructure Performance|*|Agent|Agent Channels|App Config Poll\" earliest=-1d@d latest=@d " | addcoltotals sum | tail 1

But I dont know how to compare with todays. Can someone please help

I did try this
https://www.splunk.com/en_us/blog/tips-and-tricks/compare-two-time-ranges-in-one-report.html

but it doesnt work for me since my data is from a custom command I guess

[DavidHourani · Accepted solution]

Hi @Harishma,

What do you get from your search you exclude earliest and latest ?

| dyntapplications dynt_server=psg-dynatrace-qa.abc.com | search name= | map search="|dyntmetricdata dynt_server=psg-dynatrace-qa.abc.com application=$name$ metric_path=\"Application Infrastructure Performance||Agent|Agent Channels|App Config Poll\" 

Does this give you result based on your time picker or does it return all time based on your custom command ?

You can run an eval command to tag today's data as today and yesterday data as yesterday and then take it from there for the comparing both days. Something like this would do :

...
| eval startToday = relative_time(now(),"-24h@h")
 | eval startYesterday = relative_time(now(),"-48h@h")
 | eval marker = case(_time >= startToday, "Today",
                      _time >=startYesterday,"Yesterday",
                      1=1,"Outside Range")

Let me know if that helps.

Cheers,
David

[Harishma]

Does this give you result based on your time picker or does it return all time based on your custom command ?

This gives result based on _time in custom command i.e it uses earliest and latest and doesnt use time picker

I tried something like below and it didnt help me

[| dyntapplications dynt_server=psg-dynatrace-qa.abc.com | search name=* | map search="|dyntmetricdata dynt_server=psg-dynatrace-qa.abc.com application=$name$ metric_path=\"Application Infrastructure Performance||Agent|Agent Channels|App Config Poll\" earliest=-1d@d latest=@d " |fields sum _time| addcoltotals sum | tail 1 ] | eval ReportKey="today" | append [ search [| dyntapplications dynt_server=psg-dynatrace-qa.abc.com | search name= | map search="|dyntmetricdata dynt_server=psg-dynatrace-qa.abc.com application=$name$ metric_path=\"Application Infrastructure Performance|*|Agent|Agent Channels|App Config Poll\" earliest=-0d@d latest=now " |fields sum _time| addcoltotals sum | tail 1] | eval ReportKey="today" ] | chart count by ReportKey

The eval command you gave me is adding two additional columns called startToday and startYesterday.
How can I use it to compare the two Sums?

[DavidHourani]

Hi @Harishma,

Actually the important field that's added in the search I sent you is marker it will allow you to see which data is from today and which data is from yesterday.

So running something like this would allow you to compare both days on a timechart :

     ...
     | eval startToday = relative_time(now(),"-24h@h")
      | eval startYesterday = relative_time(now(),"-48h@h")
      | eval marker = case(_time >= startToday, "Today",
                           _time >=startYesterday,"Yesterday",
                           1=1,"Outside Range")
    | timechart count by marker

So the overall search should be like this :

| dyntapplications dynt_server=psg-dynatrace-qa.abc.com 
| search name= | map search="|dyntmetricdata dynt_server=psg-dynatrace-qa.abc.com application=$name$ metric_path=\"Application Infrastructure Performance||Agent|Agent Channels|App Config Poll\" earliest=-1d@d latest=now " 
|fields sum _time 
| rename sum as total
| eval startToday = relative_time(now(),"-24h@h")
| eval startYesterday = relative_time(now(),"-48h@h")
| eval marker = case(_time >= startToday, "Today",
                           _time >=startYesterday,"Yesterday",
                           1=1,"Outside Range")
| timechart sum(total) as total by marker

[Harishma]

@DavidHourani

Thankyou ver much that helps, I can now see the variation over time in a graph.

Another help if possible,
Can you please let me know if its possible to calculate the difference % Increase/Decrease over the 24 hours?
For example yesterday's sum was 100 and today's its 90 and that's a drop by 10% ...
If not %, Can we calculate increase or decrease value over 24 hours ? Could you please help me..

[DavidHourani]

Yes its possible to get the % increase and decrease, you can do that using autoregress command which will allow you to see both the data from now with the previous value on the same line, from there you can run aneval command to get the % change.

Reference for autoregress can be found here :
https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Autoregress