Splunk Search
Highlighted

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?

Explorer

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=vse | 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 SITEID the percentage of "\x00" SessionID's by SITEID compared to the Total number of entries per SITEID.

I envision the stats tab would look similar to:

SITE_ID DATE Total Percent _Missed

siteid1 9/16 1000 15%

siteid1 9/17 1000 17%

siteid1 9/18 1000 13%

siteid1 9/19 1000 14%

siteid1 9/20 1000 14%

siteid1 9/21 1000 34%

siteid1 9/22 1000 40%

siteid2 9/17 1000 17%

siteid2 9/18 1000 12%

siteid2 9/19 1000 15%

siteid2 9/20 1000 56%

siteid2 9/21 1000 22%

siteid2 9/22 1000 13%

siteid2 9/23 1000 15%

...

Any help in the right direction greatly appreciated!

Highlighted

Re: 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?

Contributor

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)."%"

View solution in original post

Highlighted

Re: 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?

Explorer

Thank you Jim, that is right on the money!

I have added a timechart:

sourcetype=vodstberror | bucket
time span=1d | stats count(eval(SessionID="\x00")) AS
NumberMissed, count as TotalEvents
by SITEID _time | stats
values(eval(round((Number
Missed/TotalEvents)*100)))
as Percent
Missed by time, SITEID |
fieldformat
PercentMissed=tostring(PercentMissed)."%"
| timechart perday(PercentMissed) by
SITE_ID useother=f limit=12