Hi, I use the below query to find published documents and the actions taken against them.
sourcetype="iis-2" | extract auto=true | search DocAction NOT DocType="*Research*" NOT [| inputlookup testers_lookup | fields cs_username] NOT cs_username="*HTML*" | fillnull value="-" DocType DocAction publicationId docid DocAut | stats count by DocTitle DocType DocAction publicationId docid DocAut | eval DocType=urldecode(DocType) | eval DocTitle=urldecode(DocTitle) | eval publicationId=urldecode(publicationId)
In the field DocAction I have the values =PubInt and =PubExt for when a document is published.
Also in the DocAction field I have the value =View for when a document is viewed.
The question I want to answer is "What documents are published but do not get viewed in a given time period?"
Something like = Return all DocTitle where DocAction ="Pub" and there are no hits against the same DocTitle where DocAction="View"
Can anyone help?
Thanks,
Dan
Try following
sourcetype="iis-2" | extract auto=true | search DocAction NOT DocType="*Research*" NOT [| inputlookup testers_lookup | fields cs_username] NOT cs_username="*HTML*" | fillnull value="-" DocType DocAction publicationId docid DocAut | stats count by DocTitle DocType DocAction publicationId docid DocAut | eval DocType=urldecode(DocType) | eval DocTitle=urldecode(DocTitle) | eval publicationId=urldecode(publicationId)
*Add this to your search**
| stats values(DocAction) as DocAction by DocTitle,DocType,publicationId,docid,DocAut
| eval isPubNotViewed=CASE((isnotnull(mvfind(DocAction,"PubInt")) OR isnotnull(mvfind(DocAction,"PubExt"))) AND isnull(mvfind(DocAction,"View")),"Yes",1=1,"No")
| where isPubNotViewed="Yes"
Try following
sourcetype="iis-2" | extract auto=true | search DocAction NOT DocType="*Research*" NOT [| inputlookup testers_lookup | fields cs_username] NOT cs_username="*HTML*" | fillnull value="-" DocType DocAction publicationId docid DocAut | stats count by DocTitle DocType DocAction publicationId docid DocAut | eval DocType=urldecode(DocType) | eval DocTitle=urldecode(DocTitle) | eval publicationId=urldecode(publicationId)
*Add this to your search**
| stats values(DocAction) as DocAction by DocTitle,DocType,publicationId,docid,DocAut
| eval isPubNotViewed=CASE((isnotnull(mvfind(DocAction,"PubInt")) OR isnotnull(mvfind(DocAction,"PubExt"))) AND isnull(mvfind(DocAction,"View")),"Yes",1=1,"No")
| where isPubNotViewed="Yes"
just add "|stats count(eval(DocAction="View")) as readCount by DocTitle, DocType, publicationId, docid,DocAut | where readCount < 5"
Quick follow up question, how would I change the above so that I can see all documents that were only viewed 1-4 times, so the DocAction="View" has less than 5 hits?
This works great, I had to remove the PublicationID and docid as this only gets added after a document is published, this seemed to cause the query not to run correctly.