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
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
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
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
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
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
and also duplicate entries
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.
yep. It basically filled the empty rows with the words "NULL" now
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..
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
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.
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
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