topic Re: How do I sort my results from my sample data into different columns? in Splunk Search

How do I sort my results from my sample data into different columns?

bharpur183 — Thu, 03 Nov 2016 20:22:17 GMT

I have the below data that I want to sort and show up in different columns as
1. Device (that shows the different rpp's)
2. Daily_AVG
3. Threshold
4. Date

11/3/16
10:00:06.000 AM 
*2016/11/03 10:00:06|     688|  4032| 14319|          opennms|             1323|BADE_DB-RPP-1A-4-3 CURRENT_IZ|10:00:06 opennms:epms:1323:bade_db-rpp-1a-4-3 current_Iz:BADE_Threshold:        Device=rpp-1a-4-3 current_Iz  Daily_AVG=179.99  Threshold=180.00  UOM=amps 
ALMN_cond_id = 4032 ALMN_mach_id = 14319 ALMN_procmgr = opennms ALMN_uniq_alert_id = BADE_DB-RPP-1A-4-3 CURRENT_IZ DCNETWORK = 1323 almn_id = 4032 almn_msg = 10:00:06 opennms:epms:1323:bade_db-rpp-1a-4-3 current_Iz:BADE_Threshold: Device=rpp-1a-4-3 current_Iz Daily_AVG=179.99 Threshold=180.00 UOM=amps date_hour = 10 date_mday = 3 date_minute = 0 date_month = november date_second = 6 date_wday = thursday date_year = 2016 date_zone = local host = nymsgmon1 index = conr linecount = 1 machine_id = 14319 machine_name = cnjr-opennms punct = *//_::|_____|__|_|__________|_____________|----_|: source = /bb/data/almn_filter120.out sourcetype = almn splunk_server = syslnypsplix16 timeendpos = 20 timestartpos = 1


11/3/16
10:00:05.000 AM 
*2016/11/03 10:00:05|     687|  4032| 14319|          opennms|              306|BADE_DB-RPP-A4-5-2-CURRENT-IZ|10:00:05 opennms:epms:306:bade_db-rpp-a4-5-2-current-Iz:BADE_Threshold:        Device=rpp-a4-5-2-current-Iz  Daily_AVG=182.79  Threshold=180.00  UOM=amps 
ALMN_cond_id = 4032 ALMN_mach_id = 14319 ALMN_procmgr = opennms ALMN_uniq_alert_id = BADE_DB-RPP-A4-5-2-CURRENT-IZ DCNETWORK = 306 almn_id = 4032 almn_msg = 10:00:05 opennms:epms:306:bade_db-rpp-a4-5-2-current-Iz:BADE_Threshold: Device=rpp-a4-5-2-current-Iz Daily_AVG=182.79 Threshold=180.00 UOM=amps date_hour = 10 date_mday = 3 date_minute = 0 date_month = november date_second = 5 date_wday = thursday date_year = 2016 date_zone = local host = nymsgmon1 index = conr linecount = 1 machine_id = 14319 machine_name = cnjr-opennms punct = *//_::|_____|__|_|__________|______________|------ source = /bb/data/almn_filter120.out sourcetype = almn splunk_server = syslnypsplix16 timeendpos = 20 timestartpos = 1


11/3/16
10:00:05.000 AM 
*2016/11/03 10:00:05|     686|  4032| 14319|          opennms|             1323|BADE_DB-RPP-1A-5-1 CURRENT_IY|10:00:05 opennms:epms:1323:bade_db-rpp-1a-5-1 current_Iy:BADE_Threshold:        Device=rpp-1a-5-1 current_Iy  Daily_AVG=181.79  Threshold=180.00  UOM=amps 
ALMN_cond_id = 4032 ALMN_mach_id = 14319 ALMN_procmgr = opennms ALMN_uniq_alert_id = BADE_DB-RPP-1A-5-1 CURRENT_IY DCNETWORK = 1323 almn_id = 4032 almn_msg = 10:00:05 opennms:epms:1323:bade_db-rpp-1a-5-1 current_Iy:BADE_Threshold: Device=rpp-1a-5-1 current_Iy Daily_AVG=181.79 Threshold=180.00 UOM=amps date_hour = 10 date_mday = 3 date_minute = 0 date_month = november date_second = 5 date_wday = thursday date_year = 2016 date_zone = local host = nymsgmon1 index = conr linecount = 1 machine_id = 14319 machine_name = cnjr-opennms punct = *//_::|_____|__|_|__________|_____________|----_|: source = /bb/data/almn_filter120.out sourcetype = almn splunk_server = syslnypsplix16 timeendpos = 20 timestartpos = 1


11/3/16
10:00:05.000 AM 
*2016/11/03 10:00:05|     685|  4032| 14319|          opennms|             1323|BADE_DB-RPP-1A-5-1 UTILIZATION|10:00:04 opennms:epms:1323:bade_db-rpp-1a-5-1 utilization:BADE_Threshold:        Device=rpp-1a-5-1 utilization  Daily_AVG=45.46  Threshold=45.00  UOM=% 
ALMN_cond_id = 4032 ALMN_mach_id = 14319 ALMN_procmgr = opennms ALMN_uniq_alert_id = BADE_DB-RPP-1A-5-1 UTILIZATION DCNETWORK = 1323 almn_id = 4032 almn_msg = 10:00:04 opennms:epms:1323:bade_db-rpp-1a-5-1 utilization:BADE_Threshold: Device=rpp-1a-5-1 utilization Daily_AVG=45.46 Threshold=45.00 UOM=% date_hour = 10 date_mday = 3 date_minute = 0 date_month = november date_second = 5 date_wday = thursday date_year = 2016 date_zone = local host = nymsgmon1 index = conr linecount = 1 machine_id = 14319 machine_name = cnjr-opennms punct = *//_::|_____|__|_|__________|_____________|----_|: source = /bb/data/almn_filter120.out sourcetype = almn splunk_server = syslnypsplix16 timeendpos = 20 timestartpos = 1


11/3/16
10:00:05.000 AM 
*2016/11/03 10:00:05|     684|  4032| 14319|          opennms|              306|BADE_DB-RPP-A4-5-2-UTILIZATION|10:00:04 opennms:epms:306:bade_db-rpp-a4-5-2-utilization:BADE_Threshold:        Device=rpp-a4-5-2-utilization  Daily_AVG=45.63  Threshold=45.00  UOM=% 
ALMN_cond_id = 4032 ALMN_mach_id = 14319 ALMN_procmgr = opennms ALMN_uniq_alert_id = BADE_DB-RPP-A4-5-2-UTILIZATION DCNETWORK = 306 almn_id = 4032 almn_msg = 10:00:04 opennms:epms:306:bade_db-rpp-a4-5-2-utilization:BADE_Threshold: Device=rpp-a4-5-2-utilization Daily_AVG=45.63 Threshold=45.00 UOM=% date_hour = 10 date_mday = 3 date_minute = 0 date_month = november date_second = 5 date_wday = thursday date_year = 2016 date_zone = local host = nymsgmon1 index = conr linecount = 1 machine_id = 14319 machine_name = cnjr-opennms punct = *//_::|_____|__|_|__________|______________|-----| source = /bb/data/almn_filter120.out sourcetype = almn splunk_server = syslnypsplix16 timeendpos = 20 timestartpos = 1



11/2/16
3:38:22.000 PM  
*2016/11/02 15:38:22|     273|  4032| 20463|          opennms|               98| ORANGEBURG-UPS|15:38:22 opennms:epms:98:Orangeburg-UPS:SCC-1B:active:p1:                   Operator Instructions: {TEAM 471717441} 
ALMN_cond_id = 4032 ALMN_mach_id = 20463 ALMN_procmgr = opennms ALMN_uniq_alert_id = ORANGEBURG-UPS DCNETWORK = 98 almn_id = 4032 almn_msg = 15:38:22 opennms:epms:98:Orangeburg-UPS:SCC-1B:active:p1: Operator Instructions: {TEAM 471717441} date_hour = 15 date_mday = 2 date_minute = 38 date_month = november date_second = 22 date_wday = wednesday date_year = 2016 date_zone = local host = nymsgmon1 index = conr linecount = 1 machine_id = 20463 machine_name = aob-opennms punct = *//_::|_____|__|_|__________|_______________|_-|:: source = /bb/data/almn_filter64.out sourcetype = almn splunk_server = syslnjpsplix19 timeendpos = 20 timestartpos = 1



10/30/16
7:28:35.000 AM  
*2016/10/30 07:28:35|     142|  4032| 20463|          opennms|               98| ORANGEBURG-UPS|07:28:35 opennms:epms:98:Orangeburg-UPS:SCC-1B:active:p1:                   Operator Instructions: {TEAM 471717441} 
ALMN_cond_id = 4032 ALMN_mach_id = 20463 ALMN_procmgr = opennms ALMN_uniq_alert_id = ORANGEBURG-UPS DCNETWORK = 98 almn_id = 4032 almn_msg = 07:28:35 opennms:epms:98:Orangeburg-UPS:SCC-1B:active:p1: Operator Instructions: {TEAM 471717441} date_hour = 7 date_mday = 30 date_minute = 28 date_month = october date_second = 35 date_wday = sunday date_year = 2016 date_zone = local host = nymsgmon1 index = conr linecount = 1 machine_id = 20463 machine_name = aob-opennms punct = *//_::|_____|__|_|__________|_______________|_-|:: source = /bb/data/almn_filter64.out sourcetype = almn splunk_server = syslnypsplix30 timeendpos = 20 timestartpos = 1


10/30/16
7:28:32.000 AM  
*2016/10/30 07:28:32|     141|  4032| 20463|          opennms|               98|ORANGEBURG-UTILITY|07:28:31 opennms:epms:98:Orangeburg-Utility:MV_B_1:sag_event:Duration=0.05:Min_PNV=73.80:Max_PNV=101.04 Operator Instructions: {TEAM 538412650} 
ALMN_cond_id = 4032 ALMN_mach_id = 20463 ALMN_procmgr = opennms ALMN_uniq_alert_id = ORANGEBURG-UTILITY DCNETWORK = 98 almn_id = 4032 almn_msg = 07:28:31 opennms:epms:98:Orangeburg-Utility:MV_B_1:sag_event:Duration=0.05:Min_PNV=73.80:Max_PNV=101.04 Operator Instructions: {TEAM 538412650} date_hour = 7 date_mday = 30 date_minute = 28 date_month = october date_second = 32 date_wday = sunday date_year = 2016 date_zone = local host = nymsgmon1 index = conr linecount = 1 machine_id = 20463 machine_name = aob-opennms punct = *//_::|_____|__|_|__________|_______________|-|::_ source = /bb/data/almn_filter64.out sourcetype = almn splunk_server = syslnypsplix30 timeendpos = 20 timestartpos = 1


10/30/16
7:28:31.000 AM  
*2016/10/30 07:28:31|     140|  4032| 20463|          opennms|               98|ORANGEBURG-UTILITY|07:28:31 opennms:epms:98:Orangeburg-Utility:MV_A_1:sag_event:Duration=0.05:Min_PNV=72.94:Max_PNV=101.26 Operator Instructions: {TEAM 538412650} 
ALMN_cond_id = 4032 ALMN_mach_id = 20463 ALMN_procmgr = opennms ALMN_uniq_alert_id = ORANGEBURG-UTILITY DCNETWORK = 98 almn_id = 4032 almn_msg = 07:28:31 opennms:epms:98:Orangeburg-Utility:MV_A_1:sag_event:Duration=0.05:Min_PNV=72.94:Max_PNV=101.26 Operator Instructions: {TEAM 538412650} date_hour = 7 date_mday = 30 date_minute = 28 date_month = october date_second = 31 date_wday = sunday date_year = 2016 date_zone = local host = nymsgmon1 index = conr linecount = 1 machine_id = 20463 machine_name = aob-opennms punct = *//_::|_____|__|_|__________|_______________|-|::_ source = /bb/data/almn_filter64.out sourcetype = almn splunk_server = syslnypsplix30 timeendpos = 20 timestartpos = 1


10/30/16
7:28:19.000 AM  
*2016/10/30 07:28:19|     138|  4032| 20463|          opennms|               98| ORANGEBURG-UPS|07:28:19 opennms:epms:98:Orangeburg-UPS:SCC-2B:active:p1:                   Operator Instructions: {TEAM 471717441} 
ALMN_cond_id = 4032 ALMN_mach_id = 20463 ALMN_procmgr = opennms ALMN_uniq_alert_id = ORANGEBURG-UPS DCNETWORK = 98 almn_id = 4032 almn_msg = 07:28:19 opennms:epms:98:Orangeburg-UPS:SCC-2B:active:p1: Operator Instructions: {TEAM 471717441} date_hour = 7 date_mday = 30 date_minute = 28 date_month = october date_second = 19 date_wday = sunday date_year = 2016 date_zone = local host = nymsgmon1 index = conr linecount = 1 machine_id = 20463 machine_name = aob-opennms punct = *//_::|_____|__|_|__________|_______________|_-|:: source = /bb/data/almn_filter64.out sourcetype = almn splunk_server = syslnjpsplix21 timeendpos = 20 timestartpos = 1

Re: How do I sort my results from my sample data into different columns?

gokadroid — Thu, 03 Nov 2016 20:58:42 GMT

If all four fields are already extracted as date, device, daily_avg, threshold then all you need to do is

your query to return date, device, daily_avg, threshold 
| table device, daily_avg, threshold, date

If date is not extracted then you can use _time

your query to return device, daily_avg, threshold 
| eval date=strftime(_time, "%Y-%m-%d")
| table device, daily_avg, threshold , date

If the fields are not already extracted then extract them first as below and then table:

your query to return events
|rex "(?<date>\d{4}\/\d{2}\/\d{2})\s.*Device\=(?<device>[\S]+)\s.*Daily_AVG\=(?<dailyAvg>[\S]+)\s*Threshold\=(?<threshold>[\S]+)\s*"
| table device, dailyAvg, threshold , date
| fillnull value="NULL" 
| search device!="NULL" AND dailyAvg!="NULL" AND threshold!="NULL" AND date!="NULL"

Updated the fillnull piece as per comments
See extraction here

Re: How do I sort my results from my sample data into different columns?

bharpur183 — Thu, 03 Nov 2016 21:08:23 GMT

I tried the above, but the output returns are column names with no data below them .The 4 columns are created but with no data

Re: How do I sort my results from my sample data into different columns?

bharpur183 — Thu, 03 Nov 2016 21:09:49 GMT

this is hwo it looks:

device daily_avg threshold date
2016-11-03
2016-11-03
2016-11-03
2016-11-03
2016-11-03

Re: How do I sort my results from my sample data into different columns?

gokadroid — Thu, 03 Nov 2016 21:10:36 GMT

Looks like fields are not extracted...use the third option in my answer above which extracts the information first and then tables.

your query to return events
|rex "(?<date>\d{4}\/\d{2}\/\d{2})\s.*Device\=(?<device>[\S]+)\s.*Daily_AVG\=(?<dailyAvg>[\S]+)\s*Threshold\=(?<threshold>[\S]+)\s*"
| table device, dailyAvg, threshold , date

Re: How do I sort my results from my sample data into different columns?

bharpur183 — Thu, 03 Nov 2016 21:16:47 GMT

Thanks @gokadriod , that worked great. The only thing now is that there a bunch of empty rows between the table now :

rpp-a4-5-2-utilization 45.63 45.00 2016/11/03
empty row
empty row
empty row
rpp-1a-5-1 45.56 45.00 2016/10/28
empty row
empty row
empty row
empty row
empty row
empty row
rpp-a4-5-2-utilization 45.93 45.00 2016/10/28

Re: How do I sort my results from my sample data into different columns?

bharpur183 — Thu, 03 Nov 2016 21:17:46 GMT

and also duplicate entries

Re: How do I sort my results from my sample data into different columns?

gokadroid — Thu, 03 Nov 2016 21:29:02 GMT

Empty rows are from the events which do not have these values as part of event log line...the event lines which do not have this data (device, avg and threshold) will show up as blank. So in your initial search query to return events use unique values that will only filter out these lines in which you are interested from where you want to extract these four fields.

OR append these at the end of query
|fillnull value="NULL" | search NOT NULL

That's should solve the blanks.

As for duplicate events since it might be events have different time (but on same day) hence chances are that the data "date, device, average and threshold" appear duplicate but are actually difference events.

Re: How do I sort my results from my sample data into different columns?

bharpur183 — Thu, 03 Nov 2016 21:41:34 GMT

yep. It basically filled the empty rows with the words "NULL" now

Re: How do I sort my results from my sample data into different columns?

gokadroid — Thu, 03 Nov 2016 21:47:01 GMT

did you not use search NOT NULL piece right at the end of the query?? That's should have got rid of all the NULL values.. If it solves and helps then please accept the answer and upvote..

Re: How do I sort my results from my sample data into different columns?

bharpur183 — Thu, 03 Nov 2016 21:54:11 GMT

I used as
|fillnull value="NULL" | search NOT NULL

And with this the empty rows show as :

device dailyAvg threshold date
NULL NULL NULL NULL
NULL NULL NULL NULL
NULL NULL NULL NULL
NULL NULL NULL NULL
NULL NULL NULL NULL
NULL NULL NULL NULL
NULL NULL NULL NULL
NULL NULL NULL NULL
NULL NULL NULL NULL
NULL NULL NULL NULL
NULL NULL NULL NULL

Re: How do I sort my results from my sample data into different columns?

gokadroid — Thu, 03 Nov 2016 22:00:36 GMT

ok, lets tweak that bit then

|fillnull value="NULL" | search device!="NULL" AND dailyAvg!="NULL" AND threshold!="NULL" AND date!="NULL"

Only one can also work but lets use all of them to ensure all are non null.

Re: How do I sort my results from my sample data into different columns?

bharpur183 — Thu, 03 Nov 2016 23:39:43 GMT

works like a charm now.
Thanks a lot for your help with this.
I am new and learning it and this really helped a lot