Search multiple column in logs and give count of respective column

[sunnyparmar]

Hi,

My question is divided into 2 parts -

1.) I have a log file in which there are about 20-22 columns but i want to search for only 3 columns in my query i.e. supplier name, supplier address and VAT Reg. . I want to make query for these 3 columns only and show the counts respective of columns.
AND
2.) with this my VAT Reg. no. column has 11 digit numeric vales for all the suppliers but I want that if any supplier VAT Reg. no. will be more than that 11 digit numeric value or if it contains alphabet in it then it will show it in some other column or give some error for that supplier so how it will be possible?

Thanks
Ankit

[woodcock · Accepted solution]

Based on this clairification:

my columns are like this (ABN column = VAT Reg.):
ABN |SupplierAddress | SupplierName |date_hour |date_mday | date_minute| date_month| date_second |date_wday |date_year |date_zone |eventtype| host |index |linecount |product| punct | source |sourcetype| splunk_server |splunk_server_group |tag |tag::eventtype |timeendpos |timestamp| timestartpos| vendor |_raw |_time|

This should do it:

   index=sand_box sourcetype="sc-kofax-extracts" | eval VATlen=len(ABN) | where VATlen>11 OR NOT isnum(ABN)

[sunnyparmar]

Apologies for replying late as I am doing some other works also.. based on your above given query it is running but showing all the columns where you have mentioned only ABN column and secondly now If i am running your first query that you gave me in starting [index=sand_box sourcetype="sc-kofax-extracts" $supplier name$=* $supplier address$=* $VAT Reg.$=* | eval VATlen=len(ABN) | where VATlen>11 OR NOT isnum(ABN)] now it is giving me result "no result found".. Do you have any idea why this happening with both queries?

Regards
Ankit

[woodcock]

Forget about the first answer (I just deleted it); it was full of conjecture and was rendered irrelevant by your clarification. If you need just those 3 fields, you can do this:

    index=sand_box sourcetype="sc-kofax-extracts" | table SupplierName SupplierAddress ABN

[sunnyparmar]

it is the tab delimited file from where splunk taking values so It is taking some null values as well as in some columns there is no values and only (|) sign are there so could you please give me some idea that how to avoid null values in table?

Regards
Ankit

[sunnyparmar]

Hey.. I got the data exactly I want so once again thanks to you but there is one little problem with the query

index=sand_box sourcetype="sc-kofax-extracts" | table SupplierName SupplierAddress ABN | stats values(*) as * by SupplierName, SupplierAddress | eval VATlen=len(ABN) | where VATlen>11 OR NOT isnum(ABN)

I am using above given query when i am executing it, it is showing the supplier whom ABN no. less than 11 digit but still one supplier is showing in result with 11 digit numeric number so do you have any idea why it is showing that supplier?

Thanks & Regards
Ankit

[woodcock]

It must be because there is leading or trailing whitespace around it, which will cause the isnum check to fail.

[sunnyparmar]

thanks .. I will dig it into it..

Regards
Ankit