Splunk Search

Search Where one value present and not another

DanielFordWA
Contributor

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

Tags (2)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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"

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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

DanielFordWA
Contributor

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?

0 Karma

DanielFordWA
Contributor

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.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...