topic Re: How to count distinct occurrences of one field based on another field? in Splunk Search

How to count distinct occurrences of one field based on another field?

raduenea — Mon, 29 Jun 2015 14:46:10 GMT

I have a CSV file similar to the one below:
timestamp, fullcommand, testname, details
time1, c1, test1, details1
time2, c2, test1, details2
time3, c3, test1, details3
time4, c1, test2, details4
time5, c3, test2, details5
time6, c1, test3, details6
time7, c2, test3, details7
time8, c3, test3, details8
time9, c4, test3, details9
time10, c5, test4, details10
...

I'm trying to extract (and count) all tests that contain c2 and create a pie chart that should have 2 values: count of distinct tests that contain c2 and all other tests that don't contain c2 command in any of their csv entries.

I'm using a search like the one below:

source="output.csv" | eval bool=if(match(fullcommand,".*c2.*"),"Command2 Tests", "Other tests") | chart dc(testname) as Tests by bool

This seems to be getting me the correct c2 tests distinct count, but when counting all other tests I get the total amount of tests. I suspect this is because the tests that are matched by my if condition, also have entries that do not match my condition and therefore are counted also as tests that don't contain c2.

Do you have any suggestion as to how I should build my search in order to get the desired result? Is it possible? or do I have to change the CSV structure somehow?

Re: How to count distinct occurrences of one field based on another field?

stephanefotso — Mon, 29 Jun 2015 15:01:38 GMT

Hello, Here is an option:

 source="output.csv" fullcommand="c2"|stats count(testname) as  "Command2 Tests"  |join [search source="output.csv"  fullcommand!="c2"|stats count(testname) as  "Other tests"  ] |table  "Command2 Tests" "Other tests"

Thanks

Re: How to count distinct occurrences of one field based on another field?

woodcock — Mon, 29 Jun 2015 15:06:30 GMT

If I understand you correctly, this should do it:

source="output.csv" | eval c2test=if(match(fullcommand,".*c2.*"),testname,null()) | stats dc(c2test) AS "Command2 Tests" dc(testname) AS AllTests | eval "Other Tests" = AllTests - $Command2 Tests$ | fields - AllTests

Re: How to count distinct occurrences of one field based on another field?

raduenea — Tue, 30 Jun 2015 08:37:28 GMT

Thanks a lot for your answer. I finally did something like this:
source="output.csv" | eval c2test=if(match(fullcommand, ".c2."),testname,null()) | stats dc(c2test) AS "Command2 Tests", dc(testname) AS AllTests | eval other = AllTests - $Command2 Tests$ | fields - AllTests | transpose

I had to transpose the stats table to be able to show the data also in a Pie Chart. I saw that Pie charts only support 1 column as data and one column as labels for that data.
Thanks again!

Re: How to count distinct occurrences of one field based on another field?

raduenea — Tue, 30 Jun 2015 08:38:43 GMT

Thanks a lot for your answer. I didn't manage to make it work like I wanted using your example. But you gave me an idea for another issue that I have. Thanks again!