topic Re: How to sort data on the basis of each field using splunk SQL. in Knowledge Management

How to sort data on the basis of each field using splunk SQL.

disha — Mon, 24 Sep 2012 22:51:33 GMT

CustomerID             Time              CrashCount        EventDescription     
20:12:13:14:0A:45   09/19/2012 20:12:13.1   07       Poor IB SNR                
20:12:13:14:0A:42   09/19/2012 20:12:13.1   04           HDD FULL           
20:12:13:14:0A:45   09/19/2012 20:12:13.1   07       Poor IB SNR                       20:12:13:14:0A:45    09/19/2012 20:12:13.1   02           HDD FULL               
20:12:13:14:0A:41   09/19/2012 20:12:13.1   05       Poor IB SNR

I have a data of the type shown above. The search that is generating this data is:

 <param name="search">sourcetype="$sourcetype$" 
    | spath path="EID" output=EventID 
    | spath path="CT" output=Critical 
    | spath path="SID" output=StbID 
    | search EventID="$EventID$" 
    | search Critical="$Critical$" 
    | search StbID="*" 
    | fields - _raw 
    | fields +  StbID, _time, EventID 
    | join type=inner EventID 
      [ SEARCH sourcetype="jsonformat" 
      | spath path="EID" output=EventID 
      | spath path="EventDescription" output=EventDescription 
      | FIELDS EventID, EventDescription ] 
    | rename _time AS "Time", StbID AS "CustomerID" 
    | convert ctime(Time) 
    | search EventDescription="VMS*" 
    | join type=inner EventID 
       [ SEARCH sourcetype="jsonxmlall" 
       | spath path="EID" output=EventID
       | spath path="CNT" output=CrashCount 
       | spath path="LPD" output=LeakPerDay 
       | spath path="IO" output=IOwait 
       | spath path="SNR" output=SNRValue 
       | spath path="TMP" output=HardDiskTemp 
       | fields EventID, CrashCount, LeakPerDay, IOwait, SNRValue, HardDiskTemp ]
  </param>

I need to sort this search on the basis of each column. Can you help me how to achieve that as I have tried so many things but not able to do that.

Thanks in advance.

Re: How to sort data on the basis of each field using splunk SQL.

Ayn — Mon, 24 Sep 2012 23:02:41 GMT

First of all, Splunk does not use SQL. It has its own search language.

Second, could you explain more clearly what you'd like to do? You have your table consisting of various columns, what's stopping you from sorting?

Re: How to sort data on the basis of each field using splunk SQL.

disha — Mon, 24 Sep 2012 23:11:05 GMT

Yes I understand Splunk has its own search engine. I am trying to sort the data of each column but when I am adding SORT -fieldname, it is sorting the column names not the data as
"custid time event count" is getting sortred as "count custid event time" not the data of these fields.

Re: How to sort data on the basis of each field using splunk SQL.

Ayn — Tue, 25 Sep 2012 06:05:21 GMT

If you have the table you showed at the top, just adding a | sort - field should do what you want. Additionally you could just click directly in the table headers for sorting.

Re: How to sort data on the basis of each field using splunk SQL.

disha — Tue, 25 Sep 2012 16:58:20 GMT

Yes, I have done that but the funny thing is happening as it is sorting the field data but as well as it is sorting the column names also I wrote above like "custid time event count" is getting sortred as "count custid event time". Can you tell me why it is happening or how we can fix that. This simplest thing is getting stuck from two days..:(

Re: How to sort data on the basis of each field using splunk SQL.

melting — Tue, 25 Sep 2012 17:04:16 GMT

@disha,

Perhaps you could show sample output that is in your table. Thanks.

Re: How to sort data on the basis of each field using splunk SQL.

disha — Tue, 25 Sep 2012 17:54:23 GMT

CustomerID Time EventID CrashCount EventDescription HardDiskTemp IOwait LeakPerDay SNRValue
1 10:12:13:14:0A:46 09/19/2012 19:30:40 09 VMS: HDD Full

2 10:12:13:14:0A:46 09/19/2012 19:30:40 09 VMS: HDD Full

after putting | sort EventID ..The output is
CrashCount CustomerID EventDescription EventID HardDiskTemp IOwait LeakPerDay SNRValue Time
1 2 10:12:13:14:0A:20 VMS: Stack Crash 02 09/19/2012 19:30:05
2 2 10:12:13:14:0A:20 VMS: Stack Crash 03
As you can see that output is sorted out on the basis of EventID but the field names are also rearranged in sorted order which is wrong.

Re: How to sort data on the basis of each field using splunk SQL.

melting — Tue, 25 Sep 2012 18:04:54 GMT

I am not sure why the field order is also getting sorted. Perhaps running the sort then the table command will put things right.

... | sort EventID | Table CustomerID Time EventID ....

Re: How to sort data on the basis of each field using splunk SQL.

disha — Tue, 25 Sep 2012 20:21:41 GMT

This is working. Thanks