Hello Guys! Thank you in advance for your help ,
My data: Events that contain a field named SEGT which may be empty or may contain a unique number that can be repeated for example: SEGT=[1,1," ", 2, " ", 4, 4587, 7856, " "]
what I am trying to do: Create a table with 2 columns first column named Empty which will count all the event with the field SEGT="" and second column named RES with all the distinct count of the values in the field SEGT that are not empty.
My Code:
| Search
| stats count(eval(SEGT="")) As Empty
| stats dc(eval(SEGT!="")) As RES
| Table Empty RES
But I'm not getting the results that I want, and I will really appreciate if you guys could help me out!
hi @andres91302,
| eventstats count as TOTAL
--- counts the total number of events retaining all the raw events. TOTAL value will be the same for all the rows.
| where match(SEGT, "\d+")
--- where command evaluates match function to filter search results. The match function returns TRUE if the regular expression finds a match against any substring of the string value. In this case a number in field SEGT.
| stats dc(SEGT) as RES, latest(TOTAL) as TOTAL, count(SEGT) as SEGT_CNT
--- latest returns the chronologically latest seen occurrence of a value in TOTAL. We just need the first/latest value as the TOTAL is the same for all the rows.
I did some correction in your search. Can you please try below search?
YOUR_SEARCH
| stats count(eval(trim(SEGT)=="")) As Empty, dc(eval(tonumber(SEGT))) as RES
| table Empty RES
Sample Search:
| makeresults | eval SEGT="1,1, ,2, ,4,4587,7856, ", SEGT=split(SEGT,","), OtherData="hello" | mvexpand SEGT
| rename comment as "Upto Now is sample data only"
| stats count(eval(trim(SEGT)=="")) As Empty, dc(eval(tonumber(SEGT))) as RES
| table Empty RES
hi @andres91302,
Look like dc(eval()) returns 1 always.
Try this:
| eventstats count as TOTAL
| where match(SEGT, "\d+")
| stats dc(SEGT) as RES, latest(TOTAL) as TOTAL, count(SEGT) as SEGT_CNT
| eval Empty = TOTAL - SEGT_CNT
| table Empty, RES
If this reply helps you, an upvote/like would be appreciated.
hi @andres91302,
| eventstats count as TOTAL
--- counts the total number of events retaining all the raw events. TOTAL value will be the same for all the rows.
| where match(SEGT, "\d+")
--- where command evaluates match function to filter search results. The match function returns TRUE if the regular expression finds a match against any substring of the string value. In this case a number in field SEGT.
| stats dc(SEGT) as RES, latest(TOTAL) as TOTAL, count(SEGT) as SEGT_CNT
--- latest returns the chronologically latest seen occurrence of a value in TOTAL. We just need the first/latest value as the TOTAL is the same for all the rows.
Oh wow! THANK YOU SO SO MUCH for this.... you have no idea what it means to me, i's because people like you that I belive in a better world ahead. thank you for your detailed explanation, I wanna praise you!