topic Re: Group By Field in Splunk Search

Group By Field

IRHM73 — Wed, 04 Nov 2015 11:35:10 GMT

Hi, I wonder whether someone may be able to help me please.

I'm running the query below which works fine.

 index=main auditSource="iht" auditType=Questionnaire "detail.version"=1 | rename detail.activity  AS activity, detail.easytouse  AS select, detail.nino AS nino | eval activity=if(activity=="","Not filled",activity) | makemv allowempty=true delim="," activity| mvexpand activity | eval activity = case(activity ==1, "Register", activity==2, "Provide asset information", activity==3, "Provide gift information", activity==4, "Provide debt information", activity==5, "Provide exemption information", activity==6, "Increase Threshold", activity==7, "Check estate report", activity==8, "Declare and submit application", activity==9,"Request clearance", 1=1, activity) | eval select = case(select ==1, "Very easy", select==2,"Easy",select==3,"Neither easy nor difficult",select=4,"Difficult",select==5,"Very difficult",select=="","Not filled") | rex field=nino mode=sed "s/(\S{1})\S{1}/\1X/g" | stats list(activity) as activityList, values(select) as selectList, values(generatedAt) as timeList by nino, generatedAt| fields nino, timeList, activityList, selectList| sort nino, timeList

But what I'm trying to do is now group this by the nino field.

I've tried changing the final two pipes with this:

| stats count by nino | fields nino, timeList, activityList, selectList

But the problem is, is that although I can see the nino values, all the other fields are blank i.e. timeList, activityList, selectList

I just wondered whether someone may be able to tell me where I've gone wrong please.

Many thanks and kind regards

Chris

Re: Group By Field

IRHM73 — Wed, 04 Nov 2015 12:01:59 GMT

All, I just wanted let you know I've fixed this with:

 index=main auditSource="iht-frontend" auditType=Questionnaire "detail.version"=1 | rename detail.activity  AS activity, detail.easytouse  AS select, detail.nino AS nino | eval activity=if(activity=="","Not filled",activity) | makemv allowempty=true delim="," activity| mvexpand activity | eval activity = case(activity ==1, "Register", activity==2, "Provide asset information", activity==3, "Provide gift information", activity==4, "Provide debt information", activity==5, "Provide exemption information", activity==6, "Increase Threshold", activity==7, "Check estate report", activity==8, "Declare and submit application", activity==9,"Request clearance", 1=1, activity) | eval select = case(select ==1, "Very easy", select==2,"Easy",select==3,"Neither easy nor difficult",select=4,"Difficult",select==5,"Very difficult",select=="","Not filled") | eval time=strptime(generatedAt, "%Y-%m-%dT%H:%M:%S")|convert timeformat="%d/%b/%Y %H:%M.%S" ctime(time) | stats list(activity) as activityList, values(select) as selectList, values(time) as timeList by nino, time | fields nino, timeList, activityList, selectList| sort nino, timeList| rex field=nino mode=sed "s/(\S{1})\S{1}/\1X/g" | stats values(activityList) values(selectList) by timeList

This doesn't group by nino as I would have liked but I went for second best and grouped by the "timeList" i.e. "generatedAt" time.

Many thanks and kind regards

Chris

Re: Group By Field

woodcock — Wed, 04 Nov 2015 14:15:41 GMT

This should work:

index=main auditSource="iht" auditType=Questionnaire "detail.version"=1 | rename detail.activity  AS activity, detail.easytouse  AS select, detail.nino AS nino | eval activity=coalesce(activity, "Not filled") | makemv allowempty=true delim="," activity| mvexpand activity | eval activity = case(activity ==1, "Register", activity==2, "Provide asset information", activity==3, "Provide gift information", activity==4, "Provide debt information", activity==5, "Provide exemption information", activity==6, "Increase Threshold", activity==7, "Check estate report", activity==8, "Declare and submit application", activity==9,"Request clearance", true(), activity) | eval select = case(select ==1, "Very easy", select==2,"Easy",select==3,"Neither easy nor difficult",select=4,"Difficult",select==5,"Very difficult",select=="","Not filled") | rex field=nino mode=sed "s/(\S{1})\S{1}/\1X/g" | stats list(activity) as activityList, values(select) as selectList, values(generatedAt) as timeList by nino

Re: Group By Field

woodcock — Wed, 04 Nov 2015 16:10:16 GMT

You should click "Accept" for the best answer.

Re: Group By Field

IRHM73 — Thu, 05 Nov 2015 06:39:59 GMT

Hi @woodcock, thank you for coming back to me with this, but unfortunately it didn't work properly.

Although, this did group by the nino, it took away the correct values for the associated "activityList" and "selectList".

Many thanks and kind regards

Chris