topic Re: How to create a table with a static column A and dynamic column B based on search values? in Splunk Search

How to create a table with a static column A and dynamic column B based on search values?

priyenshah6 — Wed, 14 Jan 2015 11:50:43 GMT

I want to create a table as:

Column A, Column B
LoginFailure, YES
LoginSuccess, NO
Account Lockout, YES

Basically YES/NO values are determined depending on if that event occurred.

My challenge is if LoginSuccess event never occurred, it does not get displayed in my report. So I need a report with static number of rows (which will be predefined list of events I want to monitor) and values as Yes/No depending if that event occurred for a given timeframe.

Re: How to create a table with a static column A and dynamic column B based on search values?

somesoni2 — Thu, 15 Jan 2015 23:06:55 GMT

Try something like this

your search producing columnA and ColumnB | append [| gentimes start=-1 | eval columnA="LoginFailure,LoginSuccess,Account Lockout" | table columnA | makemv delim="," columnA | mvexpand columnA] | stats values(columnB) as columnB by columnA | fillnull value="NO" columnB

Re: How to create a table with a static column A and dynamic column B based on search values?

ppablo — Wed, 21 Jan 2015 01:58:41 GMT

Hi @priyenshah6

Just following up with this question, but did @somesoni2's answer below help solve your question?

Re: How to create a table with a static column A and dynamic column B based on search values?

priyenshah6 — Wed, 21 Jan 2015 05:05:12 GMT

Thanks @somesoni2 for your response. I used Lookup approach to resolve the issue. Figured it was easy to manage Lookup file. Here is how I did it:

| inputlookups Events.csv | eval count=0 | append [search xyz [inputlookup Events.csv] | stats count by name] | stats sum(count) as total by name | eval status=if(total>0,"Yes","No")

here my Lookup file has list of all event names that I want to monitor and need status as Yes/No depending if they occurred in given time frame.