topic Re: Pass field into a subsearch from stats command in Splunk Search

Pass field into a subsearch from stats command

ssaenger — Mon, 01 Feb 2021 13:07:37 GMT

Hi,

i have data

name	binary	keynumber
Steve	1100	12345
Steve	100	13246
Steve		12347
Charles		23456

I am trying to count the whether the position in the binary from right to left has a 1 in position 3 and 4 and as a percentage of the number of events.

eg result

name	events	4thbinary	3rdbinary	%4th	%3rd
Steve	3	1	2	33	66

i was trying to get the 4 position first as this will give me the Names with a binary entry, i then thought i could join and run a subsearch to get the all the events, i then wold do an appendcols to get entries with a 1 in the 3rd binary position in the string.

index=summary sourcetype=prod source=service binary NOT NULL
|eval red=substr(binary, -4, 1) |stats count(red) AS red by name

| join type=left name [search index=summary sourcetype=prod source=service
| dedup name keynumber
|stats count(keynumber) AS Events by name]

|appendcols [search index=summary sourcetype=prod source=service binary NOT NULL
|eval blue=substr(binary, -3, 1) |stats count(blue) AS blue by name]

|table name events red blue

however i cannot get my events to equal the correct value, it only returns a value if the binary field is populated.

i have looked at map and field but could also not get these to work.

Re: Pass field into a subsearch from stats command

ITWhisperer — Mon, 01 Feb 2021 16:10:43 GMT

Try something like this

index=summary sourcetype=prod source=service | eval red=substr(binary, -4, 1) | eval blue=substr(binary, -3, 1) | fillnull value=0 red blue | stats sum(red) AS red sum(blue) as blue by name

Re: Pass field into a subsearch from stats command

ssaenger — Tue, 02 Feb 2021 09:54:19 GMT

Hi ITWhisperer,

thanks, based on this i have added

| stats sum(red) AS red sum(blue) as blue count(keynumber) as events by name

However i also get the name charles returned.

How would i only get the name Steve returned as per the results table above?

i did try
binary not null

however, i loose the line with key number 12347 -

Re: Pass field into a subsearch from stats command

ITWhisperer — Tue, 02 Feb 2021 17:00:04 GMT

What criteria do you use to decide whether to keep Charles or Steve?

Re: Pass field into a subsearch from stats command

ssaenger — Wed, 03 Feb 2021 09:39:48 GMT

i was using the criteria

binary not null

so this will give me the entries for steve.

However from this i then want the total number of entries for Steve.

This is why i initially thought i would need to use a join, as i would pass the name field in order to get the total number of fields.

Re: Pass field into a subsearch from stats command

ITWhisperer — Wed, 03 Feb 2021 09:53:08 GMT

So any name with at least 1 non-null binary?

The "where" might need to be "search"

Re: Pass field into a subsearch from stats command

ssaenger — Wed, 03 Feb 2021 10:22:56 GMT

hmm, i seem to lose results by using this method, tried search as well as the where clause.

I lost two 'names' compared to when i used 'binary NOT NULL'

Re: Pass field into a subsearch from stats command

ITWhisperer — Wed, 03 Feb 2021 10:27:15 GMT

What events do you have for these "lost" names and how are these different from Charles?

Re: Pass field into a subsearch from stats command

ssaenger — Wed, 03 Feb 2021 10:45:05 GMT

I dont seem to get any results for the name charles - or entries which dont have a populated binary field - which is great.

However, it doesnt seem to count all the events correctly.

Re: Pass field into a subsearch from stats command

ITWhisperer — Wed, 03 Feb 2021 10:49:27 GMT

How are you counting the events?

Re: Pass field into a subsearch from stats command

ssaenger — Wed, 03 Feb 2021 11:01:34 GMT

i have edited the line you gave me to include

| stats sum(red) AS red sum(blue) as blue count(keynumber) as events by name

Re: Pass field into a subsearch from stats command

ITWhisperer — Wed, 03 Feb 2021 11:03:05 GMT

| stats sum(red) AS red sum(blue) as blue count(keynumber) as events count as total by name

Re: Pass field into a subsearch from stats command

ssaenger — Wed, 03 Feb 2021 11:21:01 GMT

Hi,

i am using the following to check on an individual name - this differs when i remove

name="Steve"

also differs if i add

binary NOT NULL

index=summary sourcetype=prod source=service name="Steve"
| eval red=substr(binary, -4, 1)
| eval blue=substr(binary, -e, 1)
| stats sum(red) AS Red sum(blue) as Blue count(keynumber) as Events count as total by name
| eval Percentage_Red=(Red/Events)*100
| eval Percentage_Red=round(Red)
| eval Percentage_Blue=(Blue/Events)*100
| eval Percentage_Blue=round(Percentage_Blue)
| table name Events Red Blue Percentage_Red Percentage_Blue
| sort name

adding your entry of

| search binary !=0
| fillnull value=0 red blue

makes no difference when a name field is declared

Re: Pass field into a subsearch from stats command

ITWhisperer — Wed, 03 Feb 2021 11:37:01 GMT

There are some typos here so it is not clear what you have tried

The answer to my previous question is that you are counting keynumber to determine how many events each name has so you don't need the count as total I suggested

Using where binary != 0 is different to where binarycount != 0

Re: Pass field into a subsearch from stats command

ssaenger — Thu, 04 Feb 2021 08:56:46 GMT

Hi,

thanks. In the end i used a nested search to extract the name using the binary NOT NULL, then passed this through to the main search.

Thanks,