Splunk Search

How to write a search using a CSV file to display a list of lent materials by user based on status?

erichard
Explorer

Hello,

I have a list of assets like this:

date,material,username,status
01/12/15,"IPad #4654654",eric,lent
01/12/15,"Iphone #4547879",paul,lent
01/15/15,"IPad #4654654",eric,return
01/16/15,"Keyboard #454456",eric,lent
01/17/15,"Nexus 7 #414456",eric,lent

and I would like to extract the list of materials that are actually lent by user. In this case:

eric   Keyboard   #454456    01/16/15
       Nexus 7    #414456    01/17/15
paul   Iphone     #4547879   01/12/15

How I can do this?

Thanks in advance for your help.

0 Karma
1 Solution

javiergn
Super Champion

Try this:

| inputcsv mycsv.csv
| eval statusInteger = if(match(status,"lent"), 1, -1)
| eval date = strptime(date, "%m/%d/%y")
| stats sum(statusInteger) as status, max(date) as date by username, material
| where status != 0 
| fields - status
| fieldformat date=strftime(date, "%m/%d/%y")
| stats list(material) as material, list(date) as date by username

Output:

alt text

View solution in original post

0 Karma

javiergn
Super Champion

Try this:

| inputcsv mycsv.csv
| eval statusInteger = if(match(status,"lent"), 1, -1)
| eval date = strptime(date, "%m/%d/%y")
| stats sum(statusInteger) as status, max(date) as date by username, material
| where status != 0 
| fields - status
| fieldformat date=strftime(date, "%m/%d/%y")
| stats list(material) as material, list(date) as date by username

Output:

alt text

0 Karma

erichard
Explorer

Thanks it's working !

0 Karma

somesoni2
Revered Legend

Assuming you already have the fields extracted from your csv type data, try something like this

your base search status=lent | stats list(material) as material list(date) as date by username
0 Karma

erichard
Explorer

Thanks for your answer, but it's not working as i need, with your answer i have :

eric IPad #4654654
Keyboard #454456
Nexus 7 #414456

The material IPad has beed returned (01/15/15,"IPad #4654654",eric,return) however it shouldn't appear.

0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

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

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...