Search gives a different result in dashboard vs searchbar

[dbcase]

Hi,

I have this query

index=mso_statistics sourcetype=ic_connectivity_5min-too_small  stat_name=subscribers cox OR coxtouchstone|stats max(stat_val) as "Subscribers"|eventstats sum(Subscribers) as coxtotal|table coxtotal|appendcols [search  index=cox host="cox*" /rest/icontrol/login 200  |rex ".*\"(?<loginid>[^\"]+)\"$"|dedup loginid|stats count as logincount]|stats max(coxtotal) as totalnumberofsubscribers max(logincount) as totalnumberofuniqelogins |eval percentage=totalnumberofuniqelogins/totalnumberofsubscribers|table totalnumberofuniqelogins totalnumberofsubscribers percentage|eval finalvalue=round(percentage,2)*100|table finalvalue

That just returns a single value (percentage). I know it is wordy because I'm pulling my hair out to try to figure out what is going on. If I run this in a search bar it works every time. If I run it in a dashboard for 1 day it works, for 7 days it kinda works (meaning the number is close but not quite right), for 30 days it is way off (44% is what the panel reports vs 74% which is what is should report). Has anyone seen a query work in search but be off in a dashboard?

[dbcase]

Notes:

I have a separate panel that generates the total number of subscribers and another panel that generates the total number of unique logins. I just need to do that math to generate the percentage. Is there a way to get the values from the other 2 panels so they could be used in the 3rd panel?

[cmerriman]

see my answer below in the comment section, i've highlighted how to set tokens in your panels to use the results of them in a third panel.

[cmerriman · Accepted solution]

just reading through this syntax, i'm going to try to clean it up, because i don't understand why you need |eventstats sum(Subscribers) as coxtotal when your preceding stats command should have the same total. I also cut out some of the tables and evals because you don't really need them. Try this and see if you're getting the same results. Check the Inspect Job to see if anything looks off, if your dashboard is still wrong and report back 🙂
one thing to note is how long the subsearch in your appendcols takes. it could be timing out, causing inaccurate readings. if that's the case, we might want to try to write it all in one search.

index=mso_statistics sourcetype=ic_connectivity_5min-too_small  stat_name=subscribers cox OR coxtouchstone
|stats max(stat_val) as coxtotal
|appendcols [search  index=cox host="cox*" /rest/icontrol/login 200  |rex ".*\"(?<loginid>[^\"]+)\"$"|dedup loginid|stats count as logincount]
|stats max(coxtotal) as totalnumberofsubscribers max(logincount) as totalnumberofuniqelogins 
|eval finalvalue=round(totalnumberofuniqelogins/totalnumberofsubscribers,2)*100
|table finalvalue

[cmerriman]

This might work instead of using appendcols:

( index=mso_statistics sourcetype=ic_connectivity_5min-too_small  stat_name=subscribers cox OR coxtouchstone) OR (index=cox host="cox*" /rest/icontrol/login 200)
|rex ".*\"(?<loginid>[^\"]+)\"$"
|eval logins=if(index="cox",loginid,null())
|stats max(stat_val) as totalnumberofsubscribers dc(logins) as totalnumberofuniqelogins 
 |eval finalvalue=round(totalnumberofuniqelogins/totalnumberofsubscribers,2)*100
 |table finalvalue

[cmerriman]

to answer your comment -

set tokens to use the results of those panels. similar to below should work.

<panel>....
<query>....</query>
<done><set token="subscribers">$result.totalnumberofsubscribers$</set></done>
</search>
....
<panel>....
<query>....</query>
<done><set token="logins">$result.totalnumberofuniqelogins$</set></done>
</search>
....
<panel>....
<query>|makeresults|eval finalvalue=round($logins$/$subscribers$*100,2)</query>

[dbcase]

Hi Cmerriman

Oh I like the last one but I'm doing something wrong (no surprise)....

Here is what I have Panel 1

<title>Unique Logins for $time_field.earliest$</title>
        <search>
          <query>host="cox*"  index=cox host="cox*" /rest/icontrol/login 200  |rex ".*\"(?&lt;loginid&gt;[^\"]+)\"$"|dedup loginid|stats count</query>
          <earliest>$time_field.earliest$</earliest>
          <latest>$time_field.latest$</latest>
          <done><set token="logins">$result.totalnumberofuniquelogins$</set></done>
        </search>

and Panel 2

<title>Total Number of Subscribers for $time_field.earliest$</title>
        <search>
          <query>index=mso_statistics sourcetype=ic_connectivity_5min-too_small  stat_name=subscribers cox OR coxtouchstone|stats max(stat_val) as "Subscribers"|eventstats sum(Subscribers) as coxtotal|table coxtotal</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <done><set token="subscribers">$result.totalnumberofsubscribers$</set></done>
        </search>

Panel 3

<title>Unique Login Engagement Percentage  for $time_field.earliest$</title>
        <search>
          <query>|makeresults|eval finalvalue=round($logins$/$subscribers$*100,2)</query>
          <earliest>$time_field.earliest$</earliest>
          <latest>$time_field.latest$</latest>
        </search>

The resulting value in panel 3 is 0, could you let me know where I went off the trail?

TY!

[dbcase]

Figured it out, I need to store the stats values as "something" and set the token to the "something"