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.
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 -
What criteria do you use to decide whether to keep Charles or Steve?
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.
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"
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'
What events do you have for these "lost" names and how are these different from Charles?
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.
How are you counting the events?
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
| stats sum(red) AS red sum(blue) as blue count(keynumber) as events count as total by name
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
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
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,
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