I am trying to get the data into a chart from an index were a user may answer a question daily.
This is what I need to produce:
sun | mon | tue | wed | thu | fri | sat | |
bob@email.com | - | NO | NO | NO | Yes | - | - |
aaron@email.com | Yes | - | - | NO | - | - | - |
chuck@email.com | NO | NO | NO | Yes | NO | - | Yes |
This i s what I am currently getting:
sun | mon | tue | wed | thu | fri | sat | |
bob@email.com | - | - | - | - | Yes | - | - |
bob@email.com | - | - | - | NO | - | - | - |
bob@email.com | - | - | NO | - | - | - | - |
bob@email.com | - | NO | - | - | - | - | - |
aaron@email.com | - | - | - | NO | - | - | - |
aaron@email.com | - | - | - | NO | - | - | - |
aaron@email.com | Yes | - | - | - | - | - | - |
chuck@email.com | - | - | NO | - | - | - | - |
chuck@email.com | - | - | - | - | NO | - | - |
chuck@email.com | - | - | - | Yes | - | - | - |
chuck@email.com | NO | - | - | - | - | - | - |
chuck@email.com | - | NO | - | - | - | - | - |
chuck@email.com | - | - | - | - | - | - | Yes |
Here is what I have so far, but I am stuck and not to sure what process I should use:
index=someindex
| eval qualdayOfWeek=strftime(_time, "%a")
| rex field=_raw "is\s(?<status>(NO|YES))\sto"
| eval sun=if(qualdayOfWeek="Sun", $status$, "-")
| eval mon=if(qualdayOfWeek="Mon", $status$, "-")
| eval tue=if(qualdayOfWeek="Tue", $status$, "-")
| eval wed=if(qualdayOfWeek="Wed", $status$, "-")
| eval thu=if(qualdayOfWeek="Thu", $status$, "-")
| eval fri=if(qualdayOfWeek="Fri", $status$, "-")
| eval sat=if(qualdayOfWeek="Sat", $status$, "-")
| table email, sun, mon, tue, wed, thu, fri, sat
Hi @CSULeigh
You want to use stats to group by email. Give this a try...
index=someindex
| eval qualdayOfWeek=strftime(_time, "%a")
| rex field=_raw "is\s(?<status>(NO|YES))\sto"
| eval {qualdayOfWeek}=case(
qualdayOfWeek="Sun", $status$
,qualdayOfWeek="Mon", $status$
,qualdayOfWeek="Tue", $status$
,qualdayOfWeek="Wed", $status$
,qualdayOfWeek="Thu", $status$
,qualdayOfWeek="Fri", $status$
,qualdayOfWeek="Sat", $status$
,true(), "-" )
| table email, Sun, Mon, Tue, Wed, Thu, Fri, Sat
| stats values(*) AS * BY email
I've also shown how to use an eval "case" statement with a field name substitution i.e. {field} - not that it really matters and could use eval. You could also "rename" the colums to lowercase if you prefer (| rename Sun AS sun Mon AS mon Tue AS ...).
Also note that you if you time period goes over multiple weeks that you may have more than one result per week day column.
Hope this helps.
index=someindex
| eval qualdayOfWeek=strftime(_time, "%a")
| rex field=_raw "is\s(?<status>(NO|YES))\sto"
| eval sun=if(qualdayOfWeek="Sun", $status$, "-")
| eval mon=if(qualdayOfWeek="Mon", $status$, "-")
| eval tue=if(qualdayOfWeek="Tue", $status$, "-")
| eval wed=if(qualdayOfWeek="Wed", $status$, "-")
| eval thu=if(qualdayOfWeek="Thu", $status$, "-")
| eval fri=if(qualdayOfWeek="Fri", $status$, "-")
| eval sat=if(qualdayOfWeek="Sat", $status$, "-")
| stats values(status) as status by email, sun, mon, tue, wed, thu, fri, sat
Hi @CSULeigh
You want to use stats to group by email. Give this a try...
index=someindex
| eval qualdayOfWeek=strftime(_time, "%a")
| rex field=_raw "is\s(?<status>(NO|YES))\sto"
| eval {qualdayOfWeek}=case(
qualdayOfWeek="Sun", $status$
,qualdayOfWeek="Mon", $status$
,qualdayOfWeek="Tue", $status$
,qualdayOfWeek="Wed", $status$
,qualdayOfWeek="Thu", $status$
,qualdayOfWeek="Fri", $status$
,qualdayOfWeek="Sat", $status$
,true(), "-" )
| table email, Sun, Mon, Tue, Wed, Thu, Fri, Sat
| stats values(*) AS * BY email
I've also shown how to use an eval "case" statement with a field name substitution i.e. {field} - not that it really matters and could use eval. You could also "rename" the colums to lowercase if you prefer (| rename Sun AS sun Mon AS mon Tue AS ...).
Also note that you if you time period goes over multiple weeks that you may have more than one result per week day column.
Hope this helps.