Splunk Search

How to use values of column as column name?

apple143
Engager

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

  • If I could do like above not only based on time space but also one of other fields, please let me know..
0 Karma
1 Solution

FrankVl
Ultra Champion

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

View solution in original post

0 Karma

FrankVl
Ultra Champion

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

View solution in original post

0 Karma

apple143
Engager

WOW that's what I want!
Thank you very much.

You save my life.. Thank you!!

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

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
0 Karma

apple143
Engager

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?

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

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

0 Karma

apple143
Engager

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

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@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

0 Karma

apple143
Engager

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 😞

0 Karma

FrankVl
Ultra Champion

See my answer below, think that does what you want.

0 Karma

FrankVl
Ultra Champion

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.

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.