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

[ryhluc01]

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

[skoelpin · Accepted solution]

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]

@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

[skoelpin]

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]

@skoelpin 😕 No results found

[skoelpin]

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]

@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.

[skoelpin]

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]

I appreciate all your help! You were wonderful.