Splunk Search

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
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=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!

1 Solution

jimodonald
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

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

kwanx
Explorer

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

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...