Splunk Search

How do you return a table of a value by a department and then display it by how many days ago it occurred ?

ryhluc01
Communicator

I need to return a table of a value by a department and then display it by how many days ago it occurred (Very Important, Please Help) Thank you!

I need a table of the average AMI age by the department for the past 5 days (today included) rounded to the 10th power.

I'm having problems trying to make this eval return the day I want based on the AMI avg calculation.

This is what I have so far:

index=Foo "DataStringValue"="AMI Age"  
| stats dedup_splitvals=t count AS "Count of 1530130937.1457581"  by Department.StringValue, ami_date.StringValue 
| sort Department.StringValue 
| fields - _span  
| rename ami_date.StringValue AS Date, Department.StringValue AS Vertical, instance_id.StringValue AS Instance_ID  
| eval C=strptime(Date,"%Y-%m-%dT%H:%M:%S.%Q") 
| eval A=((now()-C)/86400) 
| stats avg(A)  as Age by Vertical

Ive tried adding:

| eval Time=(split(Date,"T")) 
| eval t=mvindex(Time,0)

I've also tried:

| eval  "Average Age Yesterday"=relative_time(now(),"-1d@d")
| eval  "Average Age 2 days ago"=relative_time(now(), "-2d@d")
0 Karma
1 Solution

skoelpin
SplunkTrust
SplunkTrust

By using stats your dropping time in line 2. If you want the average of the entire 5 day period, you can put it all in a single bucket by adding | bin _time span=5d

Try this

index=Foo "DataStringValue"="AMI Age" 
| bin _time span=5d
| stats dedup_splitvals=t count AS "Count of 1530130937.1457581" by _time, Department.StringValue, ami_date.StringValue 
| sort Department.StringValue 
| fields - _span 
| rename ami_date.StringValue AS Date, Department.StringValue AS Vertical, instance_id.StringValue AS Instance_ID 
| eval C=strptime(Date,"%Y-%m-%dT%H:%M:%S.%Q") 
| eval A=((now()-C)/86400) 
| stats avg(A) as Age by _time, Vertical

View solution in original post

skoelpin
SplunkTrust
SplunkTrust

By using stats your dropping time in line 2. If you want the average of the entire 5 day period, you can put it all in a single bucket by adding | bin _time span=5d

Try this

index=Foo "DataStringValue"="AMI Age" 
| bin _time span=5d
| stats dedup_splitvals=t count AS "Count of 1530130937.1457581" by _time, Department.StringValue, ami_date.StringValue 
| sort Department.StringValue 
| fields - _span 
| rename ami_date.StringValue AS Date, Department.StringValue AS Vertical, instance_id.StringValue AS Instance_ID 
| eval C=strptime(Date,"%Y-%m-%dT%H:%M:%S.%Q") 
| eval A=((now()-C)/86400) 
| stats avg(A) as Age by _time, Vertical

ryhluc01
Communicator

@skoelpin
I'm trying to display the avg age for each day (up to five days) by the department

So it would look like:
Department--------average age today----average age yesterday
dept 1------------------------7654.8------------------------9543.2
dept 2------------------------1234.6 -----------------------6789.1

0 Karma

skoelpin
SplunkTrust
SplunkTrust

Gotcha. You should change your bucket values to days then like this

index=Foo "DataStringValue"="AMI Age" 
 | bin _time span=1d
 | stats dedup_splitvals=t count AS "Count of 1530130937.1457581" by _time, Department.StringValue, ami_date.StringValue 
 | sort Department.StringValue 
 | fields - _span 
 | rename ami_date.StringValue AS Date, Department.StringValue AS Vertical, instance_id.StringValue AS Instance_ID 
 | eval C=strptime(Date,"%Y-%m-%dT%H:%M:%S.%Q") 
 | eval A=((now()-C)/86400) 
 | stats avg(A) as Age by _time, Vertical

ryhluc01
Communicator

@skoelpin 😕 No results found

0 Karma

skoelpin
SplunkTrust
SplunkTrust

What's your timespan set to? You should set it back to atleast 5 days.

You can also troubleshoot this by removing line by line to see where the issue is like this

 index=Foo "DataStringValue"="AMI Age" 
  | bin _time span=1d
  | stats dedup_splitvals=t count AS "Count of 1530130937.1457581" by _time, Department.StringValue, ami_date.StringValue 

This should produce 3 columns and 5 rows. Each row will represent 1 days worth of counts. Once you confirm that is working, keep adding lines until your next transformational line. if the query above does not return results, then the issue is with the first 3 lines. You should then remove the stats command and see if it populates

index=Foo "DataStringValue"="AMI Age" 
  | bin _time span=1d
  | stats  count AS "Count of 1530130937.1457581" by _time, Department.StringValue, ami_date.StringValue 
  | rename ami_date.StringValue AS Date, Department.StringValue AS Vertical, instance_id.StringValue AS Instance_ID 
  | eval C=strptime(Date,"%Y-%m-%dT%H:%M:%S.%Q") 
  | eval A=((now()-C)/86400) 

Since you added 2 evals, you should have 2 additional columns. Once you confirm you have that, add that last stats on and see if you're getting results. If it worked up until that last stats then you know the issue is with the last line

Lastly, get rid of the dedup_splitvals=t flag and don't put quotes around your fieldnames, just the values. I'd also reccomend getting rid of those sorts. You should also save the formatting and renaming to after the transformational commands. Best for last

ryhluc01
Communicator

@skoelpin Hey so this is what my supervisor is thinking(were not sure but were unable to use _time to affect this departments result query): She thinks the data isnt using splunks _time but the AMI creation time instead.

So this is what the json is looking like in splunk:
"ApproximateFirstReceiveTimestamp": "1547719362928",
"SentTimestamp": "1547719362927"

We end up not pulling dates and times from years ago that wont use splunks _time

Any ideas? Also, thanks for all of your help.

0 Karma

skoelpin
SplunkTrust
SplunkTrust

Your event should have a timestamp near the beginning of the event, when data is indexed, Splunk will assign a timestamp which is what you see in a separate column before the event. _time is in epoch time format, so anytime you wanna split by day for instance, you need to split by Splunk's _time. Perhaps you can post an event or two to help clear this up? If Splunk is indeed using the AMI time and not the timestamp from the event then you need to fix that before addressing this.

Also, feel free to upvote if any of this has helped 🙂

ryhluc01
Communicator

I appreciate all your help! You were wonderful.

Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...