Building for the Splunk Platform

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!

Using Machine Learning for Hunting Security Threats

REGISTER NOW Seeing the exponential hike in global cyber threat spectrum, organizations are now striving more ...

Security Highlights | November 2022 Newsletter

 November 2022 2022 Gartner Magic Quadrant for SIEM: Splunk Named a Leader for the 9th Year in a RowSplunk is ...

Platform Highlights | November 2022 Newsletter

 November 2022 Skill Up on Splunk with our New Builder Tech Talk SeriesCan you build it? Yes you can! *play ...