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()

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

niketn
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
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...