How to get 7 day timechart of percentage of missing Session_ID's by SITE_ID compared to Total number of entries per SITE_ID?

[kwanx]

Been wrapped around this a few days now without luck.

Starting Query: Tells me how many Session_ID's were not included in my log (should be 20 character MAC Address-like number set; when it is missing, it is logged as "\x00".

sourcetype=v_s_e | stats count(eval(Session_ID="\x00")) AS "Percent Missed"

This log contains data for 12 sites (SITE_ID).

My goal is to get to a 7 Day timechart span=1d that shows by SITE_ID the percentage of "\x00" Session_ID's by SITE_ID compared to the Total number of entries per SITE_ID.

I envision the stats tab would look similar to:

SITE_ID DATE Total Percent _Missed

site_id_1 9/16 1000 15%

site_id_1 9/17 1000 17%

site_id_1 9/18 1000 13%

site_id_1 9/19 1000 14%

site_id_1 9/20 1000 14%

site_id_1 9/21 1000 34%

site_id_1 9/22 1000 40%

site_id_2 9/17 1000 17%

site_id_2 9/18 1000 12%

site_id_2 9/19 1000 15%

site_id_2 9/20 1000 56%

site_id_2 9/21 1000 22%

site_id_2 9/22 1000 13%

site_id_2 9/23 1000 15%

...

Any help in the right direction greatly appreciated!

[jimodonald · Accepted solution]

This is completely off the cuff and not tested, but here's a little something to get you started.

sourcetype=v_s_e | bucket _time span=1d | stats count(eval(Session_ID="x00")) AS Number_Missed, count as Total_Events by SITE_ID _time | stats values(eval(round((Number_Missed/Total_Events)*100))) as Percent_Missed by _time, SITE_ID | fieldformat Percent_Missed=tostring(Percent_Missed)."%"

[kwanx]

Thank you Jim, that is right on the money!

I have added a timechart:

sourcetype=vod_stb_error | bucket
_time span=1d | stats count(eval(Session_ID="\x00")) AS
Number_Missed, count as Total_Events
by SITE_ID _time | stats
values(eval(round((Number_Missed/Total_Events)*100)))
as Percent_Missed by _time, SITE_ID |
fieldformat
Percent_Missed=tostring(Percent_Missed)."%"
| timechart per_day(Percent_Missed) by
SITE_ID useother=f limit=12