Splunk Search

Assistance on search using appendcols where the subsearch can have a different number of rows

Explorer

I am having issues with a search / Sub-search with appendcols when the number of rows are different.

I have a summary search to collect the license usage data by index into a summary index for the the MBs Usedfor each index I have in the environment.
The data looks like this in the index:

Index=syslog    MBs Used=660336
Index=wineventlog    MBs Used=347123

Now we are trying to get a search that will show us the % difference for the index by day or week
This is to provide us information if our license volume shoots up, we can find the indexes involved in this issue.
The search that uses this summary index is below.

This was working fine until we added a new index. Once the new index was added, each Index2 column was off by 1 row once that index name hit in the list. And this is skewing the Index2 column so the comparison (%difference) is skewed. I expect the same will happen as we decommission indexes also.

Any assistance on how to take this issue into account having a different number of rows for each search would be appreciated.

%difference search - comparing yesterday to 7 days ago

index=index_metric_summary "License Pool"=site1 earliest=@d latest=now
| sort Index | stats median("MB's Used") as yesterday by Index 
 | appendcols 
     [ search index=index_metric_summary "License Pool"=site1 earliest=-7d@d latest=-6d@d | sort Index
     | rename Index as Index2
     | stats median("MB's Used") as previous by Index2] 
 | eval %difference=round(((yesterday-previous)/previous)*100,2)

| table Index Index2 previous yesterday %difference

This is what the output looks like around the point with the new index is listed

Index               Index2            previous       yesterday      %difference
atapower          atapower                 376           400              6.38
cardfile          cardfile                 596           594             -0.34
cbs_logs_new      cbs_logs_qa                0          63890           249778    <<--  wrong compare for %difference
cbs_logs_qa    cbs_logs_stress        1046            7262           94.26
0 Karma
1 Solution

Explorer

With help from to4kawa, here is my final version of this search to find the difference in the index daily license usage to see if any indexes are negatively impacting us.

  • Added to restrict just the indexes that have a delta of more then 20% up or down
  • Had to rename my column for MBs because I named it in the summary index with a space and a hyphen (ugh)

    | multisearch [ search index=index_metric_summary  "License Pool"=pool1 earliest=@d latest=now | eval days="yesterday" ]
    [ search index=index_metric_summary "License Pool"=pool1 earliest=-7d@d latest=-6d@d  | eval days="previous"]
    
    | rename  "MB's Used" AS MB
    | stats median(eval(if(days="yesterday", MB, null()) )) as yesterday, median(eval(if(days="previous", MB, null()) )) as previous by Index
    
    | eval %difference=round(((yesterday-previous)/previous)*100,2)
    
      search %difference > 20  OR  %difference < -20
    | sort - %difference
    
    | table Index previous yesterday %difference
    

View solution in original post

0 Karma

Explorer

With help from to4kawa, here is my final version of this search to find the difference in the index daily license usage to see if any indexes are negatively impacting us.

  • Added to restrict just the indexes that have a delta of more then 20% up or down
  • Had to rename my column for MBs because I named it in the summary index with a space and a hyphen (ugh)

    | multisearch [ search index=index_metric_summary  "License Pool"=pool1 earliest=@d latest=now | eval days="yesterday" ]
    [ search index=index_metric_summary "License Pool"=pool1 earliest=-7d@d latest=-6d@d  | eval days="previous"]
    
    | rename  "MB's Used" AS MB
    | stats median(eval(if(days="yesterday", MB, null()) )) as yesterday, median(eval(if(days="previous", MB, null()) )) as previous by Index
    
    | eval %difference=round(((yesterday-previous)/previous)*100,2)
    
      search %difference > 20  OR  %difference < -20
    | sort - %difference
    
    | table Index previous yesterday %difference
    

View solution in original post

0 Karma

Explorer

Two mistakes in the above - sorry

%difference search - comparing yesterday to 7 days ago

index=index_metric_summary "License Pool"=site1 earliest=@d latest=now
 | sort Index | stats median("MB's Used") as yesterday by Index 
  | appendcols 
      [ search index=index_metric_summary "License Pool"=site1 earliest=-7d@d latest=-6d@d | sort Index
      | rename Index as Index2
      | stats median("MB's Used") as previous by Index2] 
  | eval %difference=round(((yesterday-previous)/previous)*100,2)
| table Index Index2 previous yesterday %difference

And the output report for the "skewed" line should be:

cbs_logs_new      cbs_logs_qa           63890             0           -249778    <<--  wrong compare for %difference
0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!