topic Re: How to combine results from 2 servers into 1 combined field? in Splunk Search

How to combine results from 2 servers into 1 combined field?

echojacques — Fri, 22 Nov 2013 15:52:39 GMT

Hello,

Is there a way to combine the results for 2 different servers (DNS names) into a third field that becomes the 'combined' field? For example, a search returns the following:

Dest      Action    Total
Server1   Failure   10
Server2   Failure   20

How can I combine the results from Server 1 and Server 2 into a new field called Server3 (the combined field) to return the following:

Dest      Action    Total
Server3   Failure   30

The reason I ask is because Server1 and Server2 are really the same server. Server1 is the internal interface and Server2 is the external interface. However, these DNS names are treated as 2 different servers in Splunk and I'd like to combine the Totals that I'm tracking in my dashboards into a new "Server3" field. I hope this makes sense.

Thanks.

Re: How to combine results from 2 servers into 1 combined field?

alacercogitatus — Fri, 22 Nov 2013 16:06:50 GMT

Basic search and stats.

This just counts events by the Action in the event (Failure, Success, etc)

your_search | stats count by Action

If you have the number of failures in a field:

your_search | stats sum(failure_count_field) by Action

Re: How to combine results from 2 servers into 1 combined field?

echojacques — Fri, 22 Nov 2013 16:13:06 GMT

Thanks, but in my results, I have many other servers so I can't simply count by action. What I'd like to do is combine the results for 2 specific server names only... and leave the rest alone. Something like Server1+Server2=Server3.

Re: How to combine results from 2 servers into 1 combined field?

somesoni2 — Fri, 22 Nov 2013 16:14:55 GMT

You have only two servers in your logs or there could be other servers which have just one instance?

Re: How to combine results from 2 servers into 1 combined field?

alacercogitatus — Fri, 22 Nov 2013 16:15:18 GMT

try this:

your_search | eval new_host = case(host=="Server1","Server3",host=="Server2","Server3",1=1,host) | stats count by Action new_host

Re: How to combine results from 2 servers into 1 combined field?

echojacques — Fri, 22 Nov 2013 16:16:12 GMT

There are many other servers... so I need to peel out Server1 and Server2 (since they are the same server) and put them into a new result Server3...

Thanks

Re: How to combine results from 2 servers into 1 combined field?

echojacques — Fri, 22 Nov 2013 16:32:53 GMT

So here's my search along with my attempt of the suggested eval and it didn't work... you'll see that I'm trying to combine "MAIL001" and MAIL002" into "MAIL".

action=failure `authentication` | eval MAIL=case(host=="MAIL001",host=="MAIL002",1=1,host) | stats dc(_raw) as Total by user,src,dest,app,action | sort -Total

Re: How to combine results from 2 servers into 1 combined field?

alacercogitatus — Fri, 22 Nov 2013 17:05:51 GMT

You are missing sections of your case statement.

action=failure 'authentication' | eval new_host = case("host=="MAIL001","MAIL",host=="MAIL002","MAIL",1=1,host) | stats dc(_raw) as Total by user, src,dest,app,action,new_host | sort -Total

Re: How to combine results from 2 servers into 1 combined field?

echojacques — Fri, 22 Nov 2013 18:25:49 GMT

Thanks, I think you got me on the right track 🙂 I'll play around with this eval statement.

Re: How to combine results from 2 servers into 1 combined field?

Gilberto_Castil — Fri, 22 Nov 2013 21:21:30 GMT

An alternate way to accomplish your goal is to create a lookup table which offers an alias for the servers you have identified. For instance, consider a case in which you have multiple servers in your data:

Fri Nov 22 12:09:01 EST 2013 Dest=Server1 Action=Failure
Fri Nov 22 12:10:01 EST 2013 Dest=Server2 Action=Failure
Fri Nov 22 12:11:01 EST 2013 Dest=Server4 Action=Failure
Fri Nov 22 12:12:01 EST 2013 Dest=Server5 Action=Failure
Fri Nov 22 12:13:01 EST 2013 Dest=Server1 Action=Failure
Fri Nov 22 12:14:01 EST 2013 Dest=Server2 Action=Failure
Fri Nov 22 12:15:01 EST 2013 Dest=Server4 Action=Failure
Fri Nov 22 12:16:01 EST 2013 Dest=Server5 Action=Failure
Fri Nov 22 12:17:01 EST 2013 Dest=Server1 Action=Failure
Fri Nov 22 12:18:01 EST 2013 Dest=Server2 Action=Failure

When you enumerate the data in Splunk you see the results based on the data:

index=test Action="Failure" sourcetype="test-1385147713"
| stats count by Dest Action

Let's say that you create a lookup table with the alias for the targeted servers:

Dest,Name
Server2,Server3
Server1,Server3

You instantiate this by creating the CSV file under $SPLUNK_HOME\etc\apps\search\lookups\. In this case we name that file server_alias.csv.

We complement that with an entry in props.conf and transforms.conf as follows:

# props.conf
[test-1385147713]
LOOKUP-server_aliases = server_aliases Dest AS Dest OUTPUTNEW

# transforms.conf
[server_aliases]
default_match = NULL
filename = server_aliases.csv
min_matches = 1

You can do all of this work through the SplunkWeb GUI. If you prefer to do that way, please follow the guidelines in our documentation.

You would then use a quick trick to enumerate the value like this:

index=test Action="Failure" sourcetype="test-1385147713"
| eval Name=if(Name="NULL",Dest,Name)
| stats count by Name Action

This is now a fairly easy and flexible way to achieve your initial goal.

-gc