Splunk Search

Get column count as new column

keronedave
Explorer

I have three columns from a search query. I would like to count the items in one column and display it next to the other two columns. EG

Column A Column B Column C (count of columnB)
ip add 1 vuln1 4
vuln2
vuln3
vuln4

ip add 2 vuln1 2
vuln2

etc.

query:
.. | transaction ipadd |table ipadd, vuln

All help highly appreciated.

0 Karma
1 Solution

woodcock
Esteemed Legend

Like this:

 YourBaseSearchHere
| eventstats dc(FieldNameToCountValuesOfHere) BY Other Field Names Here But Not The Field Name In the dc()

View solution in original post

msmithgsky
New Member

What if you wanted to list the vuln columns and a total number at the end of each row, not summarize the values on the vuln column but count the number of vuln columns. Seems addtotals only allows for sum and not a count.

ipadd vuln1 vlun2 vuln3...... total columns

0 Karma

woodcock
Esteemed Legend

Like this:

 YourBaseSearchHere
| eventstats dc(FieldNameToCountValuesOfHere) BY Other Field Names Here But Not The Field Name In the dc()

View solution in original post

keronedave
Explorer

Hi Woodcock,

Thanks for your response,

Your solution worked a treat!!!. I did amend it a bit.

basesearch
| transaction ipadd
| eventstats dc(columnB) as columnC by ipadd | table columnA, columnB, columnC
0 Karma

woodcock
Esteemed Legend

Great! Now get rid of that nasty transaction. It will cause you no end of trouble. There are some good hints in other answers.

0 Karma

niketnilay
Legend

You can do this though stats instead of transaction if ipadd is the only key for correlation

 <YourBaseSearch>
| stats values(vuln) as vuln dc(vuln) as VulnCount by ipadd

You do not need transaction

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

gcusello
SplunkTrust
SplunkTrust

Hi keronedave,
try something like this:

your_search 
| transaction ipadd 
| stats values(ColumnB) AS ColumnB values(ColumnC) AS ColumnC count by ColumnA 
| rename count AS Column D

Bye.
Giuseppe

0 Karma

keronedave
Explorer

Hi Giuseppe,
Thanks for your response
Your query counts by the ipadd field giving a result of one. The columnC is hypothetical at the moment. It is what am looking to add. I tried the query as is and the result was columnC was always 1 which is less than the sum of values in columnB

This edited query gives a false result (higher count than what columnB has)

my search
| transaction ipadd
| stats values(ColumnB) AS ColumnB count by ColumnA
| rename count AS Column D

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!