Splunk Search
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Ask a Question
Solved! Jump to solution

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

bharpur183
Explorer
‎11-03-2016 01:22 PM

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
0 Karma
Reply
1 Solution
Solved! Jump to solution
Solution

gokadroid
Motivator
‎11-03-2016 01:58 PM

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

View solution in original post

0 Karma
Reply
Solved! Jump to solution
Solution

gokadroid
Motivator
‎11-03-2016 01:58 PM

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

0 Karma
Reply
Solved! Jump to solution

bharpur183
Explorer
‎11-03-2016 02:08 PM

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

Reply
Solved! Jump to solution

gokadroid
Motivator
‎11-03-2016 02:10 PM

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

0 Karma
Reply
Solved! Jump to solution

bharpur183
Explorer
‎11-03-2016 02:16 PM

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

0 Karma
Reply
Solved! Jump to solution

bharpur183
Explorer
‎11-03-2016 02:17 PM

and also duplicate entries

0 Karma
Reply
Solved! Jump to solution

gokadroid
Motivator
‎11-03-2016 02:29 PM

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.

0 Karma
Reply
Solved! Jump to solution

bharpur183
Explorer
‎11-03-2016 02:41 PM

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

0 Karma
Reply
Solved! Jump to solution

gokadroid
Motivator
‎11-03-2016 02:47 PM

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..

0 Karma
Reply
Solved! Jump to solution

bharpur183
Explorer
‎11-03-2016 02:54 PM

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

0 Karma
Reply
Solved! Jump to solution

gokadroid
Motivator
‎11-03-2016 03:00 PM

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.

0 Karma
Reply
Solved! Jump to solution

bharpur183
Explorer
‎11-03-2016 04:39 PM

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

0 Karma
Reply
Solved! Jump to solution

bharpur183
Explorer
‎11-03-2016 02:09 PM

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

Reply
Get Updates on the Splunk Community!

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...

Introducing the 2024 Splunk MVPs!

We are excited to announce the 2024 cohort of the Splunk MVP program. Splunk MVPs are passionate members of ...

Splunk Custom Visualizations App End of Life

The Splunk Custom Visualizations apps End of Life for SimpleXML will reach end of support on Dec 21, 2024, ...