Count Field values based on different criteria

[andres91302]

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! 

[kamlesh_vaghela]

@andres91302 

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

[andres91302]

Hi kamlesh_vaghela thank you for your reply (You look great with those glasses by the way) would you be so kind to explain why are you using count(eval(trim(SEGT)=="")) the function trim? and dc(eval(tonumber(SEGT))) tonumber? what do they do? I will be forever happy

[manjunathmeti]

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.

[andres91302]

hello manjunathmeti thank you for being so kind and helping me solving this task. would you please explain what does every line in your code do? Im new to splunk and I cant seem to find a good source of documentation to have as a reference... what does | where match(SEGT, "\d+") do? and why did you use the funtion latest() THANKS A LOT MY FRIEND

[manjunathmeti · Accepted solution]

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.

[andres91302]

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!