Search Where one value present and not another

[DanielFordWA]

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

[somesoni2 · Accepted solution]

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"

[somesoni2]

just add "|stats count(eval(DocAction="View")) as readCount by DocTitle, DocType, publicationId, docid,DocAut | where readCount < 5"

[DanielFordWA]

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?

[DanielFordWA]

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.