Splunk Search

Group By Field

IRHM73
Motivator

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

0 Karma
1 Solution

IRHM73
Motivator

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

View solution in original post

0 Karma

woodcock
Esteemed Legend

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

IRHM73
Motivator

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

0 Karma

IRHM73
Motivator

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

0 Karma

woodcock
Esteemed Legend

You should click "Accept" for the best answer.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...