Splunk Dev

Create Missing Records of a Timechart

nouraali
Explorer
Hi ,
given the below input (4 mins of sample access log data):
_time,URI,Bytes
2021-05-18 02:01:00,a,1
2021-05-18 02:01:00,a,1
2021-05-18 02:02:00,a,1
2021-05-18 02:03:00,b,1
2021-05-18 02:03:00,b,1
2021-05-18 02:04:00,a,1
assuming a window of 2 mins from (2:01:00.000 ) to (2:03:00.000), i want to perform some computations (average and standard dev of bytes grouped by URI) as below:
source="ds1.csv" host="vgspl11hr" index="sfp" sourcetype="csv"
| table _time,URI,Bytes
| timechart span=1m avg(Bytes) AS avg_bytes, stdev(Bytes) AS std_bytes by URI limit=0
| fillnull value=""
| untable _time Measure Value
| eval Metric=mvindex(split(Measure,": "),0),uri=mvindex(split(Measure,": "),1)
| fields - Measure
| eval time_uri=_time."__".uri
| fields - uri - _time
| xyseries time_uri Metric Value
| eval _time=mvindex(split(time_uri,"__"),0),uri=mvindex(split(time_uri,"__"),1)
| fields - time_uri
exact time window between (5/18/21 2:01:00.000 AM to 5/18/21 2:03:00.000 AM), below is the output:
_time	            uri	avg_bytes	std_bytes
2021-05-18 02:01:00 a 1 0
2021-05-18 02:02:00 a 1 0
So, the timechart performed the computations on the existing URIs in the first 2 mins time window, in that case the URI=a.
However, i want the timechart to consider the existence of the URI = b.
Is there a way to have the timechart consider all the values of the URI in the computation, even if not all of the URIs existing  in that time window?
I need the output to be as below in the first 2 mins time window:
_time	            uri	avg_bytes	std_bytes
2021-05-18 02:01:00 a 1 0
2021-05-18 02:01:00 b
2021-05-18 02:02:00 a 1 0
2021-05-18 02:02:00 b
Is that possible?
I would really appreciate if you helped me.
Labels (1)
Tags (1)
0 Karma
1 Solution

nouraali
Explorer

When i specify a time window of 4 mins  (5/18/21 2:01:00.000 AM to 5/18/21 2:05:00.000 AM), the query returns all URIs .

when i specify a time window of 2 mins  (5/18/21 2:01:00.000 AM to 5/18/21 2:03:00.000 AM), the query returns the records for the existing URIs in that time window, in my case URI=a.

So the issue occurs when the time window in which i am running the search is not having events with URI=b.

However, i was able to sort this out by using:

| sort _time
| append
[| inputlookup uri.csv
| table uri]
| rename _time AS t
| filldown t
| rename t AS _time
| dedup _time uri

 

This way a record will be created for URI=b in the last bucket in the time window.

View solution in original post

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@nouraali 

Try this.

source="ds1.csv" host="vgspl11hr" index="sfp" sourcetype="csv"
| fields _time,URI,Bytes 
| append [| inputlookup uri.csv | table URI ] 
| timechart span=1m  avg(Bytes) AS avg_bytes, stdev(Bytes) AS std_bytes by URI limit=0
| fillnull value=""
| untable _time Measure Value
| eval Metric=mvindex(split(Measure,": "),0),uri=mvindex(split(Measure,": "),1)
| fields - Measure
| eval time_uri=_time."__".uri
| fields - uri - _time
| xyseries time_uri Metric Value
| eval _time=mvindex(split(time_uri,"__"),0),uri=mvindex(split(time_uri,"__"),1)
| fields - time_uri

 

Thanks
KV
▄︻̷̿┻̿═━一

If this reply helps you, an upvote would be appreciated.

0 Karma

nouraali
Explorer

this is not helping, i got the same output as attached

nouraali_0-1621782958447.png

 

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@nouraali 

 

Are getting other URI in below search?

source="ds1.csv" host="vgspl11hr" index="sfp" sourcetype="csv"
| fields _time,URI,Bytes 
| append [| inputlookup uri.csv | table URI ] 
| timechart span=1m  avg(Bytes) AS avg_bytes, stdev(Bytes) AS std_bytes by URI limit=0
0 Karma

nouraali
Explorer

When i specify a time window of 4 mins  (5/18/21 2:01:00.000 AM to 5/18/21 2:05:00.000 AM), the query returns all URIs .

when i specify a time window of 2 mins  (5/18/21 2:01:00.000 AM to 5/18/21 2:03:00.000 AM), the query returns the records for the existing URIs in that time window, in my case URI=a.

So the issue occurs when the time window in which i am running the search is not having events with URI=b.

However, i was able to sort this out by using:

| sort _time
| append
[| inputlookup uri.csv
| table uri]
| rename _time AS t
| filldown t
| rename t AS _time
| dedup _time uri

 

This way a record will be created for URI=b in the last bucket in the time window.

0 Karma
Get Updates on the Splunk Community!

Prove Your Splunk Prowess at .conf25—No Prereqs Required!

Your Next Big Security Credential: No Prerequisites Needed We know you’ve got the skills, and now, earning the ...

Splunk Observability Cloud's AI Assistant in Action Series: Observability as Code

This is the sixth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Answers Content Calendar, July Edition I

Hello Community! Welcome to another month of Community Content Calendar series! For the month of July, we will ...