topic Re: How to add two fields together based on a condition? in Splunk Search

How to add two fields together based on a condition?

SplvnkGirl — Tue, 12 Apr 2016 16:57:25 GMT

Background Information:

I have an index from a tool that is being fed into Splunk. The tool has a score field and a user field. However, the tool sometimes gives two separate scores for the same user (because of a session issue). I'm trying to create a panel in Splunk that shows the score for each user.

Question:

Is there some type of sum or eval statement I can use to add scores together if a particular user has two separate scores?

This is my search so far...

index=test sourcetype=test2 | WHERE score>=100 | eval Date=strftime(_time, " %d %b %Y %H:%I:%S") | table Date, user, score

Re: How to add two fields together based on a condition?

sundareshr — Tue, 12 Apr 2016 17:24:38 GMT

You can use the if statement.. like this .. | eval score=if(user>0, user+scrore, score) OR

what if you always add the two fields .. | eval score=score+user, if the user field is null, score will be just score.

The 3rd option, not sure this is right for your situation .. | eval score=coalesce(score, user). this will work ONLY if only one or the other has a value. if both have values, this will not add.

Re: How to add two fields together based on a condition?

SplvnkGirl — Tue, 12 Apr 2016 18:40:43 GMT

the thing is user is not an integer...can this be achieved via Distinct Count somehow?

Re: How to add two fields together based on a condition?

twinspop — Tue, 12 Apr 2016 18:49:19 GMT

I'm not sure I entirely follow the requirements. Some notes:

Move the score>=100 qualifier into the main search.

Use stats maybe to aggregate the data:

index=test sourcetype=test2 score>=100 | 
stats sum(score) as Score latest(_time) as _time by user | 
fields _time, user, Score

Re: How to add two fields together based on a condition?

sundareshr — Tue, 12 Apr 2016 19:06:20 GMT

I misunderstood. Which field has the second score? Are both scores on the same event? or are they separate events? If they are different events, you can summaries like this

.... | stats sum(score) as score sum(second_score) as second_score by user | addtotals

This will add a total column with the sum of score and second_score.

Re: How to add two fields together based on a condition?

SplvnkGirl — Tue, 12 Apr 2016 20:17:30 GMT

So I have a tool that gives user's scores. I have logs coming into Splunk for this tool. For some reason the logs in splunk shows one user with two separate scores when I use this search:

index=test sourcetype=test2 | WHERE score>=100 | eval Date=strftime(_time, " %d %b %Y %H:%I:%S") | table Date, user, score

I'm only concerned about users who have a score if 100 or above. The user who shows up twice in Splunk has a score above 100 in my tool but on the logs in Splunk the user is shown twice with two different scores (that add up to over 100). Basically the user shows up in the table twice.

example

user | score

I'm trying to achieve this:

user | score

bsmith | 104
kwarren | 112

Re: How to add two fields together based on a condition?

sundareshr — Tue, 12 Apr 2016 21:39:07 GMT

Try like this

index=test sourcetype=test2  | eval Date=strftime(_time, " %d %b %Y %H:%I:%S") | stats sum(score) as score by Date user | where score>=100

Re: How to add two fields together based on a condition?

SplvnkGirl — Wed, 13 Apr 2016 14:03:12 GMT

Hi,

This returns 0 results. It's most likely because I don't currently have a user who has a score over 100. I need the search to add the scores or combine the rows of users who show up more than once in the logs. I removed the table and clicked the "user" interesting fields to show that there are two users who have a count of 2. That makes them show up twice in the logs with two separate scores. I need a way to combine any user that has a count of more than 1 to show their total score. Thanks for your continued help!

Screenshot
http://pasteboard.co/9MCXOBt.png

Re: How to add two fields together based on a condition?

sundareshr — Thu, 14 Apr 2016 15:54:34 GMT

Should there have been users with score greater than 100?

That | stats sum(score) by date user command will add all the score for the same user. So, in your example if there are two events for a user, the stats command will add the score from both events. You could test by removing the | where score>=100 to see if there are duplicate users and if there are any users with score greater than 100