Hello. I've come to ask again continuously the question I asked few days ago
This is my last question: https://answers.splunk.com/answers/664833/how-to-separate-rows-as-column.html
My data have 3 fields: LastName, FirstName, Age
I'm using query last 2days
| tstats count where LastName=* by FirstName, Age, _time span=1d prestats=true
| stats count by FirstName, Age, _time
then I have the result like below
FirstName--------------Age---------------_time--------------------count
A--------------------------24------------------2018-06-11-----------11
A--------------------------24------------------2018-06-12-----------22
A--------------------------30------------------2018-06-11-----------33
A--------------------------30------------------2018-06-12-----------44
B--------------------------26------------------2018-06-11-----------55
B--------------------------26------------------2018-06-12-----------66
and I want to make a result like
FirstName--------------Age---------------2daysago_count---------1dayago_count
A--------------------------24-----------------11----------------------------22
A--------------------------30-----------------33----------------------------44
B--------------------------26-----------------55----------------------------66
(Not for this question, but I'm gonna make a new column to calculate differences between 2daysago_count and 1dayago_count)
What should I do?
No matter which way you search, if I can use "tstats" (for time reduction)
You can change from the first table to second one or make a new search or whatever
Help please
Try this:
| tstats count where LastName=* by FirstName, Age, _time span=1d prestats=true
| stats count by FirstName, Age, _time
| eval 2daysago_count = if(_time<relative_time(now(), "-1d@d"),count,0)
| eval 1dayago_count = if(_time>=relative_time(now(), "-1d@d"),count,0)
| stats sum(1dayago_count) as 1dayago_count sum(2daysago_count) as 2daysago_count by FirstName, Age
Try this:
| tstats count where LastName=* by FirstName, Age, _time span=1d prestats=true
| stats count by FirstName, Age, _time
| eval 2daysago_count = if(_time<relative_time(now(), "-1d@d"),count,0)
| eval 1dayago_count = if(_time>=relative_time(now(), "-1d@d"),count,0)
| stats sum(1dayago_count) as 1dayago_count sum(2daysago_count) as 2daysago_count by FirstName, Age
WOW that's what I want!
Thank you very much.
You save my life.. Thank you!!
Try
| tstats count where LastName=* by FirstName, Age, _time span=1d prestats=true
| stats count by FirstName, Age, _time
|stats ealiest(count) as 2daysago_count,latest(count) as 1dayago_count by FirstName, Age
Wow It works! Thank you.
Now I found some minor problem here,
When a count is zero, the the latest value and the earliest value are same (I mean printed the same value, cause there is only one value)
So I cannot calculate the difference between them.
How can I make it?
Even though the count is 0, it should be printed under 2days and 1 days since its split by Firstname and age. If this is not the case, please provide some sample where you have this issue. Happy to assist
In my case, one person makes data for two days. So I have to count each day by person.
But, if some person(ex. Name is AA and Age is 35) had not made any event 2 days ago (1 day ago's count is 50), I get table like this
FirstName--------------Age---------------2daysago_count---------1dayago_count
AA------------------------35-----------------50----------------------------50
and I want to make the table like below
FirstName--------------Age---------------2daysago_count---------1dayago_count
AA------------------------35-----------------0------------------------------50
@apple143, ok so where is the problem now? or are you not getting this? It would be helpful if you could say what are you getting and what you expect to get
My problem is that I cannot make the later one in my last comment.
I am only able to get the former one.
Actually I solved the problem searching twice.
Like
(table 1)
| where _time=strftime(time(), "%Y-%m-%d")
| stats count as today by FirstName, Age
| join type=left FirstName, Age
[ (table 1)
| where _time=strftime(time()-86400, "%Y-%m-%d")
| stats count as yesterday by FirstName, Age]
but I don't want to search twice for separate them.
Sorry to bother you 😞
See my answer below, think that does what you want.
The problem is that your solution assumes there are multiple entries for each Firstname,Age combination. 1 for each day. If one of those entries doesn't exist, your earliest and latest will give the same outcome, rather than correctly reflecting that one of the two columns should be 0.