Solved

How Do I Prevent Empty Query Results From Displaying In My Results List?

Posted on 2015-02-08
11
55 Views
Last Modified: 2015-02-09
My script cycles through multiple environments and if a user is found, it prints out their ID. The desired output is as follows:

environment1
<user_id>

Open in new window


However, my script is currently working to print out every environment even if there isn't a user id, like follows:

environment1
<user_id>
environment2
environment3
environment4
<user_id>

Open in new window


I would like the code to print out "NO USER FOUND" if the user_id does not exist, rather than excluding the environment altogether.

The code below is what is being utilized:

#log it all
{

  echo "environment"
  sshpass -p $ldappw ssh $ldapuser@12.345.67.89 'mysql --skip-column-names -hhost -u user -ppassword database -e \
"select user_id from users where first like '"'%${first_name}%' and last like '%${last_name}%';"'"' 


} > $log

Open in new window


Any help would be much appreciated for this issue. Unfortunately I'm just beginning to learn Linux and command line tools and am not quite proficient enough to know how to handle this problem.
0
Comment
Question by:TampaJay
  • 6
  • 5
11 Comments
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 40597441
This is a case for "CASE".

Something like this:

select CASE WHEN first like '"'%${first_name}%' and last like '%${last_name}%' and user_id is not NULL THEN user_id ELSE 'NO USER FOUND' END from users ;"'

You will have to experiment a bit (particularly with the single and double quotation marks) because right now I do not have a MySQL database at hand for testing.

Additionally, please see the MySQL docs for "CASE":
http://dev.mysql.com/doc/refman/5.5/en/control-flow-functions.html#operator_case
0
 

Author Comment

by:TampaJay
ID: 40597457
Unfortunately when running that query it will go through every record of the table printing output.
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 40597464
Parentheses?

select CASE WHEN ( first like '"'%${first_name}%' and last like '%${last_name}%' and user_id is not NULL ) THEN user_id ELSE 'NO USER FOUND' END from users ;"'

Sorry, I come from Oracle where the syntax is slightly different.
0
 

Author Comment

by:TampaJay
ID: 40597468
No, that brings up the same results.
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 40597482
Are there cases where first_name and last_name match but userid is empty?

select CASE WHEN ( first like '"'%${first_name}%' and last like '%${last_name}%' and user_id is not NULL ) THEN user_id WHEN  ( first like '"'%${first_name}%' and last like '%${last_name}%' and user_id is NULL ) THEN 'NO USER FOUND'  ELSE NULL END from users ;"'

I fear I won't be able to provide more help unless it's made clear what values "first_name" and "last_name" can take in the script as well as "first" and "last"  in the database and if "user_id" is indeed NULL or just "empty" or "space" if "the user_id does not exist".
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:TampaJay
ID: 40597579
first_name variable will be a name like "Jay" and last_name variable will be a name like "lefler"

The Database fields first and last are also first and last names.

There might be multiple accounts with the jay/lefler combination. User ID will never be a NULL value
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 40597670
It's hard to understand how it should be possible that a "user_id does not exist" (as stated in your question) when on the other hand a "User ID will never be a NULL value" (as stated in your last comment).

Be that as it may, let's make it simple (yet I fear this won't make it any better):

#log it all
{
UID=$(sshpass -p $ldappw ssh $ldapuser@12.345.67.89 'mysql --skip-column-names -hhost -u user -ppassword database -e \
"select user_id from users where first like '"'%${first_name}%' and last like '%${last_name}%';"'"')
if [[ ! -z $UID ]]; then
   echo "environment"
   echo $UID
     else
        echo "NO USER FOUND"
fi 
} > $log

Open in new window


I'm afraid you'll have to post the whole script so we might be able to see what's it all about.
0
 

Author Comment

by:TampaJay
ID: 40597711
User ids are unique but if there is no combination that matches first and last name their wont be an associated user_id.
0
 
LVL 68

Accepted Solution

by:
woolmilkporc earned 500 total points
ID: 40597891
So my last suggestion above should be the way to go.

Here it is again:

#log it all
{
UID=$(sshpass -p $ldappw ssh $ldapuser@12.345.67.89 'mysql --skip-column-names -hhost -u user -ppassword database -e \
"select user_id from users where first like '"'%${first_name}%' and last like '%${last_name}%';"'"')
if [[ ! -z $UID ]]; then
   echo "environment"
   echo $UID
     else
        echo "NO USER FOUND"
fi 
} > $log

Open in new window

0
 

Author Comment

by:TampaJay
ID: 40598475
woolmilkporc,

What if there are multiple user ID's for a first/last name combination? While I have tested your script and it appears to work for finding a single user, will it print multiple user id's if they are found?
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 40598646
As you can see I used your query. When this query returns multiple users then my script will display them.

Currently all IDs will appear in one single line. To show each ID on a line of its own change

echo $UID

to

echo "$UID"
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now