topic Re: Field value count in Splunk Search

Field value count

iamniks — Mon, 28 Sep 2020 10:06:50 GMT

csv log file data

PROJ_NAME TAG_NAME STATUS

WIWEB-A WIWEB-A_1 PASSED
WIWEB-A WIWEB-A_2 FAILED
WIWEB-A WIWEB-A_3 PASSED
WIWEB-B WIWEB-B_1 PASSED

WIWEB-C WIWEB-C_1 SKIPPED
PI-A PI-A_1 CANCELLED
PI-A PI-A_2 PASSED
PI_B PI-A_1 CANCELLED

DC_A DC_A_1 FAILED
DC_B DC_B_1 FAILED

We are expecting the results to come in the below format.

PROJ COUNT

WIWEB* 5
PI* 3
DC* 2

STATUS WIWEB PI DC

PASSED 3 1 0
FAILED 1 0 2
CANCELLED 0 2 0
SKIPPED 1 0 0

can you please suggest queries for the above two report formats?

Thanks

Re: Field value count

eelisio2 — Mon, 28 Sep 2020 10:06:57 GMT

Using your fieldnames above, the first table should be:
sourcetype="yoursourcetype" | rex field=PROJ_NAME "(?.*)[-_]" | stats count by PROJ

For the second table:
sourcetype="yoursourcetype" | rex field=PROJ_NAME "(?.*)[-_]" | stats count by STATUS, PROJ | xyseries STATUS PROJ count

or
sourcetype="yoursourcetype" | rex field=PROJ_NAME "(?.*)[-_]" | contingency STATUS, PROJ
You'll get row and column totals automatically.

Re: Field value count

eelisio2 — Wed, 16 Nov 2011 14:31:22 GMT

Make sure PROJ is upper case within the angle brackets in the rex command. There was an issue with the pasted text.

Re: Field value count

iamniks — Mon, 28 Sep 2020 10:07:10 GMT

Hi, thanks for the reply. However, we would like to group the value in PROJ_NAME field such as all value starting with WIWEB... as group-1, all DC.... as group-2, all PI... as group-3.
Then have count from TAT_NAME filed group wise along with count of status for that group.

Name: total
group-1 5
group-2 3
group-3 2

status: group-1 group-2 group-3
passed 3 1 0
failed 1 0 2

Hope this clarifies.

Re: Field value count

eelisio2 — Mon, 28 Sep 2020 10:07:18 GMT

I used the following data to test.
11/16/11 8:58:09.000 AM, WIWEB-A, WIWEB-A_1, PASSED
11/16/11 8:58:09.000 AM, WIWEB-A, WIWEB-A_2, FAILED
11/16/11 8:58:09.000 AM, WIWEB-A, WIWEB-A_3, PASSED
11/16/11 8:58:09.000 AM, WIWEB-B, WIWEB-B_1, PASSED
11/16/11 8:58:09.000 AM, WIWEB-C, WIWEB-C_1, SKIPPED
11/16/11 8:58:09.000 AM, PI-A, PI-A_1, CANCELLED
11/16/11 8:58:09.000 AM, PI-A, PI-A_1, PASSED
11/16/11 8:58:09.000 AM, PI-B, PI-B_1, CANCELLED
11/16/11 8:58:09.000 AM, DC_A, DC_A_1, FAILED
11/16/11 8:58:09.000 AM, DC_B, DC_B_1, FAILED

i created field extractions to match your fieldnames.

Re: Field value count

eelisio2 — Mon, 28 Sep 2020 10:07:21 GMT

The following search:
sourcetype="answerstest" | rex field=PROJ_NAME "(?.*)[-_]" | contingency STATUS, PROJ

uses a regex to pull out the prefix of the PROJ_NAME field and create a new field called PROJ which is used in the results table (which should match your second example)

    STATUS          WIWEB   PI  DC  TOTAL

1 PASSED 3 1 0 4
2 FAILED 1 0 2 3
3 CANCELLED 0 2 0 2
4 SKIPPED 1 0 0 1
5 TOTAL 5 3 2 10

Re: Field value count

eelisio2 — Mon, 28 Sep 2020 10:07:23 GMT

sourcetype="answerstest" | rex field=PROJ_NAME "(?.*)[-_]" | stats count by PROJ

yields the following:

    PROJ    count

1 DC 2
2 PI 3
3 WIWEB 5

Unfortunately, my cut and paste is being re-formatted.
Again, make sure that PROJ is in upper case within the angle brackets that are in the rex command.