Pass field into a subsearch from stats command

[ssaenger]

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.

[ssaenger]

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 - 

[ITWhisperer]

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

[ssaenger]

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.

[ITWhisperer]

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

index=summary sourcetype=prod source=service
| eventstats count(binary) as binarycount by name
| where binarycount != 0
| 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

The "where" might need to be "search"

[ssaenger]

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'

[ITWhisperer]

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

[ssaenger]

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.

[ITWhisperer]

How are you counting the events?

[ssaenger]

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

[ITWhisperer]

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

[ssaenger]

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

[ITWhisperer]

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

index=summary sourcetype=prod source=service name="Steve" 
| eventstats count(binary) as binarycount by name
| where binarycount != 0
| 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 count(keynumber) as Events 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

[ssaenger]

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,

[ITWhisperer]

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