Knowledge Management

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

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

Tags (1)
0 Karma
1 Solution

melting
Splunk Employee
Splunk Employee

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

View solution in original post

0 Karma

melting
Splunk Employee
Splunk Employee

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

disha
Contributor

This is working. Thanks

0 Karma

disha
Contributor

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.

0 Karma

melting
Splunk Employee
Splunk Employee

@disha,

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

0 Karma

disha
Contributor

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

0 Karma

Ayn
Legend

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.

disha
Contributor

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.

0 Karma

Ayn
Legend

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?

0 Karma
*NEW* Splunk Love Promo!
Snag a $25 Visa Gift Card for Giving Your Review!

It's another Splunk Love Special! For a limited time, you can review one of our select Splunk products through Gartner Peer Insights and receive a $25 Visa gift card!

Review:





Or Learn More in Our Blog >>